Mainframe Blog

SQL Communication Area (SQLCA) in Db2 12

4 minute read
Stephen Watts
Assembler, COBOL, or PL/I Name C Name Data Type Purpose
SQLCAID sqlcaid CHAR(8) ‘eye catcher’ for storage dumps, containing the text ‘SQLCA’. Sixth byte is ‘L’ if line number information is returned from parsing a dynamic statement or a native SQL procedure. Is not set when processing an external SQL procedure.
SQLCABC sqlcabc INTEGER Contains the length of the SQLCA: 136
SQLCODE SQLCODE INTEGER Contains the SQL return code.

0 = Successful execution

Positive = Successful execution, but with an exception condition

Negative = error condition

SQLERRML sqlerrml SMALLINT Length indicator for SQLERRMC, in the range 0 through 70.0 means that the value of SQLERRMC is not pertinent
SQLERRMC sqlerrmc VARCHAR(70) Contains 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 a possible truncation
SQLERRP sqlerrp CHAR(8) Provides a product signature and, in the case of an error, diagnostic information such as the name of the module that detected the error. First three characters are ‘DSN’
SQLERRD(1) sqlerrd[0] INTEGER For a sensitive static cursor, contains the number of rows in a result 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) sqlerrd[1] INTEGER For a sensitive static cursor, contains the number of rows in a result 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) sqlerrd[2] INTEGER Contains the number of rows that qualified to be deleted, inserted or updated after an INSERT, MERGE, UPDATE or DELETE statement. The number excludes rows affected by the triggers or referential integrity constraints the number exclude rows affected by triggers, referential integrity constraints, or inserted rows that are the result of processing a FOR PORTION OF clause for a BUSINESS_TIME period. For the OPEN of a cursor for a SELECT with a data change statement or for a SELECT INTO, SQLERRD(3) contains the number of rows affected by emdedded data change statement. The value is 0 if the SQL statement fails, indicating that all changes made in executing the statement canceled. For a DELETE statement the value will be -1 if the operation is a mass delete from a table in a segmented table space and the DELETE statement did not include selection criteria. If the delete was against a view, neither the DELETE statement nor the definition of the view included selection criteria. For a TRUNCATE statement the value will be -1. For a REFRESH TABLE statement, SQLERRD(3) contains the number of rows inserted into the materialized query table. For a rowset-oriented FETCH, contains the number of rows fetched. For SQLCODES -911 and -913, SQLERRD(3) contains the reason code for the timeout or deadlock. When an error is encountered in parsing a dynamic statement, or when parsing binding, or executing a 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. This information is not returned for an external SQL procedure
SQLERRD(4) sqlerrd[3] INTEGER generally contains timerons, a short floating point value that indicates a rough estimate of resources required. It does not reflect an estimate of the time required. When preparing a dynamically defined 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. Is also subject to change between releases of DB2 for z/OS
SQLERRD(5) sqlerrd[4] INTEGER Contains the position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE statement
SQLERRD(6) sqlerrd[5] INTEGER Internal error code
SQLWARN0 SQLWARN0 CHAR(1) W if at least one other indicator also contains a W; otherwise contains a blank
SQLWARN1 SQLWARN1 CHAR(1) W if the value of a string column was truncated when assigned to a host variable. Contains an N for non-scrollable cursors and S for scrollable cursors after the OPEN CURSOR or ALLOCATE CURSOR statement. If subsystem parameter DISABSCL is set to yes the field will not be set to N for non-scrollable cursors
SQLWARN2 SQLWARN2 CHAR(1) W if null values were eliminated from the argument of a column function; not necessarily set to W for the MIN function because its results are not dependent on the elimination of null values
SQLWARN3 SQLWARN3 CHAR(1) W if the number of result columns is larger than the number of host variables. Z if fewer locations were provided in the ASSOCIATE LOCATORS statement than the stored procedure returned
SQLWARN4 SQLWARN4 CHAR(1) W if a prepared UPDATE or DELETE statement does not include a WHERE clause. For scrollable cursor, 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. If DSNZPARM DISABSCL is set to YES, set to N for non-scrollable cursors
SQLWARN5 SQLWARN5 CHAR(1) W if the SQL statement was not executed because it is not a valid SQL statement in DB2 for z/OX. Character value of 1 (read only), 2(read and delete), or 3(read, delete and update) to reflect capability of the cursor after the OPEN CURSOR or ALLOCATE CURSOR statement if DISABSCL is set to YES, the field will not be set to N for non scrollable cursors
SQLWARN6 SQLWARN6 CHAR(1) W if addition of a month or year duration to a DATE or TIMESTAMP value results in invalid day. Indicates that the value of the day was changed to the last day of the month to make the result valid
SQLWARN7 SQLWARN7 CHAR(1) W if one or more nonzero digits were eliminated from the fractional part of a number used as the operand of a decimal multiply or divide operation
SQLWARN8 SQLWARN8 CHAR(1) W if a character that cannot be converted was replaced with a substitute character
SQLWARN9 SQLWARN9 CHAR(1) W if arithmetic exceptions were ignored during COUNT or COUNT_BIG processing. Z if the stored procedure return multiple result sets
SQLWARNA SQLWARNA CHAR(1) W if at least one character field of the SQLCA or the SQLDA names or labels is invalid due to a character conversion error
SQLSTATE sqlstate CHAR(5) A 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 blogs@bmc.com. 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 blogs@bmc.com.

BMC Bring the A-Game

From core to cloud to edge, BMC delivers the software and services that enable nearly 10,000 global customers, including 84% of the Forbes Global 100, to thrive in their ongoing evolution to an Autonomous Digital Enterprise.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) has worked at the intersection of IT and marketing for BMC Software since 2012.

Stephen contributes to a variety of publications including CIO.com, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.