DSN_USERQUERY_TABLE

The DSN_USERQUERY_TABLE table identifies statements whose access paths are influenced. The values identify the statements and the method that is used to influence access path selection. Values in the DSN_USERQUERY_TABLE are used to populate certain catalog tables when a BIND QUERY command is issued.

Begin program-specific programming interface information.

DSN_USERQUERY_TABLE is created when you modify and run the DSNTESH sample job.

You can create this table under your own schema, or under a different schema that you specify in the EXPLAININTPUTSCHEMA option of the BIND QUERY command.

Create table statement

The following statement creates a user query table:

 CREATE TABLE userid.DSN_USERQUERY_TABLE
 (
 QUERYNO        INTEGER      NOT NULL PRIMARY KEY,
 SCHEMA         VARCHAR(128) NOT NULL DEFAULT ' ',
 HINT_SCOPE     SMALLINT     NOT NULL DEFAULT 0,
 QUERY_TEXT     CLOB(2M)     NOT NULL,
 QUERY_ROWID    ROWID        NOT NULL  GENERATED ALWAYS,
 QUERYID        BIGINT       NOT NULL DEFAULT 0,
 USERFILTER     CHAR(8)      NOT NULL DEFAULT ' ',
 OTHER_OPTIONS  CHAR(128)    NOT NULL DEFAULT ' ',
 COLLECTION     VARCHAR(128) NOT NULL DEFAULT ' ',
 PACKAGE        VARCHAR(128) NOT NULL DEFAULT ' ',
 VERSION        VARCHAR(128) NOT NULL DEFAULT ' ',
 REOPT          CHAR(1)      NOT NULL DEFAULT ' ',
 STARJOIN       CHAR(1)      NOT NULL DEFAULT ' ',
 MAX_PAR_DEGREE INTEGER      NOT NULL DEFAULT -1,
 DEF_CURR_DEGREE CHAR(3)     NOT NULL DEFAULT ' ',
 SJTABLES       INTEGER      NOT NULL DEFAULT -1,
 OTHER_PARMS    VARCHAR(128) NOT NULL DEFAULT ' '
 SELECTVTY_OVERRIDE CHAR(1)  NOT NULL DEFAULT 'N'
 ACCESSPATH_HINT CHAR(1)     NOT NULL DEFAULT ' '
OPTION_OVERRIDE  CHAR (1)    NOT NULL DEFAULT ' '
 ) IN database-name.table-space-name

CCSID UNICODE;

Column descriptions

The following table describes the columns of DSN_USERQUERY_TABLE.
Table 1. DSN_USERQUERY_TABLE description
Column name Data type Description
QUERYNO INTEGER NOT NULL PRIMARY KEY The unique identifier of the query, used to correlate with PLAN_TABLE rows for statement-level access paths.
SCHEMA VARCHAR(128) NOT NULL DEFAULT ' ' Default schema name of unqualified database objects, excluding functions, in the query, or blank
HINT_SCOPE SMALLINT NOT NULL DEFAULT 0 The scope at which matching applies.
0
System-level access path hint or the default value
1
Package-level access plan hint.
QUERY_TXT CLOB(2M) NOT NULL The text of the SQL statement.
USERFILTER CHAR(8) NOT NULL A filter name that you can specify to group a set of rows together, or blank. This value can be used to delete a set of related rows at the same time with a single FREEQUERY command.
OTHER_OPTIONS CHAR(128) NOT NULL DEFAULT ' ' For IBM® internal use only, or blank
COLLECTION VARCHAR(128) NOT NULL DEFAULT ' ' The collection name of the package from the SYSIBM.SYSPACKAGE catalog table.

This value is optional when the value of the HINT_SCOPE column is 0.
PACKAGE VARCHAR(128) NOT NULL DEFAULT ' ' The name of the package for the SYSIBM.SYSPACKAGE catalog table.

This value is optional when the value of the HINT_SCOPE column is 0.
VERSION VARCHAR(128) NOT NULL DEFAULT ' ' The version of the package for retrieval of bind options for the SYSIBM.SYSPACKAGE catalog table, or '*'. This value is optional when the value of the HINT_SCOPE column is 0.

When '*' is specified, Db2 uses only COLLECTION and PACKAGE values to look up rows in the SYSIBM.SYSPACKAGE and SYSIBM.SYSQUERY catalog tables.
REOPT VARCHAR(128) NOT NULL DEFAULT ' ' The value of the REOPT bind option:
'A'
REOPT(AUTO)
'1'
REOPT(ONCE)
'N'
REOPT(NONE)
'Y'
REOPT(ALWAYS)
blank
Not specified.
STARJOIN CHAR(1) NOT NULL DEFAULT ' ' Whether star join processing was enabled for the query:
'Y'
STARJOIN enabled.
'N'
STARJOIN disabled.
blank
Not specified.
MAX_PAR_DEGREE INTEGER NOT NULL DEFAULT -1 The maximum degree of parallelism or -1 if unspecified.
DEF_CURR_DEGREE CHAR(3) NOT NULL DEFAULT ' ' Whether parallelism was enabled:
'ONE'
Parallelism disabled.
'ANY'
Parallelism enabled.
blank
Not specified.
SJTABLES INTEGER NOT NULL DEFAULT -1 The minimum number of tables to qualify for the star join processing, or -1 when not specified.
QUERYID BIGINT NOT NULL DEFAULT 0 Identifies relevant access plan hint information in the SYSIBM.SYSQUERY and SYSIBM.SYSQUERYPLAN catalog tables.
OTHER_PARMS VARCHAR(128) NOT NULL For IBM internal use only, or BLANK
SELECTVTY_OVERRIDE CHAR(1) NOT NULL DEFAULT 'N' Whether the row creates selectivity overrides for the query:
'Y'
Selectivity overrides are created.
'N'
Selectivity overrides are not created.

This value must be set to 'N' when the value of ACCESSPATH_HINT is 'Y' or blank, or the value of OPTION_OVERRIDE is blank.
ACCESSPATH_HINT CHAR(1) NOT NULL DEFAULT ' ' Whether the row specifies an access path for the query:
'Y'
Access paths are specified.
'N'
Access path are not specified.
blank
Access path might be specified, if the related PLAN_TABLE instance contains rows that specify the access path.

This value must be set to 'N' if either of the values of the SELECTVTY_OVERRIDE or OPTION_OVERRIDE columns are 'Y'

When the value of this column is blank, the value of the OPTION_OVERRIDE column must also be blank and the value of the SELECTIVITY_OVERRIDE column must be 'N’.
OPTION_OVERRIDE CHAR(1) NOT NULL DEFAULT ' ' Whether statement-level optimization parameters are created:
'Y'
Optimization parameters are created.
'N'
Optimization parameters are not created.
blank
Optimization parameters might be created, if the values of certain columns are specified in this table, and the related PLAN_TABLE instance does not contain rows that specify access paths.

This value must be set to 'N' when the value of ACCESSPATH_HINT is 'Y'.

When the value of this column is blank, the value of the ACCESSPATH_HINT column must also be blank.
End program-specific programming interface information.