This Db2 for z/OS News from the Lab blog entry was originally published on 2018-05-08.

This entry is the first in a series that provides John Campbell's perspective on some of the most popular new features and capabilities in Db2 12 for z/OS.

Fast index traversal is one of the the most important new performance features in Db2 12 for z/OS. It is an in-memory index performance optimization that enables Db2 to do fast index lookup, by avoiding expensive index B-tree traversal. To benefit from fast index traversal, data access must use a random pattern. However, SELECT, INSERT, DELETE and UPDATE statements, and in fact any operation that requires traversal of an index, can benefit from fast index traversal.

During the early support program for Db2 12, one client who studied fast index traversal in detail saw a 9.1 percent CPU reduction with a three-level index, and a 22.9 percent CPU reduction with a four-level index. So the more index levels that there are, the greater the CPU benefit from fast index traversal. However, your own mileage will vary in terms of CPU reduction.

Fast index traversal is supported by a separate fast traverse block (FTB) memory area that is allocated outside of the bufferpools. This memory area uses a concatenated structure that contains copies of non-leaf index pages only, in a relative structure. Fast index traversal does not use the bufferpools. That is, the non-leaf index pages (except for the root page) that are cached in the FTB memory area are not fixed in the bufferpool. The index pages in the bufferpool are eligible for stealing, and can be removed from the bufferpool, on a least-recently used basis when the non-leaf pages are stored in the FTB memory area. The performance benefit comes about because the FTB memory area is an L2 cache-aware B-Tree like structure, and each page is equal in size to one cache line, or 256 bytes.

Each Db2 subsystem or data sharing member uses a single FTB memory area. The size of the FTB memory area is controlled by the INDEX_MEMORY_CONTROL subsystem parameter. The default setting AUTO means that the size of the FTB memory area is allocated as 20 percent of the total memory allocated for all buffer pools, with a minimum size of 10 MB. The size of the FTB memory area is adjusted as the size of any buffer pool changes. The recalculation and adjustment of the FTB memory area happens every 10 minutes. So the size adjustment might not occur right away when a buffer pool is changed, but it is updated automatically later, on the fly. Alternatively, you can specify a fixed size for the FTB memory area, up to maximum size of 200 GB.

A daemon running in the background in each Db2 subsystem determines which indexes are good candidates for the FTB memory area. To qualify, indexes must be UNIQUE, and INCLUDE columns are also supported. The length of the index entry, including the key and any additional columns, can have a maximum size of 64 bytes. The daemon reevaluates and adjusts its priority queue every two minutes.

To determine which non-leaf index pages are good candidates for fast index traversal, the daemon maintains a counter for each index pageset partition, and it applies an internal threshold to the value of the counter. The counter is adjusted differently for each of the following events:

• For each index traversal, the counter is increased by one.
• Fore each index-only access occurs, the counter is increased by two.
• For each index leaf-page split, the counter is divided by two.
• For each index look-aside the counter is reduced by one.

From that, we hope that you can appreciate that indexes with frequent index traversals are good candidates, and indexes with frequent index leaf-page splits are bad candidates.

You can use the SYSIBM.SYSINDEXCONTROL table to control the use of fast index traversal for specific indexes. However, indexes that experience frequent index leaf-page splits are not stored in the FTB memory area, regardless of whether they are included in this table. Also, you can add entries to disable use of the FTB memory area for specific indexes.

So, how does a index pageset partition come into the FTB memory area? The answer is that the daemon task, which is zIIP-eligible, runs in the background and reevaluates and adjusts the priority queue every two minutes. The daemon is a system thread and you can identify it by using the -DISPLAY THREAD command with the TYPE(SYSTEM) option. The correlation identifier of the daemon is IFTOMK00. Message DSNV497I shows the result:

    DSNV497I  -DB2A SYSTEM THREADS -DB2 ACTIVE                  

    NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN

    ...

    DB2A     N  *     0 014.RTSTST00 SYSOPR            004C     0

    V490-SUSPENDED 17081-10:05:25.83 DSNB1TMR +00000EBF UI38562  

    DB2A     N  *     0 014.IDAEMK00 SYSOPR            004C     0

    V490-SUSPENDED 17081-10:01:16.95 DSNB1TMR +00000EBF UI38562  

    DB2A     N  *     0 014.IFTOMK00 SYSOPR            004C     0

    V490-SUSPENDED 17081-10:05:22.32 DSNB1TMR +00000EBF UI38562  

    DB2A     N  *     0 010.PM2PCP01 SYSOPR            004C     0

    V490-SUSPENDED 17081-10:05:26.51 DSNB1TMR +00000EBF UI3856

You can monitor and trace the use of the FTB memory area for fast index traversal. The -DISPLAY STATS command can be used, with the STATS(INDEXMEMORYUSAGE) option or the abbreviation STATS(IMU), to show which index pageset partitions are cached in the FTB memory area and how much memory is used for each index pageset partition. Message DSNT783I shows the result.

    DSNT783I  -DB2A                                                              

    DBID PSID DBNAME   CREATOR        INDEXNAME      LEVEL PART  SIZE(KB)        

    ---- ------------  -------------- -------------- ----- ----- --------

    0256 0005 SZI10D   § § § § § § §  SZI10X         0002  00001 00000025        

    0261 0005 SZI20D   A2345678901234 SZI20X         0002  00001 00000025        

    0262 0005 SZI30D   SYSADM         X2345678901234 0002  00001 00000025        

    0263 0005 SZI40D   SYSADM         SZI40X         0002  00001 00000025        

    ******* DISPLAY OF STATS TERMINATED *********************************        

    DSN9022I  - DB2A DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION

You can also trace IFCIDs 477 and 389 to get more detailed information about the use of the FTB memory area. IFCID 477 records the allocation and deallocation of an FTB. You can start the trace with the following command:

    -START TRACE (PERFM) DEST(SMF) IFCID(477)

IFCID 389 records all index page set partitions with have FTBs. You can start the trace with the following command:

    -START TRACE(STAT) DEST(SMF) CLASS(8) IFCID(389)

The blocks for index pageset partitions in the FTB memory area are freed in the following situations:

• Pageset close
• SQL mass delete
• ALTER INDEX, RECOVER INDEX, or REBUILD INDEX
• ALTER INDEX form COPY YES to COPY NO (and the other way around)

What about data sharing? The following diagram shows the high-level picture of the data sharing design considerations. The example assumes that the index pageset partition is group buffer pool (GBP)-dependent, and GBP-dependent protocols are being followed.

Index pages are always read and updated in the local buffer pool. The respective pages are registered in the associated GBP cache structure. When a Db2 member updates an index page, and it is pushed out to the group bufferpool cache structure, a cross-invalidate signal is sent to other Db2 members that have interest in that page. These members must refresh their copy of the page from the GBP cache structure because their existing version of the page is stale and down-level.

The FTB memory area is a read-only area. When a structure modification occurs for an index page set partition, the FTB must be refreshed. Index leaf page splits and consolidations are examples of such structure changes. A new FTB p-lock and IRLM notify are used for updating the FTB for the subject index pageset partition on all members of the data sharing group.

For migration, Db2 12 can use fast index traversal in function level 100, before you activate new function. In data sharing, the Db2 12 members can use fast index traversal in mixed-release coexistence, where some members still run Db2 11. In that case however, the FTB memory area is used only for index pageset partitions that are not GBP-dependent. If an index pageset partition becomes GBP-dependent, the FTB content is deleted or bypassed. After you activate function level 500 or higher in Db2 12, the FTB memory area can be used for GBP-dependent index page set partitions.

Related information: Db2 12 What's new: Improved random index access Fast index traversal Enabling or disabling fast index traversal at the index level

John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development and Paul McWilliams is a technical writer for Db2 for z/OS.