Now, where did that come from? Getting more out of your error messages.
Visits (10787)
Motivation
Have you ever received an error message that left you scratching your head? "My" product OpenPages is chock full of tens of thousands of lines of PL/SQL and recently the following error message crossed my desk: SQL20442N There is not enough storage to represent the array value. The full description is: The amount of memory required to represent an array value is larger than the maximum allowed for the system. And as user response the following is proposed: Possible solutions may include:
Sound advice, but when I get a runtime message from a stored procedure which calls other stored procedures, say, seven levels deep, just how do I even know which statement I'm dealing with? Unless I start peppering the code with trace points the only way to debug such a message seemed to be stepping through the DataStudio Debugger and see where it blows up. Luckily there is another way.
Secrets of the SQLCA
The SQLCA is an SQL Standard structure which is used to shuttle primarily error messages across the wire. UPDATE COMMAND OPTIONS USING A ON; Let's try to provoke a runtime error: VALUES 1000000 * 1000000;
1
Explaining the various regular elements of a SQLCA could cover another post. Things get interesting when we start with procedural logic (or try to create a view for that matter): --#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE P(OUT a INT) --#SET TERMINATOR ;
SQL0104N An unexpected token ":=" was found following "a INT) BEGIN SET a". Let's use UPDATE COMMAND OPTIONS USING A ON; and rerunning the statement to reveal the origin of this information:
SQLCA Information
The ERRD(3) field contains the value 3 which indicates an approximation of the syntax error. When we fix the syntax error: --#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE P(OUT a INT) --#SET TERMINATOR ; and call the procedure we get: CALL p(?); SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003
SQLCA Information Errd(3) has been set again! It's just that CLP didn't know to pick it up. This a feature that was added to the DB2 engine in DB2 10.5 and it simply hasn't made it to the pretty printer of the error message in CLP yet!
There is more information here though: ERRD(4) is not 0 anymore and the SQLCA eyecatcher acquired an "M" at the end. SELECT ROUTINESCHEMA, ROUTINENAME FROM SYSCAT.ROUTINES WHERE LIB_ID = 1031059920;
ROUTINENAME ROUTINESCHEMA So now we know that the -802 originate on or after line 3 of SRIELAU.P.
Note that I replaced in the above example the SET with a VALUES INTO, and I used the language "on or after line".
ConclusionIt is possible, with bit of digging, to get more information out of runtime error messages from DB2 including the routine name and line number where the error occurred. I'm eagerly awaiting the day when I can refresh this post to report that CLP and CLPPlus return this information by default. |