Mainframe Blog

Get Diagnostics in Db2 12

3 minute read
Stephen Watts

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

Statement Information

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)

Conditional Data Types

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)

Connection Information

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 [email protected]. Db2 is a registered trademark of the IBM Corporation.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.