Notes

Effect of statement: The GET DIAGNOSTICS statement does not change the contents of the diagnostics area or the SQLCA. If an SQLSTATE or SQLCODE special variable is declared in an SQL procedure, SQL function, or SQL trigger, these are set to the SQLSTATE or SQLCODE returned from issuing the GET DIAGNOSTICS statement.

If the GET DIAGNOSTICS statement is specified in an SQL function, SQL procedure, or trigger, the GET DIAGNOSTICS statement must be the first executable statement specified in the handler that will handle the error.

If information is desired about a warning,

Case of return values: Values for identifiers in returned diagnostic items are not delimited and are case sensitive. For example, a table name of "abc" would be returned, simply as abc.

Data types for items: The following table shows, the SQL data type for each diagnostic item. When a diagnostic item is assigned to a variable, the variable must be compatible with the data type of the diagnostic item.

Table 58. Data Types for GET DIAGNOSTICS Items
Item Name Data Type
Statement Information Item
COMMAND_FUNCTION VARCHAR(128)
COMMAND_FUNCTION_CODE INTEGER
DB2_DIAGNOSTIC_CONVERSION_ERROR INTEGER
DB2_GET_DIAGNOSTICS_DIAGNOSTICS VARCHAR(32740)
DB2_LAST_ROW INTEGER
DB2_NUMBER_CONNETIONS INTEGER
DB2_NUMBER_PARAMETER_MARKERS INTEGER
DB2_NUMBER_RESULT_SETS INTEGER
DB2_NUMBER_ROWS DECIMAL(31,0)
DB2_NUMBER_SUCCESSFUL_SUBSTMTS INTEGER
DB2_RELATIVE_COST_ESTIMATE INTEGER
DB2_RETURN_STATUS INTEGER
DB2_ROW_COUNT_SECONDARY DECIMAL(31,0)
DB2_ROW_LENGTH INTEGER
DB2_SQL_ATTR_CONCURRENCY CHAR(1)
DB2_SQL_ATTR_CURSOR_CAPABILITY CHAR(1)
DB2_SQL_ATTR_CURSOR_HOLD CHAR(1)
DB2_SQL_ATTR_CURSOR_ROWSET CHAR(1)
DB2_SQL_ATTR_CURSOR_SCROLLABLE CHAR(1)
DB2_SQL_ATTR_CURSOR_SENSITIVITY CHAR(1)
DB2_SQL_ATTR_CURSOR_TYPE CHAR(1)
DYNAMIC_FUNCTION VARCHAR(128)
DYNAMIC_FUNCTION_CODE INTEGER
MORE CHAR(1)
NUMBER INTEGER
ROW_COUNT DECIMAL(31,0)
TRANSACTION_ACTIVE INTEGER
TRANSACTIONS_COMMITTED INTEGER
TRANSACTIONS_ROLLED_BACK INTEGER
Connection Information Item
CONNECTION_NAME VARCHAR(128)
DB2_AUTHENTICATION_TYPE CHAR(1)
DB2_AUTHORIZATION_ID VARCHAR(128)
DB2_CONNECTION_METHOD CHAR(1)
DB2_CONNECTION_NUMBER INTEGER
DB2_CONNECTION_STATE INTEGER
DB2_CONNECTION_STATUS INTEGER
DB2_CONNECTION_TYPE SMALLINT
DB2_DYN_QUERY_MGMT INTEGER
DB2_ENCRYPTION_TYPE CHAR(1)
DB2_PRODUCT_ID VARCHAR(8)
DB2_SERVER_CLASS_NAME VARCHAR(128)
DB2_SERVER_NAME VARCHAR(128)
Condition Information Item
CATALOG_NAME VARCHAR(128)
CLASS_ORIGIN VARCHAR(128)
COLUMN_NAME VARCHAR(128)
CONDITION_IDENTIFIER VARCHAR(128)
CONDITION_NUMBER INTEGER
CONSTRAINT_CATALOG VARCHAR(128)
CONSTRAINT_NAME VARCHAR(128)
CONSTRAINT_SCHEMA VARCHAR(128)
CURSOR_NAME VARCHAR(128)
DB2_ERROR_CODE1 INTEGER
DB2_ERROR_CODE2 INTEGER
DB2_ERROR_CODE3 INTEGER
DB2_ERROR_CODE4 INTEGER
DB2_INTERNAL_ERROR_POINTER INTEGER
DB2_LINE_NUMBER INTEGER
DB2_MESSAGE_ID CHAR(10)
DB2_MESSAGE_ID1 VARCHAR(7)
DB2_MESSAGE_ID2 VARCHAR(7)
DB2_MESSAGE_KEY INTEGER
DB2_MODULE_DETECTING_ERROR VARCHAR(128)
DB2_NUMBER_FAILING_STATEMENTS INTEGER
DB2_OFFSET INTEGER
DB2_ORDINAL_TOKEN_n VARCHAR(32740)
DB2_PARTITION_NUMBER INTEGER
DB2_REASON_CODE INTEGER
DB2_RETURNED_SQLCODE INTEGER
DB2_ROW_NUMBER INTEGER
DB2_SQLERRD_SET CHAR(1)
DB2_SQLERRD1 INTEGER
DB2_SQLERRD2 INTEGER
DB2_SQLERRD3 INTEGER
DB2_SQLERRD4 INTEGER
DB2_SQLERRD5 INTEGER
DB2_SQLERRD6 INTEGER
DB2_TOKEN_COUNT INTEGER
DB2_TOKEN_STRING VARCHAR(70)
MESSAGE_LENGTH INTEGER
MESSAGE_OCTET_LENGTH INTEGER
MESSAGE_TEXT VARCHAR(32740)
PARAMETER_MODE VARCHAR(5)
PARAMETER_NAME VARCHAR(128)
PARAMETER_ORDINAL_POSITION INTEGER
RETURNED_SQLSTATE CHAR(5)
ROUTINE_CATALOG VARCHAR(128)
ROUTINE_NAME VARCHAR(128)
ROUTINE_SCHEMA VARCHAR(128)
SCHEMA_NAME VARCHAR(128)
SERVER_NAME VARCHAR(128)
SPECIFIC_NAME VARCHAR(128)
SUBCLASS_ORIGIN VARCHAR(128)
TABLE_NAME VARCHAR(128)
TRIGGER_CATALOG VARCHAR(128)
TRIGGER_NAME VARCHAR(128)
TRIGGER_SCHEMA VARCHAR(128)

SQL statement codes and strings: The following table represents the possible values for COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, DYNAMIC_FUNCTION, and DYNAMIC_FUNCTION_CODE diagnostic items.

The values in the following table are assigned by the ISO and ANSI SQL Standard and may change as the standard evolves. Include sqlscds in the include source files in library QSYSINC should be used when referencing these values.

Table 59. SQL Statement Codes and Strings
Type of statement Statement string Statement code
ALLOCATE DESCRIPTOR ALLOCATE DESCRIPTOR 2
ALTER PROCEDURE ALTER ROUTINE 17
ALTER SEQUENCE ALTER SEQUENCE 134
ALTER TABLE ALTER TABLE 4
assignment-statement ASSIGNMENT 5
CALL CALL 7
CASE CASE 86
CLOSE (static SQL) CLOSE CURSOR 9
CLOSE (dynamic SQL) DYNAMIC CLOSE CURSOR 37
COMMENT COMMENT -7
COMMIT COMMIT WORK 11
compound-statement BEGIN END 12
CONNECT CONNECT 13
CREATE ALIAS CREATE ALIAS -8
CREATE DISTINCT TYPE CREATE TYPE 83
CREATE FUNCTION CREATE ROUTINE 14
CREATE INDEX CREATE INDEX -14
CREATE PROCEDURE CREATE ROUTINE 14
CREATE SCHEMA CREATE SCHEMA 64
CREATE SEQUENCE CREATE SEQUENCE 133
CREATE TABLE CREATE TABLE 77
CREATE TRIGGER CREATE TRIGGER 80
CREATE VIEW CREATE VIEW 84
DEALLOCATE DESCRIPTOR DEALLOCATE DESCRIPTOR 15
DECLARE GLOBAL TEMPORARY TABLE DECLARE GLOBAL TEMPORARY TABLE -21
DELETE Positioned (static SQL) DELETE CURSOR 18
DELETE Positioned (dynamic SQL) DYNAMIC DELETE CURSOR 38
DELETE Searched DELETE WHERE 19
DESCRIBE DESCRIBE 20
DESCRIBE TABLE DESCRIBE TABLE -24
DISCONNECT DISCONNECT 22
DROP ALIAS DROP ALIAS -25
DROP DISTINCT TYPE DROP TYPE 35
DROP FUNCTION DROP ROUTINE 30
DROP INDEX DROP INDEX -30
DROP PACKAGE DROP PACKAGE -32
DROP PROCEDURE DROP ROUTINE 30
DROP SCHEMA DROP SCHEMA 31
DROP SEQUENCE DROP SEQUENCE 135
DROP TABLE DROP TABLE 32
DROP TRIGGER DROP TRIGGER 34
DROP VIEW DROP VIEW 36
EXECUTE EXECUTE 44
EXECUTE IMMEDIATE EXECUTE IMMEDIATE 43
FETCH (static SQL) FETCH 45
FETCH (dynamic SQL) DYNAMIC FETCH 39
FOR FOR 46
FREE LOCATOR FREE LOCATOR 98
GET DESCRIPTOR GET DESCRIPTOR 47
GOTO GOTO -37
GRANT (any type) GRANT 48
HOLD LOCATOR HOLD LOCATOR 99
IF IF 88
INSERT INSERT 50
ITERATE ITERATE 102
LABEL LABEL -39
LEAVE LEAVE 89
LOCK TABLE LOCK TABLE -40
LOOP LOOP 90
OPEN (static SQL) OPEN 53
OPEN (dynamic SQL) DYNAMIC OPEN 40
PREPARE PREPARE 56
Prepared DELETE Positioned (dynamic SQL) PREPARABLE DYNAMIC DELETE CURSOR 54
Prepared UPDATE Positioned (dynamic SQL) PREPARABLE DYNAMIC UPDATE CURSOR 55
REFRESH TABLE REFRESH TABLE -41
RELEASE (connection) RELEASE CONNECTION -42
RELEASE SAVEPOINT RELEASE SAVEPOINT 57
RENAME INDEX RENAME INDEX -43
RENAME TABLE RENAME TABLE -44
REPEAT REPEAT 95
RESIGNAL RESIGNAL 91
RETURN RETURN 58
REVOKE (any type) REVOKE 59
ROLLBACK ROLLBACK WORK 62
SAVEPOINT SAVEPOINT 63
SELECT INTO SELECT 65
select-statement (dynamic SQL) SELECT CURSOR 85
SET CONNECTION SET CONNECTION 67
SET CURRENT DEBUG MODE SET CURRENT DEBUG MODE -75
SET CURRENT DEGREE SET CURRENT DEGREE -47
SET DESCRIPTOR SET DESCRIPTOR 70
SET ENCRYPTION PASSWORD SET ENCRYPTION PASSWORD -48
SET PATH SET PATH 69
SET RESULT SETS SET RESULT SETS -64
SET SCHEMA SET SCHEMA 74
SET SESSION AUTHORIZATION SET SESSION AUTHORIZATION 76
SET TRANSACTION SET TRANSACTION 75
SET transition-variable ASSIGNMENT 5
SET variable ASSIGNMENT 5
SIGNAL SIGNAL 92
UPDATE Positioned (static SQL) UPDATE CURSOR 81
UPDATE Positioned (dynamic SQL) DYNAMIC UPDATE CURSOR 42
UPDATE Searched UPDATE WHERE 82
VALUES STANDALONE FULLSELECT -69
VALUES INTO VALUES INTO -66
WHILE WHILE 97
Unrecognized statement a zero length string 0

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used: