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. The argument that is passed to this table function should be the name of the role, specified as a string in quotes, or the special keyword CURRENT_ROLE, which indicates the current role in effect. For a definition of role containment, see "Syntax for roles" in GRANT statement.

For example:

SELECT * FROM TABLE (SYSCS_DIAG.CONTAINED_ROLES('READER')) AS T1
SELECT * FROM TABLE (SYSCS_DIAG.CONTAINED_ROLES(CURRENT_ROLE)) AS T2

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.

For example:

SELECT * 
    FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('myderbyerrors.log')) 
    AS T1

Tip: By default Derby log files contain only boot, shutdown, and error messages. 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.

The returned table has the following columns:

Column Name	Type	Length	Nullability	Contents
CONGLOMERATENAME	VARCHAR	128	true	The name of the conglomerate, which is either the table name or the index name. (Unlike the SYSCONGLOMERATES column of the same name, table ID's do not appear here).
ISINDEX	SMALLINT		false	Is not zero if the conglomerate is an index, 0 otherwise.
NUMALLOCATEDPAGES	BIGINT		false	The number of pages actively linked into the table. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
NUMFREEPAGES	BIGINT		false	The number of free pages that belong to the table. When a new page is to be linked into the table the system will move a page from the NUMFREEPAGES list to the NUMALLOCATEDPAGES list. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
NUMUNFILLEDPAGES	BIGINT		false	The number of unfilled pages that belong to the table. Unfilled pages are allocated pages that are not completely full. Note that the number of unfilled pages is an estimate and is not exact. Running the same query twice can give different results on this column.
PAGESIZE	INTEGER		false	The size of the page in bytes for that conglomerate.
ESTIMSPACESAVING	BIGINT		false	The estimated space which could possibly be saved by compressing the conglomerate, in bytes.

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

SELECT T2.*
    FROM 
        SYS.SYSTABLES systabs,
        TABLE (SYSCS_DIAG.SPACE_TABLE(systabs.tablename)) AS T2
    WHERE systabs.tabletype = 'T'

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

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.

For example:

SELECT * 
    FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE')) 
    AS T2

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.

For example:

SELECT * 
    FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION('somederby.log')) 
    AS T1

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.