DB2 11 for z/OS DPSI Performance Improvements

By Toine Michielse, IDUG DB2 11 White Paper Editorial Committee

Introduction

With large tablespaces, well designed NPIs are a blessing to improve the performance and cost of queries against those tables. At the same time, they pose a performance challenge for utilities like partition level LOAD as well an availability challenge for the switch phase of an online REORG. Dataset Partitioned Secondary Indexes (DPSI) were introduced in DB2 V8 in order to improve partition independence and overcome the above mentioned issues with NPIs.

Unfortunately there is no such thing as a free lunch. Unless the query has predicates on the partitioning column(s) of the table, access through a DPSI will require DB2 to probe each and every partition of the DPSI to evaluate the predicates on the DPSI column(s). The more partitions are defined, the larger the overhead and additional cost.

Page range screening from join predicates

DB2 11 improves queries where predicates on the partitioning columns cannot be delivered because the values are coming from the outer table in a join. DB2 11 will allow such join predicates to limit the DPSI scans to only those partitions that match the value of the join predicates passed to the index scan. Let me illustrate this with an example.

Assume the following tables:

• Account (account#, holder, type)
• Booking (account#,amount, date) – partitioned on account#, DPSI on date)

And the following query. It has accesses the booking table using a nested loop join as the inner table.

select a.account#, b.amount, b.date
  from account a
      ,booking b
where a.account# = b.account#
   and a.type = 'savings'
   and b.date = '2013.05.01'

 With DB2 11, the join predicate (a.account# = b.account#) can now be used to limit the partitions of the DPSI on date that need to be scanned. If this takes place, the PAGE_RANGE column of the plan_table will indicate this as usual. Further, the STAGE column of DSN_FILTER_TABLE will contain 'PAGERANGE' for the predicates in question.

The following restrictions apply:

• The join predicates must be equal predicates on leading column(s)
• The length, data type and CCSID of the columns in the join predicate must be the same

DPSI based parallelization

The page range screening improvement described previously requires that join predicates match the partitioning columns. There are also many situations where this is not the case. If a DPSI is available to support the join predicates, DB2 11 can still improve the performance of such queries using intra query parallelism driven from the DPSI partition boundaries.

Assume the following tables:

• Account (account#, holder, type)
• Booking (account#,amount, month) – partitioned on month, DPSI on account#)

And the following query

select a.account#, b.amount
  from account a
      ,booking b
where a.account# = b.account#
   and a.type = 'savings'

 In this case DB2 11 can actually parallelize the query according to the partitioning limits so that each child task will process exactly one partition of the DPSI. Each child task will repeat the processing on the outer table but will join to a single partition of the DPSI. If the outer table needs sorting and the result is less than 32K rows, an in-memory work file will be used to reduce overhead. Please note that this enhancement is limited to nested loop joins.

A new ZPARM (PARAMDEG_DPSI) is available to control DPSI driven parallelism. If PARAMDEG_DPSI is set to DISABLE, this will only disable DPSI driven parallelism. The query may still parallelize in other ways. The other way around is also possible. That is, if PARAMDEG = 1 but PARAMDEG_DPSI > 1 then DPSI driven parallelism will be available but no other parts of the query will be parallelized.    

DPSI "Return in order" enhancements

DPSI return in order means that data manager will ensure that data retrieved through a DPSI is returned as if it was an NPI. In V9/V10, this was broken when the DPSI index was an index on expression and screening predicates were used.

DB2 11 now fully supports "return in order" for index-on-expression DPSI. With this support, additional sort steps (plan_table row with method '3') can be avoided. Further, index lookaside is now supported for DPSI "return in order".

For more information on this subject, please look out for the full IDUG DB2 11 Technical White Paper which will be published later in 2013.