SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure

This procedure allows you to request XPLAIN style processing of runtime statistics. 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 the 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 by either 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 "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's 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('');