Db2 GET DIAGNOSTICS Statement Information Items

Db2 statement diagnostics after SQL execution.

GET DIAGNOSTICS is useful when a COBOL + Db2 program needs more detail than a single SQLCODE. After an UPDATE, a multi-row FETCH, an OPEN, a PREPARE, or a stored procedure CALL, statement-information items can tell the program how many rows were affected, how many conditions exist, whether warning detail was discarded, or which cursor attributes Db2 used.

This page focuses only on statement-information items. For the base syntax, read DB2 GET DIAGNOSTICS Statement. For diagnostic details tied to a specific warning or error, use the related condition information page. For connection diagnostics, use the connection information page.

What Statement Information Means

Statement information describes the last SQL statement that ran before GET DIAGNOSTICS. It is not the same as condition information. A statement item answers questions such as:

• How many rows did the last UPDATE, INSERT, DELETE, MERGE, or FETCH affect?
• How many warnings or errors are available in the diagnostics area?
• Did Db2 discard any condition records because the diagnostic area was too small?
• After an OPEN or ALLOCATE, is the cursor scrollable, held, rowset-positioned, static, dynamic, sensitive, or insensitive?

IBM documents these items in the Db2 for z/OS GET DIAGNOSTICS statement reference. The target host variable must be compatible with the diagnostic item data type.

Statement Information Items and Data Types

Item	Data type	When to use it
DB2_GET_DIAGNOSTICS_DIAGNOSTICS	VARCHAR(32672)	Returns text about errors or warnings from the GET DIAGNOSTICS statement itself, such as truncation while assigning a diagnostic value.
DB2_LAST_ROW	INTEGER	After a multiple-row FETCH, returns +100 when the last row is in the returned rowset; otherwise it returns zero.
DB2_NUMBER_PARAMETER_MARKERS	INTEGER	After PREPARE, returns the number of parameter markers in the prepared SQL statement.
DB2_NUMBER_RESULT_SETS	INTEGER	After CALL, returns the number of result sets returned by the stored procedure.
DB2_NUMBER_ROWS	DECIMAL(31,0)	After OPEN or FETCH, returns the result-table row count when known. After PREPARE, it can return the estimated result count. For sensitive dynamic cursors, treat it as approximate.
DB2_RETURN_STATUS	INTEGER	After a stored procedure CALL, returns the procedure status when the procedure uses a RETURN statement.
DB2_SQL_ATTR_CURSOR_HOLD	CHAR(1)	After ALLOCATE or OPEN, returns Y when the cursor can remain open across units of work, or N when it cannot.
DB2_SQL_ATTR_CURSOR_ROWSET	CHAR(1)	After ALLOCATE or OPEN, returns Y when the cursor supports rowset positioning, or N for row-positioned operation only.
DB2_SQL_ATTR_CURSOR_SCROLLABLE	CHAR(1)	After ALLOCATE or OPEN, returns Y for a scrollable cursor or N for a forward-only cursor.
DB2_SQL_ATTR_CURSOR_SENSITIVITY	CHAR(1)	After ALLOCATE or OPEN, returns I for insensitive cursor behavior or S for sensitive cursor behavior.
DB2_SQL_ATTR_CURSOR_TYPE	CHAR(1)	After ALLOCATE or OPEN, returns F for forward cursor, S for static cursor, or D for dynamic cursor.
MORE	CHAR(1)	Returns Y when Db2 discarded some warning or error records because the diagnostic area needed too much storage; otherwise returns N.
NUMBER	INTEGER	Returns the number of condition records stored for the previous SQL statement. Use this before looping through CONDITION 1, CONDITION 2, and so on.
ROW_COUNT	DECIMAL(31,0)	Returns rows affected by the previous data-change statement or rows fetched by a multiple-row FETCH. After TRUNCATE or some mass-delete cases, Db2 can return -1.
DB2_SQL_NESTING_LEVEL	INTEGER	Returns the current nesting level for a compiled SQL function, native SQL procedure, or trigger. Outside that nesting context, the value is zero.

COBOL Example: Capture ROW_COUNT After UPDATE

Use ROW_COUNT when the program must log how many rows the last SQL statement changed. In COBOL, define a packed decimal or numeric host variable that can hold DECIMAL(31,0).

01  WS-ROW-COUNT        PIC S9(9) COMP-5.

EXEC SQL
    UPDATE CUSTOMER
       SET STATUS = 'I'
     WHERE LAST_ORDER_DATE < :WS-CUTOFF-DATE
END-EXEC.

IF SQLCODE = 0
   EXEC SQL
       GET DIAGNOSTICS :WS-ROW-COUNT = ROW_COUNT
   END-EXEC
END-IF.

If the update qualifies 250 customer rows, WS-ROW-COUNT receives 250. Do not use this as a substitute for checking SQLCODE; use it after the SQL statement has completed successfully or when your error path expects this item to be available.

COBOL Example: Count Diagnostic Conditions

NUMBER is useful when one SQL statement can return more than one warning or error condition. After reading NUMBER, the program can loop through condition items such as RETURNED_SQLSTATE, DB2_RETURNED_SQLCODE, and MESSAGE_TEXT.

01  WS-DIAG-COUNT       PIC S9(9) COMP-5.
01  WS-DIAG-ID          PIC S9(9) COMP-5.
01  WS-RETURNED-SQLCODE PIC S9(9) COMP-5.
01  WS-MESSAGE-TEXT     PIC X(240).

EXEC SQL
    GET DIAGNOSTICS :WS-DIAG-COUNT = NUMBER
END-EXEC.

PERFORM VARYING WS-DIAG-ID FROM 1 BY 1
        UNTIL WS-DIAG-ID > WS-DIAG-COUNT
   EXEC SQL
       GET DIAGNOSTICS CONDITION :WS-DIAG-ID
           :WS-RETURNED-SQLCODE = DB2_RETURNED_SQLCODE,
           :WS-MESSAGE-TEXT     = MESSAGE_TEXT
   END-EXEC
END-PERFORM.

For basic SQLCODE and SQLSTATE handling, see the SQLCA guide. GET DIAGNOSTICS adds detail, but the program still needs a clear SQL error-handling rule.

Cursor Attribute Items

The cursor attribute items are useful after OPEN when a program needs to confirm cursor behavior. For example, a Db2 browse program might record whether a cursor is scrollable, static or dynamic, rowset-positioned, and sensitive or insensitive.

01  WS-CURSOR-SCROLLABLE PIC X.
01  WS-CURSOR-TYPE       PIC X.
01  WS-CURSOR-SENSITIVE  PIC X.

EXEC SQL
    OPEN C1
END-EXEC.

IF SQLCODE = 0
   EXEC SQL
       GET DIAGNOSTICS
           :WS-CURSOR-SCROLLABLE = DB2_SQL_ATTR_CURSOR_SCROLLABLE,
           :WS-CURSOR-TYPE       = DB2_SQL_ATTR_CURSOR_TYPE,
           :WS-CURSOR-SENSITIVE  = DB2_SQL_ATTR_CURSOR_SENSITIVITY
   END-EXEC
END-IF.

For scrollable cursor design rules, see Db2 Scrollable Cursor Guidelines for COBOL Programs.

Common Mistakes

• Reading ROW_COUNT after another SQL statement has already run. The diagnostics area belongs to the previous eligible SQL statement.
• Moving straight to CONDITION 2 without checking NUMBER.
• Using a short character host variable for DB2_GET_DIAGNOSTICS_DIAGNOSTICS and then ignoring truncation.
• Assuming DB2_NUMBER_ROWS is exact for every cursor. Sensitive dynamic cursors can make this value approximate.
• Defining all targets as PIC X. Numeric items such as ROW_COUNT, NUMBER, and DB2_NUMBER_ROWS need compatible numeric host variables.

FAQ

Is ROW_COUNT the same as SQLERRD(3)?

They can overlap for common row-count cases, but GET DIAGNOSTICS ROW_COUNT is the direct statement-information item. Use your shop standard consistently and test the specific SQL statement type.

When should I use NUMBER?

Use NUMBER before reading condition information. It tells the program how many diagnostic condition records are available for the previous SQL statement.

Can GET DIAGNOSTICS be dynamically prepared?

No. In Db2 for z/OS, GET DIAGNOSTICS is an executable statement for embedded applications, and it cannot be dynamically prepared.

Keep the call close to the SQL statement you are diagnosing. If another SQL statement runs first, the diagnostic area may no longer describe the statement you meant to inspect.