You can insert rows into the SYSIBM.SYSINDEXCONTROL catalog table to control which indexes or index partitions can use fast index traversal (sometimes called fast traverse blocks
or FTB
), and when those objects can use fast index traversal.
Ensure that your authorization ID has the privileges to insert, update, and delete rows in the SYSIBM.SYSINDEXCONTROL catalog table.
Indexes with definitions that meet the criteria for fast index traversal are called eligible indexes. For more information, see "Index eligibility for fast index traversal" in Fast index traversal. Eligible indexes that Db2 determines have enough activity to make fast index traversal worthwhile are called candidate indexes.
When you enable fast index traversal through the INDEX_MEMORY_CONTROL subsystem parameter, you enable fast index traversal for all candidate indexes in the Db2 subsystem while memory is available. If you want to increase the priority for fast index traversal usage for only a subset of the candidate indexes, you can specify those indexes in the SYSIBM.SYSINDEXCONTROL catalog table.
However, if the (SELECTED,AUTO) or (SELECTED,n) settings are used for the INDEX_MEMORY_CONTROL subsystem parameter, Db2 ignores SYSIBM.SYSINDEXCONTROL rows that do not specify ACTION='A'.![End of change](../../deltaend.gif)
You can use the DISPLAY STATS command with the INDEXTRAVERSECOUNT count to identify the indexes with the most traversals. For more information, see -DISPLAY STATS command (Db2).![End of change](../../deltaend.gif)
To specify exceptions to the automatic processing that Db2 performs when it considers candidate indexes or index partitions for fast index traversal, insert rows like the ones in these examples into SYSIBM.SYSINDEXCONTROL:
- To specify that a candidate index to has high priority for fast index traversal at any time, insert a row with the index name into SYSIBM.SYSINDEXCONTROL, with an action of force ('F'), and NULL for the month, day, start time, and end time.
For example, to specify that index SYSADM.IX1 gets high priority for fast index traversal all the time, insert a row like this one:
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES(NULL, NULL, 'IX1', 'SYSADM',
'F', 'F', 'W',
NULL, NULL, NULL, NULL);
![End general-use programming interface information.](../cmn/../art/gupi_cls.svg)
- To specify that an index partition has high priority for fast index traversal for a specified time interval on a certain day of the month or week, insert a row with the index name and partition number into SYSIBM.SYSINDEXCONTROL, with an action of force ('F'), the day of the week or month and the time for the high priority to be in effect.
![Begin general-use programming interface information.](../cmn/../art/gupi_opn.svg)
For example, to specify that partition 10 of index SYSADM.IX1 gets high priority for fast index traversal each Monday from 8:00 AM to 10:00 AM local time, and each Tuesday from 3:00 PM to 5:00 PM local time, insert rows like this one:
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES(NULL, 10, 'IX1', 'SYSADM',
'F', 'F', 'W',
NULL, 1, '08:00:00','10:00:00');
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES(NULL, 10, 'IX1', 'SYSADM',
'F', 'F', 'W',
NULL, 2, '15:00:00','17:00:00');
![End general-use programming interface information.](../cmn/../art/gupi_cls.svg)
- To completely disable fast index traversal for an index in a member of a data sharing group, insert a row with the member name into SYSIBM.SYSINDEXCONTROL, with the index name, the data sharing member ID, and an action of disable ('D').
![Begin general-use programming interface information.](../cmn/../art/gupi_opn.svg)
For example, to specify that fast index traversal for index SYSADM.IX1 is disabled on data sharing members DB2A and DB2B, insert rows like this one:
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES('DB2A', NULL, 'IX1', 'SYSADM',
'F', 'D', 'W',
NULL, NULL, NULL, NULL);
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES('DB2B', NULL, 'IX1', 'SYSADM',
'F', 'D', 'W',
NULL, NULL, NULL, NULL);
![End general-use programming interface information.](../cmn/../art/gupi_cls.svg)
- To disable fast index traversal for an index on a single member of a data sharing group for a specified time interval on a certain day of the month or week, insert a row into SYSIBM.SYSINDEXCONTROL, with the member name, the index name, the data sharing member ID, the day identifier, the start time and end time, and an action of disable ('D').
![Begin general-use programming interface information.](../cmn/../art/gupi_opn.svg)
For example, to specify that fast index traversal for index SYSADM.IX1 is disabled on data sharing member DB2C on the first day of each month between midnight and noon, insert a row like this one:
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES('DB2C', NULL, 'IX1', 'SYSADM',
'F', 'D', 'M',
1, NULL, '00:00:00','12:00:00');
![End general-use programming interface information.](../cmn/../art/gupi_cls.svg)
To enable fast index traversal for only specific selected indexes, complete the following steps:
- Add rows to the SYSIBM.SYSINDEXCONTROL catalog table and specify 'A' for the ACTION column for each index that you want to select for fast index traversal.
For example, to specify that index SYSADM.IX1 is selected for fast index traversal, insert a row like this one:
INSERT INTO SYSIBM.SYSINDEXCONTROL
(SSID, PARTITION, IXNAME, IXCREATOR,
TYPE, ACTION, MONTH_WEEK,
MONTH, DAY, FROM_TIME, TO_TIME)
VALUES(NULL, NULL, 'IX1', 'SYSADM',
'F', 'A', 'W',
NULL, NULL, NULL, NULL);
- Set the INDEX_MEMORY_CONTROL subsystem parameter to (SELECTED,AUTO).
You can also specify (SELECTED,n), where n (in the range 1–200,000) is the buffer pool storage limit in megabytes.
Fast index traversal in enabled only for any indexes recorded in SYSIBM.SYSINDEXCONTROL with 'A' in the ACTION column.
![End of change](../../deltaend.gif)
After you insert rows into SYSIBM.SYSINDEXCONTROL, Db2 processes the requested changes to fast index traversal within 10 minutes.
![Begin program-specific programming interface information.](../cmn/../art/pspi_opn.svg)
To monitor the effect of your changes to fast index traversal, start a statistics class 8 trace for IFCID 389 and a performance class 4 trace for IFCID 477.
![End program-specific programming interface information.](../cmn/../art/pspi_cls.svg)