[Db2 zOS] Index convertion from NPI/NPSI to DPSI

Johan Sundborg

[Db2 zOS] Index convertion from NPI/NPSI to DPSI

Hello!

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.

I would prefer to not drop/create the indexes in order to get rid of "piecesize" and apply "partitioned" (plus rebind packages) but rather do a more smooth conversion. I have found a thread suggesting a solution, but that's for LUW... https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.partition.doc/doc/t0054730.html

I'm guessing that I'm not the first person wanting to do this kind of conversion and hoping that our great community got some tips or solutions :)

We are on version 12.

Kind regards
Johan

Phil Grainger

[Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Johan Sundborg)
Be careful

DPSIs can be great for speeding up utilities, BUT can severely compromise SQL performance

And scanning of the index may now need to be performed once PER PARTITION. I’d suggest taking a look at and high frequency SQL that is using the indexes before you convert them..

Phil G

Sent from my iPad

On 3 Feb 2020, at 10:01, Johan Sundborg <[login to unmask email]> wrote:



Hello!

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.

I would prefer to not drop/create the indexes in order to get rid of "piecesize" and apply "partitioned" (plus rebind packages) but rather do a more smooth conversion. I have found a thread suggesting a solution, but that's for LUW... https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.partition.doc/doc/t0054730.html

I'm guessing that I'm not the first person wanting to do this kind of conversion and hoping that our great community got some tips or solutions :)

We are on version 12.

Kind regards
Johan

-----End Original Message-----

Michael Hannan

RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Johan Sundborg)

In Reply to Johan Sundborg:

Hello!

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.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 04, 2020 - 11:11 AM (Europe/Berlin)

Jack Campbell

RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Johan Sundborg)

Johan

I would also be somewhat wary of the overhead of DPSI's when referencing multiple partitions, each one is a separate VSAM dataset that would need to be opened/read. I also personally avoid them for any UNIQUE indexes (*I think also recommend by IBM).

Its been a couple of years since I lasted did any real testing on DPSI's, but I recall the more partitions referenced the bigger the impact versus a NPSI.

As mentioned PARTLEVEL REORG's can be performed in parallel, but yes the NPSI's are rebuilt from scratch. However take a look at REORG_PART_SORT_NPSI (SORTNPSI), see if this can help

REORG_PART_SORT_NPSI = AUTO, YES, NO

AUTO – Specifies that if sorting all keys of the non-partitioned secondary indexes improves the elapsed time and CPU performance, all keys are sorted.

YES – Specifies that if sorting all keys of the non-partitioned secondary indexes improves the elapsed time, all keys are sorted.

NO – Specifies that only keys of the non-partitioned secondary indexes that are in the scope of the REORG are sorted.

HTH

Jack

Michael Hannan

RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Jack Campbell)

In Reply to Jack Campbell:

Johan

I would also be somewhat wary of the overhead of DPSI's when referencing multiple partitions, each one is a separate VSAM dataset that would need to be opened/read. I also personally avoid them for any UNIQUE indexes (*I think also recommend by IBM).

Great if you only have one UNIQUE index and it happens to be the partitioning index. Not always the case.

You would not want DB2 to be trying to enforce uniqueness on a DPSI, since it would have to check all partitions for a duplicate key. Hence this is another major point to show that you can't decide the set of columns and Unique enforcement for an index and then pick between NPSI an DPSI. I have seen sites use a DPSI that destroyed performance due to probes and scans having to look in all partitions, when it simply was not necessary.

At SQL Ref Guide for DB2 zOS (V9 to 12) re DPSIs: " UNIQUE and UNIQUE WHERE NOT NULL are not allowed unless the columns in the index are a superset of the partitioning columns."   At V8 was slightly more restrictive: "UNIQUE and UNIQUE WHERE NOT NULL are also not allowed."

So you are in fact forced to avoid UNIQUE mostly for DPSIs. It just would not be practical for IBM to implement Unique enforcement.

DPSI has to be part of the index design from start since it too different in characteristics from an NPSI. When Data Partitioning first came in, the myth was promulgated that we had the choice now for our old NPI indexes to become PIs, NPSIs, or DPSIs. Strike and forget the 3rd choice, since it is mostly not an equivalent option.  

If I consider a DPSI (as special purpose technique), it is always an additional index for a specific set of purposes (with good partition restriction predicates), and never a direct replacement for an existing NPSI. If it happens to allow me to drop other indexes later, that is just an added bonus. In fact I hardly ever do want to ALTER important characteristics of indexes affecting access paths. CREATE the new index and DROP unnecessary indexes (not used by access paths and constraints) at a possibly later time. Nice that IBM tells us when indexes were last used to satisfy a query these days.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 06, 2020 - 05:43 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 06, 2020 - 05:51 AM (Europe/Berlin)

Steven Lamb

RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Jack Campbell)

From about v9 onwards, you cannot perform concurrent partition level Reorgs if you have NPIs.

We had a play with REORG_PART_SORT_NPSI and it seemed to achieve very little, so we don't bother with it. Increasing the number of partitions in the part-level Reorgs (LISTPARTS) was far more effective in reducing elapsed time.

 

Regards,

Steve

Johan Sundborg

RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
(in response to Michael Hannan)

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 indexes.

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 = 8
MAXIMUM UTILITY PARALLELISM IS 23 BASED ON NUMBER OF PARTITIONS, INDEXES AND STATISTICS

While in the zparms we have PARAMDEG_UTIL: 15

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 misunderstood?

Regards
Johan


In Reply to Michael Hannan:

In Reply to Johan Sundborg:

Hello!

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.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd