We've been investigating the new SQL support for XML in V7 and finally got it working but ... we were being frustrated by the fact that when the XML validator detected an error there was no obvious way to know where and what the problem was. This information was available in the job log in a useful form, but we really didn't want to have to retrieve messages from the log.
We had used the SQL operation GET DIAGNOSTICS for some other specific bits of information but we hadn't realized just how useful it could be. It turns out it's possible to programatically retrieve the information we needed. As a quick study of the manual will show you, there are literally dozens of different pieces of information you can retrieve. Some of them are even useful! Once we realized this, the biggest problem was trying to understand the information in the documentation and lack of useful examples. Not sure if the manuals these days are written in SQLese, or Unixese, or what, but we find them less than obvious in many cases.
Eventually, with the help of some friends via email and on the midrange.com RPG list, we established that the values we needed were in the DB2_ORDINAL_TOKEN_ series of variables. Each of these in turn contain the substitution text for the message in the job log. In our case we were looking at message SQ20399 and that only has two substitution variables. DB2_ORDINAL_TOKEN_1, which contains the position of the error within the XML document, and DB2_ORDINAL_TOKEN_2 which contains details of the actual error (e.g., cvc-datatype-valid.1.2: The value "X" is not a valid value for the "int" datatype.). If we want to retrieve the actual error message we can do that via the aptly named MESSAGE_TEXT variable but most of the time we already know that from the SQLSTATE or SQLCODE values.
The final part of the puzzle is the actual SQL request to get information. For our purposes here it would look like this:
GET DIAGNOSTICS EXCEPTION 1
:errorText = MESSAGE_TEXT,
:errorToken1 = DB2_ORDINAL_TOKEN_1,
:errorToken2 = DB2_ORDINAL_TOKEN_2;
Note that you can combine multiple requests into a single statement. Having multiple "=" signs in a statement looks really strange, but that's SQL syntax for you.
Couple of final points here:
- The word EXCEPTION is identified as CONDITION in the current manual but the two are equivalent and we find EXCEPTION more meaningful. The "1" simply means the last executed SQL statement.
- While the documentation indicates a varying length field of 32,672 characters for the receiver variables, that is not a fixed requirement. You can size them as needed.
- You don't have to use DSPMSGD to determine the number of substitution variables. The value DB2_TOKEN_COUNT can be retrieved if you need to know how many there are.
As we noted earlier dozens of values are available in the diagnostics. We intend to dig into it a little more and will report back on any that we find.