Extracts diagnostic information about the execution of the
previous SQL statement or SQL routine environment.
The GET DIAGNOSTICS statement captures diagnostic information
from an Oracle Rdb maintained data structure called the
diagnostics area. In the ANSI/ISO SQL standard, the diagnostics
area consists of two components: a single header area and an
array of detail areas. Oracle Rdb extracts information only from
the header component and the first element of the detail area
(Exception 1):
o Header area
Contains status information about rows and transactions, for
example, the number of rows affected by an INSERT, UPDATE, or
DELETE statement or the type of transaction that is active.
See the statement-item-name argument for a complete list of
the status information you can retrieve from the header area.
o Detail area (Exception 1)
Contains diagnostic information that corresponds to the status
that would be reported in the SQLSTATE or SQLCODE status
parameter. The EXCEPTION . . . RETURNED_SQLSTATE argument
retrieves the SQLSTATE status information from the detail
area. The EXCEPTION . . . RETURNED_SQLCODE argument retrieves
the SQLCODE status information from the detail area.
1 – Environment
You can use the GET DIAGNOSTICS statement only within the
compound statement of a multistatement procedure:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a multistatement procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
GET DIAGNOSTICS -------------------------------------------+
+----------------------------------------------------------+
+-+-+-+-> <parameter> +---> = statement-item-name -----+-------------+>
| | +-> <variable> -+ | |
| +---------------- , <------------------------------+ |
+-> EXCEPTION --> 1 -+-+-> <parameter> +> = statement-info-clause ++
| +-> <variable> -+ |
+-------------------- , <--------------------+
statement-item-name =
----+-> ACCESS_MODE -----------------+-->
+-> CALLING_ROUTINE -------------+
+-> CONNECTION_NAME -------------+
+-> CURRENT_ROW -----------------+
+-> DATABASE_HANDLE -------------+
+-> GLOBAL_TRANSACTION ----------+
+-> HOT_STANDBY_MODE ------------+
+-> IMAGE_NAME -----------------+
+-> ISOLATION_LEVEL -------------+
+-> LIMIT_CPU_TIME --------------+
+-> LIMIT_ELAPSED_TIME ----------+
+-> LIMIT_ROWS_FETCHED ----------+
+-> ROW_COUNT -------------------+
+-> SERVER_IDENTIFICATION -------+
+-> TRACE_ENABLED ---------------+
+-> TRANSACTION_ACTIVE ----------+
+-> TRANSACTION_CHANGE_ALLOWED --+
+-> TRANSACTION_SEQUENCE --------+
+-> TRANSACTION_TIMESTAMP -------+
+-> TRANSACTIONS_COMMITTED -----+
+-> TRANSACTIONS_ROLLED_BACK ----+
statement-info-clause =
-+-> RETURNED_SQLSTATE --+--->
+-> RETURNED_SQLCODE ---+
3 – Arguments
3.1 – IMAGE_NAME
Requests that the activating image name be returned to the
caller. The image name includes the node name from which the
attach was started. This might be a node different to that on
which the Oracle Rdb server is running.
The data is returned to the caller as a VARCHAR (255) value and
should be assigned to either a VARCHAR or CHAR data type that
supports the ASCII character set.
3.2 – EXCEPTION
Returns the exception condition following the execution of an
SQL statement (other than GET DIAGNOSTICS). Either SQLCODE or
SQLSTATE can be returned.
3.3 – parameter
Retrieves information about the statement execution recorded
in the diagnostics area and stores it in a simple target
specification (a parameter or variable).
3.4 – RETURNED_SQLCODE
Requests the SQLCODE be returned to the target variable or
parameter. The data type of the returned information is
INTEGER. Oracle Rdb only returns success (0) and warning status
(positive value) for SQLCODE. Any error status will cause the
compound statement, or stored routine to return to the calling
application.
3.5 – RETURNED_SQLSTATE
Requests the SQLSTATE be returned to the target variable or
parameter. The data type of the returned information is CHAR(5).
Oracle Rdb only returns success ('00000') and warning status for
SQLSTATE. Any error status will cause the compound statement, or
stored routine to return to the calling application.
3.6 – statement-item-name
Specifies the kind of diagnostic information you can retrieve
about a previously executed SQL statement. You can gather the
following diagnostic data:
o ACCESS_MODE returns the character string READ ONLY, READ
WRITE, or BATCH UPDATE to indicate the type of transaction
that is active. These character strings are of the CHAR data
type. The argument also returns NONE when no transaction is
active. See the SET_TRANSACTION statement for a description of
transaction access modes.
o CALLING_ROUTINE returns a string of data type CHAR(31) of
the name of the calling routine. If there is no name for the
calling routine, spaces are returned.
o CONNECTION_NAME returns the current connection name.
o DATABASE_HANDLE returns the current database handle (or
stream) identifier.
o CURRENT_ROW returns the integer value for the number of rows
that have been fetched by the innermost FOR control statement.
o GLOBAL_TRANSACTION returns an integer of 1 when a global
transaction is active and an integer of 0 otherwise.
o ISOLATION_LEVEL returns the character string READ COMMITTED,
REPEATABLE READ, or SERIALIZABLE to indicate the isolation
level of a transaction. These character strings are of
the CHAR data type. The argument also returns NONE when no
transaction is active. See the SET_TRANSACTION statement for a
description of transaction isolation
levels.
o LIMIT_CPU_TIME returns an INTEGER value for the session's
execution CPU time limit in seconds. If zero (0)
is returned it is equivalent to no CPU time limit.
This value is established by either the logical name
RDMS$BIND_QG_EXEC_CPU_TIMEOUT or the SET QUERY EXECUTION LIMIT
CPU TIME statement.
o LIMIT_ELAPSED_TIME returns an INTEGER value for the session's
execution elapsed time limit in seconds. If zero (0) is
returned it is equivalent to no elapsed time limit. This value
is established by either the logical name RDMS$BIND_QG_EXEC_
ELAPSED_TIMEOUT or the SET QUERY EXECUTION LIMIT ELAPSED TIME
statement.
o LIMIT_ROWS_FETCHED returns a BIGINT value for the session's
row limit. If zero (0) is returned, it is equivalent to no
row limit. This value is established by the logical name
RDMS$BIND_QG_REC_LIMIT.
o ROW_COUNT returns an integer for the number of rows affected
by an INSERT, searched UPDATE, searched DELETE, or a FOR
cursor loop statement.
o TRACE_ENABLED returns an INTEGER value to indicate if the
TRACE flag has been enabled using the statement SET FLAGS
'TRACE', or by either of the logical names RDMS$SET_FLAGS
or RDMS$DEBUG_FLAGS. A zero (0) is returned if the flag is
disabled, otherwise a one (1) is returned to indicate that
tracing is enabled.
o TRANSACTION_ACTIVE returns an integer of 1 when a transaction
is active and an integer of 0 otherwise.
o TRANSACTIONS_COMMITTED returns an integer value for the number
of transactions that have been committed during the processing
of a multistatement procedure.
o TRANSACTIONS_ROLLED_BACK returns an integer value for the
number of transactions that have been rolled back during the
processing of a multistatement procedure.
o TRANSACTION_CHANGE_ALLOWED
There are many situations where the SQL language programmer
would like to start or end a transaction but does not know if
a transaction statement (SET TRANSACTION, START TRANSACTION,
COMMIT or ROLLBACK) is currently permitted. The transaction
statements are not permitted in the following cases:
o During a multi-database or global transaction. In this case
the transaction must be coordinated by the client, not a
server-based procedure.
o When a BEGIN ATOMIC compound statement is in the outer
scope.
o When a FOR cursor loop is active in an outer scope.
The TRANSACTION_CHANGE_ALLOWED clause allows the programmer
to detect these restricted locations and conditionally execute
a COMMIT, ROLLBACK, START TRANSACTION or SET TRANSACTION as
needed.
The result data type is INTEGER. If transaction changes are
permitted then a value one (1) will be assigned. Otherwise the
result will be zero (0).
o HOT_STANDBY_MODE
This option returns a text string that indicates if this
database is participating in a Hot Standby configuration as
master (returns 'MASTER'), or as standby (returns 'STANDBY'),
or is not in such a configuration (returns 'NONE').
The result data type is CHAR (31).
o SERVER_IDENTIFICATION
This option returns a text string containing the Oracle Rdb
release number. This is useful for log file annotation.
The result data type is CHAR (31).
o TRANSACTION_TIMESTAMP
This option returns the date and time that the last
transaction was started. If a transaction is not active, the
returned date and time may be for a prior transaction.
NOTE
The database server will start transactions when
performing database operations. So, this timestamp may
reflect the time of an internal transaction.
If the default date format is SQL99, this option returns a
value with the data type TIMESTAMP(2), otherwise it returns a
DATE (VMS) data type. The default date format can be changed
using either the SET DIALECT or SET DEFAULT DATE FORMAT
statements, or one of the associated module attributes.
o TRANSACTION_SEQUENCE
This is the transaction sequence number (TSN) assigned to
the most recently started transaction. The TSN is a unique
indicator of database transaction activity, however, please
note that the TSN may be reused in some cases. The TSN
for a READ ONLY transaction reflects the transaction state
which is visible to the transaction and, therefore, it was
previously assigned to a READ WRITE transaction. If a READ
WRITE transaction does not perform database I/O or was rolled
back, that TSN may be reused by a subsequent READ WRITE
transaction.
This option returns a BIGINT data type.
3.7 – variable
Retrieves information about the statement execution recorded
in the diagnostics area and stores it in a simple target
specification (a parameter or variable).
4 – Examples
Example 1: Using a GET DIAGNOSTICS statement to retrieve row
count
PROCEDURE increate_nh (SQLSTATE, :rows_affected INTEGER);
BEGIN ATOMIC
UPDATE salary_history
SET salary_amount = salary_amount * 1.05
WHERE salary_end IS NULL
AND employee_id IN (SELECT employee_id
FROM employees
WHERE state = 'NH' );
GET DIAGNOSTICS :rows_affected = ROW_COUNT;
END;
Example 2: Using RETURNED_SQLSTATE
SQL> DECLARE :Y CHAR(5);
SQL> BEGIN
cont> SET :Y = 'Hello';
cont> GET DIAGNOSTICS EXCEPTION 1 :Y = RETURNED_SQLSTATE;
cont> END;
SQL> PRINT :Y;
Y
00000
SQL>
Example 3: Using RETURNED_SQLCODE
SQL> DECLARE :X INTEGER;
SQL> BEGIN
cont> SET :X = 100;
cont> GET DIAGNOSTICS EXCEPTION 1 :X = RETURNED_SQLCODE;
cont> END;
SQL> PRINT :X;
X
0
Example 4: Returning the current connection name
SQL> CONNECT TO 'ATTACH FILENAME mf_personnel' AS 'my_connection';
SQL> DECLARE :conn_name VARCHAR(20);
SQL> BEGIN
cont> GET DIAGNOSTICS :conn_name = CONNECTION_NAME;
cont> END;
SQL> PRINT :conn_name;
CONN_NAME
my_connection
Example 5: Using the TRANSACTION_TIMESTAMP and TRANSACTION_
SEQUENCE options
SQL> set transaction read write;
SQL> show transaction
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Read Write
Transaction information returned by base system:
a read-write transaction is in progress
- updates have not been performed
- transaction sequence number (TSN) is 0:256
- snapshot space for TSNs less than 0:256 can be reclaimed
- recovery unit journal filename is USER2:[RDM$RUJ]SCRATCH$00018679B3AD.RUJ;1
- session ID number is 8
SQL>
SQL> declare :x date vms;
SQL>
SQL> begin get diagnostics :x = transaction_timestamp; end;
SQL> print :x;
X
27-MAY-1999 22:39:17.02
SQL>
SQL> declare :y bigint;
SQL>
SQL> begin get diagnostics :y = transaction_sequence; end;
SQL> print :y;
Y
256
SQL>
SQL> select current_timestamp from rdb$database;
27-MAY-1999 22:39:18.20
1 row selected
SQL>
SQL> commit;
Example 6: Using the HOT_STANDBY_MODE and SERVER_IDENTIFICATION
options
SQL> set flags 'trace';
SQL> declare :id, :hsmode char(31);
SQL> begin
cont> get diagnostics :id = SERVER_IDENTIFICATION,
cont> :hsmode = HOT_STANDBY_MODE;
cont> trace :id, :hsmode;
cont> end;
~Xt: Oracle Rdb V7.1 NONE
Example 7: Using the LIMIT_CPU_TIME, LIMIT_ROWS_FETCHED, and
LIMIT_ELAPSED_TIME options
SQL> set flags 'trace';
SQL> set query execution limit elapsed time 10 minutes;
SQL> begin
cont> declare :row_limit integer;
cont> declare :elapsed_limit integer;
cont> declare :cpu_limit integer;
cont> get diagnostics
cont> :cpu_limit = LIMIT_CPU_TIME,
cont> :row_limit = LIMIT_ROWS_FETCHED,
cont> :elapsed_limit = LIMIT_ELAPSED_TIME;
cont> trace 'LIMIT_ROWS_FETCHED: ', :row_limit;
cont> trace 'LIMIT_CPU_TIME: ', :cpu_limit;
cont> trace 'LIMIT_ELAPSED_TIME: ', :elapsed_limit;
cont> end;
~Xt: LIMIT_ROWS_FETCHED: 0
~Xt: LIMIT_CPU_TIME: 0
~Xt: LIMIT_ELAPSED_TIME: 600
SQL>
Example 8: Using the TRACE_ENABLED keyword in a compound
statement
SQL> declare :x integer;
SQL> begin
cont> get diagnostics :x = TRACE_ENABLED;
cont> end;
SQL> print :x;
X
0
SQL> set flags 'trace';
SQL> begin
cont> get diagnostics :x = TRACE_ENABLED;
cont> end;
SQL> print :x;
X
1