Checking the execution of SQL statements by using the SQLCA
One way to check whether an SQL statement executed successfully is to use the SQL communication area (SQLCA). This area is set apart for communication with DB2®.
About this task
If you use the SQLCA, include the necessary instructions to display information that is contained in the SQLCA in your application program. Alternatively, you can use the GET DIAGNOSTICS statement, which is an SQL standard, to diagnose problems.
- When DB2 processes an SQL statement, it places return codes that indicate the success or failure of the statement execution in SQLCODE and SQLSTATE.
- When DB2 processes a FETCH statement, and the FETCH is successful, the contents of SQLERRD(3) in the SQLCA is set to the number of returned rows.
- When DB2 processes a multiple-row FETCH statement, the contents of SQLCODE is set to +100 if the last row in the table has been returned with the set of rows.
- When DB2 processes an UPDATE, INSERT, or DELETE statement, and the statement execution is successful, the contents of SQLERRD(3) in the SQLCA is set to the number of rows that are updated, inserted, or deleted.
- When DB2 processes a TRUNCATE statement and the statement execution is successful, SQLERRD(3) in the SQLCA is set to -1. The number of rows that are deleted is not returned.
- If SQLWARN0 contains W, DB2 has
set at least one of the SQL warning flags (SQLWARN1 through SQLWARNA):
- SQLWARN1 contains N for non-scrollable cursors and S for scrollable cursors after an OPEN CURSOR or ALLOCATE CURSOR statement.
- SQLWARN4 contains I for insensitive scrollable cursors, S for sensitive static scrollable cursors, and D for sensitive dynamic scrollable cursors, after an OPEN CURSOR or ALLOCATE CURSOR statement, or blank if the cursor is not scrollable.
- SQLWARN5 contains a character value of 1 (read only), 2 (read and delete), or 4 (read, delete, and update) to indicate the operation that is allowed on the result table of the cursor.