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:

When recreating an SQL object in a routine, information on the successfully recreated routine is included in this diagnostic information.

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
                        |CONSTRAINT_SCHEMA
                        |CONSTRAINT_NAME
                        |MESSAGE_TEXT
                        |QUERY_NAME
                        |CONDITION_IDENTIFIER}

Operands

NUMBER
This operand is specified to obtain the number of diagnostic information items in the diagnostics area. Specify a data type of SMALLINT.
MORE
This operand is specified to obtain information about whether there is more diagnostic information than can be stored in the diagnostics area.
Specify a data type of CHAR with a length of one byte.
If all of the diagnostic information has been stored in the diagnostics area, this will be set to N, otherwise it will be set to Y.

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 must 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 the error-containing 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 must be specified as INTEGER.
ERROR_SQL
This operand is specified to obtain a character string that indicates the type of the SQL diagnostic information.
Specify a data type of VARCHAR with a length of 32 bytes (64 bytes if character set name UTF16 is specified).
The following table lists the character strings that are set.

Table 6-2 Character strings that are set in ERROR_SQL

Classification (SQL type)Whether character string is setCharacter string that is set
RoutineOther
DefineExecutePre-processExecute
Definition SQLALTER INDEXNNYYALTER INDEX
ALTER PROCEDURENNYYALTER PROCEDURE
ALTER ROUTINENNYYALTER ROUTINE
ALTER TABLENNYYALTER TABLE
ALTER TRIGGERNNYYALTER TRIGGER
COMMENT (annotate a table)NNYYCOMMENT TABLE
COMMENT (annotate a column)NNYYCOMMENT COLUMN
CREATE AUDITNNYYCREATE AUDIT
CREATE CONNECTION SECURITYNNYYCREATE CONNECTION SECURITY
CREATE FUNCTIONNNYYCREATE FUNCTION
CREATE PUBLIC FUNCTIONNNYYCREATE FUNCTION
CREATE INDEXNNYYCREATE INDEX
CREATE PROCEDURENNYYCREATE PROCEDURE
CREATE PUBLIC PROCEDURENNYYCREATE PROCEDURE
CREATE SCHEMANNYYCREATE SCHEMA
CREATE SEQUENCENNYYCREATE SEQUENCE
CREATE TABLENNYYCREATE TABLE
CREATE TRIGGERNNYYCREATE TRIGGER
CREATE TYPENNYYCREATE TYPE
CREATE VIEWNNYYCREATE VIEW
CREATE PUBLIC VIEWNNYYCREATE VIEW
DROP AUDITNNYYDROP AUDIT
DROP CONNECTION SECURITYNNYYDROP CONNECTION SECURITY
DROP DATA TYPENNYYDROP DATA TYPE
DROP FUNCTIONNNYYDROP FUNCTION
DROP PUBLIC FUNCTIONNNYYDROP FUNCTION
DROP INDEXNNYYDROP INDEX
DROP PROCEDURENNYYDROP PROCEDURE
DROP PUBLIC PROCEDURENNYYDROP PROCEDURE
DROP SCHEMANNYYDROP SCHEMA
DROP SEQUENCENNYYDROP SEQUENCE
DROP TABLENNYYDROP TABLE
DROP TRIGGERNNYYDROP TRIGGER
DROP VIEWNNYYDROP VIEW
DROP PUBLIC VIEWNNYYDROP VIEW
GRANT CONNECTNNYYGRANT CONNECT
GRANT DBANNYYGRANT DBA
GRANT RDAREANNYYGRANT RDAREA
GRANT SCHEMANNYYGRANT SCHEMA
GRANT access privilegesNNYYGRANT ACCESS
GRANT AUDITNNYYGRANT AUDIT
REVOKE CONNECTNNYYREVOKE CONNECT
REVOKE DBANNYYREVOKE DBA
REVOKE RDAREANNYYREVOKE RDAREA
REVOKE SCHEMANNYYREVOKE SCHEMA
REVOKE access privilegesNNYYREVOKE ACCESS
Data manipulation SQLALLOCATE CURSOR statementNNNYALLOCATE CURSOR
ASSIGN LIST statementNNYYASSIGN LIST
CALL statementYYYYCALL
CLOSE statementYYNNCLOSE
NNYY(See the SQL types specified in the cursor)
DEALLOCATE PREPARE statementNNNY--
DECLARE CURSORYNNNDECLARE CURSOR
NNYY(See the SQL types for the dynamic SELECT statement)
DELETE statementYYYYDELETE
Preparable dynamic DELETE statement: positioningNNYYDELETE
DESCRIBE statementNNYY(See the SQL types specified in the DESCRIBE statement)
DESCRIBE CURSOR statementNNYY(See the SQL types specified in the DESCRIBE CURSOR statement)
DESCRIBE TYPE statementNNYY(See the SQL types specified in the DESCRIBE TYPE statement)
DROP LIST statementNNYYDROP LIST
EXECUTE statementNNYY(See the SQL types executed by the EXECUTE statement)
EXECUTE IMMEDIATE statementNNYY(See the SQL types executed by the EXECUTE IMMEDIATE statement)
FETCH statementYYNNFETCH
NNYY(See the SQL types specified in the cursor)
FREE LOCATOR statementNNYYFREE LOCATOR
INSERT statementYYYYINSERT
OPEN statementYYNNOPEN
NNYY(See the SQL types specified in the cursor)
PREPARE statementYYYY(See the SQL types specified in the PREPARE statement)
PURGE TABLE statementYYYYPURGE TABLE
Single-line SELECT statementYYYYSELECT
Dynamic SELECT statementNNYYSELECT
UPDATE statementYYYYUPDATE
Preparable dynamic UPDATE statement: positioningNNYYUPDATE
Assignment statementYYYYSET
Control SQLCALL COMMAND statementYYYYCALL COMMAND
COMMIT statementYYYYCOMMIT
CONNECT statementNNNN--
DISCONNECT statementNNNN--
LOCK statementYYYYLOCK TABLE
ROLLBACK statementYYYYROLLBACK
SET SESSION AUTHORIZATION statementNNYYSET SESSION AUTHORIZATION
Embedded language grammarBEGIN DECLARE SECTIONNNNN--
END DECLARE SECTIONNNNN(Not applicable)
ALLOCATE CONNECTION HANDLENNNN--
FREE CONNECTION HANDLENNNN--
DECLARE CONNECTION HANDLE SETNNNN(Not applicable)
DECLARE CONNECTION HANDLE UNSETNNNN(Not applicable)
GET CONNECTION HANDLENNNN--
COPYNNNN(Not applicable)
GET DIAGNOSTICSNNNN--
COMMAND EXECUTENNNN--
SQL prefixNNNN(Not applicable)
SQL terminatorNNNN(Not applicable)
WHENEVERNNNN--
SQLCODE variableNNNN(Not applicable)
SQLSTATE variableNNNN(Not applicable)
PDCNCTHDL type variable declarationNNNN(Not applicable)
INSTALL JARNNYYINSTALL JAR
REPLACE JARNNYYREPLACE JAR
REMOVE JARNNYYREMOVE JAR
INSTALL CLIBNNYYINSTALL CLIB
REPLACE CLIBNNYYREPLACE CLIB
REMOVE CLIBNNYYREMOVE CLIB
DECLARE AUDIT INFO SETNNNN(Not applicable)
Routine control SQLSQL variable declarationYYNNDECLARE
Cursor declarationYNNNDECLARE CURSOR
YYNNSELECT
Condition declarationYNNNDECLARE CONDITION
Handler declarationYNNNDECLARE HANDLER
SQL procedure statementYYNN(See the SQL types specified in the SQL procedure statement)
Compound statementYYNNBEGIN
IF statementYYNNIF
LEAVE statementYYNNLEAVE
RETURN statementYYNNRETURN
WHILE statementYYNNWHILE
FOR statementYYNNFOR
WRITE LINE statementYYNNWRITE LINE
SIGNAL statementYYNNSIGNAL
RESIGNAL statementYYNNRESIGNAL
Other than the aboveYYYYSingle-byte space
Legend
Y: Character string in the Whether character string is set columns is set.
N: Character string is not set.
--: No character string to set.
Parentheses indicate a comment.

ROUTINE_TYPE
This operand is specified to acquire the type of the error-containing function or procedure. Specify a data type of CHAR with a length of one byte (or two bytes if character set name UTF16 is specified). 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-containing function or procedure. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
In case of the following errors, the authorization identifier is set to PUBLIC.
  • Error when deleting a public function or public procedure definition
  • Error when executing a CALL statement to a public procedure
ROUTINE_NAME
This operand is specified to acquire the identifier of the error-containing function or procedure. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
TRIGGER_SCHEMA
This operand is specified to acquire the authorization identifier of the error-containing trigger. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
TRIGGER_NAME
This operand is specified to obtain the authorization identifier of the error-containing trigger. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
CONSTRAINT_SCHEMA
This operand is specified to obtain the authorization identifier of the error-containing constraint if the error was caused by a check constraint violation or referential constraint violation. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
CONSTRAINT_NAME
This operand is specified to obtain the name of the error-containing constraint if the error was caused by a check constraint violation or referential constraint violation. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
MESSAGE_TEXT
This operand is specified to obtain the message text. Specify a data type of VARCHAR with a length of 254 bytes (or 508 bytes if character set name UTF16 is specified).
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. Specify a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
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 a data type of VARCHAR with a length of 30 bytes (or 60 bytes if character set name UTF16 is specified).
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. Only the results of the most recently executed SQL statement can be obtained. For details about which SQL statements return results, see Table 6-2.
  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. 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.
  7. If no information is provided in the explanation of the condition details name of an SQL, a one-byte blank or 0 is set.
  8. 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: