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.
DSN_USERQUERY_TABLE is created when you modify and run the DSNTESH sample job.
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 ' '
) IN database-name.table-space-name
CCSID UNICODE;
Column descriptions
The following
table describes the columns of DSN_USERQUERY_TABLE.
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.
|
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:
|
STARJOIN | CHAR(1) NOT NULL DEFAULT ' ' | Whether star join processing was enabled for
the query:
|
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:
|
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 |