Querying the catalog for index and table space information

Authorities and privileges

To query the catalog:

1. Open the Query Catalog window.
2. Select the Indexes radio button or the Table Spaces radio button to specify the type of object you want information about.
3. Specify query criteria from the drop down list. When you select a query, the corresponding variables display.
4. Specify variables in the available fields.
5. Click OK the run your query.

Specifying search criteria

The search conditions vary depending on the type of object that you want information about:

 

Index conditions	Table space conditions
With no or old RUNSTATS information	With no or old RUNSTATS information
With clustering level problems	With more than 'n' percent relocated rows
With index levels greater than 'n'	With more than 'n' percent dropped space
Indexes with large leaf pages distance	With table space locking
Which are non-segmented and contain more than one table
Which are type 1	Without STOSPACE information
With long RID chains and are type 1	Exceeding allocated primary quantity
Not used by any plan or package with static SQL
Without STOSPACE information
Exceeding allocated primary quantity

With no or old RUNSTATS information

This option shows objects that lack or have outdated RUNSTATS information.

Tip: Consider running the RUNSTATS utility on objects that match this query.

With clustering level problems

This option shows objects that have more than 10 percent of their rows in a far offset position or it shows objects that are defined as clustering but have a clustering ratio less than 95 percent.

Tip: Consider reorganizing tables or redesigning indexes, tables, or your applications. When redesigning objects and programs to avoid clustering level problems, consider the following factors:

• Insert, update, and delete patterns
• Insert, update, and delete frequencies
• Free space and reorganize frequencies
• Clustering sequences

With index levels greater than 'n'

This option shows indexes that have a level greater than the value that you specify in the Number of index levels field.  Index levels that exceed 2 or 3 can reduce the performance of your applications.

Tip: Consider reorganizing the indexes or redesigning the indexes and tables. When reorganizing these indexes or redesigning these indexes and tables, consider the following factors:

• Key lengths
• Free space
• Insert, update, and delete patterns
• Insert, update, and delete frequencies

Indexes with large leaf pages distance

This option shows indexes that have large leaf page distances. Generally, leaf page distances greater than 200 might indicate a disorganized index. This rule is a general indicator, and in some cases large leaf distances are acceptable.

Which are type 1

This option shows indexes that are type 1.

Tip: Consider converting the index to a type 2 index.

With long RID chains

This option shows indexes that have long record identifier (RID) chains, which reduce performance during delete operations and during updates of the indexed data. Long RID chains result from a low number of distinct key values in relation to the total number of rows in the table.

Tip: Consider redesigning the index so that the key values are more likely to be distinct and so that the index is a type 2 index.

Not used by any plan or package with static SQL

This option shows indexes that are not used by any packages or plans.

Tip: If these indexes are not used in QMF or any other dynamic SQL, then you should consider dropping them.

Without STOSPACE information

This option shows objects that do not have STOSPACE information.

Tip: To collect STOSPACE information, run the STOSPACE utility. Consider running the STOSPACE utility periodically.

Exceeding allocated primary quantity

This option shows spaces that exceed the primary allocation.

Tip: If your query finds spaces that exceed primary quality, consider extending the primary quality.

With more than 'n' percent relocated rows

This option shows spaces in which more than 'n' percent of the rows do not reside in their original pages. These rows have been relocated.


Tip: Consider reorganizing the spaces or redesigning the program that update their rows.

With more than 'n' percent dropped space

This option shows spaces in which more than 'n' percent of the space contains dropped tables. This dropped space cannot be reused.
Tip: Consider reorganizing the spaces or using segmented table spaces for the tables.

With table space locking

This option shows spaces that have the locking rule of  'S'. When the locking rule equals 'S', DB2 uses table space locking when accessing tables in the space.

Tip: You should use table space locking for only read-only tables or tables that are accessed by one user (or batch job) at a time. Otherwise consider changing the locking rule to 'A' by altering the table's lock size.

Which are non-segmented and contain more than one table

This option shows non-segmented spaces that contain more than one table.
Tip: In most cases, such spaces should contain only a single table. Unless your design calls for more than one table in a non-segmented space, consider moving the tables to separate spaces.