Ah, yes. You all have convinced me that converting index from
NPSI to DPSI and vise versa is not a good idea, it was however an
interesting thought and a good reason for reading up more on
PARALLEL seems to be used regardless if specifies or not.
Specifies the maximum number of subtasks that are to be
started in parallel to reorganize a table space. If the PARALLEL
keyword is omitted, the maximum number of subtasks is limited by
either the number of partitions that are being unloaded or the
number of indexes that are built.
I looked at some local values and in the reorg output from last
3 jobs and this was displayed
INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS =
MAXIMUM UTILITY PARALLELISM IS 23 BASED ON NUMBER OF
PARTITIONS, INDEXES AND STATISTICS
While in the zparms we have PARAMDEG_UTIL:
Since PARALLEL is not specified on the reorg-discard job
then Db2 calculates an "optimal value" and for some reason seem to
think that 8 is best. I'm a little unsure about the implications of
specifying the keyword and get a feeling that if you pick a number
that's higher than the number Db2 have calculated then your choice
will be ignored, is that correct or have I
In Reply to Michael Hannan:
In Reply to Johan Sundborg:
I got tasked with speeding up a reorg for a big UTS PBR table
(over 3 billion rows) and got the idea of splitting the reorg into
several using partition parallelism. But as I understand it, this
require all the indexes to be partitioned and I've got several
non-partitioned secondary indexes (NPSI) on the table.
Reorgs can do multiple partitions in parallel. See the PARALLEL
keyword. This works with NPSIs too I am quite sure. Range
Partitioning tables is key to making the parallel possible. Do not
use Rebalance, I think.
You can also Reorg a limited set of partitions in one go. That
would be a desperate measure perhaps.
While I can understand the logic that if all indexes were
partitioned, then each partition Reorg could be independent, I
don't think it will improve the total performance that much over
ordinary parallel, although I have not benchmarked it.
DPSIs are not an alternative to NPSIs, in my view. They are
quite different and should be designed to suit your application.
Can be used if SQL predicates will always severely limit the
partitions examined by any query to one or very few.
Switching NPSI to DPSI makes no sense to me. Redesigning your
indexes based on the query set does, and if a DPSI happens to come
out, great, but that is not common. One query could use the DPSI
and another might need a NPSI (with the same columns). In the end
the choice of a DSPI for an access path is an Optimizer decision.
It can be better than a good NPSI but not that often.
DB2 Application Performance Specialist
CPT Global Ltd