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.
