>>---EXEC SQL---GET-.---------.-DIAGNOSTICS-->
+-CURRENT-+
+-----------,--------------+
V |
>-----.-.- :hvar1 = --.-ROW_COUNT--.->
| | |
| +-NUMBER-----+
|
| +-----------------,------------------+
| V |
+--CONDITION--condition-.-:hvar2 = --.-DB2_RETURNED_SQLCODE--.->
| |
+---RETURNED_SQLCODE----+
| |
+---RETURNED_SQLSTATE---+
| |
+---MESSAGE_TEXT--------+
| |
+---DB2_ROW_NUMER-------+
| |
+---ROW_NUMBER----------+
| |
+---COLUMN_NUMBER-------+
>---END-EXEC---><
| hvar1 | A host variable to receive a statement-level diagnostic information item. |
| condition | A literal or host variable that specifies the diagnostic record from which diagnostic information is retrieved. |
| hvar2 | A host variable to receive a statement-level diagnostic information item |
| ROW_COUNT | The number of rows affected by the previous statement. For INSERT, UPDATE, or DELETE, this is the number of rows inserted, updated, or deleted, respectively. For FETCH, it is the number of rows returned to the application. This must be retrieved into a numeric host variable, usually a 32-bit integer. |
| NUMBER | The number of diagnostic records available to be queried in subsequent GET DIAGNOSTICS statements. This must be retrieved into a numeric host variable, usually a 32-bit integer. |
| DB2_RETURNED_SQLCODE | The SQLCODE value associated with the diagnostic record. This must be retrieved into a numeric host variable, usually a 32-bit integer. |
| RETURNED_SQLCODE | The SQLCODE value associated with the diagnostic record, which is the same as DB@_RETURNED_SQLCODE. This must be retrieved into a numeric host variable, usually a 32-bit integer. |
| RETURNED_SQLSTATE | The SQLSTATE value associated with the diagnostic record. This must be retrieved into a PIC X(n) host variable, where n can be any size, but is usually 5. |
| MESSAGE_TEXT | The message text associated with the diagnostic record. This must be retrieved into a PIC X(n) host variable, where n can be any size. |
| DB2_ROW_NUMBER | The row number associated with the diagnostic record. This is useful with multi-row operations such as insert statements using parameter arrays and block fetches. |
| ROW_NUMBER | The same as DB2_ROW_NUMBER. |
| COLUMN_NUMBER | The column or parameter number associated with the diagnostic record. This is useful in determining which host variable(s), string truncation, and other warnings apply to. |
01 condNum pic s9(8) comp-5.
01 retSqlCode pic s9(8) comp-5.
01 retMessagetext pic x(120).
01 retSqlState pic x(5).
exec sql get diagnostics :condCount = number end-exec
perform varying condNum from 1 by 1 until condNum > condCount
exec sql
get diagnostics condition :condNum
:retSqlState = RETURNED_SQLSTATE,
:retSqlCode = DB2_RETURNED_SQLCODE,
:retMessageText = MESSAGE_TEXT
end-exec
display retSqlCode ' ' retsqlState ' ' retMessageText
end-perform
Comments:
GET DIAGNOSTICS is supported on Windows platforms only, and for ODBC and ADO.NET only.
When an OpenESQL statement is executed, one or more diagnostic records are generated to record diagnostic information about the execution. The SQLCA can return information from only one diagnostic record. You can use GET DIAGNOSTICS to both determine if more than one record was generated, and then to query individual records.
In most cases when a statement executes successfully, only one record is generated. A FETCH statement can potentially generate as many as one record per retrieved column if there are truncation warnings or null values returned with no indicator variable. If a SQL Server stored procedure executes TSQL RAISEERROR statements and COBOL DISPLAY statements in CLR stored procedures, then each of these generates a diagnostic record for the caller.
GET DIAGNOSTICS has no effect on the SQLCA or SQLSTATE, SQLCODE declared by an application.
No diagnostic information is available for execution of GET DIAGNOSTICS statements.
You can use the SQL Error Mapping feature with GET DIAGNOSTICS. See SQL Error Mapping for details.[6]