With this SQLCODE list, from the “bad guys” through to the “odd guys” – e.g. the SQLCODE 100 – and then onto the “not so bad guys”, you can see a practical list of current SQLCODEs in production from around the globe, including their meaning. This review also analyzes the behaviour of some SQLCODEs and also proposes some solutions to get your Db2 z/OS programs correctly processing these SQLCODEs.
Well perhaps not quite that dramatic! Our SQL WorkLoadExpert for z/OS software (aka WLX) has been running at customer sites for a while now with the “Failing Statements” Use Case active. So I thought it would be interesting to review the eclectic mix of SQLCODEs that it receives…
Bad Guys
These all have negative SQLCODE values and so are bad…but look at the mix…
SQLCODE COUNT ------- ----- -30081 209 Communication error -30080 1 Communication error -20398 3 Error during XML Parsing -20385 1 PENDING DDL caused error -20289 3 Invalid string unit -20223 4 Encryption facility not available -20186 5 A dynamic SQL clause is invalid -20183 1 ALTER ADD PARTITION invalid syntax -20008 2 Attempt to use unsupported feature -4743 3 Attempt to use new function -950 4 LOCATION name invalid -926 1 ROLLBACK not valid in IMS, CICS or RRSAF -925 816 COMMIT not valid in IMS, CICS or RRSAF -913 6 Deadlock or Timeout -911 8 Deadlock or Timeout -905 3 RLF timeout -904 165 Unavailable resource -876 2 xxx cannot be created or altered -874 1 Encoding scheme conflict -846 17 Invalid IDENTITY or SEQUENCE -822 1 SQLDA contains invalid data address -811 39,231 More than one row returned for a SELECT -805 389 Package not found in PLAN -804 21 SQLDA is incorrect -803 4,437,491 Duplicate INSERT attempted -713 2 Replacement value is invalid -669 8 Object cannot be dropped -650 2 ALTER cannot be executed -647 18 BP for implicit not activated -644 5 Invalid value for keyword -637 3 Duplicate keyword or clause -628 5 Clauses are mutually exclusive -612 4 LOCKMAX 0 only if LOCK SIZE TS or TAB -607 11 Operation is not defined for this object -601 49 Object already exists -556 1 Priv cannot be revoked -553 1 Authorization not valid (SET CURRENT SQLID) -552 1 Auth does not have the privilege -551 8 Authorization failure -530 4 Insert or Update of Foreign Key invalid -525 9 Statement in error at bind time -518 8 EXECUTE does not identify a prepared stmt -516 15 Describe for a not prepare -514 8 Cursor not in a prepared state -512 9 Statement reference remote obj invalid -502 2 Cursor in an OPEN is already OPEN -501 18,825,773 Cursor in a FETCH or CLOSE is not OPEN -471 1,832 Procedure/Function failed -440 4 Routine not found -433 9 VALUE is too long -421 8 Operands of SET not the same column count -420 266 String value is not acceptable to function -419 2 Decimal divide invalid – negative scale -418 2 Statement contains invalid parameter markers -413 14 Overflow or Underflow -408 12 VALUE incompatible with target -407 22 UPDATE, INSERT, SET is NULL column NOT NULL -406 12 Calculated/Derived numeric out of range -405 8 Numeric constant out of range -401 2 Incompatible data types -390 5 Object not valid where it is used -338 9 An ON clause is invalid -327 1 Row is outside the bounds of last partition -313 12 Number of host vars not equal par.markers -312 4 Variable not defined or unusable -311 98 Length of input host variable out of bounds -310 154,274 Decimal host var contains no decimal data -305 2,244,618 NULL value cannot be assigned -214 1 An expression is invalid -304 1 Value cannot be assigned data type range -303 1 Value cannot be assigned data type incompat -302 13 Value of input var/arg x too large -301 3 Value of input var/arg x cannot be used -220 10 Column in PLAN_TABLE is incorrect -219 916 Required PLAN_TABLE does not exist -214 1 Expression invalid -208 7 Order by invalid -206 90 x is not valid -205 11 x is not a column of table -204 7,665 x is an undefined name -203 1 A reference to col x is ambiguous -199 30 Illegal use of keyword -196 4 Col cannot be dropped -183 5 Out of range for date/timestamp -181 1,156,892 String expression of datetime is invalid -180 8 Date, Time or Timestamp invalid -171 4 Data type/len/val of arg x of y is invalid -170 8 Invalid no. args -158 1 No.cols not equal result table -151 4 Update of catalog col not allowed -138 4 2nd or 3rd arg in SUBSTR out of range -126 2 Select contains UPDATE and ORDER BY -122 6 Col or exp in the select list is not valid -120 1 Aggregate or OLAP not valid -117 29 No. values not equal no. columns -109 7 Clause is not permitted -107 3 Name is too long -104 23 Illegal symbol -103 1 Invalid numeric constant -84 2 Unacceptable SQL statement -10 1 Non-terminated string constant
Now the ones that jump out are
811 Badly written SELECT that “normally” returns one row – Code must be corrected or the SELECT changed into a CURSOR.
803 This is the all-time classic “Should I insert or update?” problem. It could well be that MERGE is actually a better way forward.
501 This is down to the application development logic being “Always first CLOSE the cursor” which is naturally madness but is “how it is” – This needs a code change to just comment out the crazy CLOSE!
310 Oh dear! Looks pretty nasty to me…
305 Null indicators “forgotten” – quite probably an SQL coding error. Typically a LEFT OUTER JOIN style SQL that “normally” gets a match. SQL and/or code must be checked and changed. COALESCE can help here!
181 What is going on with datetime formats???
Not so Bad Guys
These all have positive SQLCODE values and so are not so bad…
SQLCODE COUNT
------- ----
98 397 Dynamic SQL ends in a ;
162 6 TS in check pending
203 2 Qualified column name resolved using non-unique
or unexposed name
222 21,652 Reading a hole with sensitive scrollable
238 23 SQLDA not set-up correctly but enough space
is there for the LOB descripton
347 37 Recursive SQL has no “brake” and could loop
354 289 Multi-row fetch got warnings. GET DIAGNOSTICS
must be used
403 2 ALIAS points to non-existent table
445 2 VALUE has been truncated
466 25 Stored proc returned nnn sets of data
535 4 Positioned UPDATE/DELETE may depend on the order
of rows (self-referencing constraint)
562 1 GRANT ignored as already held
585 1,643 Collection appears more than once
when setting a special register
610 7 DDL has caused an object to enter PENDING
20272 1 TS converted to table part from index part
20520 80,115 Deprecated function usageHere the ones that jump out are
222 This is normal if using sensitive scrollable cursors, probably ok.
585 Why double set the SCHEMA? Just wastes CPU cycles
20520 Whoops! Which feature/function do they mean? I have often seen old PLAN_TABLE usage causing this.
Weird Bad Guys
These two are just odd…
SQLCODE COUNT
------- -----
0 25 Everything was ok or perhaps not?
Could be warnings were issued…
100 117 Not found, End of Cursor or ???From the documentation 100 is actually more interesting than you would think
Explanation:
One of the following conditions occurred:
- No row met the search conditions specified in an UPDATE or DELETE statement.
- The result of a SELECT INTO statement was an empty table.
- The result of the subselect of an INSERT statement is empty.
- A FETCH statement was executed when the cursor was positioned after the last row of the result table.
- No available rows qualified for return when SKIP LOCKED DATA was specified with isolation level CS or RS.
- A FETCH statement that returns a rowset was issued, but there were not enough rows after the current cursor position to reposition the cursor on a full rowset. The cursor has been positioned on a partial rowset. If a target was specified, data was returned only for the number of rows that were actually fetched for the partial rowset. The number of rows that were returned is in field SQLERRD3 of the SQLCA.
– When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.
– This SQLCODE is also issued when LOB data cannot be returned. This situation can occur when an application is running with isolation level UR and another application has locked the LOB table space.
Ignore the usual suspects
With WLX you can also exclude a list of SQLCODEs that you are not interested in. I would be tempted to add 0, 98, 100, 222, 394, 466 and 535 as a starting point.
Does it matter?
Well of course the answer is “It Depends!”
If the programs are correctly processing the SQLCODEs then everything is fine. If, however, error checking is missing, incorrect WHENEVER logic is used, or stuff is simply ignored, then it is probably one of the sources of bad data at your site. It can also cost you money as cpu is not really free.
Measured response
Just for fun (yes, I’m weird like that) I coded a COBOL program that connected to Db2 and then did 1,000,000 CLOSE cursors. On our little machine, the 1,000,000 -501’s caused 68.10 Seconds of CPU – and this was just the CLOSE, no error handling or WHENEVERs involved at all – so the saving could/should be even more. With the CLOSE not being there the job took just 0.11 Seconds of CPU.
Then I did another test, because I know a JAVA framework that loves ending with ROLLBACK, ROLLBACK, COMMIT (I kid you not dear readers!),
and here’s how my 1,000,000 test results look:
Program with Rollback, Rollback, Commit 94.40 Seconds of CPU
Program with Rollback, Commit 71.10 Seconds of CPU
Program with Rollback 19.62 Seconds of CPU
Program with Commit 58.13 Seconds of CPU
So the best thing for performance is to just ROLLBACK! Lol!
How are you?
What do you have at your shop in production? Do you have any tips or tricks about these, or any other, SQLCODEs that you have experienced?
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect