DB2 Sequential Detection and Index Lookaside

Sequential Detection and Index Lookaside

From https://docs.broadcom.com/doc/the-db2-for-zos-performance-handbook

The significance of these two performance enhancers cannot be overstated. Sequential detection is DB2’s ability to detect a sequential access pattern, at a thread level and across one or multiple units of work, for table spaces and indexes. When a sequential access pattern is detected DB2 will launch one or more sequential prefetch engines that are independent of the thread requesting the data, in an effort to stage the data in advance of the thread request. This can result in amazingly efficient read I/O response times or zero read I/O response times in some situations. DB2 will continue to monitor the access to confirm whether or not pages of data are being requested in a sequentially available fashion and can engage or disengage the sequential prefetch on the fly. Index lookaside is a method in which DB2 keeps track of the index value ranges and checks whether or not an index entry requested is on the same leaf page as the previous request. If DB2 determines that the key being requested is indeed on the same leaf page as the previous request, then the entry can be returned without a traverse of the index b-tree or even a getpage request for the leaf page. This can result in a tremendous amount of CPU saved for index intensive operations.

It is important to note that the mechanisms for sequential detection and index lookaside are stored in the thread storage of the thread making the SQL requests. Each thread thus has its own storage for these mechanisms independent of other threads, and whether or not this storage is maintained across the boundaries of a unit of work is dictated by the RELEASE setting of the package the thread is using for the process. If the setting is RELEASE(COMMIT) then you are in essence telling DB2 that, “when I commit, I am not coming back”. So, the storage is released on every commit and the potential for index lookaside and sequential detection is reduced. If the setting is RELEASE(DEALLOCATE) then the memory for these mechanisms is retained across a unit of work, and the potential for sequential detection and index lookaside greatly increases. For this reason the setting of RELEASE(DEALLOCATE) is highly recommended for batch processing, as well as for high speed transaction processing. For transaction processing under CICS, you’ll have to use protected threads as well as RELEASE(DEALLOCATE), and for remote threads, you will need to employ high performance DBATs (described in Section 3). RELEASE(DEALLOCATE) is further described in the BIND options section of Section 3.

Effective use of these performance enhancers depends greatly upon the design of your database and application processes. A common cluster amongst joined tables, or even tables accessed individually in a repeating pattern, can affect sequential detection and index lookaside. In addition, for large batch processes the input to the process can be sorted to match the clustering sequence of the search in order to take advantage of these features. Even with a massive skip sequential process, I have witnessed a benefit to sequential detection (you probably won’t get index lookaside for a skip sequential process); whereas, even with a terrible buffer hit ratio of -241%, sequential prefetch was still a better performer than random access. With an input to the process organized in the same sequence as the data stored in the indexes and tables, RELEASE(DEALLOCATE), protected threads, and high performance DBATs, you can achieve extreme transaction rates by taking advantage of these performance enhancers.