This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
Bold text indicates clauses or options that are supported only syntactically.
GET DIAGNOSTICS {statement-information | condition-information | combined-information}
statement-information:
{host-variable1 = statement-information-item-name | host-variable1 = DB2_GET_DIAGNOSTICS_DIAGNOSTICS} [,...]
statement-information-item-name:
{DB2_LAST_ROW | DB2_NUMBER_PARAMETER_MARKERS | DB2_NUMBER_RESULT_SETS |
DB2_RETURN_STATUS | DB2_SQL_ATTR_CURSOR_HOLD |
DB2_SQL_ATTR_CURSOR_ROWSET | DB2_SQL_ATTR_CURSOR_SCROLLABLE |
DB2_SQL_ATTR_CURSOR_SENSITIVITY | DB2_SQL_ATTR_CURSOR_TYPE |
MORE | NUMBER | ROW_COUNT} [,...]
condition-information:
CONDITION {host-variable2 | integer}
{host-variable3 = {condition-information-item-name | connection-information-item-name}} [,...]
condition-information-item-name:
{CATALOG_NAME | CONDITION_NUMBER | CURSOR_NAME | DB2_ERROR_CODE1 |
DB2_ERROR_CODE2 | DB2_ERROR_CODE3 | DB2_ERROR_CODE4 | DB2_INTERNAL_ERROR_POINTER |
DB2_MESSAGE_ID | DB2_MODULE_DETECTING_ERROR | DB2_ORDINAL_TOKEN_n | DB2_REASON_CODE |
DB2_RETURNED_SQLCODE | DB2_ROW_NUMBER | DB2_SQLERRD_SET | DB2_SQLERRD1 | DB2_SQLERRD2 |
DB2_SQLERRD3 | DB2_SQLERRD4 | DB2_SQLERRD5 | DB2_SQLERRD6 | DB2_TOKEN_COUNT | MESSAGE_TEXT |
RETURNED_SQLSTATE | SERVER_NAME}
connection-information-item-name:
{DB2_AUTHENTICATION_TYPE | DB2_AUTHORIZATION_ID | DB2_CONNECTION_STATE |
DB2_CONNECTION_STATUS | DB2_ENCRYPTION_TYPE | DB2_SERVER_CLASS_NAME | DB2_PRODUCT_ID}
combined-information:
host-variable4 = ALL {STATEMENT | {CONDITION | CONNECTION} [host-variable5 | integer]} [,...]
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 UDB for z/OS, see that product's documentation for the diagnostic information that is returned.
Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be the data type as specified in Data types for GET DIAGNOSTICS items.
The host variable is assigned the value of the specified statement information item. If the value is truncated when assigning it to the host 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, then the host 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.
Contains textual information about errors or warnings that may 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.
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.
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.
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.
If the previous SQL statement was an OPEN or a FETCH that caused the size of the result table to be unknown, 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.
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.
For an ALLOCATE or OPEN statement, indicates whether a cursor can be held open across multiple units of work.
Otherwise, a blank is returned.
For an ALLOCATE or OPEN statement, indicates whether or not a cursor can be accesses using rowset positioning.
Otherwise, a blank is returned.
For an ALLOCATE or OPEN statement, indicates whether or not a cursor can be scrolled forward and backward.
Otherwise, a blank is returned.
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.
Otherwise, a blank is returned.
For an ALLOCATE or OPEN statement, indicates the type of cursor, whether a cursor type is forward-only, static, or dynamic.
Otherwise, a blank is returned.
Indicates whether some of the warning and errors from the previous SQL statement were stored or discarded.
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.
Identifies the number of rows associated with the previous SQL statement that was executed.
If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted, or updated by that statement, excluding rows affected by either triggers or referential integrity constraints.
If the previous SQL statement is a multiple-row FETCH, ROW_COUNT identifies the number of rows fetched.
A value of -1 indicates a mass delete from a table in a segmented table space.
Otherwise, or if the server only returns an SQLCA, the value zero is returned.
Assigns the values of the specified condition information to the associated host variables. The host 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 host 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 host 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.
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 host variable specified must be a numeric data type or an error occurs. An indicator variable is not allowed for this host variable. If a value is specified that is less than or equal to zero or greater than the number of available diagnostics, an error occurs.
Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be the data type as specified in Data types for GET DIAGNOSTICS items for the indicated condition-information item.
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.
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.
If the returned SQLSTATE is class 24 (Invalid Cursor State), the name of the cursor is returned. Otherwise, the empty string is returned.
Returns an internal error code (SQLERRD1). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
Returns an internal error code (SQLERRD2). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
Returns an internal error code (SQLERRD3). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
Returns an internal error code (SQLERRD4). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.
For some errors, this is a negative value that is an internal error pointer. Otherwise, the value 0 is returned.
Corresponds to the message that is contained in the MESSAGE_TEXT diagnostic item (for example, DSNT102I or DSNU180I).
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.
Returns the nth token. n must be a value from 1 to 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.
Contains the reason code for errors that have a reason code token in the message text. Otherwise, the value zero is returned.
Returns the number of the row where the condition was encountered, when such information is available and applicable.
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 diagnositics area. Otherwise, a blank is returned.
Returns the value of sqlerrd(1) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
Returns the value of sqlerrd(2) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
Returns the value of sqlerrd(3) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
Returns the value of sqlerrd(4) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
Returns the value of sqlerrd(5) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
Returns the value of sqlerrd(6) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.
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.
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.
Provides information about the last SQL statement executed if it was a CONNECT statement.
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.
Contains the connection state:
Otherwise, or if the server only returns an SQLCA, the value zero is returned.
Contains a value of:
Otherwise, or if the server only returns an SQLCA, the value zero is returned.
For a CONNECT or SET CONNECTION statement, contains one of the following values:
Otherwise, the empty string is returned.
The level of encryption for the connection:
Otherwise, a blank is returned.
Returns a product signature. If the application server is an IBM relational database product, the form is pppvvrrm, where:
For example, if the application server is Version 8 of DB2 UDB for z/OS in new-function mode with the latest maintenance, the value would be 'DSN08015'.
Provides a text representation of all the information gathered about the execution of the SQL statement.
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;
Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be VARCHAR. If the length of host-variable4 is not sufficient to hold the full returned diagnostic string, the string is truncated, a warning is returned, and the GET_DIAGNOSTICS_DIAGNOSITICS item of the diagnostics area is updated with the details of this condition.
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.
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 host-variable5 or integer is supplied after CONDITION, the format is the same as described above for the ALL option. If host-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;
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 host-variable5 or integer is supplied after CONNECTION, the format is the same as described for the ALL option. If host-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 condition, as shown in the following example:
CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN08010;
Identifies the diagnostic for which ALL CONDITION or ALL CONNECTION information is requested. The host variable specified must be a numeric data type or an error occurs. An indicator variable is not allowed for this host variable or 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.
The GET DIAGNOSTICS statement does not change the contents of the diagnostics area (SQLCA). If an SQLSTATE or SQLCODE special variable is declared in the SQL procedure, these are set to the SQLSTATE or SQLCODE returned from issuing the GET DIAGNOSTICS statement. The SQLSTATE and SQLCODE values from before the GET DIAGNOSTICS statement was issued are still available in the diagnostics area by issuing a GET DIAGNOSTICS for RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE.
| Item | Data Type |
|---|---|
| Statement Information | |
| DB2_GET_DIAGNOSTICS_DIAGNOSTICS | VARCHAR(32672) |
| DB2_LAST_ROW | INTEGER |
| DB2_NUMBER_PARAMETER_MARKERS | INTEGER |
| DB2_NUMBER_RESULT_SETS | INTEGER |
| DB2_NUMBER_ROWS | DECIMAL(31,0) |
| DB2_RETURN_STATUS | INTEGER |
| 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) |
| MORE | CHAR(1) |
| NUMBER | INTEGER |
| ROW_COUNT | DECIMAL(31,0) |
| CATALOG NAME | VARCHAR(128) |
| CONDITION_NUMBER | INTEGER |
| 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_MESSAGE_ID | CHAR(10) |
| DB2_MODULE_DETECTING_ERROR | CHAR(8) |
| DB2_ORDINAL_TOKEN_n | VARCHAR(515) |
| DB2_REASON_CODE | INTEGER |
| DB2_RETURNED_SQLCODE | INTEGER |
| DB2_ROW_NUMBER | DECIMAL(31,0) |
| DB2_TOKEN_COUNT | INTEGER |
| MESSAGE_TEXT | VARCHAR(32672) |
| RETURNED_SQLSTATE | CHAR(5) |
| SERVER_NAME | VARCHAR(128) |
| Connection Information | |
| DB2_AUTHENTICATION_TYPE | CHAR(1) |
| DB2_AUTHORIZATION_ID | VARCHAR(128) |
| DB2_CONNECTION_STATE | INTEGER |
| DB2_CONNECTION_STATUS | INTEGER |
| DB2_ENCRYPTION_TYPE | CHAR(1) |
| DB2_PRODUCT_ID | VARCHAR(8) |
| DB2_SERVER_CLASS_NAMED | CHAR(128) |
| ALL | VARCHAR(32672) |
Only if you are using multi-row fetch or insert is there a need to have the extended diagnostic information that is provided by servers that support the Open Group Version 3 DRDA standard.