<< SYSVIEWS system table | XPLAIN_THREAD_ID >>

XPLAIN style tables

<< SYSVIEWS system table | XPLAIN_THREAD_ID >>

Derby Reference Manual

240

XPLAIN style tables

Derby optionally creates database tables to hold statistics information captured
using XPLAIN style. You can have zero, one, or many sets of these tables;
each set of tables is stored in a separate schema. The schema which is
used for capturing statement execution information is specified using the

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA

system procedure

You can query these tables to analyze the behavior of statement execution.

All of the above system tables reside in the schema which you specified. Because this is
not the default schema, qualify all queries accessing the system tables with the schema
name.

You can create the schema and tables ahead of time if you wish, but usually it is easier
to let Derby automatically create the schema and the tables for you. You can capture
multiple sets of data into the same tables, or you can specify a different schema each
time.

See "Working with RunTimeStatistics" in the Tuning Derby for additional information.

SYSXPLAIN_STATEMENTS system table

The SYSXPLAIN_STATEMENTS table captures information about statements which
have been executed using RUNTIMESTATISTICS with XPLAIN style.

See "Working with RunTimeStatistics" in Tuning Derby for information on how to
configure this.

Each row in this table describes a single statement which has been captured. Depending
on the precise configuration of the RUNTIMESTATISTICS and XPLAIN features, there
may be additional rows in the other XPLAIN system tables with additional information; the
STMT_ID and TIMING_ID columns in this table are used to join against those tables.

Rows in this table are added automatically when Derby has been configured
appropriately. The rows remain in the table until you delete them or drop the table.

The following table shows the contents of the SYSXPLAIN_STATEMENTS system table.

Table 62.

SYSXPLAIN_STATEMENTS system table

Column Name

Type

Length

Nullable

Contents

STMT_ID

CHAR

false

A unique identifier for
this particular captured
statement.

STMT_NAME

VARCHAR

128

true

The name of the
associated query or
statement. This value
is NULL if the user did
not assign a name (by
calling java.sql.Statement.setCursorName()).

STMT_TYPE

CHAR

false

A code indicating what
type of statement this
is: 'S' for SELECT,
'I' for INSERT, 'U'