There are two types of diagnostic table expressions in :
- Diagnostic tables
- Tables that are like any other table in .
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 .
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
.
System diagnostic table expressions provided by
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
If you reference a diagnostic table in
a DDL statement or a compression procedure, 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
All users can access this diagnostic table function, whether or not the
database has authentication and SQL authorization enabled.
SYSCS_DIAG.ERROR_LOG_READER diagnostic table functionThe
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.
For a database for which authentication and SQL authorization are both
enabled, only the
database owner can
access this diagnostic table function.
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 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.
For example:SELECT *
FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('myderbyerrors.log'))
AS T1
By default 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 log
files. You can then query that information by using the SYSCS_DIAG.ERROR_LOG_READER
diagnostic table function.
SYSCS_DIAG.ERROR_MESSAGES diagnostic tableThe SYSCS_DIAG.ERROR_MESSAGES
diagnostic table shows all of the SQLStates, locale-sensitive error messages,
and exception severities for a database.
You can reference the SYSCS_DIAG.ERROR_MESSAGES diagnostic table directly
in a statement.
For example:SELECT * FROM SYSCS_DIAG.ERROR_MESSAGES
All users can access this diagnostic table, whether or not the database has
authentication and SQL authorization enabled.
SYSCS_DIAG.LOCK_TABLE diagnostic tableThe SYSCS_DIAG.LOCK_TABLE
diagnostic table shows all of the locks that are currently held in the database. You can reference
the SYSCS_DIAG.LOCK_TABLE diagnostic table directly in a statement.
For
example:SELECT * FROM SYSCS_DIAG.LOCK_TABLE
All users can access this diagnostic table, whether or not the database has
authentication and SQL authorization enabled.
When
the SYSCS_DIAG.LOCK_TABLE diagnostic table is referenced in a statement, a
snapshot of the lock table is taken. A snapshot 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
snapshot is taken.
SYSCS_DIAG.SPACE_TABLE diagnostic table functionThe
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.
All users can access this diagnostic table function, whether or not the
database has authentication and SQL authorization enabled.
To access the SYSCS_DIAG.SPACE_TABLE diagnostic table function, you must use
the SQL table function syntax. You can invoke the table function in the
following ways:
- If invoked with no arguments, the table function retrieves
space information for all tables and indexes in the database.
- If invoked with one argument, a tableName, the table
function retrieves information for the specified table in the current
schema.
- If invoked with two arguments, a schemaName followed by
a tableName, the table function retrieves information for
the specified schema and table.
The returned table has the columns shown in the following table.
Columns returned by the SYSCS_DIAG.SPACE_TABLE function
Column Name
Type
Length
Nullable
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
5
false
Is not zero if the conglomerate is an index, 0 otherwise.
NUMALLOCATEDPAGES
BIGINT
20
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
20
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
20
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
10
false
The size of the page in bytes for that conglomerate.
ESTIMSPACESAVING
BIGINT
20
false
The estimated space which could possibly be saved by compressing the conglomerate, in bytes.
TABLEID
CHAR
36
false
The id of the table to which the conglomerate belongs.
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()) AS T2
WHERE systabs.tabletype = 'T'
AND systabs.tableid = T2.tableid;
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 tableThe
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
For a database for which authentication and SQL authorization are both
enabled, only the
database owner can
access this diagnostic table.
SYSCS_DIAG.STATEMENT_DURATION diagnostic table functionYou
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.
For a database for which authentication and SQL authorization are both
enabled, only the
database owner can
access this diagnostic table function.
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 T1where T1 is a user-specified table name that is any valid
identifier.
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
By default 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 log
files. You can then query that information by using the SYSCS_DIAG.STATEMENT_DURATION
diagnostic table function.
SYSCS_DIAG.TRANSACTION_TABLE diagnostic tableThe
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 snapshot of the transaction table is taken. A snapshot 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 snapshot is taken.
For a database for which authentication and SQL authorization are both
enabled, only the
database owner can
access this diagnostic table.