urn:lsid:ibm.com:blogs:entries-c3f1cfe8-cfc4-4845-b1aa-c79f17033d85SQL Tips for DB2 LUW - Tags - sqlca Interesting SQL solutions for DB2 LUW as well as hints on how to move Oracle applications to DB203012017-10-04T09:42:05-04:00IBM Connections - Blogsurn:lsid:ibm.com:blogs:entry-bcede7bc-5c11-4ecd-ab71-de00bb0ee52bNow, where did that come from? Getting more out of your error messages.SergeRielau120000D76FactivefalseSergeRielau120000D76FactivefalseComment Entriesapplication/atom+xml;type=entryLikestrue2014-01-09T15:38:26-05:002014-01-09T15:45:57-05:00<h1 dir="ltr">
Motivation</h1>
<p dir="ltr">
Have you ever received an error message that left you scratching your head?<br />
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.<br />
DB2&#39;s messages, overall tend not to be too bad compared to what I have seen with competitors when it comes down to regular SQL.<br />
But things get really interesting when you add SQL PL (or PL/SQL) to the mix.</p>
<p dir="ltr">
&quot;My&quot; product OpenPages is chock full of tens of thousands of lines of PL/SQL and recently the following error message crossed my desk:</p>
<p dir="ltr" style="margin-left: 40px;">
<strong>SQL20442N&nbsp; There is not enough storage to represent the array value.</strong></p>
<p dir="ltr">
The full description is:</p>
<p dir="ltr" style="margin-left: 40px;">
The amount of memory required to represent an array value is larger than the maximum allowed for the system.</p>
<p dir="ltr">
And as user response the following is proposed:</p>
<p dir="ltr">
Possible solutions may include:</p>
<ul dir="ltr">
<li>
Correct the statement that is attempting to create the array value</li>
<li>
Reduce the number of elements in the array or the sizes of some of&nbsp;the elements</li>
<li>
Increase the value of APPLHEAPSZ or APPL_MEMORY if they are not set&nbsp;to AUTOMATIC</li>
<li>
Increase the amount of physical memory available to the system.</li>
</ul>
<p dir="ltr">
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&#39;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 <a href="http://www.ibm.com/developerworks/downloads/im/data/">DataStudio</a> Debugger and see where it blows up.</p>
<p dir="ltr">
Luckily there is another way.</p>
<p dir="ltr">
&nbsp;</p>
<h1 dir="ltr">
Secrets of the SQLCA</h1>
<p dir="ltr">
The SQLCA is an SQL Standard structure which is used to shuttle primarily error messages across the wire.<br />
You can see the SQLCA in CLP using the -a option when you start it or by running:</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">UPDATE COMMAND OPTIONS USING A ON;</span></p>
<p dir="ltr">
Let&#39;s try to provoke a runtime error:</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">VALUES 1000000 * 1000000;</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">1<br />
-----------<br />
<br />
SQLCA Information<br />
<br />
&nbsp;sqlcaid : SQLCA&nbsp;&nbsp;&nbsp;&nbsp; sqlcabc: 136&nbsp;&nbsp; sqlcode: -802&nbsp;&nbsp; sqlerrml: 0<br />
&nbsp;sqlerrmc:<br />
&nbsp;sqlerrp : SQLRI0AD<br />
&nbsp;sqlerrd : (1) -2146041840&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2) 16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3) 0<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4) 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5) -500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6) 0<br />
&nbsp;sqlwarn : (1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (7)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (9)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (11)<br />
&nbsp;sqlstate: 22003</span></p>
<p dir="ltr">
Explaining the various regular elements of a SQLCA could cover another post.<br />
For today I shall only refer you to the docs <a href="http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0002212.html">SQLCA&nbsp;(SQL&nbsp;communications&nbsp;area)</a>.</p>
<p dir="ltr">
Things get interesting when we start with procedural logic (or try to create a view for that matter):</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">--#SET TERMINATOR @</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">CREATE OR REPLACE PROCEDURE&nbsp; P(OUT a INT)<br />
BEGIN<br />
SET a := 1000000 * 100000;<br />
END<br />
@</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">--#SET TERMINATOR ;</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">SQL0104N&nbsp; An unexpected token &quot;:=&quot; was found following &quot;a INT)&nbsp; BEGIN&nbsp; SET a&quot;.<br />
Expected tokens may include:&nbsp; &quot;=&quot;.&nbsp; LINE NUMBER=5.&nbsp; SQLSTATE=42601</span></p>
<p dir="ltr">
Let&#39;s use</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">UPDATE COMMAND OPTIONS USING A ON;</span></p>
<p dir="ltr">
and rerunning the statement to reveal the origin of this information:</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">SQLCA Information<br />
<br />
&nbsp;sqlcaid : SQLCA&nbsp;&nbsp;&nbsp;&nbsp; sqlcabc: 136&nbsp;&nbsp; sqlcode: -104&nbsp;&nbsp; sqlerrml: 25<br />
&nbsp;sqlerrmc: :=&yuml;a INT)<br />
BEGIN<br />
SET a&yuml;=<br />
&nbsp;sqlerrp : SQLNP01F<br />
&nbsp;sqlerrd : (1) -2145779603&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2) 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3) <strong>3</strong><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4) 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5) -705&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6) 0<br />
&nbsp;sqlwarn : (1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (7)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (9)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (11)<br />
&nbsp;sqlstate: 42601</span></p>
<p dir="ltr">
The ERRD(3) field contains the value 3 which indicates an approximation of the syntax error.<br />
This has been working since many years, but what happens for runtime errors?</p>
<p dir="ltr">
When we fix the syntax error:</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">--#SET TERMINATOR @</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">CREATE OR REPLACE PROCEDURE&nbsp; P(OUT a INT)<br />
BEGIN<br />
VALUES 1000000 * 100000 INTO a;<br />
END<br />
@</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">--#SET TERMINATOR ;</span></p>
<p dir="ltr">
and call the procedure we get:</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">CALL p(?);</span></p>
<p dir="ltr">
SQL0802N&nbsp; Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">SQLCA Information<br />
<br />
&nbsp;sqlcaid : SQLCA<strong>M</strong>&nbsp;&nbsp;&nbsp; sqlcabc: 136&nbsp;&nbsp; sqlcode: -802&nbsp;&nbsp; sqlerrml: 0<br />
&nbsp;sqlerrmc:<br />
&nbsp;sqlerrp : SQLRI0AD<br />
&nbsp;sqlerrd : (1) -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2) 16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3) 3<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4) 1031059920&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5) -500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6) 0<br />
&nbsp;sqlwarn : (1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (7)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (9)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (11)<br />
&nbsp;sqlstate: 22003</span><br />
&nbsp;</p>
<p dir="ltr">
Errd(3) has been set again! It&#39;s just that CLP didn&#39;t know to pick it up.</p>
<p dir="ltr">
This a feature that was added to the DB2 engine in DB2 10.5 and it simply hasn&#39;t made it to the pretty printer of the error message in CLP yet!</p>
<p dir="ltr">
There is more information here though: ERRD(4) is not 0 anymore and the SQLCA eyecatcher acquired an &quot;M&quot; at the end.<br />
The ERRD(4) corresponds to the SYSCAT.ROUTINES.LIB_ID column and the &quot;M&quot; tells it that this is the case.<br />
&nbsp;</p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;">SELECT ROUTINESCHEMA, ROUTINENAME FROM SYSCAT.ROUTINES WHERE LIB_ID = 1031059920;</span></p>
<p dir="ltr">
<span style="font-family:courier new,courier,monospace;"><u>ROUTINENAME ROUTINESCHEMA</u><br />
SRIELAU&nbsp;&nbsp;&nbsp;&nbsp; P</span></p>
<p dir="ltr">
So now we know that the -802 originate on or after line 3 of SRIELAU.P.</p>
<p dir="ltr">
Note that I replaced in the above example the SET with a VALUES INTO, and I used the language &quot;on or after line&quot;.<br />
If&nbsp; the statement is &quot;too simple&quot; then the procedural compiler may optimize out the line-number increments.<br />
<br />
You still get the routine of course and you will get a good idea where the error is from if not the exact line.</p>
<p dir="ltr">
&nbsp;</p>
<h1 dir="ltr">
Conclusion</h1>
<p dir="ltr">
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.</p>
<p dir="ltr">
I&#39;m eagerly awaiting the day when I can refresh this post to report that CLP and CLPPlus return this information by default.</p>
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&#39;s messages,...008693urn:lsid:ibm.com:blogs:entries-c3f1cfe8-cfc4-4845-b1aa-c79f17033d85SQL Tips for DB2 LUW2017-10-04T09:42:05-04:00