We want "INDEXDEFER" keyword in REORG Utility

venkata rama rajesh mallina

We want "INDEXDEFER" keyword in REORG Utility

Hi Team

We had experienced some space issues  while performing LOAD and  REORG with extremely large data. In such cases we followed the below process

               1. Drop the indexes

               2. Perform the LOAD/REORG

               3. Re-create the indexes with DEFER YES option

               4. Rebuild the indexes

           But it is good see that INDEXDEFER option in LOAD. With this option we can avoid index building in LOAD utility. After the LOAD we can perform rebuild indexes. With this option we can also avoid building NPI's again and again if we are performing PART level LOAD's.

          Now my question is why can't we have INDEXDEFER option in REORG. Theoretically specking it was not required since we always prefer online REORG. If indexes are rebuild pending after the REORG, then it may cause outage. But practically speaking we need this option to reduce outage while handling REORG on extremely large PBG data where we are facing space issues. I mean in such case of problem scenario's we can follow the below process 

               1. Perform reorg with INDEXDEFER

               2. Rebuild the indexes

         Such that we can avoid the DROP and create of indexes.

Venkata Rama Rajesh

DB2 DBA for Z/OS

Michael Hannan

RE: We want "INDEXDEFER" keyword in REORG Utility
(in response to venkata rama rajesh mallina)

Venkata,

I am not sure if this was completely obvious to you and all, but if you are willing to incur an outage, then instead of Reorg, you can use a combination of Unload and Load with INDEXDEFER and load on a partition basis. REBUILD the indexes or just NPIs afterwards.

You maybe even able to use Cross Loader functionality (INCURSOR) with INDEXDEFER. I have not investigated and tried this. If it works it may perform well. (I don't really play with the Utilities that much as more into SQL stuff)

You can also drop the NPI Indexes, Reorg, and then Recreate the NPIs Defer and Rebuild them. It was not too clear to me why was so important to avoid dropping and recreating indexes if you are will suffer the outage.

All of these options come with an outage.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

We want "INDEXDEFER" keyword in REORG Utility
(in response to Michael Hannan)
Michael,

Without knowing Venkata’s actual reason for avoiding a DROP/CREATE on NPIs in this scenario, we may speculate that his concern is the forced REBIND of index-dependent plans which results from the DROP/CREATE. That would certainly give me pause in such a case.

--Phil Sevetson

From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 6:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: We want "INDEXDEFER" keyword in REORG Utility


Venkata,

I am not sure if this was completely obvious to you and all, but if you are willing to incur an outage, then instead of Reorg, you can use a combination of Unload and Load with INDEXDEFER and load on a partition basis. REBUILD the indexes or just NPIs afterwards.

You maybe even able to use Cross Loader functionality (INCURSOR) with INDEXDEFER. I have not investigated and tried this. If it works it may perform well. (I don't really play with the Utilities that much as more into SQL stuff)

You can also drop the NPI Indexes, Reorg, and then Recreate the NPIs Defer and Rebuild them. It was not too clear to me why was so important to avoid dropping and recreating indexes if you are will suffer the outage.

All of these options come with an outage.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Larry Jardine

We want "INDEXDEFER" keyword in REORG Utility
(in response to venkata rama rajesh mallina)
What outage are you currently experiencing with your REORG of a PBG? It should only be the last logapply and switch phase.
If minimizing an outage is your concern, then deferring index rebuild will not help, it will elongate the outage (indexes would be in PSRB).
What space issues are you experiencing? Sortspace during the reorg? Or space issues with the tablespace partitions?
Do you need to reorg these PBG tablespaces often? Perhaps they would be better suited for PBR?

Larry Jardine
Aetna

From: venkata rama rajesh mallina [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 1:29 AM
To: [login to unmask email]
Subject: [DB2-L] - We want "INDEXDEFER" keyword in REORG Utility


Hi Team

We had experienced some space issues while performing LOAD and REORG with extremely large data. In such cases we followed the below process

1. Drop the indexes

2. Perform the LOAD/REORG

3. Re-create the indexes with DEFER YES option

4. Rebuild the indexes

But it is good see that INDEXDEFER option in LOAD. With this option we can avoid index building in LOAD utility. After the LOAD we can perform rebuild indexes. With this option we can also avoid building NPI's again and again if we are performing PART level LOAD's.

Now my question is why can't we have INDEXDEFER option in REORG. Theoretically specking it was not required since we always prefer online REORG. If indexes are rebuild pending after the REORG, then it may cause outage. But practically speaking we need this option to reduce outage while handling REORG on extremely large PBG data where we are facing space issues. I mean in such case of problem scenario's we can follow the below process

1. Perform reorg with INDEXDEFER

2. Rebuild the indexes

Such that we can avoid the DROP and create of indexes.

Venkata Rama Rajesh

DB2 DBA for Z/OS

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

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

venkata rama rajesh mallina

RE: We want "INDEXDEFER" keyword in REORG Utility
(in response to Larry Jardine)

Hi Larry Jardine

We are facing the space issue in many ways.

To state one example, PBG tablespace having many indexes. tablespaces and indexes consuming 100's of GB. We does not have enough volumes to accommodate shadow datasets for that tablepsace and indexes. Storage team in that account is not in the position to help us  by adding volumes. To address the issue we should run the REORG on that day itself. Being PBG we had some problems in part level reorg. So we dropped the indexes. Then performed the REORG. Just to avoid shadow datasets allocation for indexes. Then we re-created indexes, and builded th indexes.

Venkata Rama Rajesh

Larry Jardine

We want "INDEXDEFER" keyword in REORG Utility
(in response to venkata rama rajesh mallina)
Why are you running reorg in the first place?

I’m not being argumentative, I just want to understand your issue, since we don’t experience whatever your use-case is. We use PBG for data that has high inserts/appends with little need for reorg.

Larry Jardine
Aetna

From: venkata rama rajesh mallina [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 11:11 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: We want "INDEXDEFER" keyword in REORG Utility


Hi Larry Jardine

We are facing the space issue in many ways.

To state one example, PBG tablespace having many indexes. tablespaces and indexes consuming 100's of GB. We does not have enough volumes to accommodate shadow datasets for that tablepsace and indexes. Storage team in that account is not in the position to help us by adding volumes. To address the issue we should run the REORG on that day itself. Being PBG we had some problems in part level reorg. So we dropped the indexes. Then performed the REORG. Just to avoid shadow datasets allocation for indexes. Then we re-created indexes, and builded th indexes.

Venkata Rama Rajesh

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

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Michael Hannan

RE: We want "INDEXDEFER" keyword in REORG Utility
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

Michael,

Without knowing Venkata’s actual reason for avoiding a DROP/CREATE on NPIs in this scenario, we may speculate that his concern is the forced REBIND of index-dependent plans which results from the DROP/CREATE. That would certainly give me pause in such a case.

I was thinking of REBIND Packages with APREUSE, when it occurred to me just now, that I am not sure if access path reuse works after dropping and recreating indexes, i.e. Does Db2 know that indexes are the same definition as what they were before the drop? I guess Db2 cannot go by just the name of the index matching. The Create Timestamp may matter, unless it knows the columns etc. Putting the Index Stats back as before is not that difficult to do.

In reply to Larry:

Hopefully their reason for Reorg is not to merely get rid of Extents. Sites maybe happy to not bother Reorging, but it can be performance costly.

Major reasons for Table Reorg:
* Many updated rows moved to another Page causing a lot of extra cost for indirect reference even by Tablespace scans. Obviously not applicable to Tables with low Update. MAXROWS was a mechanism to try to reduce this before we had the new Freespace for Updated rows.

* Scans of the table using the cluster index, or joins in cluster sequence, or even probes in a loop that just happen to be in cluster sequence, can perform much better if the table is well Reorged rather than being in a semi chaotic sequence and partitioned consistent with cluster (not random partitioning). Random probes are not much affected. Cluster by a single column random key is often not very useful and a waste, unless data will be sequentially processed in sequence of that key. Measures of the Clustering by an Index are: REORGUNCLUSTINS, CLUSTERRATIOF, and DATAREPEATFACTORF (easier to understand than Clusterratiof).

In a site not Reorging their tables, a major boost to batch performance and elapsed times reductions were seen after tables did get Reorged. They were sceptical till they saw the good improvement. Was an easy way to help their batch finish earlier.

Occasionally my tuning recommendation for an SQL is as simple as Reorg the table.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 05, 2018 - 01:25 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 05, 2018 - 01:47 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 05, 2018 - 01:49 AM (Europe/Berlin)