TL;DR Summary: Get Diagnostics in Db2 12 details the GET DIAGNOSTICS statement, offering insights into diagnostic items, descriptions, and data types for effective SQL troubleshooting.
- Covers diagnostic items such as DB2_GET_DIAGNOSTICS_DIAGNOSTICS and DB2_ERROR_CODE.
- Enhances understanding of SQL operations and error handling in Db2 12.
| Item | Description | Data type |
| DB2_GET_DIAGNOSTICS _DIAGNOSTICS | After a GET DIAGNOSTICS statement if any error or warning occurred, this item contains all of the diagnostics as a single string | VARCHAR(32672) |
| DB2_LAST_ROW | After a multiple-row FETCH statement, contains a value of +100 if the last row in the table is in the rowset that was returned | INTEGER |
| DB2_NUMBER_ PARAMETER_MARKERS | After a PREPARE statement, contains number of parameter markers in the prepared statement | INTEGER |
| DB2_NUMBER_RESULT_ SETS | After a CALL statement that invokes a stored procedure this item contains the number of result sets that are returned by the procedure | INTEGER |
| DB2_NUMBER_ROWS | After an OPEN or FETCH statement for which the size of the result table is known, this item contains the number of rows in the result table. After a PREPARE statement, this item contains the estimated number of rows in the result table for the prepared statement. For SENSITIVE DYNAMIC cursors this item contains the approximate number of rows | DECIMAL(31,0) |
| DB2_RETURN_STATUS | After CALL statement that invokes an SQL procedure, this item contains the return status if the procedure contains a RETURN statement | INTEGER |
| DB2_SQL_ATTR _CURSOR_HOLD | After ALLOCATE or OPEN statement, indicates whether the cursor can be held open across multiple units of work (Y or N) | CHAR(1) |
| DB2_SQL_ATTR _CURSOR_ROWSET | After ALLOCATE or OPEN statement, indicates whether the cursor can use rowset positioning (Y or N) | CHAR(1) |
| DB2_SQL_ATTR _CURSOR_SCROLLABLE | After ALLOCATE or OPEN statement indicates whether the cursor is scrollable (Y or N) | CHAR(1) |
| DB2_SQL_ATTR _CURSOR_SENSITIVITY | After ALLOCATE or OPEN statement indicates whether the cursor shows updates made by other processes (sensitivity A, I or S) | CHAR(1) |
| DB2_SQL_ATTR _CURSOR_TYPE | After ALLOCATE or OPEN statement, indicates whether the cursor is declared static (S for INSENSITIVE or SENSITIVE STATIC) or dynamic (D for SENSITIVE DYNAMIC). | CHAR(1) |
| DB2_SQL_NESTING_ LEVEL | After a CALL statement, this item identifies the current level of nesting or recursion in effect when the GET DIAGNOSTICS statement was executed | INTEGER |
| MORE | After any SQL statement, indicates whether some conditions items were discarded because of insufficient storage (Y or No) | CHAR(1) |
| NUMBER | After any SQL statement, 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 | INTEGER |
| ROW_COUNT | After DELETE, INSERT, UPDATE, or FETCH, 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 | DECIMAL(31,0) |
| Item | Description | Data type |
| CATALOG_NAME | The server name of the table that owns a constraint that caused an error, or that caused an access rule or check violation | VARCHAR(128) |
| CONDITION_NUMBER | Number of the condition | INTEGER |
| CURSOR_NAME | Name of a cursor in an invalid cursor state | VARCHAR(128) |
| DB2_ERROR_CODE1 | This item contains an internal error code | INTEGER |
| DB2_ERROR_CODE2 | This item contains an internal error code | INTEGER |
| DB2_ERROR_CODE3 | This item contains an internal error code | INTEGER |
| DB2_ERROR_CODE4 | This item contains an internal error code | INTEGER |
| DB2_INTERNAL _ERROR_POINTER | Negative value that has an internal error pointer | INTEGER |
| DB2_LINE_NUMBER | Line number where an error is encountered in parsing a dynamic statement | INTEGER |
| DB2_MESSAGE_ID | Message ID that corresponds to the message that is contained in the CHAR(10) MESSAGE_TEXT diagnostic item | CHAR(10) |
| DB2_MODULE_ DETECTING_ERROR | After any SQL statement, indicates which module detected the error | CHAR(8) |
| DB2_ORDINAL_TOKEN_n | After any SQL statement, contains the nth token, where n is a value from 1 to 100 | VARCHAR(515) |
| DB2_REASON_CODE | After any SQL statement, reason code for errors with reason code token in message text | INTEGER |
| DB2_RETURNED_SQLCODE | After any SQL statement, contains the SQLCODE for the condition | INTEGER |
| DB2_ROW_NUMBER | After any SQL statement that involves multiple rows, contains the row number on which DB2 detected the condition | DECIMAL(31,0) |
| DB2_TOKEN_COUNT | After any SQL statement, contains the number of tokens available for the condition | INTEGER |
| MESSAGE_TEXT | After any SQL statement, contains message text associated with SQLCODE | VARCHAR(32672) |
| RETURNED_SQLSTATE | After any SQL statement, contains the SQLSTATE condition | CHAR(5) |
| SERVER_NAME | After a CONNECT, DISCONNECT or SET CONNECTION statement, contains the name of the server specified in statement | VARCHAR(128) |
| Item | Description | Data type |
| DB2_AUTHENTICATION_TYPE | Authentication type (S, C, T or blank0 | CHAR(1) |
| DB2_AUTHORIZATION_ID | Authorization ID used by connected server | VARCHAR(128) |
| DB2_CONNECTION_STATE | Indicates whether connection is unconnected (-1), local (0), or remote (1) | INTEGER |
| DB2_CONNECTION_STATUS | Indicates whether updates can be committed for the current unit of work (1=Yes, 2=No) | INTEGER |
| DB2_ENCRYTION_TYPE | Level of encryption for the connection: A= Only Authentication tokens (authid and password) are encrypted D= All data for the connection is encrypted | CHAR(1) |
| DB2_SERVER_CLASS_NAME | After a CONNECT or SET CONNECTION statement, contains DB2 server class name | VARCHAR(128) |
| DB2_PRODUCT_ID | Contains the DB2 product signature | VARCHAR(8) |
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.
Db2 Guide
- Db2 12 for z/OS: An Introduction
- Scalar Functions in Db2 12
- How to Read IBM<sup>®</sup> Syntax Diagrams for Db2 12
- Language Elements in Db2 12
- Aggregate Functions in Db2 12
- Table & Row Functions in Db2 12
- Queries in Db2 12
- Commands in Db2 12
- SQL Control Statements in Db2 12
- Explain Tables in Db2 12
- DSNZPARMs in Db2 12
- Bind Parameters in Db2 12
- Db2 Limits in Db2 12
- SQL Communication Area (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Get Diagnostics in Db2 12
- IFCIDS in Db2 12
- Catalog Tables in Db2 12
- Exceptions in Db2 12
- XML Schema Repository Tables in Db2 12
- Updatable Catalog Statistics in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return Codes in Db2 12
- SQL Error Return Codes in Db2 12
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.