Db2 Input Tables in Db2 12
(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
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 |
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 |
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.