GET DIAGNOSTICS (Retrieve diagnostic information)

Function

If the SQL statement that was executed immediately before is any of the following, this statement acquires relevant error information and diagnostic information from the diagnostic area:

If the preceding SQL statement has executed remote database access, GET DIAGNOSTICS obtains the error information that was returned by the distributed server.

Format

GET DIAGNOSTICS
  {:embedded-variable=statement-information-item-name
      [,:embedded-variable=statement-information-item-name]...
   |EXCEPTION condition-number
      :embedded-variable=each-item-of-condition-name
         [,:embedded-variable=each-item-of-condition-name]...}

statement-information-item-name::={NUMBER | MORE}
each-item-of-condition-name::={RETURNED_SQLCODE
                        |ERROR_POSITION
                        |ERROR_SQL_NO
                        |ERROR_SQL
                        |ROUTINE_TYPE
                        |ROUTINE_SCHEMA
                        |ROUTINE_NAME
                        |TRIGGER_SCHEMA
                        |TRIGGER_NAME
                                                                          |MESSAGE_TEXT
                        |RDNODE_NAME
                        |QUERY_NAME
                        |CONDITION_IDENTIFIER}

Operands

NUMBER
This operand is specified to determine the number of errors in the diagnostics area. The data type of statement-information-item-name should be SMALLINT.
MORE
This operand is specified to determine the results of the comparison between the number of errors that have occurred and the number of errors in the diagnostics area. The data type of statement-information-item-name should be CHAR with a length of 1 byte. A Y is returned if the number of errors that have occurred is greater than the number of errors in the diagnostics area. If these numbers are equal, an N is returned.

Specifies in an embedded variable the ordinal number of diagnostic information to be obtained.

RETURNED_SQLCODE
This operand is specified to obtain the value of the return code (SQLCODE). The INTEGER data type must be specified.
ERROR_POSITION
This operand is specified when acquiring the position of the error in the SQL statement if a syntax error occurs. The data type should be specified as INTEGER. If an error other than a syntax error has occurred, the value 0 is set.
ERROR_SQL_NO
This operand is specified to acquire a number indicating the type of error-ending SQL procedure statement in the routine when one of the following SQL statements is executed:
  • CREATE PROCEDURE
  • CREATE FUNCTION
  • CREATE TYPE
  • CREATE TRIGGER
  • ALTER PROCEDURE
  • ALTER ROUTINE
  • ALTER TRIGGER
  • CALL statement
  • Data manipulation SQL statement that induces a trigger (only trigger action-related information can be acquired)
The number indicates the position of the SQL statement in the procedure, beginning with 0. The data type should be specified as INTEGER.
ERROR_SQL
This operand is specified to acquire a character string indicating the type of the error-ending SQL procedure statement in the routine when one of the following SQL statements is executed:
  • CREATE PROCEDURE
  • CREATE FUNCTION
  • CREATE TYPE
  • CREATE TRIGGER
  • ALTER PROCEDURE
  • ALTER ROUTINE
  • ALTER TRIGGER
  • CALL statement
  • Data manipulation SQL statement that induces a trigger (only trigger action-related information can be acquired)
Specify the VARCHAR data type with a length of 32 bytes. One of the following character strings is set: BEGIN, SET, WHILE, IF, FOR, LEAVE, OPEN, FETCH, CLOSE, SELECT, INSERT, DELETE, UPDATE, PURGE TABLE, LOCK, COMMIT, ROLLBACK, SIGNAL, RESIGNAL, WRITE LINE, DECLARE, DECLARE CURSOR, CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE, CREATE TRIGGER, RETURN, or CALL.
SELECT is set when a dynamic SELECT statement with a WITH clause specified and a cursor declaration has executed.
ROUTINE_TYPE
This operand is specified to acquire the type of the error-ending function or procedure. The CHAR data type must be specified, with a length of 1 byte. The character P is set for a procedure, and the character F is set for a function.
ROUTINE_SCHEMA
This operand is specified to acquire the authorization identifier of the error-ending function or procedure. The VARCHAR data type must be specified with a length of 30 bytes.
ROUTINE_NAME
This operand is specified to acquire the identifier of the error-ending function or procedure. The VARCHAR data type must be specified, with a length of 30 bytes.
TRIGGER_SCHEMA
This operand is specified to acquire the authorization identifier of the error-ending trigger. Specify the VARCHAR data type with a length of 30 bytes.
TRIGGER_NAME
VARCHAR
CONSTRAINT_SCHEMA
VARCHARCONSTRAINT_NAME
VARCHARMESSAGE_TEXT
This operand is specified to obtain message texts. The VARCHAR data type must be specified, with a length of 254 bytes.
RDNODE_NAME
This operand is specified to determine the name of the RD node at which an error occurred during remote database access. The VARCHAR data type must be specified with a length of 30 bytes.
QUERY_NAME
This operand is specified to obtain the query name of the query specification when an error occurs during execution of a dynamic SELECT statement with a WITH clause specified or a cursor declaration. The VARCHAR data type must be specified with a length of 30 bytes.
CONDITION_IDENTIFIER
This operand is specified to acquire the SIGNAL statement, the condition name specified in the RESIGNAL statement, or the SQLSTATE value that was executed in an SQL procedure or in a trigger. Specify the VARCHAR data type with a length of 30 bytes.
If an SQLSTATE value is specified, 'SQLSTATE:XXXXX' (where XXXXX denotes the specified SQLSTATE value) is set.

Common rules

  1. GET DIAGNOSTICS cannot be executed dynamically.
  2. GET DIAGNOSTICS is effective only on the following SQL statements; it cannot be executed on any other SQL statements:
    • Definition SQL statement
    • Data manipulation SQL statement
    • Control SQL statement
    • An SQL statement that executed remote database access.
  3. The data type of the embedded variable in which a condition number is specified should be SMALLINT.
  4. The embedded variable for receiving statement-information-item or each-item-of-condition should be of the same data type as the respective item.
  5. A value less than 0 or a value greater than the number of errors in the diagnostics area cannot be specified in a condition number.
  6. For details of obtaining error details and taking corrective action when remote database access is performed, see the HiRDB Version 8 UAP Development Guide.
  7. In the case of errors that occurred before SQL analysis, it may not be possible to obtain diagnostic information with GET DIAGNOSTICS. If GET DIAGNOSTICS is executed under such a circumstance, 0 errors are returned. Such error information must be obtained by referring to the SQLCA that was in effect at the time the errors occurred.
  8. If no information is provided in the explanation of the condition details name of an SQL, a one-byte blank or 0 is set.
  9. With respect to errors that may occur during the execution of a function, GET DIAGNOSTICS may fail to acquire any of the following types of diagnostic information:
    • ROUTINE_TYPE
    • ROUTINE_SCHEMA
    • ROUTINE_NAME

Example

Obtain the following diagnostics information for a previously executed CREATE FUNCTION statement: