SYSCS_DIAG diagnostic tables and functions

Derby provides
a set of system table expressions which you can use to obtain diagnostic information
about the state of the database and about the database sessions.

There are two types of diagnostic table expressions in Derby:

Diagnostic tables

Tables that are like any other table in Derby.
You can specify the diagnostic table name anywhere a normal table name is
allowed.

Diagnostic table functions

Functions that are like any other function in Derby.
Diagnostic table functions can accept zero or more arguments, depending on
the table function that you use. You must use the SQL-defined table function
syntax to access these functions.

The following table shows the types and names of the
diagnostic table expressions in Derby.

Table 1. System diagnostic table expressions provided by Derby

Diagnostic table expression

Type of expression

SYSCS_DIAG.ERROR_LOG_READER

Table function

SYSCS_DIAG.ERROR_MESSAGES

Table

SYSCS_DIAG.LOCK_TABLE

Table

SYSCS_DIAG.SPACE_TABLE

Table function

SYSCS_DIAG.STATEMENT_CACHE

Table

SYSCS_DIAG.STATEMENT_DURATION

Table function

SYSCS_DIAG.TRANSACTION_TABLE

Table

Restriction: If you reference a diagnostic table in
a DDL statement or a compression procedure, Derby returns
an exception.

SYSCS_DIAG.ERROR_LOG_READER diagnostic table function

The
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function contains all the useful
SQL statements that are in the derby.log file or a log
file that you specify.

One use of this diagnostic table function is
to determine the active transactions and the SQL statements in those transactions
at a given point in time. For example, if a deadlock or lock timeout occurred
you can find the timestamp (timestampConstant) in the error log.

To
access the SYSCS_DIAG.ERROR_LOG_READER diagnostic table function, you must
use the SQL table function syntax.

For example:

SELECT *
FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER())
AS T1

where T1 is a user-specified table name that is any valid
identifier.

You can specify a log file name as an optional argument
to the SYSCS_DIAG.ERROR_LOG_READER diagnostic table function. When you specify
a log file name, the file name must be an expression whose data type maps
to a Java string.

Tip: By default Derby log
files contain only boot, shutdown, and error messages. In the Tuning Derby guide,
see the derby.stream.error.logSeverityLevel property
and the derby.language.logStatementText property for
instructions on how to print more information to Derby log
files. You can then query that information by using the SYSCS_DIAG.ERROR_LOG_READER
diagnostic table function.

SYSCS_DIAG.ERROR_MESSAGES diagnostic table

The SYSCS_DIAG.ERROR_MESSAGES
diagnostic table shows all of the SQLStates, locale-sensitive error messages,
and exception severities for a Derby database.
You can reference the SYSCS_DIAG.ERROR_MESSAGES diagnostic table directly
in a statement.

For example:

SELECT * FROM SYSCS_DIAG.ERROR_MESSAGES

SYSCS_DIAG.LOCK_TABLE diagnostic table

The SYSCS_DIAG.LOCK_TABLE
diagnostic table shows all of the locks that are currently held in the Derby database. You can reference
the SYSCS_DIAG.LOCK_TABLE diagnostic table directly in a statement.

For
example:

SELECT * FROM SYSCS_DIAG.LOCK_TABLE

When
the SYSCS_DIAG.LOCK_TABLE diagnostic table is referenced in a statement, a
snap shot of the lock table is taken. A snap shot is used so that referencing
the diagnostic table does not alter the normal timing and flow of the application.
It is possible that some locks will be in a transition state when the snap
shot is taken.

SYSCS_DIAG.SPACE_TABLE diagnostic table function

The
SYSCS_DIAG.SPACE_TABLE diagnostic table function shows the space usage of
a particular table and its indexes. You can use this diagnostic table function
to determine if space might be saved by compressing the table and indexes.

To
access the SYSCS_DIAG.SPACE_TABLE diagnostic table function, you must use
the SQL table function syntax. This diagnostic table function takes two arguments,
the schemaName and the tableName.
The tableName argument is required. If you do not specify
the schemaName, the current schema is used.

For
example, use the following query to return the space usage for all of the
user tables and indexes in the database:

Both the schemaName and
the tableName arguments must be expressions whose data
types map to Java strings. If the schemaName and the tableName are
non-delimited identifiers, you must specify the names in upper case.

SYSCS_DIAG.STATEMENT_CACHE diagnostic table

The
SYSCS_DIAG.STATEMENT_CACHE diagnostic table shows the contents of the SQL
statement cache. You can reference the SYSCS_DIAG.STATEMENT_CACHE diagnostic
table directly in a statement.

For example:

SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE

SYSCS_DIAG.STATEMENT_DURATION diagnostic table function

You
can use the SYSCS_DIAG.STATEMENT_DURATION diagnostic table function to analyze
the execution duration of the useful SQL statements in the derby.log file
or a log file that you specify.

You can also use this diagnostic table
function to get an indication of where the bottlenecks are in the JDBC code
for an application.

To access the SYSCS_DIAG.STATEMENT_DURATION diagnostic
table function, you must use the SQL table function syntax.

For example:

SELECT *
FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION())
AS T1

where T1 is a user-specified table name that is any valid
identifier.

Restriction: For each transaction ID, a row is
not returned for the last statement with that transaction id. Transaction
IDs change within a connection after a commit or rollback, if the transaction
that just ended modified data.

You can specify a log file name as
an optional argument to the SYSCS_DIAG.STATEMENT_DURATION diagnostic table
function. When you specify a log file name, the file name must be an expression
whose data type maps to a Java string.

Tip: By default Derby log
files contain only boot, shutdown, and error messages. In the Tuning Derby guide,
see the derby.stream.error.logSeverityLevel property
and the derby.language.logStatementText property for
instructions on how to print more information to Derby log
files. You can then query that information by using the SYSCS_DIAG.STATEMENT_DURATION
diagnostic table function.

SYSCS_DIAG.TRANSACTION_TABLE diagnostic table

The
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table shows all of the transactions
that are currently in the database. You can reference the SYSCS_DIAG.TRANSACTION_TABLE
diagnostic table directly in a statement.

For example:

SELECT * FROM SYSCS_DIAG.TRANSACTION_TABLE

When
the SYSCS_DIAG.TRANSACTION_TABLE diagnostic table is referenced in a statement,
a snap shot of the transaction table is taken. A snap shot is used so that
referencing the diagnostic table does not alter the normal timing and flow
of the application. It is possible that some transactions will be in a transition
state when the snap shot is taken.