By the way, why do you care about speeding up a REORG? Using SHRLEVEL CHANGE keeps the REORG from interfering with the application except for a few moments during the last logapply and switch phase.
Database Advisor, Aetna, a CVS Health Company
CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by email or telephone and destroy all copies of this communication and any attachments.
From: Phil Grainger <[login to unmask email]>
Sent: Monday, February 3, 2020 5:14 AM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI
**** External Email - Use Caution ****
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..
Sent from my iPad
On 3 Feb 2020, at 10:01, Johan Sundborg <[login to unmask email]<mailto:[login to unmask email]>> wrote:
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 https://urldefense.proofpoint.com/v2/url?u=https-3A__www.ibm.com_support_knowledgecenter_en_SSEPGG-5F9.7.0_com.ibm.db2.luw.admin.partition.doc_doc_t0054730.html&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=u5sEbf6xst24t-yim669CemWUBdLTcR5La0vjVRuQ-o&s=0RzzXJDCkYJMCUkqp3MQ9r-xx_33lwP2LsV9Ky5UUss&e=
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.
-----End Original Message-----
-----End Original Message-----
NOTICE TO RECIPIENT OF INFORMATION:
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.
This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.
Thank you. Aetna