# 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
SQL clauses
SQL expressions
JOIN operations
SQL queries
Built-in functions