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.
The following table shows the types and names of the diagnostic table expressions in 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 |
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.
SELECT * FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER()) AS T1where 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.
SELECT * FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('myderbyerrors.log')) AS T1
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.
SELECT * FROM SYSCS_DIAG.ERROR_MESSAGES
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.
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.
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.
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.
SELECT * FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE')) AS T2
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.
SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE
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.
SELECT * FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION()) AS T1where 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.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.
SELECT * FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION('somederby.log')) AS T1
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.
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.