How to get descriptive error messages from DB2?

Question

When I call a SQL statement via JDBC on the DB2 and the statement fails, I catch an SQLException with the following message text:

com.ibm.db2.jcc.a.nn: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703,
    SQLERRMC=O.METADATENSATZ, DRIVER=3.52.95

I tried an automatic translation of the message according to the error list published by IBM, but there are placeholders inside the messages referencing other elements of the exception.

While looking for these elements inside the exception, I found the DB2ExceptionFormatter and tried to use it to access the missing elements.

But here I stopped, because the DB2ExceptionFormatter gave me a clue:

Error occurred while trying to obtain message text from server. Only message tokens are available.

So my question is: What do I have to configure to get the correct messages from the DB2 server?

If I can get a human readable message from the server, I could use it directly and wouldn't have to translate it by myself.

Answer 1

I found a hint here:

retrieveMessagesFromServerOnGetMessage:

Specifies whether JDBC SQLException.getMessage calls cause the IBM DB2 Driver for JDBC and SQLJ to invoke a DB2 for z/OS stored procedure that retrieves the message text for the error. The data type of this property is boolean. The default is false, which means that the full message text is not returned to the client.

---

I tried this, but the output of sqlException.getMessage() only changed to

O.METADATENSATZ

without any surrounding message text.

---

Now I found this:

Before you can use certain functions of the IBM® Data Server Driver for JDBC and SQLJ on a DB2® for z/OS® subsystem, you need to install a set of stored procedures and create a set of tables.

...

WLM must be installed on the z/OS system.

WLM is the DB2 Workload Manager that isn't available for the DB2 Express edition I'm using for development :-(

Answer 2

I'm not sure what message reference you're looking at above (it seems to be iSeries?) but you're better off going to the DB2 Message Reference over here.

Looking up SQL0206 gets us this page, with the following information:

name is not valid in the context where it is used.

The SQLERRMC is "O.METADATENSATZ" so I would take this to mean you have sent an SQL statement to DB2 and it is reporting that "O.METADATENSATZ" is not valid...either the column doesn't exist or the table "O" does not exist.

As the message reference states, if you want to automatically translate DB2 error messages:

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, typing "? SQL0206" in a DB2 CLP will get you the error message.