Bookmark and Share
RSS

Recent Posts

DB2 12 In-Memory Index Optimization

September 27, 2016

Last month I wrote about the trend of increasing the amount of real storage when configuring new mainframe systems. DB2 12 (which is set for delivery in the fourth quarter of 2016) features many new performance improvements that are designed to take advantage of available real storage. One of these enhancements is called In-Memory Index Optimization -- aka, index fast traversal blocks (FTBs).

The FTB is a separate area outside of the buffer pool that requires additional real memory. This memory is an optimized structure for fast index lookups that provide random index access. The FTB contains the non-leaf structure of the index. The size of this memory area is controlled by a new system parameter, INDEX_MEMORY_CONTROL. When set to the default value of AUTO, the memory size allocated will be the minimum of either 500MB or 20 percent of the total allocated buffer pool storage across all buffer pools. An index is eligible to use FTBs when it's UNIQUE and has a maximum length of 64 bytes.

DB2 will automatically determine over time which indexes will and won't benefit from being in the FTB area. Indexes that are heavily used for read-only are good candidates for FTBs, provided the index access is predominantly random. On the other hand, indexes that frequently go through index page splits due to insert activity aren't well-suited for FTBs.

Each DB2 member in a data sharing environment has its own FTBs. The DISPLAY STATS command allows you to see which indexes use FTBs. Similarly to how auto cleanup of pseudo deletes are controlled through a table, DB2 12 uses the SYSINDEXCONTROL table to specify the time window when an index can be used in the FTB area.

In addition, two new IFCIDs have been introduced -- 389 and 477 -- to track the FTB usage at a detail level.

I look forward to working with DB2 12 and learning more about how DB2 will allocate real memory to save CPU and reduce elapse times.

Posted September 27, 2016 | Permalink