DB2 Server for VSE & VM: Control Center Operations Guide for VM

Chapter 32. Index Reorganization Tool

Overview

A database index is a set of pointers, logically ordered on a column or set of columns in a table, that exist to provide faster access and force uniqueness of rows within a table. Indexes are stored on index pages in the same DBSPACE as their associated tables. Control information about indexes is stored on header pages in the DBSPACE.

Extensive modifications to a table can fragment its indexes. This can lead to increased I/O and execution time whenever those indexes are used.

The Index Reorganization tool automates index maintenance by analyzing the indexes in your databases and/or reorganizing those that can benefit from it. You can select the indexes to be examined by:

1. Owner
2. DBSPACE name
3. Number of pages

To invoke the Index Reorganization tool through the panel interface,

1. Enter Option U on the Main Menu and press ENTER.
2. Enter Option RI on the Database Utility Functions screen and press ENTER.

To invoke the Index Reorganization tool directly from the CMS READY prompt:

1. Type SQLRINDX and press ENTER.

Note:
The user must own the indexes or have DBA authority in the target database. The user must have at least level 3 (Administrator) authority on the service machine for that database.

Indexes are reorganized using these methods:

1. Primary key indexes and unique indexes are reorganized using the ALTER TABLE statement.
2. Other active, valid indexes are reorganized using the database DBSU REORGANIZE INDEX command.

Note:
System catalog table indexes are analyzed BUT NOT REORGANIZED. Use Control Center's system catalog index reorganization utility to reorganize system catalog indexes. (Option RC on the Database Utility Functions menu).

The product index reorganizations run in multiple user mode (with the database up). They may execute on your own virtual machine or on a support machine. Remember that you or the support machine must be either the owner of the index or have DBA authority in the target database.

Features

The Index Reorganization tool:

• Runs in multiple user mode
• Lets you select indexes by:
  • Owner
  • DBSPACE name {with wildcard}
  • Number of DBSPACE pages
• Generates a list of indexes requiring reorganization
• Reorganizes indexes based on a previous candidate list
• Analyzes and performs the reorganizations automatically
• Allows scheduling of analysis and/or reorganization
• Provides an index analysis report (LISTINDEX)
• Provides an index reorganization report (INDEXPRIOR or RUNINDEX)

How the Index Reorganization Tool Works

The Index Reorganization tool uses the system catalogs to access information about an index. It calculates the number of pages that each index should occupy according to the guidelines given in Appendix "A" of the DB2 Server for VSE & VM Database Administration manual. Only valid, active indexes, primary keys, and unique constraints are considered in these calculations. The total of all calculated index pages is then compared to the actual number of pages the indexes occupy in the DBSPACE (from SHOW DBSPACE). If indexes occupy more than the calculated number of pages, the indexes in that DBSPACE are considered candidates for reorganization.

Two ranking factors are used to assign a relative need of reorganization for the indexes in a DBSPACE. They are:

1. Percent of index pages to be reclaimed by index reorganization:

   (Actual index pages - calculated index pages) / total index pages

2. Number of index pages to be reclaimed by index reorganization:

   Actual index pages - calculated index pages

Indexes in DBSPACES with higher ranking factors are reorganized first.

Note:
Page calculations for indexes on variable length columns are over estimated in accordance with the database guidelines. Over estimation keeps an index from being reorganized when it is not needed. Only valid and active indexes are reorganized. Up-to-date Statistics will help ensure accurate LISTINDEX calculations. PCTFREE retains its previous value.

Command Mode Invocation

To execute the Index Reorganization tool in command mode, the syntax is:

>>-+-SQLRINDX--------------------+----------------------------->< '-dbname function--(--option--'

where:

Parameter

Description

dbname

is the machine name (not the database name) of the database where the maintenance will be performed.

function

LISTINDEX, INDEXPRIOR, or RUNINDEX (See Functions).

options

(See the list of the valid parameters as described in Options).

If no parameters are specified, the Index Maintenance Utility Screen is displayed.

Index Maintenance Utility Screen

The Control Center Index Maintenance Utility Screen is shown in Figure 196. The first two parameters, FUNCTION and DATABASE, are required. They identify the function you want to perform and the database against which you want to perform it. If you are initialized to a database, that name will appear in the DATABASE field.

Depending on the function selected, the parameters below the line labeled "Options" will allow you to limit the scope of candidate selection and set time or quantity based execution limits.

Figure 196. Index Maintenance Utility Screen

+--------------------------------------------------------------------------------+
|*************    SQLRINDX - DATABASE INDEX MAINTENANCE UTILITY  **************  |
|                                                                                |
|FUNCTION ==>  LI       LI - List Index Reorg Candidates                         |
|              --       IP - Reorg Indexes using Prior List                      |
|                       RI - Evaluate and Reorg Indexes                          |
|                                                                                |
|DATABASE ==>  SQLDBA                                                            |
|              --------                                                          |
|    -----------------------------  Options  ------------------------------      |
|DBSPACE OWNER ==> SQLDBA              ( ALL, PUBLIC, PRIVATE, ownername )       |
|                  ------------------                                            |
|DBSPACE NAME  ==>                     ( blank for ALL, use % for wildcard )     |
|                  ------------------                                            |
|                                                                                |
|                                                                                |
|TIME LIMIT  ==>  180    (Minutes)        QTY LIMIT ==> 10    (Max. Run Qty)     |
|                 -----                                 -----                    |
|                                                                                |
|                                                                                |
|DBSPACE PAGES:  MINIMUM ==> 0          MAXIMUM     ==> 999999                   |
|                            -------                    -------                  |
| PF1 HELP      PF3 QUIT       PF6 Schedule        PF10 Process Immediately      |
+--------------------------------------------------------------------------------+

Functions

Each Index Reorganization function generates report and output files which are stored on the service machine for that database. Refer to Files and Reports for a description of these files. The function field can have these values:

Function

Description

LI (List Index)

Analyze indexes in the selected DBSPACES and list the DBSPACES with indexes requiring reorganization.

IP (Index Previous)

Reorganize indexes in DBSPACES identified as candidates in a previous evaluation by LI (List Index) or RI (Run Index). The previous candidate list (dbname INDXMAIN) must exist on the A disk of the service machine. The options selected will determine which DBSPACES in the INDXMAIN file will be selected. Reorganization proceeds until the TIME or QTY LIMIT is reached.

RI (Reorganize Indexes)

Analyze indexes in the selected DBSPACES and create a list of candidate indexes. Reorganize indexes based on the Options selected until the TIME or QTY LIMIT is reached.

Options

These options apply in panel interface as well as command mode.

Option

Description

DBSPACE OWNER

Used with the LI, IP, and RI functions. Specifies whether ALL, PUBLIC, PRIVATE, or specific DBSPACE owner(s) are selected for analysis or reorganization. The percent sign (%) can be used at the beginning or end of a specified DBSPACE owner to select LIKE DBSPACE owners. Default is ALL.

Command mode syntax: OWNer=

DBSPACE NAME

Used with the LI, IP and RI functions. Specifies a DBSPACE name or group of names to be selected for analysis. The percent sign (%) can be used as at the beginning or end of a DBSPACE name to select LIKE DBSPACES. Default is ALL.

Command mode syntax: DBSPname=

TIME LIMIT

Used with the IP and RI functions. Limits reorganization to a specific amount of time in minutes. Once this limit is reached, no more index reorganizations are started. Default is 180 minutes.

Command mode syntax: TIME=

QTY LIMIT

Used with the IP and RI functions. Limits index reorganization to the specified number of DBSPACES. Default is 10.

Command mode syntax: QTY=

DBSPACE PAGES

Used with the LI, IP and RI functions. Limits index analysis (LISTINDEX) or reorganization (INDEXPRIOR, RUNINDEX) to DBSPACES that fall within the specified range of pages. Default minimum is 0, default maximum is 999999.

Command mode syntax: DBMIN=, DBMAX=

Files and Reports

Each index reorganization function generates report and output files which are created on the virtual machine where the Index Reorganization is run and sent to the service machine for that database.

The index reorganization files remain on the "A" disk of the user or Control Center support machine and the Control Center service machine. The files on the service machine are used for future index maintenance and to ensure that:

• Index analysis and reorganizations will not be needlessly duplicated by other DBA's
• All DBA's for a database will have easy access to the same index reorganization information.

Each file used in the Index Reorganization process is described below:

File

Description

dbname INDXPICK

Created during the LISTINDEX and RUNINDEX functions. Contains a list of DBSPACES and indexes selected for reorganization. This is kept as a record of LISTINDEX selections and not updated by INDEXPRIOR or RUNINDEX functions. Renamed by reversing the filetype when another LISTINDEX is performed against the same database. Overwritten on the service machine.

dbname INDXMAIN

Created by both the LISTINDEX and RUNINDEX functions. Contains a list of DBSPACES and indexes selected for reorganization. Sent to the service machine and the user machine executing INDEXPRIOR. Returned to the service machine when processing is completed. Updated by removing entries reorganized by INDEXPRIOR and RUNINDEX. Deleted when all entries have been removed. Renamed by reversing the filetype when another LISTINDEX is performed against the same database. Overwritten on the service machine.

dbname XXXXINDX

Created by LISTINDEX function where XXXX = the first 4 characters of OWNER. Summarizes the DBSPACE index analysis and reports whether a DBSPACE has been chosen as a candidate for reorganization. Replaced when another LISTINDEX is performed on the same database with the same OWNER. Backed-up on the user machine's A disk before being overwritten.

dbname RUNINDX

Created by the INDEXPRIOR and RUNINDEX functions. Shows the result of index reorganization on a DBSPACE's index pages.

SQLRINDX DBSUERR

Created during INDEXPRIOR and RUNINDEX. Contains a listing of any DBSU errors encountered. Backed up by reversing the filetype before being over written. Remains on the user's A disk and is not kept on the service machine.

Sample Index Analysis Report

The figure which follows is an example of a DBSPACE Index Reorganization Candidates Report.

Figure 197. DBSPACE Index Reorganization Candidates Report

DBSPACE INDEX REORGANIZATION CANDIDATES REPORT Dbname: WMAVM1.SQL34DBA CNTRLID: SQMSTTS1 03/13/97 08:43:58 OPTIONS: Dbspaces: ALL.ALL, min=0, max=999999, qty=3, time=180 DBSPACES EXAMINED = 23 ************************************************************** CRITERIA 1 = Percent of Index Pages to be Reclaimed by Reorg CRITERIA 2 = Number of Index Pages to be Reclaimed by Reorg **************************************************************************** ANDYS.ANDYS DBSPACENO: 9 NBR PAGES OCCUPIED PAGES %FREE EMPTY SHOW DBSPACE INDEX: 200 100 ( 50%) 98% 0 CALCULATED INDEX: 50 ( 25%) RECLAIM: 50 ( 25%) Index reorganization candidate **************************************************************************** RAYM.RAYM DBSPACENO: 12 NBR PAGES OCCUPIED PAGES %FREE EMPTY SHOW DBSPACE INDEX: 84 17 ( 20%) 45% 0 CALCULATED INDEX: 17 ( 0%) RECLAIM: 0 ( 0%) Actual occupied index pages are less than/equal calculated index pages. Indexes will NOT be reorganized ************************************************************************* PUBLIC.SYS0001 DBSPACENO: 1 NBR PAGES OCCUPIED PAGES %FREE EMPTY SHOW DBSPACE INDEX: 7680 4433 ( 57%) 61% 0 CALCULATED INDEX: 113 ( 0%) RECLAIM: 4320 ( 56%) WARNING: This DBSPACE contains system indexes. Only the non-system indexes in this dbspace will be reorged by SQLRINDX. Use CTRLCTR Reorganize Catalog Index tool to reorganize System catalog indexes. Index reorganization candidate. ******* THERE ARE 2 CANDIDATES FOR REORGANIZATION *****

In the example above, all 23 DBSPACES in the database were included. QTY= 3 was specified so analysis ceased after the third DBSPACE. Two DBSPACES were identified as candidates for index reorganization.

Sample Index Reorganization Report

The figure which follows is an example of a DBSPACE Index Reorganization Report.

Figure 198. DBSPACE Index Reorganization Candidates Report

DBSPACE INDEX REORGANIZATION REPORT Database: WMAVM1.SQL34DBA 03/13/97 11:33:50 ELAPSE CALC OWNER DBSPACENAME SPNO TIME ST W1 W2 IPGS INDEX STATISTICS _____ __________________ ____ ______ __ ____ ____ ____ ________________ ANDYS ANDYS 9 0.01 00 0% 0 50 200 100(50%) 98% ****** After REORG: 200 50(25%) 98% Number of dbspaces successfully reorged = 1 Total elapsed time for all reorgs = 1.00 minute End of Report for SQL34DBA INDEX REORG JOB NOTE : SPNO - dbspace number ELAPSE- elapsed time, in minutes, for the REORG W1 - Percent of index pages reclaimed by reorg W2 - Number of index pages reclaimed by reorg CALC IPGS - Number of pages indexes should occupy ST - status of the REORG '00' Good Reorg 'CC' Good Reorg but calculated index pages were under estimated 'SY' Good Reorg but System Catalog Indexes in this Dbspace were not Reorged 'TT' Not enough time left to reorg this space 'XX' Bad Index Reorgs occurred in this dbspace DATA/INDEX BEFORE - the results of a SHOW DBSPACE command before the REORG was done, format: tttt uu(pp%) ff% where: tttt is total pages, uu is pages used, pp is percent of pages used, ff is avg. percent free per page. DATA/INDEX AFTER - the results of a SHOW DBSPACE command after the REORG was done