TUNE INDEX

The TUNE INDEX utility performs the following functions:
idmscu19
The TUNE INDEX utility performs the following functions:
  • Adopts orphans in an index structure. An
    orphaned indexed record
    is a record whose index pointer does not point back to the index record (SR8) that contains the record's index entry. Orphans occur as the result of splitting an existing SR8 into two records to accommodate a new entry. As part of the split, some of the entries are moved to a new SR8, but the index pointer in their associated records is not adjusted to reflect the change, resulting in "orphaned" records. By eliminating orphans, runtime database performance is improved when traversing from an indexed record to its associated index entry.
  • Moves the top level SR8 to its optimal location.
  • Optionally rebalances the index structure. Rebalancing ensures that the resulting index structure is a balanced tree and has a minimal number of levels and SR8's. You can temporarily override the index block contains value of the index and the page reserve value of the area that contains the index structure. Using these overrides allows tuning the index while allowing for future growth.
  • Optionally resequences the index structure. Resequencing puts the SR8 records in physical sequence. By resequencing the index structure, database performance is improved when accessing the index structure sequentially at the bottom level.
Under some circumstances the process of moving the top level SR8 to its optimal location might cause some degree of resequencing to be performed even if RESEQUENCE NO is specified, or when the RESEQUENCE option is allowed to default to NO.
This article describes the following information:
2
2
Authorization
A user must have DBAWRITE access on all areas processed by the utility.
Syntax
  ►►─ TUNE INDEX FOR ─────────────────────────────────────────────────────────────►  ►─┬─ AREA sql-area-name ──────────────────────────────────────────────────────┬►    │                                                                           │    │         ┌───────────────────────────────,───────────────────────────────┐ │    ├─ TABLE ─▼─┬──────────────┬ table-name ┬────────────────┬┬──────────────┬┴─┤    │           └ schema-name. ┘            └ ix-or-con-spec ┘└ tune-options ┘  │    │                                                                           │    └─ DBNAME dbname SUBSCHEMA ss-name ─┬────────────┬──────────────────────────┘                                        └─ set-spec ─┘    ►─┬─────────────────────────────────────────────┬─────────────────────────────►    └─ DEFAULT ─┬────────────────┬─ tune-options ─┘                └─ TUNE OPTIONS ─┘  ►─┬──────────────────────────────────────┬────────────────────────────────────►    └─ COMMIT INTERVAL ─┬─ cmt-interval ─┬─┘                        └─ 100 ◄─────────┘  ►─┬──────────────────────────────────────┬────────────────────────────────────►◄    └─ NOTIFY INTERVAL ─┬─ not-interval ─┬─┘                        └─ 0 ◄───────────┘  
Expansion of
 
ix-or-con-spec
   ┌─────────────────────────────────────────────────────────────────────┐ ►►─┴─┬─ CONSTRAINT ─┬─ constraint-name ──────────────────────────────┬─┬─┴─────►◄      │              │                                                │ │      │              │     ┌──────────────────,─────────────────┐     │ │      │              └─ ( ─▼─ constraint-name ─┬───────────────┬┴─ ) ─┘ │      │                                        └─ tune-options ┘        │      └─ INDEX ─┬─ index-name ───────────────────────────────┬──────────┘                │                                            │                │     ┌──────────────,─────────────────┐     │                └─ ( ─▼─ index-name ─┬────────────────┬┴─ ) ─┘                                     └─ tune-options ─┘  
Expansion of
 
set-spec
►►─── SET ─┬─ set-name ──────────────────────────────┬─────────────────────────►◄            │                                         │            │     ┌───────────────,─────────────┐     │            └─ ( ─▼─ set-name ┬────────────────┬┴─ ) ─┘                              └─ tune-options ─┘  
Expansion of
 
tune-options
    ┌───────────────────────────────────────────────────────────────────────┐ ►►──▼─┬─ REBALANCE ─┬─ NO ◄───┬───────────────────────────────────────────┬─┴──►◄       │             └─ YES ───┘                                           │       │                                                                   │       ├─ RESEQUENCE ─┬─ NO ◄───┬──────────────────────────────────────────┤       │              └─ YES ───┘                                          │       │                                                                   │       ├─ TEMPORARY INDEX UTILIZATION ┬────┬─┬ ixutil-pct ┬ PERCENT ─┬─┬───┤       │                              └ IS ┘ │            └─ % ──────┘ │   │       │                                     └─ key-count ─────────────┘   │       │                                                                   │       └─ TEMPORARY PAGE RESERVE ┬────┬─┬ page-reserve-pct ┬ PERCENT ─┬─┬──┘                                 └ IS ┘ │                  └─ % ──────┘ │                                        └─ reserve-character-count ─────┘  
Parameters
  • schema-name
    Identifies the schema that will qualify the table name.
    If omitted, the current session associated with the user session schema is used. Schema-name is required if there is no current session schema.
  • table-name
    Identifies the table that is constrained by an indexed constraint.
  • ix-or-con-spec
    Identifies constraints and indexes on the current table that is being tuned.
    If omitted, all indexed constraints and indexes on the current table are processed.
  • sql-segment.area-name
    Identifies an sql-defined area to be selected for processing.
    All tables with indexed constraints in the area are processed.
  • dbname
    Identifies the dbname to be used when binding the subschema.
  • ss-name
    Identifies the subschema to be used for processing a non-SQL database.
  • set-name
    Identifies the indexed sets within the subschema that are to be processed.
    If omitted, all linked indexed sets defined in the subschema are processed. (Linked indexed sets are indexed sets with index pointers.)
  • cmt-interval
    Specifies the commit interval. After every cmt-interval record read, a commit is issued. If omitted, the default interval is 100. If specified as zero (0), no commits are issued.
  • not-interval
    Specifies the length of the notify interval in minutes. After each interval expires, a message is issued stating how far the job has progressed. If the notify interval is specified as 0 or allowed to default to 0, no notify messages are created.
  • constraint-name
    Identifies an indexed constraint on the current table that is to be tuned.
  • index-name
    Identifies an index on the current table that is to be tuned.
  • DEFAULT TUNE OPTIONS
    The tune options to be used during the processing of an index if tune options have not been specifically specified.
  • REBALANCE
    Specifies whether to rebalance the index. A well-balanced index has the minimum number of index levels and best performance if the index is frequently accessed vertically from top to bottom.
    • YES
      Rebalances the index.
      Rebalancing an index can be resource-intensive.
    • NO
      No rebalancing is done.
  • RESEQUENCE
    Specifies whether to resequence the index. A properly sequenced index is important only if the index is frequently accessed sequentially at the bottom level.
    • YES
      Resequences the index for optimum performance.
      Resequencing an index can be resource-intensive.
    • NO
      No resequencing is done.
  • TEMPORARY INDEX UTILIZATION
    Specifies a temporary override for the operation. If not specified, the current run-time value for INDEX BLOCK CONTAINS is used and index blocks are used at 100%.
    • ixutil-pct
      Specifies the percentage of the maximum number of entries that each index block should contain after tuning is complete.
      ixutil-pct
      is an integer in the range 10 through 100. The number of entries of an index block is computed as
      index-block-contains
      *
      ixutil-pct
      / 100.
    • key-count
      Specifies the maximum number of entries that each index block should contain after tuning is complete.
      key-count
      is an integer in the range 3 through 8180.
      If the specified value exceeds the current run-time value of the INDEX BLOCK CONTAINS, the key-count value is ignored and the INDEX BLOCK CONTAINS value will be used.
  • TEMPORARY PAGE RESERVE
    Specifies a temporary override of the page reserve for the area in which the index resides. If not specified or specified as NULL, the page reserve of the area in which the index resides is used.
    • page-reserve-pct
      Specifies the percentage of each page to leave as free space if it contains a portion of an index being tuned.
      page-reserve-pct
      is an integer in the range 0 through 30. The page reserve of the area is computed as
      area-page-size
      *
      page-reserve-pct
      / 100.
    • reserve-character-count
      Specifies the number of characters to reserve on each page to accommodate increases in the length of records or rows stored on the page if it contains a portion of an index being tuned.
      reserve-character-count
      is an integer with a value not larger than 30% of the page size.
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.
JCL Considerations
When you submit a TUNE INDEX utility through the batch command facility in local mode, the JCL to execute the facility must include statements to define the files containing the areas to be processed. To run under central version, a SYSCTL statement is needed.
Examples
The following example directs the TUNE INDEX utility to adopt orphaned index records in the EMPDEMO dbname using subschema EMPSS01:
tune index for dbname empdemo subschema empss01;
The following example directs the TUNE INDEX utility to adopt orphaned index records, rebalance and resequence the index. It also shows how to temporarily override the DMCL or subschema values for PAGE RESERVE and INDEX BLOCK CONTAINS.
TUNE INDEX FOR DBNAME EMPDEMO SUBSCHEMA EMPSS01            SET (EMP-NAME-NDX)            DEFAULT TUNE OPTIONS               REBALANCE  YES               RESEQUENCE YES               TEMPORARY INDEX UTILIZATION IS 80 %               TEMPORARY PAGE RESERVE IS 15 PERCENT            NOTIFY INTERVAL 1000;
Sample Output
The following example directs the TUNE INDEX utility to adopt orphaned index records in the EMPDEMO dbname using subschema EMPSS01:
IDMSBCF  nn.n                            CA IDMS Batch Command Facility TUNE INDEX FOR DBNAME EMPDEMO SUBSCHEMA EMPSS01; Status = 0        SQLSTATE = 00000        Messages follow: DB002994 C0M333: IDMSTUNE  -  processing started DB002994 C0M333: IDMSTUNE  -  Indexes selected for processing: DB002994 C0M333: IDMSTUNE  -  SKILL-EXPERTISE (IBC=30) in area EMPDEMO.ORG-DEMO-REGION (PGRSV=0) DB002994 C0M333: IDMSTUNE  -  EMP-NAME-NDX (IBC=40) in area EMPDEMO.EMP-DEMO-REGION (PGRSV=0) DB002994 C0M333: IDMSTUNE  -  OFFICE-EMPLOYEE (IBC=30) in area EMPDEMO.ORG-DEMO-REGION (PGRSV=0) DB002994 C0M333: IDMSTUNE  -  SKILL-NAME-NDX (IBC=30) in area EMPDEMO.ORG-DEMO-REGION (PGRSV=0) DB002994 C0M333: IDMSTUNE  -  JOB-TITLE-NDX (IBC=30) in area EMPDEMO.ORG-DEMO-REGION (PGRSV=0) DB002994 C0M333: IDMSTUNE  -  Statistics for area EMP-DEMO-REGION DB002994 C0M333: IDMSTUNE  -  Orphan adoption read 36 records (of which 8 SR8s) DB002994 C0M333: IDMSTUNE  -  Orphan adoption adopted 20 index orphans DB002994 C0M333: IDMSTUNE  -  Resequencing read 4 records DB002994 C0M333: IDMSTUNE  -  Statistics for area ORG-DEMO-REGION DB002994 C0M333: IDMSTUNE  -  Orphan adoption read 259 records (of which 63 SR8s) DB002994 C0M333: IDMSTUNE  -  Orphan adoption adopted 0 index orphans DB002994 C0M333: IDMSTUNE  -  Resequencing read 126 records DB002994 C0M333: IDMSTUNE  -  425 total records read DB002994 C0M333: IDMSTUNE  -  20 total index orphans adopted DB002994 C0M333: IDMSTUNE  -  5 indexes/sets processed DB002994 C0M333: IDMSTUNE  -  processing completed AutoCommit will COMMIT transaction Command Facility ended with no errors or warnings
The following example directs the TUNE INDEX utility to adopt orphaned index records, rebalance and resequence the index. It also shows how to temporarily override the DMCL or subschema values for PAGE RESERVE and INDEX BLOCK CONTAINS.
TUNE INDEX FOR DBNAME EMPDEMO SUBSCHEMA EMPSS01            SET (EMP-NAME-NDX)            DEFAULT TUNE OPTIONS               REBALANCE  YES               RESEQUENCE YES               TEMPORARY INDEX UTILIZATION IS 80 %               TEMPORARY PAGE RESERVE IS 15 PERCENT            NOTIFY INTERVAL 1000;
The following report sample is produced by the TUNE INDEX utility.
TUNE INDEX FOR DBNAME EMPDEMO SUBSCHEMA EMPSS01            SET (EMP-NAME-NDX)            DEFAULT TUNE OPTIONS               REBALANCE  YES               RESEQUENCE YES               TEMPORARY INDEX UTILIZATION IS 80 %               TEMPORARY PAGE RESERVE IS 15 PERCENT            NOTIFY INTERVAL 1000; Status = 0        SQLSTATE = 00000        Messages follow: DB002994 C0M333: IDMSTUNE  -  processing started DB002994 C0M333: IDMSTUNE  -  Indexes selected for processing: DB002994 C0M333: IDMSTUNE  -  EMP-NAME-NDX (IBC=32) in area EMPDEMO.EMP-DEMO-REGION (PGRSV=644) DB002994 C0M333: IDMSTUNE  -  Statistics for area EMP-DEMO-REGION DB002994 C0M333: IDMSTUNE  -  Orphan adoption read 34 records (of which 6 SR8s) DB002994 C0M333: IDMSTUNE  -  Orphan adoption adopted 20 index orphans DB002994 C0M333: IDMSTUNE  -  Rebalancing read 65 records DB002994 C0M333: IDMSTUNE  -  Resequencing read 35 records DB002994 C0M333: IDMSTUNE  -  134 total records read DB002994 C0M333: IDMSTUNE  -  20 total index orphans adopted DB002994 C0M333: IDMSTUNE  -  1 indexes/sets processed DB002994 C0M333: IDMSTUNE  -  processing completed