(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
DSN_USERQUERY_TABLE
Identifies statements whose access paths are influenced
Column name | Data type | Description |
QUERYNO | INTEGER | Unique identifier of the query, used to correlate with PLAN_TABLE rows for statement-level access paths |
SCHEMA | VARCHAR(128) | Defaut schema name of unqualified database objects in the query, or blank |
HINT_SCOPE | SMALLINT | Scope at which matching applied 0 System-level access path hint 1 Package-level access path hint |
QUERY_TXT | CLOB(2M) | The text of the SQL statement |
USERFILTER | CHAR(8) | A filter name that you can specifiy to group a set of rows together, or blank |
OTHER_OPTIONS | CHAR(128) | For IBM® internal use only, or blank |
COLLECTION | VARCHAR(128) | Collection name of package from SYSPACKAGE catalog table |
PACKAGE | VARCHAR(128) | Name of package for SYSPACKAGE table |
VERSION | VARCHAR(128) | Version of package for retrieval of bind options for SYSPACKAGE catalog table |
REOPT | VARCHAR(128) | Vaue of the REOPT bind option: ‘A’ REOPT(AUTO) ‘1’ REOPT(ONCE) ‘N’ REOPT(NONE) ‘Y’ REOPT(ALWAYS) Blank Not specified |
STARJOIN | CHAR(1) | Whether star join processing was enabled ‘Y’ STARJOIN enabled ‘N’ STARJOIN disabled Blank Not specified |
MAX_PAR_DEGREE | INTEGER | Maximum degree of parallelism |
DEF_CURR_DEGREE | CHAR(3) | Whether parallelism was enabled: ‘ONE’ – Parallelism disabled ‘ANY’ – Parallelism enabled Blank Not specified |
SJTABLES | INTEGER | Minimum number of tables to qualify for star join |
QUERYID | BIGINT | Identifies relevant access plan hint information in the SYSQUERY and SYSQUERYPLAN catalog tables |
OTHER_PARMS | VARCHAR | For IBM internal use only, or BLANK |
SELECTVTY_OVERRIDE | CHAR(1) | Whether row creates selectivity overrides: ‘Y’ – Selectivity overrides are created ‘N’ – Selectivity overrides are not created |
ACCESSPATH_HINT | CHAR(1) | Whether row specifies an access path for query: ‘Y’ – access paths are specified ‘N’ – access paths are not specified Blank – access path might be specified |
OPTION_OVERRIDE | CHAR(1) | Whether statement-level optimization parameters are created: ‘Y’ – Optimization parameters are created ‘N’ – Optimization parameters are not created Blank – Optimization parameters might be created |
DSN_VIRTUAL_INDEXES
Enables optimization tools to test the effect of creating and dropping indexes on the performance of particular queries
Column name | Data type | Description |
TBCREATOR | VARCHAR(128) | Schema or auth ID of owner of table which index created or dropped |
TBNAME | VARCHAR(128) | Name of table which index is created or dropped |
IXCREATOR | VARCHAR(128) | Schema or authorization ID owner of index |
IXNAME | VARCHAR(128) | Index name |
ENABLE | CHAR(1) | Whether index should be considered in scenario being tested Y – Use this index N – Do not use this index |
MODE | CHAR(1) | Whether index is being created or dropped C – index is to be created D – index is to be dropped |
UNIQUERULE | CHAR(1) | Whether index is unique D – index is not unique U – index is unique |
COLCOUNT | SMALLINT | Number of columns in the key |
CLUSTERING | CHAR(1) | Indicates whether index is clustered Y – index is clustered N – index is not clustered |
NLEAF | INTEGER | Number of active leaf pages in index |
NLEVELS | SMALLINT | Number of levels in index tree |
INDEXTYPE | CHAR(1) | Index type 2 – nonpartitioned secondary index D – data-partitioned secondary index |
PGSIZE | SMALLINT | Size, in KB, of leaf pages in index |
FIRSTKEY CARDF | FLOAT | Number of distinct values of first key column |
FULLKEY CARDF | FLOAT | Number of distince values of key |
CLUSTER RATIOF | FLOAT | Percentage of rows in clustering order |
PADDED | CHAR(1) | Whether keys are padded for varying-length column data Y – padded N – not padded |
COLNO1 | SMALLINT | Column number of first column in index key |
ORDERING1 | CHAR(1) | Order of first column in index key: A – Ascending D – Descending |
COLNOn | SMALLINT | Column number of nth column in index key |
ORDERINGn | CHAR(1) | Order of nth column in index key A – Ascending D – Descending |
KEYTARGET_COUNT | SMALLINT | Number of key-targets for an extended index |
UNIQUE_COUNT | SMALLINT | Number of columns or key-targets that make up unique constraint of an index |
IX_EXTENSION_TYPE | CHAR(1) | The type of extended index: ‘S’ – Index on a scalar expression ‘V’ – XML index Blank – A simple index |
DATAREPEAT FACTORF | FLOAT | Number of data pages expected to be touched when an index key order is followed |
SPARSE | CHAR(1) | Whether the index is sparse: ‘N’ – No ‘Y’ – Yes ‘X’ – Index excludes entries for data rows in which the key column contains the NULL value |
DSN_VIRTUAL_KEYTARGETS
Contains information about expression-based indexes and XML indexes
Column name | Data type | Description |
IXNAME | VARCHAR(128) | Name of index |
IXSCHEMA | VARCHAR(128) | Qualifier of index |
KEYSEQ | SMALLINT | Numeric position of key-target in index |
COLNO | SMALLINT | Numeric position of column in table if expression is a single column |
ORDERING | CHAR(1) | Order of key: ‘A’ – Ascending order |
TYPESCHEMA | VARCHAR(128) | Schema of data type |
TYPENAME | VARCHAR(128) | Name of data type |
LENGTH | SMALLINT | Length attribute of key-target |
LENGTH2 | INTEGER | Maximum length of data retrieved from column 0 – Not a ROWID column 40 – For a ROWID column, length of value |
SCALE | SMALLINT | Scale of decimal data |
NULLS | CHAR(1) | Whether key can contain null values: ‘N’ – NO ‘Y’ – Yes |
CCSID | INTEGER | CCSID of the key |
SUBTYPE | CHAR(1) | Subtype of data, for character keys only ‘B’ – Bit data ‘M’ – Mixed data ‘s’ – SBCS data Blank – Non-character data |
DERIVE_FROM | VARCHAR | Text of scalar expression to generated key-target value |
CARDF | FLOAT | Estimated number of distinct values |
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at [email protected]. Db2 is a registered trademark of the IBM Corporation.