2018-12: Db2 Checklist – SQLCODEs as never seen before

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 usage

Here 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…

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

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.

Roy Boxwell
Senior Architect