TDS (LDAP) with DB2 V8 cause huge amount of Dynamic Prefetch

Leonard Janus

TDS (LDAP) with DB2 V8 cause huge amount of Dynamic Prefetch
Hello list,

we are at DB2 for zOS V8, NFM with data sharing
and running a (single) TDS version 3.18 server (LDAP).
BP0 used for db2 catalog, BP1 for LDAP indexes (10K pages), BP2 for LDAP
data (20K pages) and BP7 for sort/work.

After migrating to this configuration we see a huge amount of dynamic
prefetch reads in BP2, it is 4 times the number of getpage requests.

Here are some interval numbers from omegamon:
Getpage Requests 1.042.000
Getpage Requests - Sequential 0
Getpage Requests - Random 1.042.000
...
Pages Read via Dyn Prefetch 4.305.000
Dyn Prefetch I/O Operations 143.115
Dyn Prefetch Requests 174.138

These numbers are not the problem, but this behavior results in huge IO
(up to 10k/sec) and CPU activity that we havn't seen when the LDAP
databse was part of our production db2 and the BP were mixed with other
objects. Well, I know that these are snapshot numbers but they were seen
continuously.
We tried a bigger pool (+10K) and also some changes to the BP thresholds
but faced the same behavior.

In the last resort we decided to set VPSEQT to 0 and turn off
prefetching. This seems to work fine. The IO peaks went down to some
pages a second. But what are the disadvantages?
The 'not prefetching' can be seen in omegamon:
Dyn Prefetch Requests 146.599
Prefetch Failed - No Buffer 146.599

Has anyone seen similar results with LDAP? Any hints what to do?

Thanks in advance,
Leonard

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms