Data types for GET DIAGNOSTICS items
You can use the GET DIAGNOSTICS statement to request information about the statement, condition, and connection for the last SQL statement that was executed. You must declare each target host variable with a data type that is compatible with the data type of the requested item.
The following table summarizes the data types for the diagnostics information items that you can request by using the GET DIAGNOSTICS statement.
| GET DIAGNOSTICS item | Data type | Description |
|---|---|---|
| DB2_GET_DIAGNOSTICS_DIAGNOSTICS | VARCHAR(32672) | After a GET DIAGNOSTICS statement, all of the diagnostics as a single string if any error or warning occurred |
| DB2_LAST_ROW | INTEGER | After a multiple-row FETCH statement, the value of +100 if the last row in the table is in the rowset that was returned |
| DB2_NUMBER_PARAMETER_MARKERS | INTEGER | After a PREPARE statement, the number of parameter markers in the prepared statement |
| DB2_NUMBER_RESULT_SETS | INTEGER | After a CALL statement that invokes a stored procedure, the number of result sets that are returned by the procedure |
| DB2_NUMBER_ROWS | DECIMAL(31,0) |
After an OPEN or FETCH statement for which the size of the result table is known, the number of rows in the result table After a PREPARE statement, the estimated number of rows in the result table for the prepared statement For SENSITIVE DYNAMIC cursors, the approximate number of rows Otherwise, or if the server only returns an SQLCA, the value zero |
| DB2_RETURN_STATUS | INTEGER | After a CALL statement that invokes an SQL procedure, the return status if the procedure contains a RETURN statement |
| DB2_SQL_ATTR_CURSOR_HOLD | CHAR(1) | After an ALLOCATE or OPEN statement, whether the cursor can be held open across multiple units of work (Y or N) |
| DB2_SQL_ATTR_CURSOR_ROWSET | CHAR(1) | After an ALLOCATE or OPEN statement, whether the cursor can use rowset positioning (Y or N) |
| DB2_SQL_ATTR_CURSOR_SCROLLABLE | CHAR(1) | After an ALLOCATE or OPEN statement, whether the cursor is scrollable (Y or N) |
| DB2_SQL_ATTR_CURSOR_SENSITIVITY | CHAR(1) | After an ALLOCATE or OPEN statement, whether the cursor shows updates made by other processes (sensitivity I or S) |
| DB2_SQL_ATTR_CURSOR_TYPE | CHAR(1) | After an ALLOCATE or OPEN statement, whether the cursor is forward (F), declared static (S for INSENSITIVE or SENSITIVE STATIC), or dynamic (D for SENSITIVE DYNAMIC). |
| MORE | CHAR(1) | After any SQL statement, this item indicates whether some conditions items were discarded because of insufficient storage (Y or N). |
| NUMBER | INTEGER | After any SQL statement, this item contains the number of condition items. If no warning or error occurred, or if no previous SQL statement has been executed, the number that is returned is 1. |
| ROW_COUNT | DECIMAL(31,0) | After an insert, update, delete, or fetch, this item contains the number of rows that are deleted, inserted, updated, or fetched. After PREPARE, this item contains the estimated number of result rows in the prepared statement. After TRUNCATE, it contains -1. |
| DB2_SQL_NESTING_LEVEL | INTEGER | After a CALL statement, this item identifies the current level of nesting or recursion in effect when the GET DIAGNOSTICS statement was executed. Each level of nesting corresponds to a nested or recursive invocation of a packaged SQL function, packaged SQL procedure, or trigger. If the GET DIAGNOSTICS statement is executed outside of a level of nesting, the value zero is returned. When an application connects to another server the value is reset to zero. |
| CATALOG_NAME | VARCHAR(128) | The server name of the table that owns a constraint that caused an error, or that caused an access rule or check violation |
| CONDITION_NUMBER | INTEGER | The number of the condition |
| CURSOR_NAME | VARCHAR(128) | The name of a cursor in an invalid cursor state |
| DB2_ERROR_CODE1 | INTEGER | An internal error code |
| DB2_ERROR_CODE2 | INTEGER | An internal error code |
| DB2_ERROR_CODE3 | INTEGER | An internal error code |
| DB2_ERROR_CODE4 | INTEGER | An internal error code |
| DB2_INTERNAL_ERROR_POINTER | INTEGER | For some errors, a negative value that is an internal error pointer |
| DB2_LINE_NUMBER | INTEGER | The line number where an error is encountered in parsing a dynamic statement, or parsing, binding, or executing a CREATE or ALTER statement for a native SQL procedure, compiled SQL function, or trigger The line number when a CALL statement invokes a native SQL procedure and the procedure returns an error |
| DB2_MESSAGE_ID | CHAR(10) | The message ID that corresponds to the message that is contained in the MESSAGE_TEXT diagnostic item. |
| DB2_MODULE_DETECTING_ERROR | CHAR(8) | The module that detected the error |
| DB2_ORDINAL_TOKEN_n | VARCHAR(515) | The nth token, where n is a value from 1–100 |
| DB2_REASON_CODE | INTEGER | The reason code for errors that have a reason code token in the message text |
| DB2_RETURNED_SQLCODE | INTEGER | The SQLCODE for the condition |
| DB2_ROW_NUMBER | DECIMAL(31,0) | After any SQL statement that involves multiple rows, this item contains the row number on which Db2 detected the condition |
| DB2_SQLERRD1 | INTEGER | The sqlerrd(1) value from the SQLCA that is returned by the server, or zero |
| DB2_SQLERRD2 | INTEGER | The sqlerrd(2) value from the SQLCA that is returned by the server, or zero |
| DB2_SQLERRD3 | INTEGER | The sqlerrd(3) value from the SQLCA that is returned by the server, or zero |
| DB2_SQLERRD4 | INTEGER | The sqlerrd(4) value from the SQLCA that is returned by the server, or zero |
| DB2_SQLERRD5 | INTEGER | The sqlerrd(5) value from the SQLCA that is returned by the server, or zero |
| DB2_SQLERRD6 | INTEGER | The sqlerrd(6) value from the SQLCA that is returned by the server, or zero |
| DB2_TOKEN_COUNT | INTEGER | The number of tokens available for the condition |
| MESSAGE_TEXT | VARCHAR(32672) | The message text associated with the SQLCODE |
| RETURNED_SQLSTATE | CHAR(5) | The SQLSTATE for the condition |
| SERVER_NAME | VARCHAR(128) | After a CONNECT, DISCONNECT, or SET CONNECTION statement, the name of the server specified in the statement |
| DB2_AUTHENTICATION_TYPE | CHAR(1) | The authentication type (S, C, D, E, or blank) |
| DB2_AUTHORIZATION_ID | VARCHAR(128) | The authorization ID that is used by the connected server |
| DB2_CONNECTION_STATE | INTEGER | Whether the connection is unconnected (-1), local (0), or remote (1) |
| DB2_CONNECTION_STATUS | INTEGER | Whether updates can be committed for the current unit of work (1 for Yes, 2 for No) |
| DB2_ENCRYPTION_TYPE | CHAR(1) | The level of encryption for the connection: (A) only the authentication tokens (auth ID and password) are encrypted (D) all data for the connection is encrypted |
| DB2_PRODUCT_ID | VARCHAR(8) | The Db2 product signature |
| DB2_SERVER_CLASS_NAME | CHAR(128) | After a CONNECT or SET CONNECTION statement, the Db2 server class name |
| ALL | VARCHAR(32672) | All diagnostic items set for the last SQL statement combined into one string, in the form of a semicolon separated list of all available diagnostic information |