GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement) that was executed. This diagnostic information is gathered as the previous SQL statement is executed. Some of the information available through the GET DIAGNOSTICS statement is also available in the SQLCA.
Invocation for GET DIAGNOSTICS
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization for GET DIAGNOSTICS
None required.
Syntax for GET DIAGNOSTICS
statement-information:
condition-information:
combined-information:
Description for GET DIAGNOSTICS
Diagnostic information is provided in three main areas: statement information, condition information, and combined information. After the execution of an SQL statement, information about the execution of the statement is provided as statement information, and at least one instance of condition information is provided. The number of instances of the condition information is indicated by the NUMBER item that is available in the statement information. Combined information contains a text representation of all the information gathered about the execution of the SQL statement.
The diagnostic information that is provided is specific to the server. If you are connected to a server other than Db2 for z/OS®, see that product's documentation for the diagnostic information that is returned.
- CURRENT
- Specifies that information is to be returned from the first diagnostics area. It corresponds to the previous SQL statement that was executed that was not a GET DIAGNOSTICS or compound statement. CURRENT is the default.
- STACKED
- Specifies
that information is to be returned from the stacked diagnostics area.
The stacked diagnostics area is only available within a handler in native SQL
procedures, compiled SQL functions, and triggers.
The stacked diagnostics area corresponds to the previous SQL statement (that was not
a GET DIAGNOSTICS or compound statement) that was executed before the handler was entered. If the
GET DIAGNOSTICS statement is the first statement within a handler, the current diagnostics area and
the stacked diagnostics area contain the same diagnostics information. - statement-information
- Provides information about the last SQL statement executed.
- variable1
- Identifies a variable described in the program in accordance with the rules for declaring variables. The data type of the variable must be the data type as specified in Data types for GET DIAGNOSTICS items.
The variable is assigned the value of the specified statement information item. If the value is truncated when assigning it to the variable, a warning is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition. If a DIAGNOSTICS item is not set, the variable is set to a default value, based on its data type: 0 for an exact numeric field, an empty string for a VARCHAR field, and blanks for a CHAR field.
- DB2_GET_DIAGNOSTICS_DIAGNOSTICS
- Contains textual information about errors or warnings that might have occurred in the execution of the GET DIAGNOSTICS statement. The format of the information is similar to what would be returned by a GET DIAGNOSTICS :hv = ALL statement.
- DB2_SQL_NESTING_LEVEL
- Identifies the current level of nesting or recursion that is in effect when the GET DIAGNOSTICS statement was executed. Each level of nesting corresponds to a nested or recursive invocation of a compiled SQL function, native SQL procedure, or trigger. If the GET DIAGNOSTICS statement is executed outside of a level of nesting, the value of zero is returned.
- statement-information-item-name:
-
- DB2_LAST_ROW
- For a multiple-row FETCH statement, contains a value of +100 if
the last row currently in the table is in the set of rows that have
been fetched. For cursors that are not sensitive to updates, there
would be no need to do a subsequent FETCH, because the result would
be an end-of-data indication. For cursors that are sensitive to updates,
a subsequent FETCH may return more data if a row had been inserted
before the FETCH was executed. For statements other than multiple-row
FETCH statements, or for multiple-row FETCH statements that do not
contain the last row, this variable contains the value 0.
An end of data warning might not occur and DB2_LAST_ROW might not contain +100 when the number of rows returned is equal to the number of rows requested and the last row of data returned is the last row of data.
- DB2_NUMBER_PARAMETER_MARKERS
- For a PREPARE statement, contains the number of parameter markers in the prepared statement. Otherwise, or if the server only returns an SQLCA, the value zero is returned.
- DB2_NUMBER_RESULT_SETS
- For a CALL statement, contains the actual number of result sets returned by the procedure. Otherwise, or if the server only returns an SQLCA, the value zero is returned.
- DB2_NUMBER_ROWS
- If the previous SQL statement was an OPEN or a FETCH that caused the size of the result table to be known, returns the number of rows in the result table. For SENSITIVE DYNAMIC cursors, this value can be thought of as an approximation because rows that are inserted and deleted will affect the next retrieval of this value. If the previous SQL statement was a PREPARE statement, returns the estimated number of rows in the result table for the prepared statement. Otherwise, or if the server only returns an SQLCA, the value zero is returned.
- DB2_RETURN_STATUS
- Identifies the status value returned from the stored procedure associated with the previously executed SQL statement, provided that the statement was a CALL statement that invoked a procedure that returns a status. Otherwise, or if the server only returns an SQLCA, the value zero is returned.
- DB2_SQL_ATTR_CURSOR_HOLD
- For an ALLOCATE or OPEN statement, indicates whether a cursor
can be held open across multiple units of work.
- N indicates that this cursor does not remain open across multiple units of work.
- Y indicates that this cursor remains open across multiple units of work.
- DB2_SQL_ATTR_CURSOR_ROWSET
- For an ALLOCATE or OPEN statement, indicates whether or not a
cursor can be accesses using rowset positioning.
- N indicates that this cursor supports only row positioned operations.
- Y indicates that this cursor supports rowset positioned operations.
- DB2_SQL_ATTR_CURSOR_SCROLLABLE
- For an ALLOCATE or OPEN statement, indicates whether or not a
cursor can be scrolled forward and backward.
- N indicates that this cursor is not scrollable.
- Y indicates that this cursor is scrollable.
- DB2_SQL_ATTR_CURSOR_SENSITIVITY
- For an ALLOCATE or OPEN statement, indicates whether or not a
cursor does or does not show updates to cursor rows made by other
connections.
- I indicates insensitive.
- S indicates sensitive.
- DB2_SQL_ATTR_CURSOR_TYPE
- For an ALLOCATE or OPEN statement, indicates the type of cursor,
whether a cursor type is forward-only, static, or dynamic.
- F indicates a forward cursor.
- D indicates a dynamic cursor.
- S indicates a static cursor.
- MORE
- Indicates whether some of the warning and errors from the previous
SQL statement were stored or discarded.
- N indicates that all the warnings and errors from the previous SQL statement are stored in the diagnostic area.
- Y indicates that some of the warnings and errors from the previous SQL statement were discarded because the amount of storage needed to record warnings and errors exceeded 65535 bytes.
- NUMBER
- Returns the number of errors and warnings detected by the
execution of the previous SQL statement, other than a GET DIAGNOSTICS
statement, that have been stored in the diagnostics area. If
the previous SQL statement returned an SQLSTATE of 00000 or no previous
SQL statement has been executed, the number returned is one.
The GET DIAGNOSTICS statement itself may return information via the SQLSTATE parameter, but does not modify the previous contents of the diagnostics area, except for the DB2_GET_DIAGNOSTICS_DIAGNOSTICS item.
- ROW_COUNT
- Identifies the number of rows associated with the previous SQL statement that was executed.
If the previous SQL statement is a DELETE, INSERT, UPDATE, or MERGE statement, ROW_COUNT indicates the number of rows that are qualified to be deleted, inserted, or updated by that statement, excluding rows that are affected by triggers or referential integrity constraints. The count does not include rows that are inserted as a result of processing a FOR PORTION OF clause for in an SQL data change statement.
For the OPEN of a cursor for a SELECT with a data change statement, or a SELECT INTO statement, SQLERRD(3) contains the number of rows affected by the embedded data change statement. The value is 0 if the SQL statement fails, indicating that all changes made in executing the statement canceled.
A value of -1 indicates a mass delete from a table in a segmented table space and the DELETE statement did not include selection criteria, or a truncate operation. If the delete was against a view, then neither the DELETE statement nor the definition of the view included selection criteria.
For a REFRESH TABLE statement, SQLERRD(3) contains the number of rows inserted into the materialized query table.
If the previous SQL statement is a multiple-row FETCH, ROW_COUNT identifies the number of rows fetched.
Otherwise, or if the server only returns an SQLCA, the value zero is returned.
- condition-information
- Assigns the values of the specified condition information to the associated variables. The
variable specified must be of the data type that is compatible with the data type of the specified
diagnostic-ID or an error occurs. If the value of the condition is truncated when assigning it to
the variable, an error occurs. If an indicator variable was provided, the length of the value is
returned in the indicator variable.
If a DIAGNOSTICS item is not set, then the variable is set to a default value, based on the data type of the item. The specific value will be 0 for a numeric field, an empty string for a VARCHAR field, and blanks for a CHAR field.
- variable2 or integer
- Identifies a variable described in the program in accordance with the rules for declaring variables. The value identifies the diagnostic for which information is requested. Each diagnostic that occurs while executing an SQL statement is assigned an integer. The value 1 indicates the first diagnostic, 2 indicates the second diagnostic, and so on. If the value is 1, the diagnostic information that is retrieved corresponds to the condition that is indicated by the SQLSTATE value actually returned by the execution of the previous SQL statement (other than a GET DIAGNOSTICS statement). The variable specified must be an integer data type, or an error occurs. An indicator variable is not allowed when this is a host variable; an error occurs. If a value is specified that is less than or equal to zero, or greater than the number of available diagnostics, an error occurs.
- variable3
- Identifies a variable described in the program in accordance with the rules for declaring variables. The data type of the variable must be the data type as specified in Data types for GET DIAGNOSTICS items for the indicated condition-information item.
- condition-information-item-name
-
- CATALOG_NAME
- If the returned SQLSTATE is any one of the following values, the
constraint that caused the error is a referential, check, or unique
constraint. The location (RDB) name of the server that generated
the condition is returned.
- Class 09 (Triggered Action Exception),
- Class 23 (Integrity Constraint Violation)
- Class 27 (Triggered Data Change Violation)
- 40002 (Transaction Rollback - Integrity Constraint Violation)
- 40004 (Transaction Rollback - Triggered Action Exception)
If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation), the server name of the table that caused the error is returned.
If the returned SQLSTATE is class 44 (WITH CHECK OPTION Violation), the server name of the view that caused the error is returned.
Otherwise, the empty string is returned.
The actual server name may be different than the server name specified, either implicitly or explicitly, on the CONNECT statement because of the use of aliases or synonyms.
- CONDITION_NUMBER
- Returns the number of the diagnostic returned.
- CURSOR_NAME
- If the returned SQLSTATE is class 24 (Invalid Cursor State), the name of the cursor is returned. Otherwise, the empty string is returned.
- DB2_ERROR_CODE1
- Returns an internal error code. Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
- DB2_ERROR_CODE2
- Returns an internal error code. Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
- DB2_ERROR_CODE3
- Returns an internal error code. Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
- DB2_ERROR_CODE4
- Returns an internal error code. Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
- DB2_INTERNAL_ERROR_POINTER
- For some errors, this is a negative value that is an internal error pointer. Otherwise, the value 0 is returned.
- DB2_LINE_NUMBER
- Returns the line number where an error is encountered in parsing a dynamic statement. Also returns the line number where an error is encountered in parsing,
binding, or executing a CREATE or ALTER statement for a native SQL procedure, compiled SQL function,
or trigger. DB2_LINE_NUMBER also returns the line number when a CALL
statement invokes a native SQL procedure and the procedure returns with an error. This information
is not returned for an external SQL procedure.
This value will only be meaningful if the statement source contains new line control characters.
- DB2_MESSAGE_ID
- Corresponds to the message that is contained in the MESSAGE_TEXT diagnostic item (for example, DSNT102I or DSNU180I).
- DB2_MODULE_DETECTING_ERROR
- Returns an identifier indicating which module detected the error. For a SIGNAL statement that is issued from a routine, the value 'ROUTINE' is returned. Otherwise, the string 'DSN ' is returned.
- DB2_ORDINAL_TOKEN_n
- Returns the nth token. n must be a value 1–100. For example, DB2_ORDINAL_TOKEN_1 would return the value of the first token, DB2_ORDINAL_TOKEN_2 the second token, and so on. A numeric value for a token is converted to characters before being returned. If there is no value for the token, or if the server only returns an SQLCA, an empty string is returned.
- DB2_REASON_CODE
- Contains the reason code for errors that have a reason code token in the message text. Otherwise, the value zero is returned.
- DB2_RETURNED_SQLCODE
- Returns the SQLCODE for the specified diagnostic.
- DB2_ROW_NUMBER
- For a statement that involves multiple rows, returns the number of the row where the condition was encountered, when such information is available and applicable. If SQLCODE +1 or +20237 is returned, DB2_ROW_NUMBER returns a value of 0.
- DB2_SQLERRD_SET
- A value of Y indicates that the DB2_SQLERRD1 through DB2_SQLERRD items might be set. These items are set only when communicating with a server that returns the SQLCA SQL communications area and not the new diagnostics area. Otherwise, a blank is returned.
- DB2_SQLERRD1
- Returns the value of sqlerrd(1) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_SQLERRD2
- Returns the value of sqlerrd(2) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_SQLERRD3
- Returns the value of sqlerrd(3) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_SQLERRD4
- Returns the value of sqlerrd(4) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_SQLERRD5
- Returns the value of sqlerrd(5) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_SQLERRD6
- Returns the value of sqlerrd(6) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
- DB2_TOKEN_COUNT
- Returns the number of tokens available for the specified diagnostic ID.
- MESSAGE_TEXT
- Returns the message text that is associated with the SQLCODE. This is the short text, including substituted tokens. The message text does not contain the message number. When the SQLCODE is 0, the empty string is returned, even if the RETURNED_SQLSTATE value indicates a warning condition.
- RETURNED_SQLSTATE
- Returns the SQLSTATE for the specified diagnostic.
- SERVER_NAME
- If the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION statement, returns the name of the server specified in the previous statement is returned. Otherwise, the name of the server where the statement executes is returned.
- connection-information-item-name
- Provides information about the last SQL statement executed if
it was a CONNECT statement.
- DB2_AUTHENTICATION_TYPE
- Contains an authentication type value of:
- ‘S' for a server authentication
- ‘C' for client authentication
- ‘T' for trusted server authentication
- Otherwise, or if the server only returns an SQLCA, a blank is returned
- DB2_AUTHORIZATION_ID
- Authorization ID used by connected server. Because of user ID translation and authorization exits, the local user ID may not be the authorized ID used by the server.
- DB2_CONNECTION_STATE
- Contains the connection state:
- -1 if the connection is unconnected
- 1 if the connection is connected
- DB2_CONNECTION_STATUS
- Contains a value of:
- 1 if committable updates can be performed on the connection for this unit of work
- 2 if no committable updates can be performed on the connection for this unit of work
- DB2_SERVER_CLASS_NAME
- For a CONNECT or SET CONNECTION statement, contains one of the
following values:
- QAS for Db2 for i
- QDB2 for Db2 for z/OS
- QDB2/2 for Db2 for OS/2
- QDB2/6000 for Db2 for AIX®
- QDB2/6000 PE for Db2 for AIX Parallel Edition
- QDB2/AIX64 for Db2 for AIX 64-bit
- QDB2/HPUX for Db2 for HP-UX
- QDB2/HP64 for Db2 for HP-UX 64-bit
- QDB2/LINUX for Db2 for Linux®, UNIX, and Windows
- QDB2/LINUX390 for Db2 for Linux, UNIX, and Windows
- QDB2/LINUXIA64 for Db2 for Linux, UNIX, and Windows
- QDB2/LINUXPPC for Db2 for Linux, UNIX, and Windows
- QDB2/LINUXPPC64 for Db2 for Linux, UNIX, and Windows
- QDB2/LINUXZ64 for Db2 for Linux, UNIX, and Windows
- QDB2/NT for Db2 for Linux, UNIX, and Windows
- QDB2/NT64 for Db2 for Linux, UNIX, and Windows
- QDB2/PTX for Db2 for NUMA-Q®
- QDB2/SCO for Db2 for SCO UnixWare
- QDB2/SGI for Db2 for Silicon Graphics
- QDB2/SNI for Db2 for Siemens Nixdorf
- QDB2/SUN for Db2 for SUN Solaris
- QDB2/SUN64 for Db2 for SUN Solaris 64-bit
- QDB2/Windows 95 for Db2 for Linux, UNIX, and Windows
- QSQLDS/VM for Db2 server for VSE and VM
- QSQLDS/VSE for Db2 server for VSE and VM
- DB2_ENCRYPTION_TYPE
- The level of encryption for the connection:
- A indicates only the authentication tokens (authid and password) are encrypted.
- D indicates all data is encrypted for the connection.
- Otherwise, a blank is returned.
- DB2_PRODUCT_ID
- Returns a product signature. If the application server is an IBM® relational database product, the form is pppvvrrm.
- combined-information
- Provides a text representation of all the information gathered
about the execution of the SQL statement.
- ALL
- Indicates that all diagnostic items that are set for the last SQL statement executed are to be
combined into one string. The format of the string is a semicolon separated list of all of the
available diagnostic information in the form:
item-name[(condition-number)]=value-converted-to-character...;
as shown in the following example:
NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100; - variable4
- Identifies a variable described in the program in accordance with the rules for declaring variables. The data type of the variable must be VARCHAR. If the length of variable4 is not sufficient to hold the full returned diagnostic string, the string is truncated, a warning is returned, and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition.
- STATEMENT
- Indicates that all statement-information-item-name diagnostic items that are set for the last SQL statement executed should be combined into one string. The format is the same as described for the ALL option.
- CONDITION
- Indicates that all condition-information-item-name diagnostic items that are set for the last
SQL statement executed should be combined into one string. If variable5 or
integer is supplied after CONDITION, the format is the same as described above for the ALL option.
If variable5 or integer is not supplied, the format includes a condition number
entry at the beginning of the information for that condition in the form:
CONDITION_NUMBER=x;item-name=value-converted-to-character;... where x is the number of the condition, as shown in the following example:
CONDITION_NUMBER=1;RETURNED_SQLSTATE=02000;RETURNED_SQLCODE=100; CONDITION_NUMBER=2;RETURNED_SQLSTATE=01004; - CONNECTION
- Indicates that all connection-information-item-name diagnostic items that are set for the last
SQL statement executed should be combined into one string. If variable5 or
integer is supplied after CONNECTION, the format is the same as described for the ALL option. If
variable5 or integer is not supplied, then the format includes a condition number
entry at the beginning of the information for that condition in the form: CONNECTION_NUMBER=x;item-name=value-converted-to-character;...
where x is the number of the connection, as shown in the following example:
CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN12015; - variable5 or integer
- Identifies a variable described in the program in accordance with the rules for declaring variables. The value identifies the diagnostic for which ALL CONDITION or ALL CONNECTION information is requested. The variable specified must be an integer data type or an error occurs. An indicator variable is not allowed when this is a host variable; an error occurs. If a value is specified that is less than or equal to zero or greater than the number of available diagnostics, an error occurs.
Notes for GET DIAGNOSTICS
- Effect of the
statement in a native SQL routine or trigger:
- A successful
GET DIAGNOSTICS statement does not change the contents of the diagnostics area, except for
DB2_GET_DIAGNOSTICS_DIAGNOSTICS.
If you want information about an error, the GET DIAGNOSTICS statement must be the first executable statement specified in the handler that will handle the error condition.
If you want information about a warning and a handler will get control for the warning condition, the GET DIAGNOSTICS statement must be the first executable statement specified in that handler.
If you want information about a warning and a handler will not get control for the warning condition, the GET DIAGNOSTICS statement must be the next statement executed after that previous statement.
- Considerations for the SQLSTATE and SQLCODE SQL variables
- A successful GET
DIAGNOSTICS statement does not change the value of the SQLSTATE and SQLCODE SQL variables (as used
in SQL functions and SQL procedures).
- Data types for GET DIAGNOSTICS items:
- When a diagnostic item is assigned to a variable, SQL variable, or SQL parameter, the data type of the target must be compatible with the data type of the requested diagnostic item. The following table summarizes the data types of GET DIAGNOSTICS items.
GET DIAGNOSTICS item Data type Description DB2_GET_DIAGNOSTICS_DIAGNOSTICS VARCHAR(32672) After a GET DIAGNOSTICS statement, all of the diagnostics as a single string if any error or warning occurred DB2_LAST_ROW INTEGER After a multiple-row FETCH statement, the value of +100 if the last row in the table is in the rowset that was returned DB2_NUMBER_PARAMETER_MARKERS INTEGER After a PREPARE statement, the number of parameter markers in the prepared statement DB2_NUMBER_RESULT_SETS INTEGER After a CALL statement that invokes a stored procedure, the number of result sets that are returned by the procedure DB2_NUMBER_ROWS DECIMAL(31,0) After an OPEN or FETCH statement for which the size of the result table is known, the number of rows in the result table
After a PREPARE statement, the estimated number of rows in the result table for the prepared statement
For SENSITIVE DYNAMIC cursors, the approximate number of rows
Otherwise, or if the server only returns an SQLCA, the value zero
DB2_RETURN_STATUS INTEGER After a CALL statement that invokes an SQL procedure, the return status if the procedure contains a RETURN statement DB2_SQL_ATTR_CURSOR_HOLD CHAR(1) After an ALLOCATE or OPEN statement, whether the cursor can be held open across multiple units of work (Y or N) DB2_SQL_ATTR_CURSOR_ROWSET CHAR(1) After an ALLOCATE or OPEN statement, whether the cursor can use rowset positioning (Y or N) DB2_SQL_ATTR_CURSOR_SCROLLABLE CHAR(1) After an ALLOCATE or OPEN statement, whether the cursor is scrollable (Y or N) DB2_SQL_ATTR_CURSOR_SENSITIVITY CHAR(1) After an ALLOCATE or OPEN statement, whether the cursor shows updates made by other processes (sensitivity I or S) DB2_SQL_ATTR_CURSOR_TYPE CHAR(1) After an ALLOCATE or OPEN statement, whether the cursor is forward (F), declared static (S for INSENSITIVE or SENSITIVE STATIC), or dynamic (D for SENSITIVE DYNAMIC). MORE CHAR(1) After any SQL statement, this item indicates whether some conditions items were discarded because of insufficient storage (Y or N). NUMBER INTEGER After any SQL statement, this item contains the number of condition items. If no warning or error occurred, or if no previous SQL statement has been executed, the number that is returned is 1. ROW_COUNT DECIMAL(31,0) After an insert, update, delete, or fetch, this item contains the number of rows that are deleted, inserted, updated, or fetched. After PREPARE, this item contains the estimated number of result rows in the prepared statement. After TRUNCATE, it contains -1. DB2_SQL_NESTING_LEVEL INTEGER After a CALL statement, this item identifies the current level of nesting or recursion in effect when the GET DIAGNOSTICS statement was executed. Each level of nesting corresponds to a nested or recursive invocation of a packaged SQL function, packaged SQL procedure, or trigger. If the GET DIAGNOSTICS statement is executed outside of a level of nesting, the value zero is returned. When an application connects to another server the value is reset to zero. CATALOG_NAME VARCHAR(128) The server name of the table that owns a constraint that caused an error, or that caused an access rule or check violation CONDITION_NUMBER INTEGER The number of the condition CURSOR_NAME VARCHAR(128) The name of a cursor in an invalid cursor state DB2_ERROR_CODE1 INTEGER An internal error code DB2_ERROR_CODE2 INTEGER An internal error code DB2_ERROR_CODE3 INTEGER An internal error code DB2_ERROR_CODE4 INTEGER An internal error code DB2_INTERNAL_ERROR_POINTER INTEGER For some errors, a negative value that is an internal error pointer DB2_LINE_NUMBER INTEGER The line number where an error is encountered in parsing a dynamic statement, or parsing, binding, or executing a CREATE or ALTER statement for a native SQL procedure, compiled SQL function, or trigger The line number when a CALL statement invokes a native SQL procedure and the procedure returns an error
DB2_MESSAGE_ID CHAR(10) The message ID that corresponds to the message that is contained in the MESSAGE_TEXT diagnostic item. DB2_MODULE_DETECTING_ERROR CHAR(8) The module that detected the error DB2_ORDINAL_TOKEN_n VARCHAR(515) The nth token, where n is a value from 1–100 DB2_REASON_CODE INTEGER The reason code for errors that have a reason code token in the message text DB2_RETURNED_SQLCODE INTEGER The SQLCODE for the condition DB2_ROW_NUMBER DECIMAL(31,0) After any SQL statement that involves multiple rows, this item contains the row number on which Db2 detected the condition DB2_SQLERRD1 INTEGER The sqlerrd(1) value from the SQLCA that is returned by the server, or zero DB2_SQLERRD2 INTEGER The sqlerrd(2) value from the SQLCA that is returned by the server, or zero DB2_SQLERRD3 INTEGER The sqlerrd(3) value from the SQLCA that is returned by the server, or zero DB2_SQLERRD4 INTEGER The sqlerrd(4) value from the SQLCA that is returned by the server, or zero DB2_SQLERRD5 INTEGER The sqlerrd(5) value from the SQLCA that is returned by the server, or zero DB2_SQLERRD6 INTEGER The sqlerrd(6) value from the SQLCA that is returned by the server, or zero DB2_TOKEN_COUNT INTEGER The number of tokens available for the condition MESSAGE_TEXT VARCHAR(32672) The message text associated with the SQLCODE RETURNED_SQLSTATE CHAR(5) The SQLSTATE for the condition SERVER_NAME VARCHAR(128) After a CONNECT, DISCONNECT, or SET CONNECTION statement, the name of the server specified in the statement DB2_AUTHENTICATION_TYPE CHAR(1) The authentication type (S, C, D, E, or blank) DB2_AUTHORIZATION_ID VARCHAR(128) The authorization ID that is used by the connected server DB2_CONNECTION_STATE INTEGER Whether the connection is unconnected (-1), local (0), or remote (1) DB2_CONNECTION_STATUS INTEGER Whether updates can be committed for the current unit of work (1 for Yes, 2 for No) DB2_ENCRYPTION_TYPE CHAR(1) The level of encryption for the connection: (A) only the authentication tokens (auth ID and password) are encrypted
(D) all data for the connection is encrypted
DB2_PRODUCT_ID VARCHAR(8) The Db2 product signature DB2_SERVER_CLASS_NAME CHAR(128) After a CONNECT or SET CONNECTION statement, the Db2 server class name ALL VARCHAR(32672) All diagnostic items set for the last SQL statement combined into one string, in the form of a semicolon separated list of all available diagnostic information - DRDA considerations
- The GET DIAGNOSTICS statement is supported from a current Db2 for z/OS client, regardless of the level of the server (a Db2 for z/OS Version 7 or a Db2 for Windows Version 7, for example). When the application is connected to servers that do not support the Open Group Version 3 DRDA standard, the diagnostic information that is returned by the servers is available in the condition information.
- Alternative syntax and synonyms:
- To provide
compatibility with previous releases of Db2
or other products in the Db2 family, Db2 supports the following keywords:
- RETURN_STATUS as a synonym for DB2_RETURN_STATUS
- EXCEPTION as a synonym for CONDITION
Examples for GET DIAGNOSTICS
- Example 1
-
In an application, use the GET DIAGNOSTICS statement to determine how many rows were updated.
long rcount; EXEC SQL UPDATE T1 SET C1 = C1 + 1; EXEC SQL GET DIAGNOSTICS :rcount = ROW_COUNT;After execution of this code segment, rcount will contain the number of rows that were updated.
- Example 2
-
In an application, use the GET DIAGNOSTICS statement to handle multiple SQL Errors.
long numerrors, counter; char retsqlstate[5]; long hva[5]; EXEC SQL INSERT INTO T1 FOR 5 ROWS VALUES (:hva) NOT ATOMIC CONTINUE ON SQLEXCEPTION; EXEC SQL GET DIAGNOSTICS :numerrors = NUMBER; for ( i=1;i < numerrors;i++) { EXEC SQL GET DIAGNOSTICS CONDITION :i :retsqlstate = RETURNED_SQLSTATE; ...Execution of this code segment sets and prints retsqlstate with the SQLSTATE for each error that was encountered in the previous SQL statement.
- Example 3
-
Retrieve information about a connection.
EXEC SQL GET DIAGNOSTICS CONDITION :HV_PRODUCT_ID = DB2_PRODUCT_ID; - Example 4
-
Use the GET DIAGNOSTICS statement to retrieve information that is similar to what is returned in the SQLCA
EXEC SQL GET DIAGNOSTICS CONDITION 1 :dasqlcode = DB2_RETURNED_SQLCODE, :datokencnt = DB2_TOKEN_COUNT, :datoken1 = DB2_ORDINAL_TOKEN_1, :datoken2 = DB2_ORDINAL_TOKEN_2, :datoken3 = DB2_ORDINAL_TOKEN_3, :datoken4 = DB2_ORDINAL_TOKEN_4, :datoken5 = DB2_ORDINAL_TOKEN_5, :dasqlerrd1b = DB2_MESSAGE_ID, :damsgtext = MESSAGE_TEXT, :dasqlerrp = DB2_MODULE_DETECTING_ERROR, :dasqlstate = RETURNED_SQLSTATE; - Example 5
-
:Specify the STACKED keyword on a GET DIAGNOSTICS statement that is used within a handler to access information in the diagnostics area that caused the handler to be activated:
CREATE PROCEDURE divide2 ( IN numerator INTEGER, IN denominator INTEGER, OUT divide_result INTEGER, OUT divide_error VARCHAR(70)) LANGUAGE SQL BEGIN DECLARE msg_text CHAR(70) DEFAULT ''; DECLARE divide_error CHAR(70) DEFAULT ''; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN INSERT .....; -- insert row into a log table -- get diagnostic information for the INSERT statement GET CURRENT DIAGNOSTICS CONDITION 1 msg_text = MESSAGE_TEXT; -- get information about condition that activated the handler GET STACKED DIAGNOSTICS CONDITION 1 divide_error = MESSAGE_TEXT; END; SET divide_result = numerator/denominator; END;The first GET DIAGNOSTICS statement obtains diagnostic information about the INSERT statement.
The second GET DIAGNOSTICS statement specifies the STACKED keyword. The use of the STACKED keyword allows access the stacked diagnostics area which contains the diagnostic information for the condition that caused the handler to be activated. The information about the original condition is still accessible within the handler even after another statement has been issued, such as the INSERT statement in the example.
Example 6: The following application logs information whenever a routine is invoked directly by an application rather than indirectly by another routine. The application uses the GET DIAGNOSTICS statement that specifies DB2_SQL_NESTING_LEVEL to obtain the current nesting level, and invokes the LOG_INVOCATION procedure if the nesting level is 1:CREATE PROCEDURE TEST MODIFIES SQL DATA LANGUAGE SQL BEGIN DECLARE NESTING_LEVEL INT DEFAULT 0; GET DIAGNOSTICS NESTING_LEVEL = DB2_SQL_NESTING_LEVEL; -- -- If routine is invoked at nesting level 1, -- invoke a routine to log the invocation. -- IF (NESTING_LEVEL = 1) THEN CALL LOG_INVOCATION(); END IF; -- -- Remainder of procedure logic -- ... END