Catalog Information

The SQL Object Information screen (see the following figure) displays when you select option 4, Catalog Information, from the SQL Development and Analysis Summary screen.

SQL Object Information Screen

TABLE

The fully qualified table name.

TYPE

The type of object. T indicates Table, F indicates UDF, and M indicates Materialized query tables.

NPAGES

The total number of pages on which rows of the table exist. This field can contain a value of -1 if statistics are not available.

PCTPAGES

The percentage of total pages of the table space that contain rows of the table. If the table space is segmented, the value shown is the percentage of the total pages in the set of segments assigned to the table. This field can contain a value of -1 if there are no statistics available.

CARDINALITY

The number of rows in the table. This field can contain a value of -1 if there are no statistics available.

To select an object, enter an S line command in the area to the left of the desired table. If you select a table, the Table Information screen displays (Table Information screen). If you select a user defined function, the Function Detail screen displays. For more information about the Function Detail screen, refer to Function-Detail.

The Table Information screen provides detailed information about the selected object. From this screen you can receive additional column, index, and related table information, and table partition statistics.

Table Information screen

Viewing Db2 Catalog Columns

From the Information display screens for table, index, or trigger, you can enter the BROWSE command, and the Db2 catalog data for that particular object type will display. From this display, you are able to view all catalog columns within a File-AID for Db2 Browse session. See Viewing-Db2-Catalog-Columns for more information.

Select one for more information:

Column Information

Displays the Column Information screen (Column Information Screen).

Table Index Selection

Displays the Table Index Selection screen (Table Index Selection Screen).

Related Table Information

Displays the Related Table Information screen (Related Table Information Screen).

Related Trigger Information

Displays the Trigger Information screen (Trigger Information Display Screen).

Table Partition Statistics

Displays the Table Partition Statistics screen (Table Partition Statistics Screen).

Table Check Constraints

Displays the Check Constraints screen (Check Constraints Screen).

Remarks/Comment On

Displays the Comment On screen (Comment On Screen).

Column Information

The Column Information screen (Column Information Screen) displays when the Column Information option is selected on the Table Information screen (Table Information screen). This screen contains detailed information about each column in the selected table, including column name, column type, length, scale, and default values. To view RUNSTATS statistics, enter RIGHT, or the associated PF key. Enter RIGHT again to view the COLCARDF values. The scroll amount field is ignored when scrolling right. To return to the original display, enter LEFT or the associated PF key.

Enter an S in the Column Partition Statistics field to display the Column Partition Statistics screen (Column Partition Statistics Screen).

Column Information Screen

Column Information Screen — After Scrolling Right

Column Information Screen — After Scrolling Right Twice

Column Partition Statistics

The Column Partition Statistics screen (see the following figure) displays when an S is entered next to the Column Partition Statistics field on the Column Information screen (Column Information Screen).

Column Partition Statistics Screen

Index Selection

The Table Index Selection screen (see the following figure) displays when the Index Information option is selected on the Table Information screen (Table Information screen) and more than one index exists for the selected table. If only one index exists for the selected table, the Index Information screen (Index Information screen) displays. If no index for the selected table exists, the message Index not found displays in the upper right corner of the screen.

To request additional index information, enter S in the area provided to the left of the CREATOR column.

Table Index Selection Screen

Index Information

The Index Information screen (Index Information screen) contains detailed information about the selected table’s index. If only one index exists for the table, this screen displays immediately after the Index Information option is selected on the Table Information screen (Table Information screen).

This screen lists detailed information about the selected index, including index name, creator ID, and buffer pool. From this screen, you can request additional index key information or index partition information.

Index Information screen

Index Key Information

The Index Key Information screen (see the following figure) displays when an S is entered in the Index Key Information field on the Index Information screen (Index Information screen). The Index Key Information screen contains specific information about the keys defined in the selected index.

The Column Name/Derived From field is scrollable. This field displays the Column Name and the expression for each column of the index, and has a maximum of 4,000 bytes.

Index Key Information

Index Partition Information

The Index Partition Information screen (Index Partition Information Screen) displays when an S is entered in the Index Partition Information field on the Index Information screen (Index Information screen).

The top portion of the screen describes the current index and related table. The lower portion of the screen is a scrollable list of partitions defined for the indexspace. A partitioned index will have one set of attributes for each partition. A single set of attributes with a partition of 0 indicates the index is non-partitioned.

Index Partition Information Screen

ALL Indexes Information

The ALL Indexes Information screen (ALL Indexes Information Screen) displays when an S is entered in the Display information for ALL/Used Indexes field on the Explain Detail screen (Explain Detail Screen).

The top portion of the screen displays the origin of the SQL which has been explained. This information includes the Plan name, the Collection, and the Package or DBRM name, and the statement number and type of the explained statement. The Table field is the name of the table that is accessed by this plan table row.

The remainder of the screen provides information for all the indexes existing for the table. This part of the screen is scrollable. If this plan table row indicated that an index is used, that index is flagged by >>, and the matched columns of the index are flagged in the same way.

The Column Name/Derived From field is scrollable. This field displays the Column Name and the expression for each column of the index, and has a maximum of 4,000 bytes.

When ACCESSTYPE=I and an index is identified as used, but MATCHCOLS=0, this access is referred to as non-matching index scan. For example: When the high order column(s) of an index cannot be matched, but these or other columns in the index can be used to screen data to avoid accessing all data pages. For more information, refer to the IBM Database 2 Administration Guide.

ALL Indexes Information Screen

Related Table Information

The Related Table Information screen (see the following figure) displays when the Related Table Information option is selected on the Table Information screen (Table Information screen). This screen lists each table linked to the selected table (RI relationships only).

Related Table Information Screen

Related Trigger Information

The Trigger Information screen (Trigger Information Display Screen) displays when the Related Trigger Information option is selected on the Table Information screen (Table Information screen). The screen lists information about triggers on the table.

Table Partition Statistics

The Table Partition Statistics screen (see the following figure) displays when the Table Partition Statistics option is selected on the Table Information screen (Table Information screen). This screen lists tablespace statistics for each partition in the table.

Table Partition Statistics Screen

Table Check Constraints

The Check Constraints screen (see the following figure) displays when the Table Check Constraints option is selected on the Table Information screen (Table Information screen). The Check Constraints screen displays all defined check constraints for the selected table. The WHERE field only displays the first 55 characters of the WHERE clause.

Check Constraints Screen

Remarks/Comment On

The Comment On screen (Comment On Screen) displays when the Remarks/Comment On option is selected on the Table Information screen (Table Information screen). The screen lists remarks and comments associated with the table.