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. Tuning Java DB contains general information about how to use XPLAIN style and the XPLAIN database tables.

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)

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('');