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.
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.
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:
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.