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:
- Definition SQL statement
- Data manipulation SQL statement
- Control SQL statement
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
- statement-information-item-name
- 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.
- each-item-of-condition-name
- 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
- GET DIAGNOSTICS cannot be executed dynamically.
- 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.
- The data type of the embedded variable in which a condition number is specified should be SMALLINT.
- The embedded variable for receiving statement-information-item or each-item-of-condition should be of the same data type as the respective item.
- 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.
- For details of obtaining error details and taking corrective action when remote database access is performed, see the HiRDB Version 8 UAP Development Guide.
- 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.
- If no information is provided in the explanation of the condition details name of an SQL, a one-byte blank or 0 is set.
- 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:
- Return code (embedded variable XSQLCODE with INTEGER data type)
- Error location in the SQL statement in the event of a syntax error (embedded variable XPOSITION with INTEGER data type) piece
- Number indicating position of an error-generating SQL procedure statement in the routine (embedded variable XSQL_NO with INTEGER data type)
- Character string indicating the error-generating SQL procedure statement in the routine (embedded variable XSQL with VARCHAR(32) data type)
- Message text (embedded variable XMESSAGE with VARCHAR(254) data type)
![[Figure]](figure/zu6s000a.gif)