Saturday, 24 August 2013

DB2 GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement can be used to return diagnostic information about the last SQL statement that was executed. Requests can be made for all diagnostic items or individual items of diagnostic information from the following categories:
  • Statement items, which contain information about the SQL statement as a whole
  • Condition items, which contain information about each error or warning that occurred during the execution of the SQL statement
  • Connection items, which contain information about the SQL statement if it was a CONNECT statement
The GET DIAGNOSTICS statement can be used to handle multiple SQL errors that might result from the execution of a single SQL statement. First, check SQLSTATE or SQLCODE to determine whether diagnostic information should be retrieved by using GET DIAGNOSTICS. This method is especially useful for diagnosing problems that result from a multiple-row INSERT that is specified as NOT ATOMIC CONTINUE ON SQLEXCEPTION.
Even if the GET DIAGNOSTICS statement is used in the application program to check for conditions, instructions must be included to use the SQLCA, or SQLSTATE or SQLCODE must be declared separately in the program.
When the GET DIAGNOSTICS statement is used, the requested diagnostic information is assigned to host variables. Declare each target host variable with a data type that is compatible with the data type of the requested item. Then, in order to retrieve condition information, the number of condition items must first be retrieved.
      EXEC SQL
          GET DIAGNOSTICS :rcount = ROW_COUNT
      END-SQL.


Created with Artisteer

No comments:

Post a comment