# 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.

For a database for which authentication and SQL authorization are both enabled, only the database owner can access this diagnostic table function. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information.

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.

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

Table 1. Columns returned by the SYSCS_DIAG.ERROR_LOG_READER table function
Column Name | Type | Length | Nullable | Contents
--- | --- | --- | --- | ---
TS | VARCHAR | 26 | false | The timestamp of the statement.
THREADID | VARCHAR | 40 | false | The thread name.
XID | VARCHAR | 15 | false | The transaction ID.
LCCID | VARCHAR | 15 | false | The connection ID.
DATABASE | VARCHAR | 128 | false | The database name.
DRDAID | VARCHAR | 50 | true | The DRDA ID for network server session.
LOGTEXT | LONG VARCHAR | 32,700 | false | The text of the statement or commit or rollback.

Parent topic: SYSCS_DIAG diagnostic tables and functions
Related reference
SYSCS_DIAG.CONTAINED_ROLES 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.STATEMENT_DURATION diagnostic table function
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table