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.CONTAINED_ROLES	Table function
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.CONTAINED_ROLES diagnostic table function
The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function returns all the roles contained within the specified role.
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.
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.
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.
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.
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
The SYSCS_DIAG.STATEMENT_CACHE diagnostic table shows the contents of the SQL 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.
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table
The SYSCS_DIAG.TRANSACTION_TABLE diagnostic table shows all of the transactions that are currently in the database.

Parent topic: SQL language reference

Related concepts

SQL identifiers

Statements

Built-in system functions

Built-in system procedures

Data types

Related reference

Capitalization and special characters