Checking the execution of SQL statements by using the GET DIAGNOSTICS statement
One way to check whether an SQL statement executed successfully is to ask DB2® to return the diagnostic information about the last executed SQL statement.
About this task
You can use the GET DIAGNOSTICS statement to return diagnostic information about the last SQL statement that was executed. You can request individual items of diagnostic information from the following groups of items:
- Statement items, which contain information about the SQL statement as a whole
- Condition items, which contain information about each error or warning that occurred during the execution of the SQL statement
- Connection items, which contain information about the SQL statement if it was a CONNECT statement
In addition to requesting individual items, you can request that GET DIAGNOSTICS return ALL diagnostic items that are set during the execution of the last SQL statement as a single string.
In SQL procedures, you can also retrieve diagnostic information by using handlers. Handlers tell the procedure what to do if a particular error occurs.
Use the GET DIAGNOSTICS statement to handle multiple SQL errors that might result from the execution of a single SQL statement. First, check SQLSTATE (or SQLCODE) to determine whether diagnostic information should be retrieved by using GET DIAGNOSTICS. This method is especially useful for diagnosing problems that result from a multiple-row INSERT that is specified as NOT ATOMIC CONTINUE ON SQLEXCEPTIONand multiple row MERGE statements.
Even if you use only the GET DIAGNOSTICS statement in your application program to check for conditions, you must either include the instructions required to use the SQLCA or you must declare SQLSTATE (or SQLCODE) separately in your program.
When you use the GET DIAGNOSTICS statement, you assign the requested diagnostic information to host variables. Declare each target host variable with a data type that is compatible with the data type of the requested item.
To retrieve condition information, you must first retrieve the number of condition items (that is, the number of errors and warnings that DB2 detected during the execution of the last SQL statement). The number of condition items is at least one. If the last SQL statement returned SQLSTATE '00000' (or SQLCODE 0), the number of condition items is one.
You want to display diagnostic information for each condition that might occur during the execution of a multiple-row INSERT statement in your application program. You specify the INSERT statement as NOT ATOMIC CONTINUE ON SQLEXCEPTION, which means that execution continues regardless of the failure of any single-row insertion. DB2 does not insert the row that was processed at the time of the error.
In the following example, the first GET DIAGNOSTICS statement returns the number of rows inserted and the number of conditions returned. The second GET DIAGNOSTICS statement returns the following items for each condition: SQLCODE, SQLSTATE, and the number of the row (in the rowset that was being inserted) for which the condition occurred.
EXEC SQL BEGIN DECLARE SECTION;
long row_count, num_condns, i;
long ret_sqlcode, row_num;
char ret_sqlstate[6];
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL
INSERT INTO DSN8A10.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:hva1, :hva2, :hva3)
FOR 10 ROWS
NOT ATOMIC CONTINUE ON SQLEXCEPTION;
EXEC SQL GET DIAGNOSTICS
:row_count = ROW_COUNT, :num_condns = NUMBER;
printf("Number of rows inserted = %d\n", row_count);
for (i=1; i<=num_condns; i++) {
EXEC SQL GET DIAGNOSTICS CONDITION :i
:ret_sqlcode = DB2_RETURNED_SQLCODE,
:ret_sqlstate = RETURNED_SQLSTATE,
:row_num = DB2_ROW_NUMBER;
printf("SQLCODE = %d, SQLSTATE = %s, ROW NUMBER = %d\n",
ret_sqlcode, ret_sqlstate, row_num);
}In the activity table, the ACTNO column is defined as SMALLINT. Suppose that you declare the host variable array hva1 as an array with data type long, and you populate the array so that the value for the fourth element is 32768.
If you check the SQLCA values after the INSERT statement, the value of SQLCODE is equal to 0, the value of SQLSTATE is '00000', and the value of SQLERRD(3) is 9 for the number of rows that were inserted. However, the INSERT statement specified that 10 rows were to be inserted.
Number of rows inserted = 9
SQLCODE = -302, SQLSTATE = 22003, ROW NUMBER = 4When you use the GET DIAGNOSTICS statement, you assign the requested diagnostic information to host variables. Declare each target host variable with a data type that is compatible with the data type of the requested item.
To retrieve condition information, you must first retrieve the number of condition items (that is, the number of errors and warnings that DB2 detected during the execution of the last SQL statement). The number of condition items is at least one. If the last SQL statement returned SQLSTATE '00000' (or SQLCODE 0), the number of condition items is one.