Retrieves information about the execution of the previous SQL statement from the SQL diagnostics area. The diagnostics area is a data structure that contains information about the execution status of the most recent SQL statement. Specifically, GET DIAGNOSTICS extracts information about the SQL statement as a whole from the SQL diagnostics area's header component.

Note: The GET DIAGNOSTICS EXCEPTION number extracts detail information.

Syntax

GET DIAGNOSTICS
  :param = header_info_item[ , :param = header_info_item] , ... ;

Parameters

param

A host‑language variable to receive the information returned by the GET DIAGNOSTICS statement. The host‑language program must declare a param compatible with the SQL data type of the information item.

header_info_item

One of the following keywords, which returns associated information about the diagnostics area or the SQL statement:

NUMBER  |  MORE  |  COMMAND_FUNCTION  |  DYNAMIC_FUNCTION  |  ROW_COUNT
NUMBER

The number of detail areas in the diagnostics area. Currently, NUMBER is always 1. NUMBER is type NUMERIC with a scale of 0.

MORE

A one-character string with a value of Y (all conditions are detailed in the diagnostics area) or N (all conditions are not detailed) that tells whether the diagnostics area contains information on all the conditions resulting from the statement.

COMMAND_FUNCTION

Contains the character-string code for the statement (as specified in the SQL standard), if the statements is a static SQL statement. If the statement is a dynamic statement, contains the character string EXECUTE or EXECUTE IMMEDIATE.

DYNAMIC_FUNCTION

Contains the character-string code for the statement (as specified in the SQL standard). For dynamic SQL statements only (as indicated by EXECUTE or EXECUTE IMMEDIATE in the COMMAND_FUNCTION item).

ROW_COUNT

The number of rows affected by the SQL statement.

Example

The GET DIAGNOSTICS example extracts header information about the last SQL statement executed. The information is assigned to host variables that are defined in the DECLARE SECTION of an embedded SQL program, as shown in the following example:

GET DIAGNOSTICS :num = NUMBER, :cmdfunc = COMMAND_FUNCTION ;

The GET DIAGNOSTICS statement itself does not affect the contents of the diagnostics area. This means applications can issue multiple GET DIAGNOSTICS statements to retrieve different items of information about the same SQL statement.