Utility Statements › TUNE INDEX › Usage

Usage

General Considerations

The TUNE INDEX utility has the following usage considerations:

• To use the TUNE INDEX utility, you must specify one of the following:
  • One or more tables whose indexed constraints are to be tuned
  • One or more areas containing tables whose indexed constraints are to be tuned
  • A subschema and DBNAME and optionally a list of indexed sets to be tuned
• If multiple indexes and/or multiple tables are processed in the same area, increasing the number of buffers further improves performance.
• Index tuning is a resource-intensive operation consisting mostly of CPU and I/O.

Operating modes

You can execute the TUNE INDEX utility both online (through the online command facility) and in batch through central version or batch local. When index tuning is executed by a central version, TUNE INDEX tries to minimize impact on other online tasks as follows:

• When a record or area lock conflict occurs with other applications, TUNE INDEX takes the following actions:
  • For record lock conflicts, TUNE INDEX commits the updates done so far.
  • For area lock conflicts, TUNE INDEX finishes its database session and starts a new one.
• TUNE INDEX lowers its priority to one below its normally assigned priority. If a deadlock occurs, the default deadlock selection algorithm selects the task with the lowest priority as the deadlock victim. The TUNE INDEX utility can recover from a deadlock by restarting the index tuning process of the current index occurrence.

Commit interval

You can specify a commit interval that determines the frequency with which the utility will commit. The interval specifies the number of updates that can take place before a commit is issued. You can disable committing and automatic restart by specifying a 0-commit interval. Regardless of the commit interval specified, the utility always issues a COMMIT ALL at the end of the tune process of an index occurrence to release all record locks. It also issues a COMMIT ALL if it detects that another task is waiting on a record lock that it holds and it issues a FINISH if it detects that another task is waiting on an area lock that it holds.

Notify interval

You can specify a time interval in minutes. Each time this interval expires, a message is written indicating the index tuning progress. The message is written to the job log and the operator's console if TUNE INDEX runs in local mode; otherwise, it is written to the IDMS LOG and console. You can disable notification by specifying a 0-notify interval.

TUNE OPTIONS Usage

When processing multiple indexes within a single execution of the TUNE INDEX utility the tune options to be used are determined by the following hierarchy:

1. Options specified for a particular index.
2. Options specified on a DEFAULT TUNE OPTIONS statement
3. The utility default values.