In Reply to Ben Hesed Amose Robinson:
We created a New PBR Table and the only index on the table has 5
columns(one among the 5 is the partitioning key column but at the
3rd level in the index). So when we have a query with all 5
matching columns it is not doing a PAGE RANGE Screening but instead
it is probing all the 1400 partitions in the table.
Do we need to have a index with partitioning key as the Leading
When we do a explain of the SQL it is have PAGE_RANGE as "Y" but
while on the package bind it is not getting PAGE RANGE.
what are the options to have PAGE RANGE SCREENING in the above
It is normal for DB2 to look in all partitions of DPSI, unless
predicates can limit the partitions. Only certain types of
predicates are allowed, but partitions can be determined at
runtime. V11 should have all features working.
It might be helpful if you showed the SQL and the Explain output
for the Package REBIND, and the index definition.
Just to see you have got a DPSI, you have got 5 Match cols, etc.
BTW, is the index unique, or duplicate?
A reason why a dynamic Explain works for limited partition but
static does not, could be something like mismatching datatype, but
then I would not expect Matchcols=5. Still would be nice to see
what the predicate looks like.
Must admit I am struggling to think of anything obvious.
You have not mentioned if you are querying a single table, doing
a join, etc. so would be interesting to see if there is anything
unusual about the SQL.
Is the critical 3rd col predicate equals a hostvar, literal,
join predicate, etc. and what are the data types? Is column
Without detail, the whole thing just becomes a little bit vague,
DB2 Application Performance Specialist
CPT Global Ltd