Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Installation Guide

Table Statistics Pane

ClosedView the Object Statistics pane

The Object Statistics pane is displayed on the Access Plan tab in the SQL Optimizer window. It lists information for each table, view, or alias referenced in the access plan. If the operation in the selected access plan step accesses a specific object, this object is automatically highlighted in the Table Statistics pane. You can do the following in this pane:

  • Select an object in this pane to view statistics about its indexes and columns (in the Index and Column Statistics panes, respectively).

  • Right-click a specific object to update its catalog statistics.

ClosedAppend mode

The method in which rows are inserted on pages in the table or view:

Value

Description

Rows appended

New rows are inserted at the end of the data.

Rows inserted

New rows are inserted into existing spaces if available.

ClosedCheck count

The number of check constraints defined on the table or view.

This information is based on the CHECKCOUNT value for the table or view in SYSCAT.TABLES.

ClosedCol count

The number of columns in the table or view.

This information is based on the COLCOUNT value for the table or view in SYSCAT.TABLES.

ClosedFile pages

Total number of pages allocated for this table.

This information is based on the FPAGES value for the table or view in SYSCAT.TABLES.

ClosedIndex tablespace

The name of the tablespace that holds all the indexes created for the table or view.

This information is based on the INDEX_TBSPACE value for the table or view in SYSCAT.TABLES.

ClosedKey columns

The number of columns that make up the primary key for the table or view.

This information is based on the KEYCOLUMNS value for the table or view in SYSCAT.TABLES.

ClosedKey unique

The number of unique constraints (other than a primary key constraint) defined for the table or view.

This information is based on the KEYUNIQUE value for the table or view in SYSCAT.TABLES.

ClosedLOB tablespace

The name of the tablespace that holds all long data (for LONG or LOB column types) for the table or view.

This information is based on the LONG_TBSPACE value for the table or view in SYSCAT.TABLES.

ClosedLock size

The preferred lock granularity when DML statements are executed on the table:

Value

Description

Row

Row-level lock

Lock

Table-level lock

blank

Not applicable

ClosedObject name

The qualified name of the table or view.

This information is based on the TABSCHEMA and TABNAME values for the table or view in SYSCAT.TABLES.

ClosedOverflow

Total number of overflow records in the table or view. An overflow record is an updated record that is too large to fit on the page in which it is currently stored. The record is copied to another page, and its original location is replaced with a pointer to the new page.

This information is based on the OVERFLOW value for the table or view in SYSCAT.TABLES.

ClosedPages

Total number of pages that contain data for the table or view.

This information is based on the NPAGES value for the table or view in SYSCAT.TABLES.

ClosedPct free

Percentage of each page in the table or view to be reserved for future inserts.

This information is based on the PCTFREE value for the table or view in SYSCAT.TABLES.

ClosedPart mode

The mode used to distribute data in the table or view within a partitioned database:

Value

Description

Hash on partition key

Hash on the partitioning key

Replicate across partitions

Replicate table or view data across partitions

blank

No partitioning. The table, view, or alias exists in a single partition nodegroup with no partitioning key defined. A blank also appears for nicknames.

ClosedRI children

The number of referential constraints in which the table or view is a parent (that is, the number of dependent tables of this table).

This information is based on the CHILDREN value for the table or view in SYSCAT.TABLES.

ClosedRI parents

The number of referential constraints in which the table or view is a dependent (that is, the number of parent tables of this table).

This information is based on the PARENTS value for the table or view in SYSCAT.TABLES.

ClosedRI self

The number of referential constraints in which the table or view is both a parent and a dependent.

This information is based on the SELFREFS value for the table or view in SYSCAT.TABLES.

ClosedRows

Total number of data rows in the table or view.

This information is based on the CARD value for the table or view in SYSCAT.TABLES.

ClosedStatistics time

The timestamp for the last time a change was made to statistics for the table or view.

This information is based on the STATS_TIME value for the table or view in SYSCAT.TABLES.

ClosedStatus

Status type for the object from which information is retrieved:

Value

Description

Normal

Normal status for the table, view, alias, or nickname.

Check

CHECK PENDING status on the table or nickname. Constraint checking is turned off, and SELECT, INSERT, UPDATE, and DELETE operations are not allowed on the table.

X

Inoperative view or nickname. This status can occur when the underlying tables of the view or nickname have been dropped.

ClosedTablespace

The name of the primary tablespace for the table or view.

This information is based on the TBSPACE value for the table or view in SYSCAT.TABLES.

ClosedType

The type of table or view from which information is retrieved:

Value

Description

Table

Normal table

Hierarchy

The hierarchy table associated with the implementation of a typed table hierarchy

Summary

Summary table

View

Normal view

Alias

An indirect reference to a table

Nickname

A reference to a table (data source) in a federated database

Typed table

Table created from a structured type

Type view

A view created from a structured type

ClosedVolatile

The indicator showing whether the table or view has been declared volatile. A volatile table is a table whose contents vary greatly--from empty to large--at any point in time. To generate the access plan for a volatile table, DB2's SQL optimizer tends not to rely on existing table statistics and, therefore, often uses an index scan instead of a table scan. Values for this detail include:

Value

Description

Cardinality of table is volatile

Table is volatile

blank

Table is not volatile

 

Related Documents