The GET DIAGNOSTICS statement obtains information about
the previous SQL statement that was executed.
Invocation
This statement can only be embedded
in an application program, SQL function, SQL procedure, or trigger.
It cannot be issued interactively. It is an executable statement that
cannot be dynamically prepared. It must not be specified in REXX.
Authorization
None required.
Syntax

.-CURRENT-.
>>-GET--+---------+--DIAGNOSTICS--+-statement-information-+----><
'-STACKED-' +-condition-information-+
'-combined-information--'
statement-information
.-,-------------------------------------------.
V |
|--+---variable-1-- = --statement-information-item-+--+---------|
'-variable-1-- = --DB2_GET_DIAGNOSTICS_DIAGNOSTICS-'
condition-information
|--CONDITION--+-variable-2-+------------------------------------>
'-integer----'
.-,------------------------------------------------.
V |
>----variable-3-- = --+-connection-information-item-+-+---------|
'-condition-information-item--'
combined-information
|--variable-4-- = ---------------------------------------------->
(1)
>--ALL--+----------------------------------------+--------------|
| .-,----------------------------------. |
| V | |
'---+-STATEMENT----------------------+-+-'
'-+-CONDITION--+--+------------+-'
'-CONNECTION-' +-variable-5-+
'-integer----'
Notes:
- STATEMENT can only be specified once. If variable-5 or integer is
not specified, CONDITION and CONNECTION can only be specified once.

statement-information-item
|--+-COMMAND_FUNCTION----------------+--------------------------|
+-COMMAND_FUNCTION_CODE-----------+
+-DB2_DIAGNOSTIC_CONVERSION_ERROR-+
+-DB2_LAST_ROW--------------------+
+-DB2_NUMBER_CONNECTIONS----------+
+-DB2_NUMBER_PARAMETER_MARKERS----+
+-DB2_NUMBER_RESULT_SETS----------+
+-DB2_NUMBER_ROWS-----------------+
+-DB2_NUMBER_SUCCESSFUL_SUBSTMTS--+
+-DB2_RELATIVE_COST_ESTIMATE------+
+-DB2_RETURN_STATUS---------------+
+-DB2_ROW_COUNT_SECONDARY---------+
+-DB2_ROW_LENGTH------------------+
+-DB2_SQL_ATTR_CONCURRENCY--------+
+-DB2_SQL_ATTR_CURSOR_CAPABILITY--+
+-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--------+
+-DYNAMIC_FUNCTION----------------+
+-DYNAMIC_FUNCTION_CODE-----------+
+-MORE----------------------------+
+-NUMBER--------------------------+
+-ROW_COUNT-----------------------+
+-TRANSACTION_ACTIVE--------------+
+-TRANSACTIONS_COMMITTED----------+
'-TRANSACTIONS_ROLLED_BACK--------'
connection-information-item
|--+-CONNECTION_NAME---------+----------------------------------|
+-DB2_AUTHENTICATION_TYPE-+
+-DB2_AUTHORIZATION_ID----+
+-DB2_CONNECTION_METHOD---+
+-DB2_CONNECTION_NUMBER---+
+-DB2_CONNECTION_STATE----+
+-DB2_CONNECTION_STATUS---+
+-DB2_CONNECTION_TYPE-----+
+-DB2_DYN_QUERY_MGMT -----+
+-DB2_ENCRYPTION_TYPE-----+
+-DB2_PRODUCT_ID----------+
+-DB2_SERVER_CLASS_NAME---+
'-DB2_SERVER_NAME---------'

condition-information-item
|--+-CATALOG_NAME------------------+----------------------------|
+-CLASS_ORIGIN------------------+
+-COLUMN_NAME-------------------+
+-CONDITION_IDENTIFIER----------+
+-CONDITION_NUMBER--------------+
+-CONSTRAINT_CATALOG------------+
+-CONSTRAINT_NAME---------------+
+-CONSTRAINT_SCHEMA-------------+
+-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_MESSAGE_ID1---------------+
+-DB2_MESSAGE_ID2---------------+
+-DB2_MESSAGE_KEY---------------+
+-DB2_MODULE_DETECTING_ERROR----+
+-DB2_NUMBER_FAILING_STATEMENTS-+
+-DB2_OFFSET--------------------+
+-DB2_ORDINAL_TOKEN_n-----------+
+-DB2_PARTITION_NUMBER----------+
+-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---------------+
+-DB2_TOKEN_STRING--------------+
+-MESSAGE_LENGTH----------------+
+-MESSAGE_OCTET_LENGTH----------+
+-MESSAGE_TEXT------------------+
+-PARAMETER_MODE----------------+
+-PARAMETER_NAME----------------+
+-PARAMETER_ORDINAL_POSITION----+
+-RETURNED_SQLSTATE-------------+
+-ROUTINE_CATALOG---------------+
+-ROUTINE_NAME------------------+
+-ROUTINE_SCHEMA----------------+
+-SCHEMA_NAME-------------------+
+-SERVER_NAME-------------------+
+-SPECIFIC_NAME-----------------+
+-SUBCLASS_ORIGIN---------------+
+-TABLE_NAME--------------------+
+-TRIGGER_CATALOG---------------+
+-TRIGGER_NAME------------------+
'-TRIGGER_SCHEMA----------------'
Description
- CURRENT or STACKED
- Specifies which diagnostics area to access.
- CURRENT
- Specifies
to access the first diagnostics area. It corresponds to the previous
SQL statement that was executed and that was not a GET DIAGNOSTICS
statement. This is the default.
- STACKED
- Specifies
to access the second diagnostics area. The second diagnostics area
is only available within a handler. It corresponds to the previous
SQL statement that was executed before the handler was entered and
that was not a GET DIAGNOSTICS statement. If the GET DIAGNOSTICS statement
is the first statement within a handler, then the first diagnostics
area and the second diagnostics area contain the same diagnostics
information.
- statement-information
- Returns information about the last SQL statement executed.
- variable–1
- Identifies a variable declared in the program in accordance with
the rules for declaring variables.
It must not be a
global variable.
The data type of the variable must be compatible
with the data type as specified in Table 1 for
the specified condition information item. The variable is assigned
the value of the specified statement information item according to
the retrieval assignment rules described in Retrieval assignment. If the value
is truncated when assigning it to the variable, a warning (SQLSTATE
01004) is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item of the
diagnostics area is updated with the details of this condition. If
a specified diagnostic item does not contain diagnostic information,
then the variable is set to a default value based on its data type:
- 0 for an exact numeric diagnostic item,
- an empty string for a VARCHAR diagnostic item,
- and blanks for a CHAR diagnostic item.
- condition-information
- Returns information about the condition or conditions that occurred
when the last SQL statement was executed.
- CONDITION variable–2 or integer
- 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, then the diagnostic
information retrieved corresponds to the condition 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 declared in the program in accordance with the rules
for declaring
integer
variables.
It
must not be a global variable.
The value specified must not be
less than one or greater than the number of available diagnostics.
- variable–3
- Identifies a variable declared in the program in accordance with
the rules for declaring variables.
It must not be a
global variable.
The data type of the variable must be compatible
with the data type as specified in Table 1 for
the specified condition information item. The variable is assigned
the value of the specified condition information item according to
the retrieval assignment rules described in Retrieval assignment. If the value
is truncated when assigning it to the variable, an error is returned
and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is
updated with the details of this condition. If a specified diagnostic
item does not contain diagnostic information , then the variable is
set to a default value based on its data type:
- 0 for an exact numeric diagnostic item,
- an empty string for a VARCHAR diagnostic item,
- and blanks for a CHAR diagnostic item.
- combined-information
- Returns multiple information items combined into one string.
- variable–4
- Identifies a variable declared in the program in accordance with
the rules for declaring variables.
It must not be a
global variable.
The data type of the variable must be VARCHAR. The
variable is assigned according to the retrieval assignment rules described
in Retrieval assignment. If
the length of variable–4 is not sufficient
to hold the full returned diagnostic string, the string is truncated,
an error is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item of the
diagnostics area is updated with the details of this condition.
- ALL
- Indicates that all diagnostic items that are set for the last
SQL statement executed should 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=character-form-of-the-item-value;
The
character form of a positive numeric value will not contain a leading
plus sign (+) unless the item is DB2_RETURNED_SQLCODE. In this case,
a leading plus sign (+) is added. For example: NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100;
Only
items that contain diagnostic information are included in the string.
- STATEMENT
- Indicates that all statement-information-item diagnostic
items that contain diagnostic information for the last SQL statement
executed should be combined into one string. The format is the same
as described above for ALL.
- CONDITION
- Indicates that condition-information-item diagnostic
items that contain diagnostic information for the last SQL statement
executed should be combined into one string. If variable–5 or integer is
specified, then the format is the same as described above for the
ALL option. If variable–5 or integer is
not specified, then the format includes a condition number entry at
the beginning of the information for that condition in the form:
CONDITION_NUMBER=X;item-name=character-form-of-the-item-value;
where
X is the number of the condition. For example: CONDITION_NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100;
CONDITION_NUMBER=2;RETURNED_SQLSTATE=01004;
- CONNECTION
- Indicates that connection-information-item diagnostic
items that contain diagnostic information for the last SQL statement
executed should be combined into one string. If variable–5 or integer is
specified, then the format is the same as described above for ALL.
If variable–5 or integer is
not specified, then the format includes a connection number entry
at the beginning of the information for that condition in the form:
DB2_CONNECTION_NUMBER=X;item-name=character-form-of-the-item-value;
where
X is the number of the condition. For example: DB2_CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN07010;
- variable–5 or integer
- Identifies the diagnostic for which ALL CONDITION or ALL CONNECTION
information is requested. The variable specified must be declared
in the program in accordance with the rules for declaring
integer
variables.
It must not be
a global variable.
The value specified must not be less than
one or greater than the number of available diagnostics.
statement-information-item
- COMMAND_FUNCTION
- Returns
the name of the previous SQL statement. For information about the
statement string values, see Table 2.
- COMMAND_FUNCTION_CODE
- Returns
an integer that identifies the previous SQL statement. For information
about the statement code values, see Table 2.
- DB2_DIAGNOSTIC_CONVERSION_ERROR
- Returns the value 1 if there was
a conversion error when converting a character data value for one
of the GET DIAGNOSTICS statement values. Otherwise, the value zero
is returned.
- DB2_GET_DIAGNOSTICS_DIAGNOSTICS
- After a GET DIAGNOSTICS statement,
if any errors or warnings occurred during the execution of the GET
DIAGNOSTICS statement, DB2_GET_DIAGNOSTICS_DIAGNOSTICS returns textual
information about these errors or warnings. The format of the information
is similar to what would be returned by a GET DIAGNOSTICS :hv = ALL
statement.
If a request was made for an information item that the
server does not understand, for example, if the server was at a lower DRDA® level than the requesting
client, DB2_GET_DIAGNOSTICS_DIAGNOSTICS returns the text 'Item not
supported:' followed by a comma separated list of item names that
were requested but that the server does not support.
- DB2_LAST_ROW
- For
a multiple-row-fetch statement, a value of +100 may be returned
if the set of rows that have been fetched contains the last row currently
in the table for cursors that are fetching forward, or contains the
first row currently in the table for cursors that are fetching backward.
For cursors that are not sensitive to updates, there would be no need
to do a subsequent FETCH since the result would be an end of data
indication (SQLSTATE 02000). 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. Otherwise, the value zero is returned.
If
the number of rows returned is equal to the number of rows requested,
then an end of data warning may not occur and DB2_LAST_ROW may not
contain +100.
- DB2_NUMBER_CONNECTIONS
- Returns
the number of connections that were made in order to get to the server
that fulfilled the request from the client. Each such connection may
generate a connection information item area which would be available
for the single condition.
- DB2_NUMBER_PARAMETER_MARKERS
- For a PREPARE statement, returns
the number of parameter markers in the prepared statement. Otherwise,
the value zero is returned.
- DB2_NUMBER_RESULT_SETS
- For
a CALL statement, returns the actual number of result sets returned
by the procedure. Otherwise, the value zero is returned.
- DB2_NUMBER_ROWS
If the previous SQL statement was
an OPEN or a FETCH which caused the size of the result table to be
known, returns the number of rows in the result table. For SENSITIVE
cursors, this value can be thought of as an approximation since rows
inserted and deleted will affect the next retrieval of this value.
Otherwise, the value zero is returned.
- DB2_NUMBER_SUCCESSFUL_SUBSTMTS
- For embedded compound
SQL statements, returns a count of the number of successful sub-statements.
Otherwise, the value zero is returned.
- DB2_RELATIVE_COST_ESTIMATE
- For a PREPARE statement, returns
a relative cost estimate of the resources required for every execution.
It does not reflect an estimate of the time required. When preparing
a dynamically defined statement, this value can be used as an indicator
of the relative cost of the prepared statement. The value varies depending
on changes to statistics and can vary between releases of the product.
It is an estimated cost for the access plan chosen by the optimizer.
The value zero is returned if the statement is not a PREPARE statement.
- DB2_RETURN_STATUS
- Identifies the status
value returned from the previous SQL CALL statement. If the previous
statement is not a CALL statement, the value returned has no meaning
and is unpredictable. For more information, see RETURN statement. Otherwise, the value zero is
returned.
For external procedures, if the returned SQLCODE <
0, the SQL_ERROR_CODE1 and DB2_RETURN_STATUS will be set to -1, otherwise
SQL_ERROR_CODE1 and DB2_RETURN_STATUS are set to 0.
- DB2_ROW_COUNT_SECONDARY
- Identifies
the number of rows associated with secondary actions from the previous
SQL statement that was executed. If the previous SQL statement is
a DELETE
or MERGE
, the value is the
total number of rows affected by referential constraints, including
cascaded actions and the processing of triggered SQL statements from
activated triggers. If the previous SQL statement is an INSERT or
an UPDATE, the value is the total number of rows affected as the result
of the processing of triggered SQL statements from activated triggers.
Otherwise, the value zero is returned. If the SQL statement is
run using isolation level No Commit, this value may be zero.
- DB2_ROW_LENGTH
- For
a FETCH statement, returns the length of the row retrieved. Otherwise,
the value zero is returned.
- DB2_SQL_ATTR_CONCURRENCY
- For an OPEN statement, indicates
the concurrency control option of read-only, locking, optimistic using
timestamps, or optimistic using values.
- R indicates read-only.
- L indicates locking.
- T indicates comparing row versions using timestamps or ROWIDs.
- V indicates comparing values.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_CAPABILITY
- For an OPEN statement, indicates
the capability of the cursor, whether a cursor is read-only, deletable,
or updatable.
- R indicates that this cursor can only be used to read.
- D indicates that this cursor can be used to read as well as delete.
- U indicates that this cursor can be used to read, delete as well
as update.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_HOLD
- For an OPEN statement, indicates
whether a cursor can be held open across multiple units of work or
not.
- N indicates that this cursor will not remain open across multiple
units of work.
- Y indicates that this cursor will remain open across multiple
units of work.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_ROWSET
- For an OPEN statement, whether a
cursor can be accessed using rowset positioning or not.
- N indicates that this cursor only supports row positioned operations.
- Y indicates that this cursor supports rowset positioned operations.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_SCROLLABLE
- For an OPEN statement, indicates
whether a cursor can be scrolled forward and backward or not.
- N indicates that this cursor is not scrollable.
- Y indicates that this cursor is scrollable.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_SENSITIVITY
- For an OPEN statement, indicates
whether a cursor does or does not show updates to cursor rows made
by other connections.
- I indicates insensitive.
- P indicates partial sensitivity.
- S indicates sensitive.
- U indicates unspecified.
Otherwise, a blank is returned.
- DB2_SQL_ATTR_CURSOR_TYPE
- For an OPEN statement, indicates
whether a cursor type is dynamic, forward-only, or static.
- D indicates a dynamic cursor.
- F indicates a forward-only cursor.
- S indicates a static cursor.
Otherwise, a blank is returned.
- DYNAMIC_FUNCTION
- Returns
a character string that identifies the type of the SQL-statement being
prepared or executed dynamically. For information about the statement
string values, see Table 2.
- DYNAMIC_FUNCTION_CODE
- Returns
a number that identifies the type of the SQL-statement being prepared
or executed dynamically. For information about the statement code
values, see Table 2.
- MORE
- Indicates
whether more errors were raised than could be handled.
- N indicates that all the errors and warnings from the previous
SQL statement were stored in the diagnostics area.
- Y indicates that more errors and warnings were raised from the
previous SQL statement than there are condition areas in the diagnostics
area.
- 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 success (SQLSTATE 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, REFRESH,
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 MERGE statement, ROW_COUNT identifies the total
number of rows deleted, inserted, and 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. Otherwise, the value zero is
returned.
- TRANSACTION_ACTIVE
- Returns
the value 1 if an SQL transaction is currently active, and 0 if an
SQL transaction is not currently active.
- TRANSACTIONS_COMMITTED
- If
the previous statement was a CALL, returns the number of transactions
that were committed during the execution of the SQL or external procedure.
Otherwise, the value zero is returned.
- TRANSACTIONS_ROLLED_BACK
- If the previous statement was a CALL,
returns the number of transactions that were rolled back during the
execution of the SQL or external procedure. Otherwise, the value zero
is returned.
connection-information-item
- CONNECTION_NAME
- If
the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION,
returns the name of the server specified in the previous statement.
Otherwise, the name of the current connection.
- DB2_AUTHENTICATION_TYPE
- Indicates
the authentication type, whether server or client.
- C for client authentication.
- E for DCE security services authentication.
- S for server authentication.
Otherwise, a blank is returned.
- DB2_AUTHORIZATION_ID
- Returns
the authorization id used by connected server. Because of userid translation
and authorization exits, the local userid may not be the authid used
by the server.
- DB2_CONNECTION_METHOD
- For
a CONNECT or SET CONNECTION statement, returns the connection method.
- D indicates *DUW (Distributed Unit of Work).
- R indicates *RUW (Remote Unit of Work).
- DB2_CONNECTION_NUMBER
- Returns
the number of the connections.
- DB2_CONNECTION_STATE
- Indicates
the connection state, whether connected or not.
- -1 indicates the connection is unconnected.
- 1 indicates the connection is connected.
Otherwise, the value zero is returned.
- DB2_CONNECTION_STATUS
- Indicates
whether commitable update can be performed or not.
- 1 indicates commitable updates can be performed on the connection
for this unit of work.
- 2 indicates no commitable updates can be performed on the connection
for this unit of work.
Otherwise, the value zero is returned.
- DB2_CONNECTION_TYPE
- Indicated
the connection type (either local, remote, or to a driver program)
and whether the conversation is protected or not.
- 1 indicates a connection to a local relational database.
- 2 indicates a connection to a remote relational database with
the conversation unprotected.
- 3 indicates a connection to a remote relational database with
the conversation protected.
- 4 indicates a connection to an application requester driver program.
Otherwise, the value zero is returned.
- DB2_DYN_QUERY_MGMT
- Returns
a value of 1 if DYN_QUERY_MGMT database configuration parameter is
enabled. Otherwise, the value zero is returned.
- DB2_ENCRYPTION_TYPE
- Returns
the level of encryption.
- 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, where:
- ppp identifies the product as follows: ARI for DB2® for VM and VSE, DSN for DB2 for z/OS®,
QSQ for DB2 for
i,
and SQL for all other DB2 products
- vv is a two-digit version identifier such as '04'
- rr is a two-digit release identifier such as '01'
- m is a one-digit modification level such as '0'
For example, if the application server is Version 7 of DB2 for z/OS, the value would be 'DSN07010'. Otherwise,
the empty string is returned.
- DB2_SERVER_CLASS_NAME
- Returns
the server class name. For example, DB2 for z/OS, DB2 for AIX®, DB2 for Windows®, and DB2 for
i.
- DB2_SERVER_NAME
- For
a CONNECT or SET CONNECTION statement, returns the relational database
name. Otherwise, the empty string is returned.
condition-information-item
- CATALOG_NAME
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
and the constraint that caused the error is a referential, check,
or unique constraint, the server name of the table that owns the constraint
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.
- CLASS_ORIGIN
- Returns
'ISO 9075' for those SQLSTATEs whose class is defined by ISO 9075.
Returns 'ISO/IEC 13249' for those SQLSTATEs whose class is defined
by SQL/MM. Returns 'DB2 SQL' for those SQLSTATEs whose class is defined
by IBM DB2 SQL. Returns the value set by user written
code if available. Otherwise, the empty string is returned.
- COLUMN_NAME
- If
the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation)
and the error was caused by an inaccessible column, the name of the
column that caused the error is returned. Otherwise, the empty string
is returned.
- CONDITION_IDENTIFIER
- If
the value of the RETURNED_SQLSTATE corresponds to an unhandled user-defined
exception (SQLSTATE 45000), then the condition name of the user-defined
exception is returned.
- CONDITION_NUMBER
- Returns
the number of the conditions.
- CONSTRAINT_CATALOG
- If
the returned SQLSTATE is:
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
the name of the server that contains the table that contains
the constraint that caused the error is returned. Otherwise, the empty
string is returned.
- CONSTRAINT_NAME
- If
the returned SQLSTATE is:
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
the name of the constraint that caused the error is returned.
Otherwise, the empty string is returned.
- CONSTRAINT_SCHEMA
- If
the returned SQLSTATE is:
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
the name of the schema of the constraint that caused the error
is returned. Otherwise, the empty string is 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, the value zero is returned.
- DB2_ERROR_CODE2
- Returns
an internal error code. Otherwise, the value zero is returned.
- DB2_ERROR_CODE3
- Returns
an internal error code. Otherwise, the value zero is returned.
- DB2_ERROR_CODE4
- Returns
an internal error code. Otherwise, the value zero is returned.
- DB2_INTERNAL_ERROR_POINTER
- For some errors, this will be a negative
value that is an internal error pointer. Otherwise, the value zero
is returned.
- DB2_LINE_NUMBER
- For
a CREATE PROCEDURE for an SQL function, SQL procedure, or SQL trigger
where an error is encountered parsing the SQL procedure body, returns
the line number where the error possibly occurred. Otherwise, the
value zero is returned.
- DB2_MESSAGE_ID
- Returns
the message ID corresponding to the MESSAGE_TEXT.
- DB2_MESSAGE_ID1
- Returns
the underlying IBM i CPF
escape message that originally caused this error. Otherwise, the empty
string is returned.
- DB2_MESSAGE_ID2
- Returns
the underlying IBM i CPD
diagnostic message that originally caused this error. Otherwise, the
empty string is returned.
- DB2_MESSAGE_KEY
- For
a CALL statement, returns the IBM i message key of the
error that caused the procedure to fail. For a trigger error in a
DELETE, INSERT, or UPDATE statement, returns the message key of the
error that was signaled from the trigger program. The IBM i QMHRCVPM API can be
used to return the message description and message data for the message
key. Otherwise, the value zero is returned.
- DB2_MODULE_DETECTING_ERROR
- Returns an identifier indicating
which module detected the error. For a SIGNAL statement issued from
a routine, the value 'ROUTINE' is returned. For other SIGNAL statements,
the value 'PROGRAM' is returned.
- DB2_NUMBER_FAILING_STATEMENTS
- For a NOT ATOMIC embedded compound
SQL statement, returns the number of statements that failed. Otherwise,
the value zero is returned.
- DB2_OFFSET
- For
a CREATE PROCEDURE for an SQL procedure where an error is encountered
parsing the SQL procedure body, returns the offset into the line number
where the error possibly occurred, if available. For an EXECUTE IMMEDIATE
or a PREPARE statement where an error is encountered parsing the source
statement, returns the offset into the source statement where the
error possibly occurred. Otherwise, the value zero 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. A numeric value for a token is converted to character
before being returned. If there is no value for the token, the empty
string is returned.
- DB2_PARTITION_NUMBER
- For
a partitioned database, returns the partition number of the database
partition that encountered the error or warning. If no errors or warnings
were encountered, returns the partition number of the current node.
Otherwise, the value zero is returned.
- DB2_REASON_CODE
- Returns
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
- If
the previous SQL statement is a multiple row insert or a multiple
row fetch, returns the number of the row where the condition was encountered,
when such a value is available and applicable. Otherwise, the value
zero is returned.
- DB2_SQLERRD_SET
- Returns
Y to indicate that the DB2_SQLERRD1 through DB2_SQLERRD6 items may
be set. Otherwise, a blank is returned.
- DB2_SQLERRD1
- Returns
the value of SQLERRD(1) from the SQLCA returned by the server.
- DB2_SQLERRD2
- Returns
the value of SQLERRD(2) from the SQLCA returned by the server.
- DB2_SQLERRD3
- Returns
the value of SQLERRD(3) from the SQLCA returned by the server.
- DB2_SQLERRD4
- Returns
the value of SQLERRD(4) from the SQLCA returned by the server.
- DB2_SQLERRD5
- Returns
the value of SQLERRD(5) from the SQLCA returned by the server.
- DB2_SQLERRD6
- Returns
the value of SQLERRD(6) from the SQLCA returned by the server.
- DB2_TOKEN_COUNT
- Returns
the number of tokens available for the specified diagnostic.
- DB2_TOKEN_STRING
- Returns
a X'FF' delimited string of the tokens for the specified diagnostic.
- MESSAGE_LENGTH
- Identifies
the length (in characters) of the message text of the error, warning,
or successful completion returned from the previous SQL statement
that was executed.
- MESSAGE_OCTET_LENGTH
- Identifies
the length (in bytes) of the message text of the error, warning, or
successful completion returned from the previous SQL statement that
was executed.
- MESSAGE_TEXT
- Identifies
the message text of the error, warning, or successful completion returned
from the previous SQL statement that was executed.
When the SQLCODE
is 0, the empty string is returned, even if the RETURNED_SQLSTATE
value indicates a warning condition.
- PARAMETER_MODE
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
- class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition is related to the ith parameter of the
routine, the parameter mode of the ith parameter is returned.
Otherwise, the empty string is returned.
- PARAMETER_NAME
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
- class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
the condition is related to the ith parameter of the routine,
and a parameter name was specified for the parameter when the routine
was created, the parameter name of the ith parameter is returned.
Otherwise, the empty string is returned.
- PARAMETER_ORDINAL_POSITION
- If the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
- class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition is related to the ith parameter of the
routine, the value of i is returned. Otherwise, the empty string
is returned.
- RETURNED_SQLSTATE
- Returns
the SQLSTATE for the specified diagnostic.
- ROUTINE_CATALOG
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
and the condition is related to the ith parameter of the
routine, or if the returned SQLSTATE is: - class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition was raised as the result of an assignment to
an SQL parameter during an routine invocation, the server name of
the routine is returned. Otherwise, the empty string is returned.
- ROUTINE_NAME
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
and the condition is related to the ith parameter of the
routine, or if the returned SQLSTATE is: - class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition was raised as the result of an assignment to
an SQL parameter during an routine invocation, the name of the routine
is returned. Otherwise, the empty string is returned.
- ROUTINE_SCHEMA
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
and the condition is related to the ith parameter of the
routine, or if the returned SQLSTATE is: - class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition was raised as the result of an assignment to
an SQL parameter during an routine invocation, the schema name of
the routine is returned. Otherwise, the empty string is returned.
- SCHEMA_NAME
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
and the constraint that caused the error is a referential, check,
or unique constraint, the schema name of the table that owns the constraint
is returned. If the returned SQLSTATE is class 42 (Syntax Error
or Access Rule Violation), the schema name of the table that caused
the error is returned.
If the returned SQLSTATE is class 44
(WITH CHECK OPTION Violation), the schema name of the view that caused
the error is returned. Otherwise, the empty string is returned.
- SERVER_NAME
- If
the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION,
the name of the server specified in the previous statement is returned.
Otherwise, the name of the server where the statement executed is
returned.
- SPECIFIC_NAME
- If
the returned SQLSTATE is:
- class 39 (External Routine Invocation Exception), or
- class 38 (External Routine Exception), or
- class 2F (SQL Routine Exception), or
and the condition is related to the ith parameter of the
routine, or if the returned SQLSTATE is: - class 22 (Data Exception), or
- class 23 (Integrity Constraint Violation), or
- class 01 (Warning)
and the condition was raised as the result of an assignment to
an SQL parameter during an routine invocation, the specific name of
the procedure or function is returned. Otherwise, the empty string
is returned.
- SUBCLASS_ORIGIN
- Returns
'ISO 9075' for those SQLSTATEs whose subclass is defined by ISO 9075.
Returns 'ISO/IEC 9579' for those SQLSTATEs whose subclass is defined
by RDA. Returns 'ISO/IEC 13249-1', 'ISO/IEC 13249-2', 'ISO/IEC 13249-3',
'ISO/IEC 13249-4', or 'ISO/IEC 13249-5' for those SQLSTATEs whose
subclass is defined SQL/MM. Returns 'DB2 SQL' for those SQLSTATEs
whose subclass is defined by IBM DB2 SQL. Returns the value set by
user written code if available. Otherwise, the empty string is returned.
- TABLE_NAME
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 23 (Integrity Constraint Violation), or
- class 27 (Triggered Data Change Violation), or
- 40002 (Transaction Rollback - Integrity Constraint Violation),
and the constraint that caused the error is a referential, check,
or unique constraint, the table name that owns the constraint is returned.
If the returned SQLSTATE is class 42 (Syntax Error or Access Rule
Violation), the table name that caused the error is returned.
If
the returned SQLSTATE is class 44 (WITH CHECK OPTION Violation), the
table name that caused the error is returned. Otherwise, the empty
string is returned.
- TRIGGER_CATALOG
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 27 (Triggered Data Change Violation),
the name of the trigger is returned. Otherwise, the empty string
is returned.
- TRIGGER_NAME
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 27 (Triggered Data Change Violation),
the name of the trigger is returned. Otherwise, the empty string
is returned.
- TRIGGER_SCHEMA
- If
the returned SQLSTATE is:
- class 09 (Triggered Action Exception), or
- class 27 (Triggered Data Change Violation),
the schema name of the trigger is returned. Otherwise, the empty
string is returned.
Notes
Considerations
for the diagnostics area: The GET DIAGNOSTICS statement does not
change the contents of the diagnostics area except for DB2_GET_DIAGNOSTICS_DIAGNOSTICS.
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 wanted about a warning,
- If a handler will get control for the warning condition, the GET
DIAGNOSTICS statement must be the first statement specified in that
handler.
- If 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 SQLCODE and SQLSTATE SQL variables: The
GET DIAGNOSTICS statement does not change the value of the SQLSTATE
and SQLCODE SQL variables.
Case of return values: Values for identifiers in
returned diagnostic items are not delimited and are case sensitive.
For example, a table name of "abc" would be returned, simply as abc.
Data types for items: The following table shows,
the SQL data type for each diagnostic item. When a diagnostic item
is assigned to a variable, the variable must be compatible with the
data type of the diagnostic item.
Table 1. Data Types for GET DIAGNOSTICS Items| Item Name |
Data Type |
| Statement
Information Item |
| COMMAND_FUNCTION |
VARCHAR(128) |
| COMMAND_FUNCTION_CODE |
INTEGER |
| DB2_DIAGNOSTIC_CONVERSION_ERROR |
INTEGER |
| DB2_GET_DIAGNOSTICS_DIAGNOSTICS |
VARCHAR(32740) |
| DB2_LAST_ROW |
INTEGER |
| DB2_NUMBER_CONNECTIONS |
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(1000) |
| MESSAGE_LENGTH |
INTEGER |
| MESSAGE_OCTET_LENGTH |
INTEGER |
| MESSAGE_TEXT |
VARCHAR(32740) |
| PARAMETER_MODE |
VARCHAR(5) |
| PARAMETER_NAME |
VARCHAR(128) |
| PARAMETER_ORDINAL_POSITION |
INTEGER |
| RETURNED_SQLSTATE |
CHAR(5) |
| ROUTINE_CATALOG |
VARCHAR(128) |
| ROUTINE_NAME |
VARCHAR(128) |
| ROUTINE_SCHEMA |
VARCHAR(128) |
| SCHEMA_NAME |
VARCHAR(128) |
| SERVER_NAME |
VARCHAR(128) |
| SPECIFIC_NAME |
VARCHAR(128) |
| SUBCLASS_ORIGIN |
VARCHAR(128) |
| TABLE_NAME |
VARCHAR(128) |
| TRIGGER_CATALOG |
VARCHAR(128) |
| TRIGGER_NAME |
VARCHAR(128) |
| TRIGGER_SCHEMA |
VARCHAR(128) |
SQL statement codes and strings: The following
table represents the possible values for COMMAND_FUNCTION, COMMAND_FUNCTION_CODE,
DYNAMIC_FUNCTION, and DYNAMIC_FUNCTION_CODE diagnostic items.
The values in the following table are assigned by the
ISO and ANSI SQL Standard and may change as the standard evolves.
Include sqlscds in the include source files in library QSYSINC
should be used when referencing these values.
Table 2. SQL Statement Codes and Strings| Type of statement |
Statement string |
Statement code |
ALLOCATE CURSOR |
ALLOCATE CURSOR |
1 |
| ALLOCATE DESCRIPTOR |
ALLOCATE DESCRIPTOR |
2 |
| ALTER FUNCTION |
ALTER ROUTINE |
17 |
| ALTER PROCEDURE |
ALTER ROUTINE |
17 |
| ALTER SEQUENCE |
ALTER SEQUENCE |
134 |
| ALTER TABLE |
ALTER TABLE |
4 |
| assignment-statement |
ASSIGNMENT |
5 |
ASSOCIATE LOCATORS |
ASSOCIATE LOCATORS |
–6 |
| 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 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 TYPE |
CREATE TYPE |
83 |
CREATE VARIABLE |
CREATE VARIABLE |
–83 |
| 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 CURSOR |
DESCRIBE CURSOR RESULT SET |
–72 |
DESCRIBE PROCEDURE |
DESCRIBE PROCEDURE |
–23 |
| DESCRIBE TABLE |
DESCRIBE TABLE |
–24 |
| DISCONNECT |
DISCONNECT |
22 |
| DROP ALIAS |
DROP ALIAS |
–25 |
| 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 TYPE |
DROP TYPE |
35 |
DROP VARIABLE |
DROP VARIABLE |
–84 |
DROP XSROBJECT |
DROP XSROBJECT |
–95 |
| 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 |
MERGE |
MERGE |
128 |
| 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 DECFLOAT ROUNDING MODE |
SET CURRENT DECFLOAT ROUNDING MODE |
–82 |
| SET CURRENT DEGREE |
SET CURRENT DEGREE |
–47 |
SET CURRENT IMPLICIT XMLPARSE OPTION |
SET CURRENT IMPLICIT XMLPARSE OPT |
–90 |
| 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:
- The keyword EXCEPTION can be used as a synonym for CONDITION.
- The keyword RETURN_STATUS can be used as a synonym for DB2_RETURN_STATUS.
Example
In an SQL procedure, execute a GET
DIAGNOSTICS statement to determine how many rows were updated.
CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE rcount INTEGER;
UPDATE CORPDATA.PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = deptnbr;
GET DIAGNOSTICS rcount = ROW_COUNT;
/* At this point, rcount contains the number of rows that were updated. */
END
Within an SQL procedure, handle the returned status value
from the invocation of a stored procedure called TRYIT. TRYIT could
use the RETURN statement to explicitly return a status value or a
status value could be implicitly returned by the database manager.
If the procedure is successful, it returns a value of zero.
CREATE PROCEDURE TESTIT ()
LANGUAGE SQL
A1: BEGIN
DECLARE RETVAL INTEGER DEFAULT 0;
...
CALL TRYIT
GET DIAGNOSTICS RETVAL = RETURN_STATUS;
IF RETVAL <> 0 THEN
...
LEAVE A1;
ELSE
...
END IF;
END A1
In an SQL procedure, execute a GET DIAGNOSTICS statement
to retrieve the message text for an error.
CREATE PROCEDURE divide2 ( IN numerator INTEGER,
IN denominator INTEGER,
OUT divide_result INTEGER,
OUT divide_error VARCHAR(70) )
LANGUAGE SQL
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS CONDITION 1
divide_error = MESSAGE_TEXT;
SET divide_result = numerator / denominator;
END;