GET DIAGNOSTICS
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
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization
None required.
Syntax
.-CURRENT-----. >>-GET -+-+---------+-+--DIAGNOSTICS----------------------------> '-STACKED-' >--+-| statement-information |-+------------------------------->< +-| condition-information |-+ '-| combined-information |--'
statement-information:
statement-information .-,-------------------------------------------------. V | |--+---variable1--=--| statement-information-item-name |-+-+----| +-variable1--=--DB2_GET_DIAGNOSTICS_DIAGNOSTICS---------+ '-variable1--=--DB2_SQL_NESTING_LEVEL-------------------' statement-information-item-name .-,-----------------------------------. V | |----+-DB2_LAST_ROW--------------------+-+----------------------| +-DB2_NUMBER_PARAMETER_MARKERS----+ +-DB2_NUMBER_RESULT_SETS----------+ +-DB2_NUMBER_ROWS-----------------+ +-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-information |--CONDITION--+-variable2-+-------------------------------------> '-integer---' .-,-------------------------------------------------------. V | >----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_LINE_NUMBER------------+ +-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:
combined-information .-,-------------------------------------. V (1) | |--variable4--=--ALL----+-STATEMENT-------------------------+-+--| | (2) | '-+-CONDITION--+------+-----------+-' '-CONNECTION-' +-variable5-+ '-integer---'
- STATEMENT can only be specified once.
- CONDITION and CONNECTION can only be specified once if variable5 or integer is not also specified.
Description
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 a native SQL procedure and compiled SQL functions. 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 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 or compiled SQL function. 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 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.
- 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
- 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 forDB2 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 forDB2 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 & VM
- QSQLDS/VSE for DB2 Server for VSE & 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 condition, as shown in the following example:
CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN10015;
- 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
- Effect of the statement in a native SQL routine:
- The 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:
- The GET DIAGNOSTICS statement does not change the value of the SQLSTATE and SQLCODE SQL variables.
- Data types for 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.Data types for GET DIAGNOSTICS items
Table 1. Data types for GET DIAGNOSTICS items Type of information 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) Statement Information DB2_SQL_NESTING_LEVEL INTEGER Condition Information 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_LINE_NUMBER 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_SQLERRD1 INTEGER DB2_SQLERRD2 INTEGER DB2_SQLERRD3 INTEGER DB2_SQLERRD4 INTEGER DB2_SQLERRD5 INTEGER DB2_SQLERRD6 INTEGER 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_NAME CHAR(128) Combined Information ALL VARCHAR(32672) - 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
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.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.EXEC SQL GET DIAGNOSTICS CONDITION :HV_PRODUCT_ID = DB2_PRODUCT_ID;
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.
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