Enabling or disabling fast index traversal at the index level

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.

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);

  

• 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.

  

  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');

  

• 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').

  

  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);

  

• 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').

  

  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');
  

  

• To enable fast index traversal for only specific selected indexes, complete the following steps:
  1. 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);

  2. 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.