SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure

When XPLAIN style is used, the runtime statistics are written to the SYSXPLAIN_* database tables, so that you can analyze the statistics by running queries against the tables.

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

Turn XPLAIN style on by calling this procedure with a non-empty argument. Turn XPLAIN style off by calling the procedure with an empty argument.

The argument that you provide must be a legal schema name, and you should use this argument to indicate the schema in which runtime statistics should be captured. If the schema that you specify does not already exist, it will be automatically created. If the XPLAIN tables do not already exist in this schema, they will be automatically created. Runtime statistics information about statements executed in this session will then be captured into these tables, until runtime statistics capturing is halted either by calling SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA with an empty argument or by calling SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0).

Syntax

SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA(IN VARCHAR(128) SCHEMA_NAME)

Execute privileges

If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this procedure by default. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information. The database owner can grant access to other users.

Example

To cause Derby to record statistics about statement execution in the SYSXPLAIN_* database tables in the schema named 'MY_STATS':

        call syscs_util.syscs_set_runtimestatistics(1);
        call syscs_util.syscs_set_xplain_schema('MY_STATS');

        select country from countries;

        call syscs_util.syscs_set_runtimestatistics(0);
        call syscs_util.syscs_set_xplain_schema('');