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.

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 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
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.STATEMENT_DURATION diagnostic table function.

The returned table has the columns shown in the following table.

Table 1. Columns returned by the SYSCS_DIAG.STATEMENT_DURATION table function
Column Name Type Length Nullable Contents
TS VARCHAR 26 false The timestamp of the statement.
THREADID VARCHAR 80 false The thread name.
XID VARCHAR 15 false The transaction ID.
LOGTEXT LONG VARCHAR 32,700 true The text of the statement or commit or rollback.
DURATION VARCHAR 10 false The duration, in milliseconds, of the statement.
Related reference
SYSCS_DIAG.CONTAINED_ROLES diagnostic table function
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function
SYSCS_DIAG.ERROR_MESSAGES diagnostic table
SYSCS_DIAG.LOCK_TABLE diagnostic table
SYSCS_DIAG.SPACE_TABLE diagnostic table function
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table