Db2 for z/OS and its ecosystem

By Akiko Hoshikawa and Paul McWilliams.

The fast index traversal feature of Db2 12 (often called "FTB" for "fast traverse blocks") delivers valuable Db2 transaction performance improvements without the need for application changes. FTBs use a cache-friendly optimized in-memory structure to accelerate index-tree traversals, which can significantly reduce get page operations and CPU consumption. They are enabled by default in Db2 12 by the INDEX_MEMORY_CONTROL subsystem parameter setting and can take effect as soon as Db2 12 function level 100.

Some early adopters of FTBs encountered high-impact problems related to the timing of updates to the in-memory structures, and some clients who encountered these issues disabled the use of FTBs in their Db2 environments.

In response, Db2 development has undertaken extensive analysis and quality improvements for FTBs, including: improved serviceability, improved test coverage, and resolution of all known FTB-related software defects, whether reported externally or resulting from the improved internal serviceability and test coverage.

If you currently have FTBs enabled in your Db2 12 environment, ensure that you have tested in pre-production and applied all APAR fixes listed below, to ensure that you resolve potential timing or data quality issues.

If you are waiting to re-enable FTBs or preparing to enable them for the first time, we suggest that you follow a three-step approach to start using them:

Step 1: Apply important APAR fixes

The following table lists the most important APARs to apply at the time of writing. It is not a comprehensive list of the APARs and PTFs in this area. If you apply these however, all other known relevant APARS and PTFs will be pulled in.

To enable you to more easily identify FTB-related fixes, we also created a new FIXCAT (SMP/E Fix category) IBM.DB2.FTB with keyword DB2FTB/K. For more information about FIXCATs and a complete list, see Fix Categories and RETAIN Keywords. Use these keywords to identify and apply any FTB-related additional fixes that might have become available since the writing of this blog entry.

Step 2: Enable and test the use of FTBs for specific indexes

In addition to the maintenance listed above, APAR PH23238 / UI69968 introduces a new index-level granularity capability that you can use to enable and test FTBs for a specific set of selected indexes.

To use this new capability, set the INDEX_MEMORY_CONTROL subsystem parameter to (SELECTED,AUTO) or (SELECTED,n) and add a row with ACTION='A' in the SYSINDEXCONTROL catalog table for each selected index. The criteria for building FTB structures remains the same: Db2 builds FTBs on unique indexes from the list when a sufficient number of random accesses are detected.

For more information, see Enabling or disabling fast index traversal at the index level.

Step 3: Consider system-level reactivation based on your testing

Based on the results of your testing and use of FTBs for specific indexes, consider whether your Db2 environment is best served by maintaining index-level control, or if it might benefit from system-level reactivation of FTBs by setting the INDEX_MEMORY_CONTROL subsystem parameter to AUTO.

Stay tuned for more news about FTBs

We also plan to extend the usability and capabilities of FTBs in the future, including enhancements to help you identify candidate indexes that are likely to benefit the most from FTB processing.

We'll notify you about the availability of these new features in future entries in this blog and in the other usual channels, such as in Recent Enhancements to Db2 12.