Motivation

Have you ever received an error message that left you scratching your head?
Of course you have. Matter of fact I for one cannot recall any software product that ever outright impressed me with the quality of their error messages.
DB2's messages, overall tend not to be too bad compared to what I have seen with competitors when it comes down to regular SQL.
But things get really interesting when you add SQL PL (or PL/SQL) to the mix.

"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:

• Correct the statement that is attempting to create the array value
• Reduce the number of elements in the array or the sizes of some of the elements
• Increase the value of APPLHEAPSZ or APPL_MEMORY if they are not set to AUTOMATIC
• Increase the amount of physical memory available to the system.

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.
You can see the SQLCA in CLP using the -a option when you start it or by running:

UPDATE COMMAND OPTIONS USING A ON;

Let's try to provoke a runtime error:

VALUES 1000000 * 1000000;

1
-----------

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -802   sqlerrml: 0
 sqlerrmc:
 sqlerrp : SQLRI0AD
 sqlerrd : (1) -2146041840      (2) 16               (3) 0
           (4) 0                (5) -500             (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate: 22003

Explaining the various regular elements of a SQLCA could cover another post.
For today I shall only refer you to the docs SQLCA (SQL communications area).

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)
BEGIN
SET a := 1000000 * 100000;
END
@

--#SET TERMINATOR ;

SQL0104N  An unexpected token ":=" was found following "a INT)  BEGIN  SET a".
Expected tokens may include:  "=".  LINE NUMBER=5.  SQLSTATE=42601

Let's use

UPDATE COMMAND OPTIONS USING A ON;

and rerunning the statement to reveal the origin of this information:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -104   sqlerrml: 25
 sqlerrmc: :=ÿa INT)
BEGIN
SET aÿ=
 sqlerrp : SQLNP01F
 sqlerrd : (1) -2145779603      (2) 0                (3) 3
           (4) 0                (5) -705             (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate: 42601

The ERRD(3) field contains the value 3 which indicates an approximation of the syntax error.
This has been working since many years, but what happens for runtime errors?

When we fix the syntax error:

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE  P(OUT a INT)
BEGIN
VALUES 1000000 * 100000 INTO a;
END
@

--#SET TERMINATOR ;

and call the procedure we get:

CALL p(?);

SQL0802N  Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003

SQLCA Information

 sqlcaid : SQLCAM    sqlcabc: 136   sqlcode: -802   sqlerrml: 0
 sqlerrmc:
 sqlerrp : SQLRI0AD
 sqlerrd : (1) -1               (2) 16               (3) 3
           (4) 1031059920       (5) -500             (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate: 22003
 

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.
The ERRD(4) corresponds to the SYSCAT.ROUTINES.LIB_ID column and the "M" tells it that this is the case.
 

SELECT ROUTINESCHEMA, ROUTINENAME FROM SYSCAT.ROUTINES WHERE LIB_ID = 1031059920;

ROUTINENAME ROUTINESCHEMA
SRIELAU     P

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".
If  the statement is "too simple" then the procedural compiler may optimize out the line-number increments.

You still get the routine of course and you will get a good idea where the error is from if not the exact line.

 

Conclusion

It 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.

Tags:  error source message line number sqlstate sqlca origin sql sqlcode runtime