Db2 human readable error formatter

Question

Almost all clients for DB2 return errors in format

Error: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=103, COLNO=0, DRIVER=3.57.82
SQLState:  23502
ErrorCode: -407

Than I should google for SQLCODE, than for SQLState and then try to understand meaning of SQLERRMC. It is time consuming... I would like to know is there tool which parse such format and return human friendly formatted error, such as "You are tring to insert null in position 2 where only non null values posible..."

Answer 1

To convert 'SQLERRMC=TBSPACEID=x, TABLEID=y, COLNO=z' to schema, table and column names, at a SQL prompt:

SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME
FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C
WHERE T.TBSPACEID = x
AND T.TABLEID = y
AND C.COLNO = z
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME

Credit: I found this at http://www.dbforums.com/db2/1655517-how-find-table-tbspaceid-2-tableid-1583-a.html

Answer 2

The DB2 version 9 message reference is over here. Although the messages are pretty consistent from version to version you may want to find the message reference for your specific version.

In your case, SQL 407 is here.

Additionally, as the message reference states, if you want to automatically translate DB2 error messages, you can do the following:

To invoke message help, open the command line processor and enter:

? XXXnnnnn

where XXX represents a valid message prefix and nnnnn represents a valid message number.

The message text associated with a given SQLSTATE value can be retrieved by issuing:

? nnnnn

or

? nn

where nnnnn is a five digit SQLSTATE (alphanumeric) and nn is the two digit SQLSTATE class >code (first two digits of the SQLSTATE value).

In your case above you could enter ?SQL0407 at the CLP prompt and you should get a description of the error message.

Answer 3

Also, check for IBM utility QW which may help you to get the description and possible resolutions handy.