Mainframe Blog

REXX SQLCA in Db2 12

4 minute read
Stephen Watts

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

Variable Contents
SQLCODE The SQL return code
SQLERRMC One or more tokens separated by X’FF’ that are substituted for variables in the descriptions of error conditions. It may contain truncated tokens. A message length of 70 bytes indicates possible truncation
SQLERRP A product signature, and in the case of an error, diagnostic information such as the name of the module that detected the error. For DB2 or z/OS, the product signature is ‘DSN’
SQLERRD.1 For a sensitive static cursor, contains the number of rows in a results table when the cursor position is after the last row (that is, when SQLCODE is equal to +100). Can also contain an internal error code
SQLERRD.2 For a sensitive static cursor, contains the number of rows in a results table when the cursor position is after the last row (that is, when SQLCODE is equal to +100). Can also contain an internal error code
SQLERRD.3 Contains the number of rows that qualified for the operation after an SQL data change statement (but not rows deleted as a result of CASCADE delete). For the OPEN of a cursor for a SELECT with an SQL data change statement or for a SELECT INTO, SQLERRD(3) contains number of rows affected by the embedded data change statement. Set to 0 if the SQL statement fails, indicating that all changes made in executing the statement were canceled. Set to -1 for a mass delete from a table in a segmented tablespace, for a truncate operation, or a delete from a view when neither the DELETE statement nor the definition of the view included selection criteria. For rowset-oriented FETCH statements, contains the number of rows returned in the rowset. For SQLCODES -911 and -913, SQLERRD(3) contains the reason code for the timeout or deadlock. After successful execution of the REFRESH TABLE statement, SQLERRD(3) contains the number of rows inserted into the materialized query table. When an error is encountered in parsing a dynamic statement, or when parsing, binding or executing native SQL procedure, SQLERRD(3) will contain the line number where the error was encountered. The sixth byte of SQLCAID must be ‘L’ for this to be a valid line number. This value will be meaningful only if the statement source contains new line control characters. Not returned for an external SQL procedure.
SQLERRD.4 Generally contains timerons, a short floating-point value that indicates a rough estimate of resources required. This value does not reflect an estimate of the time required to execute the SQL statement. After you prepare an SQL statement, you can use this field as an indicator of the relative cost of the prepared SQL statement. For a particular statement, this number can vary with changes to the statistics in the catalog. Subject to change between releases of DB2 for z/OS.
SQLERRD.5 The position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE statement
SQLERRD.6 An internal error code
SQLWARN.0 Blank if all other indicators are blank; W if at least one indicator also contains a W
SQLWARN.1 W if the value of a string column was truncated when assigned to a host variable
SQLWARN.2 W if no values were eliminated from the argument to a column function; not necessarily set to W for the MIN function because its results are not dependent on the elimination of null values
SQLWARN.3 W if the number of result columns is larger than the number of host variables. Z if the ASSOCIATE LOCATORS statement contains fewer locators than the stored procedure returned
SQLWARN.4 W if a prepared UPDATE or DELETE statement does not include a WHERE clause. For a scrollable cursor, contains a D for sensitive dynamic cursors, I for insensitive cursors, and S for sensitive cursors after the OPEN CURSOR or ALLOCATE CURSOR statement, blank if not scrollable
SQLWARN.5 W if the SQL statement was not executed because is not a valid SQL statement in DB2 for z/OS. Character value of 1 (read only), 2(read and delete), or 4 (read, delete and update) to reflect capability of the case after the OPEN CURSOR or ALLOCATE CURSOR statement
SQLWARN.6 W if the addition of a month or year duration to a DATE or TIMESTAMP value results in an invalid day (example June 31). Indicates that the value of the day was changed to the last day of the month to make the result valid
SQLWARN.7 W if one or more nonzero digits are eliminated from the fractional part of a number that was used as the operand of a decimal multiply or divide operation
SQLWARN.8 W if a character that cannot be converted was replaced with substitute character
SQLWARN.9 W if arithmetic exceptions were ignored during COUNT or COUNT_BIG processing. Z if the stored procedure returned multiple result sets
SQLWARN.10 W if at least one character field of the SQLCA is invalid due to a character conversion error
SQLSTATE Return code for the outcome of the most recent execution of an SQL statement

Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at [email protected]. Db2 is a registered trademark of the IBM Corporation.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.