Help

 

Catalog Statistics

Catalog Statistics displays the contents of the Db2 Catalog that are updated by the Db2 RUNSTATS utility, and that are used by the Db2 optimizer to determine access paths for SQL statements. The tables in the Db2 Catalog describe the objects in your Db2 environment. Certain columns are only available with certain Db2 releases.

Report details

Table identifies the table being reported.

DBNAME identifies the name of the database in which the table was created. If the row does not define a table, the value of DBNAME is DSNDB06.

TSNAME identifies the name of the tablespace in which the table was created, as follows:

  • If the row defines a view based on tables, TSNAME is the name of the tablespace for one of the tables

  • If the row defines a view based on other views, TSNAME's value is SYSVIEWS

  • If the row defines an alias, TSNAME's value is SYSDBAUT

CARDF identifies the number of rows in the table. For views, aliases, or if RUNSTATS has not been run, the value of CARD is -1.

NUM DEP MQTS identifies the number of dependent materialized query tables. The value is zero if the row describes an alias or a created temporary table, or if no materialized query tables are defined on the table.

SPLIT ROWS value is blank, except for VOLATILE tables, which will have 'Y' in the field to indicate to Db2 to use index access on this table whenever possible.

NPAGES identifies the number of tablespace pages that contain rows for this table. For views, aliases, or if RUNSTATS has not been run, the value of NPAGES is -1.

PCT PAGES identifies the percentage of tablespace pages that contain rows for this tables. For views, aliases, or if RUNSTATS has not been run, the value of PCTPAGES is -1.

PCT ROW COMP shows the percentage of rows compressed within the total number of active rows in the table, multiplied by 100. The value includes any row transformed by an editproc such that its length is less that the length of the original row. For views, aliases, or if RUNSTATS has not been run, the value of PCTROWCOMP is -1.

PARTKEY COL NO identifies the number of columns the partitioning key has. Zero indicates that the table does not have partitioning or use index-controlled partitioning. Tables that use table-controlled partitioning have a non-zero value.

AVG ROW LEN shows the average row length for tablespace tables. The value for a compressed table space is the compressed row length. The value for a table space that is not compressed is the uncompressed row length. -1 indicates that statistics have not been obtained.

STATSTIME shows the date and time that the last invocation of RUNSTATS updated the statistics.

 

Columns (SYSCOLUMNS)

Click next to Columns to view the list of columns for this table:

  • NAME identifies the name of the column in the table or view.

  • COLNO indicates the numeric position of the column in the table or view. For example, 5 denotes the fifth column.

  • COLTYPE identifies the data type of the column in the table or view:

Type

Meaning

INTEGER

large integer

SMALLINT

small integer

FLOAT

floating-point

CHAR

fixed-length character string

VARCHAR

variable-length character string

LONGVAR

variable-length character string

DECIMAL

decimal

GRAPHIC

fixed-length character string

VARG

variable-length character string

LONGVARG

variable-length character string

DATE

date

TIME

time

TIMESTMP

timestamp

  • LENGTH identifies the length of the column as it is physically stored.

Value

Meaning

INTEGER

4

SMALLINT

2

FLOAT

4 or 8

CHAR

string length

VARCHAR

maximum string length

LONGVAR

maximum string length

DECIMAL

numeric precision

GRAPHIC

number of DBCS characters

VARG

maximum number of DBCS characters

LONGVARG

maximum number of DBCS characters

DATE

4

TIME

3

TIMESTMP

10

  • SCALE identifies the scale of data whose COLTYPE is DECIMAL. SCALE is 0 for non-decimal columns.

  • NULLS denotes whether the column can contain null values. NULL is Y(es) if the column can contain null values, or N(o) if it cannot.

  • COLCARDF contains a number that indicates the number of distinct values in the column. If RUNSTATS has not been run, the value of COLCARD is -1.

  • PARTKEY COLSEQ gives the column’s numeric position within the table’s partitioning key (if the table uses table-controlled partitioning). Zero indicates that the column is not within the partitioning key.

  • PARTKEY ORDERING for tables that use table-controlled partitioning, shows the column’s order in the partitioning key.

  • A indicates ascending order

  • D indicates descending order.

  • Blank indicates the column is not used as part of the partitioning key.

  • FOREIGN KEY specifies the characteristics of character columns:

  • B indicates that the column can contain bit data

  • S indicates that the MIXED DATA installation option is YES and the column contains SBCS data

  • Any other character indicates SBCS, if MIXED DATA is NO, or MIXED, if MIXED DATA is YES.

Non-uniform Catalog Statistics (SYSCOLDIST)

Non-uniform column statistics are generated for all columns in a partition that participate as the first column in a composite index definition, and for which you executed RUNSTATS.  SYSCOLDIST contains one or more rows for the first key column of an index key.

Click to view non-uniform catalog statistics for columns in a partition:

  • FREQUENCYF shows the percentage of rows that contain the value specified in COLVALUE, multiplied by 100.

  • COLVALUE contains the data of a frequently occurring value.

 

Indexes (SYSINDEXES)

Indexes shows the following columns from the SYSINDEXES table, sorted by Name and Creator.

Click next to Indexes to view additional details about indexes for this table:

  • NAME identifies the name of the index.

  • CREATOR identifies the authorization ID of the owner of the index named in NAME.

  • CLUSTER ACT is an updatable column that indicates whether the rows in the table are actually clustered with this index. Y indicates an actual clustering greater than 95% while N indicates less than 95%. The Runstats Utility can also be used to update this column.

  • CLUSTER SPC indicates whether clustering was specified upon index creation (Y/N).  Only one index may be defined as the Clustering Index.

  • INDEX SPACE is an eight-byte index space name. INDEXSPACE is different from NAME when NAME is greater than eight bytes, or when more than one index has the same name (with a different CREATOR) in the same database.

  • FIRST KEYCARDF identifies the number of distinct values in the first column of the index key. If RUNSTATS has not been run, the value of FIRSTKEYCARDF is -1.

  • FULL KEYCARDF indicates the number of distinct values in the entire index key. If RUNSTATS has not been run, the value of FULLKEYCARDF is -1.

  • NLEAF indicates the number of active leaf pages. If RUNSTATS has not been run, the value of NLEAF is -1.

  • NLEVELS indicates the number of levels in the index tree. If RUNSTATS has not been run, the value of NLEVELS is -1.

  • Extension Type identifies the type of extended index:

  • N Node ID index

  • S Index on a scalar expression

  • T Spatial index

  • V XML index

  • blank Simple index

  • CLUSTER RATIOF indicates the percentage of table rows in clustered order by this index key. For partitioned indexes, CLUSTERRATIOF is the weighted average of all index partitions in terms of the number of rows in the partition. If RUNSTATS has not been run, the value of CLUSTERRATIOF is -1.

  • B POOL identifies the index’s buffer pool name.

  • P SIZE identifies the KB size of the index’s leaf pages. Valid values are 4, 8, 16, and 32.

  • UNIQ RULE indicates whether the index is unique.

  • D indicates that the index is not unique (duplicates are allowed)

  • U indicates that the index is unique

  • P indicates that the index is both unique and a primary index

  • C indicates that the index is unique and is used to enforce a UNIQUE constraint

  • N indicates that the index is unique and is defined with UNIQUE WHERE NOT NULL

  • R indicates that the index is unique and is used to enforce a non-primary parent key’s uniqueness

  • G indicates that the index is unique and is used to enforce the uniqueness of values in the ROWID GENERATED BY DEFAULT column

  • X indicates that the index is unique and is used to enforce the uniqueness of values in columns containing XML values

  • IDX TYPE indicates the type of index.

  • blank indicates a Type 1 index

  • 2 indicates a Type 2 index

  • D indicates a data-partitioned secondary index

  • P indicates a partitioning index

  • AVG KEYLEN lists the index’s average key length. If statistics have not been gathered, the value shown is -1.

  • PAD indicates whether the index keys are padded to accommodate varying-length column data:

  • Y indicates that the index has data that varies in length and that the index keys are padded.

  • N indicates that, although the index has data that varies in length, the index is not padded.

  • blank indicates that the index does not have data that varies in length.

  • CPM PRES indicates whether or not index compression is active.

  • Y indicates that it is active

  • N indicates that it is not active

  • IDX EXT TYPE lists the type of extended index the index is:

  • blank indicates that the index is a simple index

  • S indicates that the index is a scalar expression index

  • N indicates that the index is a node ID index

  • V indicates that the index is an XML index

Key Columns (SYSKEYS)

The Key Columns section shows details about the key columns that comprise the index. The SYSKEYS table contains one row for each column of an index key.

Click next to an index to view additional details about key columns for this index:

  • COLSEQ shows the numeric position of the column in the key, for example, 2 out of 5 columns.

  • COLNAME contains the name of the column of the key.

  • COLNO shows the numeric position of the column in the table, for example, 3 out of 7 columns.

  • ORDERING shows the order of the column in the key:

  • A for ascending

  • D for descending

  • COLCARD is an updatable column that lists an estimate of the number of distinct values in the column.

  • Lists, for an indicator column, the number of LOBs that are not null and have a length greater than zero.

  • A value of -1 indicates that statistics have not been gathered.

  • A value of -2 is used for the first column of an index of an auxiliary table.

Key Targets(SYSKEYTARGETS)

The Key Targets section provides details about the key targets for this index. The SYSKEYS table contains one row for each column of an index key.

Click next to an index to view additional details about key targets in this index:

  • KEYSEQ shows the numeric position of the key target in the index.

  • COLNO shows the numeric position of the column in the table if the expression is a single column. Otherwise the value is 0. For XML indexes, this field is also 0.

  • ORDERING shows the order of the column in the key:

  • A for ascending

  • D for descending

  • CARDF is

  • number of distinct values for the key-target. The value is -2 if the index is a node ID index. For an XML value index, the statistic is collected for the second key target (the DOCID column). For all other key targets of the XML value index, a value of -2 is set.

  • Lists, for an indicator column, the number of LOBs that are not null and have a length greater than zero.

  • A value of -1 indicates that statistics have not been gathered.

  • A value of -2 is used for the first column of an index of an auxiliary table.

  • Derived from Expression (50 bytes) contains the text of the scalar expression that is used to generated the key-target value. For an XML index, this is the XML pattern that is used to generate the key-target value. Otherwise DERIVED_FROM contains an empty string.

Index Plan Dependencies (SYSPLANDEP)

Click next to an index to view additional details about plan dependencies for this index:

  • BNAME is the name of the object upon which the plan depends.

  • BCREATOR depends on the BNAME:

  • If the BNAME is a table space, the BCREATOR is a database.

  • If the BNAME is a role, the BCREATOR is blank.

  • For all other BNAMEs, the BCREATOR is the schema of BNAME.

  • BTYPE indicates the type of object the BNAME identifies:

  • A indicates that the BNAME is an alias

  • E INSTEAD OF indicates that the BNAME is a trigger

  • F indicates that the BNAME is either a user-defined function or cast function

  • G indicates that the BNAME is a global temporary table

  • I indicates that the BNAME is an index

  • L indicates that the BNAME is a role

  • M indicates that the BNAME is a materialized query table

  • O indicates that the BNAME is a stored procedure

  • P indicates that the BNAME is a partitioned table space if it is defined as LARGE or with the DSSIZE parm

  • Q indicates that the BNAME is a sequence object

  • R indicates that the BNAME is a table space

  • S indicates that the BNAME is a synonym

  • T indicates that the BNAME is a table

  • V indicates that the BNAME is a view

  • DNAME (PLAN) identifies the plan’s name.

Index Package Dependencies (SYSPACKDEP)

Click next to an index to view additional details about package dependencies for this index:

  • BNAME lists the name of the object upon with the package depends.

  • BQUALIFIER lists the object’s type upon which the column’s value depends:

  • If the BNAME is a table space, the column’s value is the database name.

  • If the BNAME is a user-defined function, cast function, stored procedure, or sequence, the column’s value is the schema name.

  • If the BNAME is a role, the column’s value is blank.

  • If the BNAME is any other object type, the column’s value is the BNAME’s schema.

  • BTYPE lists the object type that the BNAME and BQUALIFIER indicate:

  • A indicates an alias

  • E INSTEAD OF indicates a trigger

  • F indicates a user-defined function or cast function

  • G indicates a global temporary table

  • I indicates an index

  • L indicates a role

  • M indicates a materialized query table

  • O indicates a stored procedure

  • P indicates a partitioned table space if it is defined as LARGE or with the DSSIZE parm

  • Q indicates a sequence object

  • R indicates a table space

  • S indicates a synonym

  • T indicates a table

  • V indicates a view

  • DNAME (PACKAGE) identifies the package name

  • DCOLLID identifies the package collection name

Indexspace real time statistics (SYSINDEXSPACESTATS)

Click to view real time statistics for this index:

  • PARTITION is the data set number within the index space. For partitioned index spaces, this value corresponds to the partition number for a single partition. For non-partitioned table spaces, this value is 0.

  • UPDATE STATS TIME is the timestamp that the row in the SYSINDEXSPACESTATS table is inserted or last updated.

  • TOTAL ENTRIES is the number of entries–including duplicate entries–in the index space or partition. A null value indicates that the number of entries is unknown.

  • NLEAF is the number of leaf pages in the index.

  • LOADR LAST TIME is the timestamp that the LOAD REPLACE utility was last run on the index space or partition. A null value indicates that the LOAD REPLACE utility has never been run on the index space or partition or that the timestamp is unknown.

  • REBUILD LAST TIME is the timestamp that the REBUILD INDEX utility was last run on the index space or partition. A null value indicates that the timestamp that the REBUILD INDEX was last run is unknown.

  • REORG LAST TIME is the timestamp that the REORG INDEX utility was last run on the index space or partition. A null value indicates that the REORG INDEX utility has never been run on the index space or partition or that the timestamp is unknown.

  • REORG INSERTS is the number of index entries that have been inserted into the index space or partition since the last time the REORG, REBUILD INDEX, or LOAD REPLACE utilities were run. A null value indicates that the number of inserted index entries is unknown.

  • REORG DELETES is the number of index entries that have been deleted from the index space or partition since the last time the REORG, REBUILD INDEX, or LOAD REPLACE utilities were run. A null value indicates that the number of deleted index entries is unknown.

  • LAST USED is the date when the index is used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. The default value is 1/1/0001.

 

Index space statistics (SYSINDEXPART)

The SYSINDEXPART table contains information about the physical structure and storage of every Db2 index.

Click to view additional information for index partitions:

  • Num contains the partition number, for partitioned indexes, or 0, for non-partitioned indexes.

  • IXNAME is the name of the partitioned index.

  • IXCREATOR indicates the owner of the index named by IXNAME.

  • CARDF indicates the number of rows in this index. If RUNSTATS has not been run, the value of CARDF is -1.

  • LEAFDIST indicates the average number of pages multiplied by 100 between consecutive index leaf pages. If RUNSTATS has not been run, the value of LEAFDIST is -1.

  • FAROFFPOSF indicates the number of rows located far from their optimal position. If RUNSTATS has not been run, the value of FAROFFPOSF is -1.

  • NEAROFFPOSF indicates the number of rows located near off from their optimal position. If RUNSTATS has not been run, the value of NEAROFFPOSF is -1.

  • PERCFREE indicates the percentage left as free space for each leaf or nonleaf page.

  • FREEPAGEs identifies how many pages are loaded before leaving a pages as free space.

 

Tablespaces (SYSTABLESPACE)

Tablespaces shows the following columns from the SYSTABLESPACE table, sorted by NAME and CREATOR:

Click to view additional information for this tablespace:

  • NAME identifies the tablespace name.

  • CREATOR identifies the owner of the tablespace in NAME.

  • NACTIVE indicates the number of active pages for this tablespace.

  • NACTIVEF is an updatable column that lists how many active pages the table space has. If a page is formatted for rows (regardless of whether it contains any), the page is considered active. A value of -1 means that statistics have not been gathered.

  • BPOOL is the name of the table space’s buffer pool.

  • LOCKRULE indicates the table space’s lock size:

  • A indicates Any

  • L indicates Large Object (LOB)

  • P indicates Page

  • R indicates Row

  • S indicates Table Space

  • T indicates Table

  • X indicates an implicitly created XML table space

  • LOCKMAX lists the number of table or table space locks a given user must acquire before escalating to the next locking level.

    • 0 indicates that lock escalation does not occur at any level.

    • n n (n must be greater than 0) indicates the maximum number of row, page, or LOB locks an application process can acquire before lock escalation occurs.

    • LOCKMAX SYSTEM is indicated by a -1. You can determine whether lock escalation is in effect by inspecting the value of the LOCKS PER TABLE(SPACE).  This may be found on installation panel DSNTIPJ:

      • Lock escalation does not occur if the field is 0

      • As it does for LOCKMAX n, lock escalation occurs if this field is non-zero, n>0.

  • SEGSIZE lists the number of pages each segment of a segmented table space contains. A value of 0 means that the table space is not segmented.

  • PAGESIZE lists the table space’s page size, in kilobytes.

  • NTABLES lists the number of tables in the table space.

Tablespace real time statistics (SYSTABLESPACESTATS)

Click to view real time statistics for this index:

  • PARTITION is the data set number within the table space. This column is used to map a data set number in a table space to its statistics. For partitioned table spaces, this value corresponds to the partition number for a single partition. For non-partitioned table spaces, this value is 0.

  • UPDATE STATS TIME is the timestamp that the row in the TABLESPACESTATS table is inserted or updated.

  • TOTAL ROWS is the number of rows or LOBs that are in the table space or partition.

  • NPAGES is the number of distinct pages with active rows in the partition or table space.

  • SPACE is the amount of space, in KB, that is allocated to the table space or partition. For multi-piece, linear page sets, this value is the amount of space in all data sets. A null value indicates the amount of space is unknown.

  • DATA SIZE is the total number of bytes that row data occupy in the data rows or LOB rows.

  • REORG LAST TIME is the timestamp that the REORG utility was last run on the table space or partition.

  • REORG INSERTS is the number of records or LOBs that have been inserted into the table space or partition or loaded into the table space or partition using the LOAD utility specified without the REPLACE option since the last time the REORG or LOAD REPLACE utilities were run. A null value indicates that the number of inserted records or LOBs is unknown.

  • REORG UPDATES is the number of rows that have been updated in the table space or partition since the last time the REORG or LOAD REPLACE utilities were run. A null value indicates that the number of updated rows is unknown.

  • REORG DELETES is the number of records or LOBs that have been deleted from the table space or partition since the last time the REORG or LOAD REPLACE utilities were run. A null value indicates that the number of deleted records or LOBs is unknown.

  • LOAD LAST TIME is the timestamp that the REORG utility was last run on the table space or partition. A null value indicates that the REORG utility has never been run on the table space or partition or that the timestamp is unknown.

 

Tablespace Partitions (SYSTABLEPART)

Tablespace Partitions contains information on tablespace partitions and physical storage characteristics of the Db2 tablespace.

Click to view additional information for tablespace partitions:

  • Num contains the partition number, for partitioned tablespaces, or 0, for non-partitioned tablespaces.

  • TSNAME is the name of the tablespace.

  • IXNAME is the name of the partitioned index. For non-partitioned indexes, IXNAME is blank.

  • IXCREATOR indicates the owner of the index named by IXNAME.

  • CARD indicates the number of rows in this tablespace or partition. If RUNSTATS has not been run, the value of CARD is -1.

  • FAR IND REF indicates the number of rows relocated far from their initial page.

  • NEAR IND REF indicates the number of rows relocated near their initial page.

  • PERC ACTIVE indicates the percentage of space used by active tables in this tablespace partition.

  • PERC DROP indicates the percentage of space used by dropped tables in this tablespace partition.

  • PAGE SAVE is the percentage of pages saved in the tablespace or partition as a result of using data compression, multiplied by 100.

  • PERC FREE shows the percentage left as free space for each page.

  • FREE PAGE identifies how many pages are loaded before leaving a pages as free space.

  • COMP indicates whether the COMPRESS attribute is YES for the partition (for a table space partition) or for the table space (for a nonpartitioned table space).

  • Y indicates that compression is defined for the table space

  • blank indicates that no compression is defined

 

Table Partitions (SYSTABSTATS)

Table Partitions section shows the statistics for each partition of the table. Db2 generates these statistics for those tables defined in partitioned tablespaces for which you executed RUNSTATS.

Click to view statistics for this partition:

  • Table shows the following columns from the SYSTABSTATS table. SYSTABSTATS contains information about each partition of a partitioned tablespace.

  • Num contains the partition number in the tablespace that contains the table.

  • CARD identifies the number of rows in the partition.

  • NPAGES identifies the total number of pages on which rows of the partition appear.

  • PCT PAGES shows the percentage of total active pages in the partition that contain rows of the table, multiplied by 100.

  • NACTIVE shows the number of active pages in the partition.

  • PCT ROW COMP shows the percentage of rows compressed within the total number of active rows in the partition, multiplied by 100. The value includes any row transformed by an editproc such that its length is less that the length of the original row.

 

Index Partitions (SYSINDEXSTATS)

The SYSINDEXSTATS table contains one row of information about each partition of a partitioned index.

Click to view statistics for the index partition:

  • Num indicates the partition number of the index.

  • NAME indicates the name of the index.

  • OWNER indicates the authorization ID of the owner of the index.

  • FIRST KEYCARD indicates the number of distinct values in the first key column of the index partition.

  • FULL KEYCARD indicates the number of distinct values in the key of the index partition.

  • NLEAF indicates the number of active leaf pages in the index partition.

  • NLEVELS indicates the number of levels in the partition index tree.

  • CLUSTERRATIO indicates the percentage of index partition rows in clustered order, multiplied by 100. If RUNSTATS has not been run, the value of CLUSTERRATIO is 0.

  • KEYCOUNTF indicates the number of rows in the partition.

 

 

SYSCOLDISTSTATS contains zero or more rows per partition for the first key column of a partitioned index.

  • FREQUENCYF shows the percentage of rows that contain the value specified in COLVALUE, multiplied by 100.

  • COLVALUE contains the data of a frequently occurring value.