Help

 

DSNB1CPF

DSNB1CPF handles dynamic prefetches for the Buffer Manager (BM).

Dynamic prefetch can improve performance over sequential prefetch for some data access that involves data which is not on consecutive pages. Dynamic prefetch can also reduce paging. Dynamic prefetch is used in prefetch situations when the pages which Db2 will access are distributed in a nonconsecutive manner. Sequential prefetch is used if the pages are distributed in a sufficiently consecutive manner.

Db2 may still sometimes use prefetch at execution time even when Db2 does not choose prefetch at bind time. This method is known as sequential detection. Db2 can use sequential detection for both index leaf pages and data pages. It is most commonly used on the inner table of a nested loop join, if the data is accessed sequentially. If a table is accessed repeatedly using the same statement (for example, DELETE in a do-while loop), the data or index leaf pages of the table can be accessed sequentially. Sequential detection can then be used if access is through:

  • SELECT or FETCH statements.

  • UPDATE and DELETE statements.

  • INSERT statements when existing data pages are accessed sequentially.

Hints

  • A backward index scan can take advantage of sequential detection in order to cause dynamic prefetch to read 32 index pages backward as needed. This can also improve I/O performance by an order of magnitude when compared to a synchronous read of index pages one page at a time.

  • When index access is used with an IN-list predicate and the list items are scattered in the index key column’s domain, the index access may not be able to benefit from sequential prefetch.

  • A SELECT or UPDATE that is run repeatedly, accessing the index for each access is when dynamic prefetch is usually used.

Reference Sources

Db2 Universal Database for z/OS, Application Programming and SQL Guide, SC18-7415-02.

Db2 Information Management Software, Db2 UDB for z/OS Version 8: Everything You Ever Wanted to Know, and MoreDb2 IBM Redbook SG24-6079-00

IBM Db2 Performance Expert for z/OS, Report Reference, SC18-7978-04.