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

Larry Jardine

[Db2 zOS] Index conversion from NPI/NPSI to DPSI
Right Phil… DPSIs should be employed if it satisfies an application’s need, not to speed up utility processing.

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.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
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.



Proprietary
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 ****
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]<mailto:[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 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.

Kind regards
Johan

-----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
Attachments

  • image003.png (3.8k)

Raymond Bell

[Db2 zOS] Index conversion from NPI/NPSI to DPSI
(in response to Larry Jardine)
Larry,

Not all Logapply and Switch phases have been created equal. I do recall a few issues with Reorg where either Logapply took much longer to apply the last log iteration than initially estimates, and/or Switch took minutes rather than the few seconds advertised. I think Db2 has made great advances in this area but it wasn’t always thus. YMMV, the value of your investment can go down as well as up, and a few other caveats just in case.

Cheers,


Raymond

From: Jardine, Lawrence J <[login to unmask email]>
Sent: 03 February 2020 12:36
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 zOS] Index conversion from NPI/NPSI to DPSI


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************
Right Phil… DPSIs should be employed if it satisfies an application’s need, not to speed up utility processing.

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.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
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.



Proprietary
From: Phil Grainger <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, February 3, 2020 5:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI

**** External Email - Use Caution ****
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]<mailto:[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 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.

Kind regards
Johan

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

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (3.8k)

Johan Sundborg

RE: [Db2 zOS] Index conversion from NPI/NPSI to DPSI
(in response to Larry Jardine)

I agree that the index change likely will effect the applications, probably some good and some bad and I'm not sure that this is the right approach. Could be to much risk and evaluations and to little reward.

If it had been a regular reorg then yes, use SHRLEVEL CHANGE and you're fine. But the issue is that the reorg is a reorg-discard and is currently running with SHRLEVEL REFERENCE and takes approximately 7 hours to complete, which to a bit to long for comfort for other applications.

I'm not 100% sure but I think I've read somewhere that you could run reorg-discard with SHRLEVEL CHANGE, but if the rows being discarded are in use during the switch-phase then the reorg will fail. And it would kind of suck to lose 7 hours of reorg time. Please correct me if I'm wrong.

Kind regards
Johan


In Reply to Larry Jardine:

Right Phil… DPSIs should be employed if it satisfies an application’s need, not to speed up utility processing.

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.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
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.



Proprietary
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 ****
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]<mailto:[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 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.

Kind regards
Johan

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

Roy Boxwell

[Db2 zOS] Index conversion from NPI/NPSI to DPSI
(in response to Johan Sundborg)
From the REORG docu:



You can specify any SHRLEVEL option with DISCARD. However, if you specify SHRLEVEL CHANGE, modifications that are made during the reorganization to data rows that match the discard criteria are not permitted. In this case, REORG TABLESPACE terminates with an error.







Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Johan Sundborg [mailto:[login to unmask email]
Sent: Tuesday, February 4, 2020 8:05 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 zOS] Index conversion from NPI/NPSI to DPSI



I agree that the index change likely will effect the applications, probably some good and some bad and I'm not sure that this is the right approach. Could be to much risk and evaluations and to little reward.

If it had been a regular reorg then yes, use SHRLEVEL CHANGE and you're fine. But the issue is that the reorg is a reorg-discard and is currently running with SHRLEVEL REFERENCE and takes approximately 7 hours to complete, which to a bit to long for comfort for other applications.

I'm not 100% sure but I think I've read somewhere that you could run reorg-discard with SHRLEVEL CHANGE, but if the rows being discarded are in use during the switch-phase then the reorg will fail. And it would kind of suck to lose 7 hours of reorg time. Please correct me if I'm wrong.

Kind regards
Johan


In Reply to Larry Jardine:

Right Phil… DPSIs should be employed if it satisfies an application’s need, not to speed up utility processing.

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.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
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.



Proprietary
From: Phil Grainger
Sent: Monday, February 3, 2020 5:14 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: [Db2 zOS] Index convertion from NPI/NPSI to DPSI

**** External Email - Use Caution ****
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]<mailto:[login to unmask email] <mailto:[login to unmask email]%3e%3e> >> 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 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 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= &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.

Kind regards
Johan

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



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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: [Db2 zOS] Index conversion from NPI/NPSI to DPSI
(in response to Raymond Bell)



In Reply to Raymond Bell:

Larry,

Not all Logapply and Switch phases have been created equal. I do recall a few issues with Reorg where either Logapply took much longer to apply the last log iteration than initially estimates, and/or Switch took minutes rather than the few seconds advertised. I think Db2 has made great advances in this area but it wasn’t always thus. YMMV, the value of your investment can go down as well as up, and a few other caveats just in case.

The last Log Apply has to drain all readers/writers first. This can be quick or take a very long time. May have a lot of objects, indexes and parts, to drain.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd