Mainframe Blog

Db2 Input Tables in Db2 12

4 minute read
Stephen Watts

(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.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.