db2 v9 redbooks and DPSI

duam lee

db2 v9 redbooks and DPSI

Thank you Robert,

I went through this, understood though the limitation is enahanced from V8 that the all partitions are not probed. But only a limited parts are probed.

In v8 earlier the local predicate was needed from partitioning key and now in V9 the manual says the limited probing due to limited page-range screening.

But to me it is same as usual. It was needed to code inside the program to limit the probe to individaul part otherwise access througgh DPSI would have to look for all parts.

Now in v9, it says the more gain if the part of the partitioning key value should be in the program already which limits the parts scan.

Say for example c1 is partioning index which is with partition by clause. But if I have a NPI which is c2,c3,c4 and programs which has access path through this c2,c3 and c4 wont gain from this limited probe.

It either demands to add c2,c3, c4 to partitioning key so that partkey becomes c1,c2,c3,c4 so that the programs having predicates on c2,c3,c4 would look for the value c2 within c1 and thus limits the scan of all parts. Now instead of probing all it reduces the part. This is fine. But it defeats the purppose of defining the c1 alone as partitioning key. Is not it? What if the c1 purpose is also partitioning and as well providing the limit key values, then in this case adding c2,c3 etc defeats the purpose of uniqueness to c1. All your comments are much appreciated.

With Thanks

Date: Mon, 27 Jul 2009 23:46:24 -0400
From: [login to unmask email]
Subject: Re: [DB2-L] db2 v9 redbooks
To: [login to unmask email]

The DPSI-related enhancements to which you refer are described in the IBM red book titled "DB2 9 for z/OS Performance Topics" (see section 2.4, "Optimization for a complex query"). Here's a link to the document:


You might also want to check out the "DB2 9 for z/OS Technical Overview" red book. The url for that one is:



2009/7/27 duam lee <[login to unmask email]>


Can any one point me to the latest improvement for DPSI in V9. I am looking at the IBM site and not getting a redbook site for V9. I am getting more on V8 than V9. Please provide me the links you have.

I heard that with V9 the latest development has come like if I have DPSI earlier like C2,c3,c4 and c5 and partitioned index is on c1 the program's access path through DPSI c2,c3,c4 has to scan thorugh all the partitions. We have to supply the vales of c1 for the queries to scan the limited partition and access path through C2,c3,c4 and C5.

Now with V9 even though the the DPSI is on C2,c3,c4, but all the sql without c1 mentioned inside program the where clause would allow db2 not to scan all the partition and it can go directly to the desired partition. Is it true or I am getting something wrong. Please share your experiences.

With Thanks

Share your memories online with anyone you want anyone you want.

Robert Catterall
Catterall Consulting

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG.org member, please register here.

Show them the way! Add maps and directions to your party invites.


* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *


IDUG Europe Attendee Testimonial-
"This was definitely the best IDUG conference I have ever attended."

Cuneyt Goksu

2 Redbooks
(in response to duam lee)

MySQL to DB2 Conversion Guide
Published: December 1, 2009 ISBN: 0738433659 476 pages
Explore the book online at
< http://www.redbooks.ibm.com/abstracts/sg247093.html?Open >

DB2 9 for z/OS Performance Topics
Revised: December 2, 2009 ISBN: 0738488836 426 pages
Explore the book online at
< http://www.redbooks.ibm.com/abstracts/sg247473.html?Open >



* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L