Are your DB2 11 SQLCODEs up-to-date?
I originally wanted to call this newsletter “SQLCODE101” but not all of my international readers may understand… Anyway, this newsletter is dedicated to that small group of people who, like me, share an interest in SQLCODEs.
The “newest” SQLCODES are not updated in copy book style checking routines
One thing I noticed years ago, is how often the “newest” codes are not updated in copy book style checking routines.
I was at one customer site once where their copy book entry looked like this:
88 SQLCA-ERROR VALUE -999 THRU -001.
And I choked on my coffee!
It actually got worse when I then saw:
88 SQLCA-WARNING VALUE +101 THRU +999.
Now these are soooo bad it hurts the eyes!
But as they were lurking in copy book code, they were simply never seen…
DB2 11, current documentation
As of DB2 11, current documentation shows the actual ranges are -30106 to -7 and +12 to +30100.
So the above COBOL should really look like this:
88 SQLCA-ERROR VALUE -32000 THRU -001. 88 SQLCA-WARNING VALUE +1 THRU +99 +101 THRU +32000.
The SQLCODE +100 is special, as it is “no row found” or “end of cursor”.
What other SQLCODEs are of general interest then?
Well here’s my list in no particular order:
-803 (Duplicate key)
This is sometimes called the “lazy update check”. First do an insert, if it bounces with a -803 make the key unique or change it to an update statement. Now this logic is fine if the majority of the inserts succeed, but if the majority are failing it is probably time to swap the logic around and try an update, then if you get a +100 do an insert instead. This also stops any “negative SQLCODE monitors” from firing off too many false positives!
-802 and its younger brother +802 (Numeric exception)
Now do you see that I have two codes here? The +802 means a numeric exception has occurred, but the SQL carries on with -2 set in the indicator variable:
+802 EXCEPTION ERROR
+802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number
Explanation: The exception error exception-type occurred while performing one of the following operations on a field that has a data-type of DECIMAL, FLOAT, SMALLINT, or INTEGER:
Whereas the –802 is a bit different:
-802 EXCEPTION ERROR
-802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number
Explanation: An exception error has occurred in the processing of an SQL arithmetic function or arithmetic expression. The exception error occurred in one of the following areas:
- In the SELECT list of an SQL SELECT statement.
- In the search condition of a SELECT, UPDATE, MERGE, or DELETE statement.
- In the SET clause of the UPDATE operation.
- During the evaluation of an aggregate function.
So you must really take good care here!
Very interesting SQLcodes
-514 and -518 (Prepared SQL gone)
Now these are *very* interesting and you should have a quick look in your monitor to see how many of these you get. I really hope that all your SQL code has retry logic to rePREPARE the SQL that has gone. I was rather surprised to see prepared statements in current active use getting these messages. But if the data is flushed from the DSC then this is what can happen!
Here’s a little snippet from one of Namik Hrle’s DSC presentations:
It gives a little “hint” that the prepared statement can be thrown from the cache at any time, (not just the obvious bad guys like RUNSTATS etc.)
-331, +335, +445 and +20141 (Truncation or Character Conversion problem)
Here, only the -331 (CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION position-number, CANNOT BE CONVERTED FROM source-ccsid TO target-ccsid, REASON reason-code) is actually returning an error code, while all the others – +335 (use of substitute character), +445, & +20141 (Truncation) – carry on regardless. This might not be what is actually desired.
+222 (Positioned on a hole)
You have positioned onto a deleted/updated row in a SENSITIVE STATIC cursor – These warnings should just trigger another fetch until either table end or a real row is found.
-911 (Timeout or Deadlock)
Now this beauty actually does the ROLLBACK for you, so you must be aware of that when you get this bad guy!
Do you have any SQLCODEs that you treat specially?
I would love to hear from you if you do!
As usual, if you have any comments or queries please feel free to drop me a line!