The DB2 12 for z/OS Blog Series – Part 8: Index FTBs

IBM has delivered many new in-memory processing capabilities in DB2 12 for z/OS, so much so that Gartner has dubbed DB2 12 for z/OS an in-memory DBMS. This is good news for those of us in the mainframe world looking to utilize memory to improve the performance of our database applications.

Perhaps the most interesting of the new in-memory features is the new Fast Traversal Blocks, or FTBs. An FTB is an in-memory structure that can be used with unique indexes. DB2 detects which indexes are frequently used for traversals, and when a threshold is hit DB2 will build an FTB for the index in a storage area outside the buffer pool. This causes the top levels of the index to be cached thereby making it efficient to perform very fast traversals through the cached levels of the index.

FTBs are either on or off for the entire DB2 subsystem. This is managed using the new DSNZPARM named INDEX_MEMORY_CONTROL. Setting this zparm to AUTO, which is the default, indicates that 500 MB or 20 percent of the buffer pool will be used for FTBs (whichever is larger). Alternatively, you can set the upper limit to a number between 10MB and 200 GB, or you can DISABLE the feature altogether.

It may be confusing to specify a percentage of the buffer pool for caching FTBs, especially so because FTBs are stored outside of DB2’s buffer pools – that means you will not be consuming valuable buffer pool space with FTBs because the FTBs are stored in their own area of memory.

FTBs are most likely to be used by DB2 shops that run many applications performing frequent lookups where the unique index is used predominantly for reads. In those scenarios FTBs may be able to deliver a significant performance improvement.

There are two new DB2 IFCID trace records that report on index FTB usage in DB2 12 for z/OS: IFCID 389 and 477. IFCID 389 traces indexes with FTB structures and IFCID 477 traces allocation and deallocation of FTB structures.

The type of information tracked by these ICFIDs includes the number of indexes with FTBs along with number of levels in the FTB and the size of the structure. Such details will be important for DBAs looking to manage and support index FTBs in DB2 12.