Re: Error message Text for SQL

On 11-Nov-2013 13:27 -0800, Jon Paris wrote:

Well it was good while it lasted.

The MESSAGE_TEXT appears to only include the level 1 text. The
actual cause of the error (which is in level 2) is not apparently
supplied.

The 70-byte limit of SQLERRMC [short name: SQLERM] to contain the "message replacement text associated with the SQLCODE" is far too restrictive.

However - by the "highly scientific" method of looking at all the
other diagnostic values I discovered that _part_ of the second level
text appears to be placed in DB2_TOKEN_STRING. No way of knowing if
this is always true - but in this instance it has what I need - which
is the reason why an XML validation failed.

That /token string/ of x'FF' separated values, in VARCHAR(1000), will contain the values for the tokens that fit within that storage. They are the /replacement variable/ values for the corresponding SQL#### message, much like SQLERRMC, but they are in character-string form rather than the internal representation that the message-data of SNDPGMMSG\QMHSNDPM would want.

Thus the ability to get what SQLERRMC can not provide, by using the GET DIAGNOSTICS via the DB2_ORDINAL_TOKEN_n values or from the DB2_TOKEN_STRING is not very desirable, but possible given the known layout for the message-data [the Message data fields formats (FMT) parameter of ADDMSGD]. Obtained via the former, esp. if the latter is too small. Not so easy, done dynamically.

Sadly it is still not as good as the job log info. It only includes
the latter part of the second level text, not the really useful
stuff like the position of the error in the source.

For the position of a syntax error within a source statement, the SQLERRD(5) [short name: SQLER5] "may contain the position of a syntax error."

So I'm better off than I was but ... why is it that every time I
start to like SQL it throws something like this at me.

That the DB2 for i SQL does not provide a nicer /integrated/ form of error handling and messaging is frustrating. Too much effort expended to avoid trouble with the standards instead of making an effort to provide things irrespective of the standards. For example... why not provide an effect MESSAGE_DATA condition-information-item that can be passed directly to the QMHSNDPM?