DB2 UDB for z/OS: Design Guidelines for High Performance and Availability

Appendix A. DB2 access techniques 423

Example: A-1 Index screening predicate

SELECT COLa FROM TB1

WHERE COLb = 1

AND COLc > 0

AND COLd < 2;

The predicates can be applied to the index, but they are not all matching predicates. COLc

and COLd are index screening predicates. There is one matching column in the example,

which is COLb.

򐂰 Multiple index access

Multiple index access uses more than one index to access a table. It is a good access path

when no single index provides efficient access and a combination of index accesses

provides efficient access. RID lists are constructed for each index involved. RIDs

qualifying in all indexes are sorted and used for data access using list prefetch.

򐂰 Index look-aside

DB2 checks whether the required entry is in the leaf page accessed by the previous call

and checks against the lowest an highest key of the leaf page. If the entry is found, DB2

can avoid the getpage and traversal of the index tree.

If the entry is not within the cached range, DB2 checks the parent nonleaf page’s lowest

and highest key. If the entry is found in the parent nonleaf range, DB2 has to perform a

getpage, but can avoid a full traversal of the index tree.

In case an entry is not found within the parent nonleaf page, DB2 starts an index probe

from the index root page. See also “Index look-aside” on page 426.

򐂰 Direct row access

Allows DB2 to navigate directly to that row without using an index access or a table space

scan to find the row.

You can find more details about direct row access in “Data access using ROWID” on

page 276.

A.2 Prefetch mechanisms

For table space scans, DB2 generally uses sequential prefetch. Sequential prefetch reads a

sequential set of pages into the buffer pool with one asynchronous I/O. Usually the maximum

number of pages is 32 for a base table.

Sometimes sequential prefetch is also used for index scans:

򐂰 If index cluster ratio greater than 0.8

򐂰 For index only, if the number of qualified leaf pages > 8

򐂰 For index and data, if the number of qualified clustered data pages > 8

If DB2 is not able to choose sequential prefetch at bind time, it may still utilize it at run time:

򐂰 DB2 determines further if data access is sequential or nearly sequential. This kind of

sequential detection is also known as

dynamic prefetch.

Data access is declared sequential if more than four out of the last eight pages are

page-sequential (not valid for DB2 internal referential integrity checks).

򐂰 Tracking is continuous, allowing access to slip into and out of sequential data access.