DSN_USERQUERY_TABLE

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.

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