SYSQUERY catalog table

Each SYSIBM.SYSQUERY table row identifies a SQL statement. The information is used to influence access path selection when matching statements are optimized. The schema is SYSIBM.

Column name Data type Description Use
QUERYID
BIGINT
NOT NULL
GENERATED
BY DEFAULT
AS IDENTITY
 Unique identifier for the query. G
QUERY_HASH
CHAR(16)
NOT NULL
FOR BIT DATA
 The hash key generated by statement text. G
SCHEMA
VARCHAR(128)
NOT NULL
 The default schema name for unqualified objects in the query or blank.

If the query contains unqualified objects and access path hints exist for the query, the access path hints are applied only if the default schema matches the schema in the access path hint.

 G
QUERY_SEC_HASH
CHAR(16)
NOT NULL
FOR BIT DATA
 The hash key generated by the modified statement text. G
QUERY_HASH_
VERSION
INTEGER
NOT NULL
 The version of the query hash. G
SOURCE
SMALLINT
NOT NULL
 The source of the row:
0
Statement-level optimization hints.
 G
USERFILTER
CHAR(8)
NOT NULL
 Filter name that is used to group a set of queries or blank. G
—
CHAR(128)
NOT NULL
 Internal use only. I
PLAN_VALID
CHAR(1)
NOT NULL
 Whether plan hints are valid:
blank
No access path i specified for the statement, but optimization parameters exist in SYSQUERYOPTS
Y
An access path is specified in SYSQUERYPLAN for the statement. The access path is also valid if the statement has already been executed and the access path was used.
N
A an access path is specified in SYSQUERYPLAN, but the access path is invalid and not used.
 G
INVALID_REASON
INTEGER
NOT NULL
 When PLAN_VALID is N, this column contains the reason that the access path is invalid. If PLAN_VALID is Y or blank, this column contains -1. For descriptions of the reason code values, see: +395. S
LOCATION
VARCHAR(128)
NOT NULL
 Not used. N
COLLECTION
VARCHAR(128)
NOT NULL
 Name of the collection of the originating query or blank. G
PACKAGE
VARCHAR(128)
NOT NULL
 Name of the package of the originating query or blank. G
VERSION
VARCHAR(128)
NOT NULL
 Version of the package or blank. G
AUTHID
VARCHAR(128)
NOT NULL
 Authorization ID this was in effect when the query was captured or blank. G
BINDTIME
TIMESTAMP
NOT NULL
 Timestamp when the package was bound or when BIND QUERY was run G
RELBOUND
CHAR(1) NOT NULL
 The release of Db2 in which the package was bound, or blank. See Release dependency indicators for values. G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies. RELBOUND should be used instead.

 G
STMTNO
INTEGER
NOT NULL
 The statement number in the package. -1 when not applicable. G
SECTNO
INTEGER
NOT NULL
 The section number in the package. -1 when not applicable. G
STMTTEXT
CLOB(2M)
INLINE
LENGTH 2048
 The text of the matching SQL statement. The value is populated from the value of the QUERY_TEXT column of the DSN_USERQUERY_TABLE table, with the following items removed:
  • Blanks including leading and trailing blanks, and embedded blanks that are not within literal strings between pairs of quotation mark symbols
  • White space, including leading and trailing white space, and white space that is not within a literal string between a pair of quotation mark symbols
  • SQL comments
  • EXPLAIN clauses
 G
QUERYNO
INTEGER
NOT NULL
WITH DEFAULT '-1'
 The query number. G
CLIENT_USERID
VARCHAR(255)
 User ID of the client. G
CLIENT_
WRKSTNNAME
VARCHAR(255)
 Name of the client workstation. G
CLIENT_APPLNAME
VARCHAR(255)
 Name of the client application. G
SELECTVTY_
OVERRIDE
 CHAR(1) NOT NULL Whether selectivity overrides are in effect for the query:
'Y'
Selectivtiy overrides are in effect
'N'
Selectivity overrides are not in effect.
 G
ACCESSPATH_
HINT
 CHAR(1) NOT NULL Whether access paths are specified for the matching statements:
'Y'
An access paths is specified and in effect
'N'
An access path hints is specified and in effect
blank
An access path might be specified. When the value is blank you must query the SYSIBM.SYSQUERYPLAN catalog table to determine whether an access path is specified
 G
OPTION_OVERRIDE
 CHAR(1) NOT NULL Whether statement-level optimization parameters are in effect for matching statements:
'Y'
Optimization parameters are in effect.
'N'
Optimization parameters are not in effect.
blank
Optimization parameters might be in effect. When the value is blank you must query the SYSIBM.SYSQUERYOPTS catalog table to determine whether option overrides are in effect.
 G
SELECTIVITY_VALID
 CHAR(1) NOT NULL Whether selectivity overrides are valid:
blank
No selectivity overrides exist for the statement.
'Y'
Selectivity overrides exist for the query. The overrides are valid if the statement has already been executed and the overrides were used.
'N'
Selectivity overrides exist but the overrides are invalid and not used.
 G
Start of changeFUNCTION_LVLEnd of change Start of changeVARCHAR(10)End of change Start of changeThe function level of the query.End of change Start of changeGEnd of change