GET DIAGNOSTICS

Enables you to get diagnostic information for the last OpenESQL statement executed.

Syntax:

>>---EXEC SQL---GET-.---------.-DIAGNOSTICS-->		    
                    +-CURRENT-+

        +-----------,--------------+
        V                          |
>-----.-.- :hvar1 = --.-ROW_COUNT--.->
      |               |            |
      |               +-NUMBER-----+
      |
      |                       +-----------------,------------------+
      |                       V                                    |  
      +--CONDITION--condition-.-:hvar2 = --.-DB2_RETURNED_SQLCODE--.->
                                           |                       |
                                           +---RETURNED_SQLCODE----+
                                           |                       |
                                           +---RETURNED_SQLSTATE---+
                                           |                       |
                                           +---MESSAGE_TEXT--------+
                                           |                       |
                                           +---DB2_ROW_NUMER-------+
                                           |                       |
                                           +---ROW_NUMBER----------+
                                           |                       |
                                           +---COLUMN_NUMBER-------+
>---END-EXEC---><

Parameters:

hvar1 A host variable to receive a statement-level diagnostic information item.
condition A literal or host variable that specifies the diagnostic record from which diagnostic information is retrieved.
hvar2 A host variable to receive a statement-level diagnostic information item
ROW_COUNT The number of rows affected by the previous statement. For INSERT, UPDATE, or DELETE, this is the number of rows inserted, updated, or deleted, respectively. For FETCH, it is the number of rows returned to the application. This must be retrieved into a numeric host variable, usually a 32-bit integer.
NUMBER The number of diagnostic records available to be queried in subsequent GET DIAGNOSTICS statements. This must be retrieved into a numeric host variable, usually a 32-bit integer.
DB2_RETURNED_SQLCODE The SQLCODE value associated with the diagnostic record. This must be retrieved into a numeric host variable, usually a 32-bit integer.
RETURNED_SQLCODE The SQLCODE value associated with the diagnostic record, which is the same as DB@_RETURNED_SQLCODE. This must be retrieved into a numeric host variable, usually a 32-bit integer.
RETURNED_SQLSTATE The SQLSTATE value associated with the diagnostic record. This must be retrieved into a PIC X(n) host variable, where n can be any size, but is usually 5.
MESSAGE_TEXT The message text associated with the diagnostic record. This must be retrieved into a PIC X(n) host variable, where n can be any size.
DB2_ROW_NUMBER The row number associated with the diagnostic record. This is useful with multi-row operations such as insert statements using parameter arrays and block fetches.
ROW_NUMBER The same as DB2_ROW_NUMBER.
COLUMN_NUMBER The column or parameter number associated with the diagnostic record. This is useful in determining which host variable(s), string truncation, and other warnings apply to.

Comments:

GET DIAGNOSTICS is supported on Windows platforms only, and for ODBC and ADO.NET only.

When an OpenESQL statement is executed, one or more diagnostic records are generated to record diagnostic information about the execution. The SQLCA can return information from only one diagnostic record. You can use GET DIAGNOSTICS to both determine if more than one record was generated, and then to query individual records.

In most cases when a statement executes successfully, only one record is generated. A FETCH statement can potentially generate as many as one record per retrieved column if there are truncation warnings or null values returned with no indicator variable. If a SQL Server stored procedure executes TSQL RAISEERROR statements and COBOL DISPLAY statements in CLR stored procedures, then each of these generates a diagnostic record for the caller.

GET DIAGNOSTICS has no effect on the SQLCA or SQLSTATE, SQLCODE declared by an application.

No diagnostic information is available for execution of GET DIAGNOSTICS statements.

You can use the SQL Error Mapping feature with GET DIAGNOSTICS. See SQL Error Mapping for details.[6]

Example:

The following code displays all the diagnostic records generated after a statement has been executed:
       01  condNum             pic s9(8) comp-5.
       01  retSqlCode          pic s9(8) comp-5.
       01  retMessagetext      pic x(120).
       01  retSqlState         pic x(5).



       exec sql get diagnostics :condCount = number end-exec     
       perform varying condNum from 1 by 1 until condNum > condCount
           exec sql 
               get diagnostics condition :condNum
                   :retSqlState = RETURNED_SQLSTATE,
                   :retSqlCode = DB2_RETURNED_SQLCODE,
                   :retMessageText = MESSAGE_TEXT
           end-exec           
           display retSqlCode  ' ' retsqlState ' ' retMessageText
       end-perform