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 blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
