MAXPARTITIONS and overhead (revisited)

Jeff Gross

MAXPARTITIONS and overhead (revisited)

From previous posts such as

Be careful how you define your partition-by-growth universal table space

and

Db2 11 for z/OS - MAXPARTITIONS Value

We have read about the warning of thread storage and overhead.  We are wondering how this was measured.  Does anyone have an example?  What was the real impact? 

Why?  We are about to embark on converting a few thousand tablespaces to UTS-PBG and we are trying to decide the impact about setting and monitoring the MAXPARTITIONS value.  We are debating setting this value around 10 for many objects in a sort of "set it and forget mode" or setting the value to 2 and then actively running health checks for when the tablespace has a PARTITIONS = MAXPARTITIONS value. 

Thanks,

Jeff

Alan Gredell

RE: MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)

We have taken the path of using MAXPARTITIONS 10 on production objects. The overhead (From John Campbell) is real but not hugely impactful. I would still run health checks on all objects. Using MAXPARTITIONS 10 provides a significant amount of space, but for objects that you know will grow more, make an exception. The great news is that altering the tablespace to add more to MAXPARTITIONS is an immediate change and your issue will immediately disappear.

don isenstadt

MAXPARTITIONS and overhead (revisited)
(in response to Alan Gredell)
Do you have to rebind the associated packages if you alter max partitions?

Get Outlook for iOS https://aka.ms/o0ukef
________________________________
From: Alan Gredell <[login to unmask email]>
Sent: Tuesday, July 28, 2020 10:37:20 AM
To: [login to unmask email] <[login to unmask email]>
Subject: [EXT] [DB2-L] - RE: MAXPARTITIONS and overhead (revisited)


We have taken the path of using MAXPARTITIONS 10 on production objects. The overhead (From John Campbell) is real but not hugely impactful. I would still run health checks on all objects. Using MAXPARTITIONS 10 provides a significant amount of space, but for objects that you know will grow more, make an exception. The great news is that altering the tablespace to add more to MAXPARTITIONS is an immediate change and your issue will immediately disappear.

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

Roy Boxwell

MAXPARTITIONS and overhead (revisited)
(in response to Alan Gredell)
Apart from all your packages being invalidated...

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 28 Jul 2020, at 16:37, Alan Gredell <[login to unmask email]> wrote:



We have taken the path of using MAXPARTITIONS 10 on production objects. The overhead (From John Campbell) is real but not hugely impactful. I would still run health checks on all objects. Using MAXPARTITIONS 10 provides a significant amount of space, but for objects that you know will grow more, make an exception. The great news is that altering the tablespace to add more to MAXPARTITIONS is an immediate change and your issue will immediately disappear.

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

Roy Boxwell

MAXPARTITIONS and overhead (revisited)
(in response to don isenstadt)
If you raise it - yes
I have an Aha idea about this...

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 28 Jul 2020, at 17:15, Don Isenstadt <[login to unmask email]> wrote:


Do you have to rebind the associated packages if you alter max partitions?

Get Outlook for iOS https://aka.ms/o0ukef
________________________________
From: Alan Gredell <[login to unmask email]>
Sent: Tuesday, July 28, 2020 10:37:20 AM
To: [login to unmask email] <[login to unmask email]>
Subject: [EXT] [DB2-L] - RE: MAXPARTITIONS and overhead (revisited)


We have taken the path of using MAXPARTITIONS 10 on production objects. The overhead (From John Campbell) is real but not hugely impactful. I would still run health checks on all objects. Using MAXPARTITIONS 10 provides a significant amount of space, but for objects that you know will grow more, make an exception. The great news is that altering the tablespace to add more to MAXPARTITIONS is an immediate change and your issue will immediately disappear.

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

Jeff Gross

RE: MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)

Thanks for the info.

As for being an immediate change, this will not be for us as our Tablespaces are still Non-UTS, so we have to REORG to get the instantiate the change.   Most of this data is lookup/reference, so it will not grow like a transaction set.  

 

Roy, waiting to hear your aha idea.....  is it worth a bier? 

Jeff

Roy Boxwell

MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)
If they ever approve it yes! However the optimizer team say thatvparallel partition access is killed if you raise the number of parts... i actually do not believe that ad PBGs have no keys so how can the optimizer control the parallel access??? Any search for my ideas and please vote for it!

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 28 Jul 2020, at 20:34, Jeff Gross <[login to unmask email]> wrote:



Thanks for the info.

As for being an immediate change, this will not be for us as our Tablespaces are still Non-UTS, so we have to REORG to get the instantiate the change. Most of this data is lookup/reference, so it will not grow like a transaction set.



Roy, waiting to hear your aha idea..... is it worth a bier?

Jeff

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

Michael Hannan

RE: MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)

Roy,

On the topic of Parallel potentially for PBG objects.

If you read the Red Books on performance, and Terry's presentations you will see that some version of Db2 introduced parallelism for non-partitioned objects. I forget now which. Db2 V7 had IN list parallelism.

Anyway the workings of parallelism are complex, but Db2 does have the ability to split into key ranges for an index on an object not range partitioned. I recall one type of parallel split was called "Strawmodel" I think. Work files are also split for parallelism.

Search of PLAN_TABLE does find parallel access paths on non-range partitioned objects.

The Explain tables for parallel DSN_PGROUP_TABLE and and DSN_PTASK_TABLE show the alternate approaches to parallel.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/perf/src/tpc/db2z_whenparallelnotused.html gives restrictions on parallelism but not really showing exactly when can be used.

One that annoys me, is Common Table Expression (CTE) read in next stage of a query can't use parallel. I think it should for materialised CTEs, but not yet there. If parallel, each leg re-reads whole CTE.  So parallel paths can repeat some of the work in some cases.

Found it now in Db2 10 zOS Redbook Performance Topics (V10 was a good guess):

"DB2 10 provides a number of improvements to query parallelism:
Record range partitioning
Straw model for workload distribution
Sort merge join improvements
Removal of some parallelism restrictions
Query parallelism degree change
Parallelism enhancements performance summary"   etc.
Interesting read in that redbook describing the alternate parallel methods. PBG is supported.

I think a major issue with PBG over PBR, is how much parallelism can be done in the Reorg. We might not want our PBGs to get too big, if Reorg takes too long, and PBR Reorg can be much shorter.

I am hoping that threshold to choose parallel paths will be determined much better by the new Db2zAI, so that we can get parallel off-load to zIIP, where previously Optimizer was refusing to do it, but clearly could help long scanners.
If we can't get parallel, then offload to Data Analytics Accelerator is another good option in modern DB2.

Michael Hannan,
DB2 Application Performance Specialist

Aurora Emanuela Dellanno

RE: MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)

Freudian slip in these uncertain times, or do you speak German? ;)

In Reply to Jeff Gross:

 Roy, waiting to hear your aha idea.....  is it worth a bier? 

Jeff

Alan Gredell

RE: MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)

My memory, and brief test, says that raising MAXPARTITIONS caused no outage, in fact, immediately cured it. I also just added 1 to a test table's value after observing the condition of all packages linked to the table in that tablespace, and there was no change in VALID or OPERATIVE columns. So RAISING (not initially creating a PBG) MAXPARTITIONS is a non-invasive change.

We had an outage in the ability to add rows to an On-Demand table. That product precisely calculates the needed size of a tablespace, and only created a value of 1 for the MAXPARTITION value. However, the calculation became obsolete when we moved to PBG, as the available space was slightly reduced. When the inserts started failing, we had an emergency fix put in to raise the MAXPARTITIONS value, and the issue was immediately corrected.  So no worry about rebinding, as far as I can tell.

Roy Boxwell

MAXPARTITIONS and overhead (revisited)
(in response to Alan Gredell)
Naturally if you have ABIND(YES) then the auto Rebind jumps in and „Saves“ you - of course your carefully crafted access path may have gone south...
None of the places I visit has that setting in production- test no problem...

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 29 Jul 2020, at 15:30, Alan Gredell <[login to unmask email]> wrote:



My memory, and brief test, says that raising MAXPARTITIONS caused no outage, in fact, immediately cured it. I also just added 1 to a test table's value after observing the condition of all packages linked to the table in that tablespace, and there was no change in VALID or OPERATIVE columns. So RAISING (not initially creating a PBG) MAXPARTITIONS is a non-invasive change.

We had an outage in the ability to add rows to an On-Demand table. That product precisely calculates the needed size of a tablespace, and only created a value of 1 for the MAXPARTITION value. However, the calculation became obsolete when we moved to PBG, as the available space was slightly reduced. When the inserts started failing, we had an emergency fix put in to raise the MAXPARTITIONS value, and the issue was immediately corrected. So no worry about rebinding, as far as I can tell.

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

Larry Jardine

MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)
Autorebind will operate with APREUSE soon. Or maybe it became available recently.

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: Boxwell, Roy <[login to unmask email]>
Sent: Wednesday, July 29, 2020 10:18 AM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: MAXPARTITIONS and overhead (revisited)

**** External Email - Use Caution ****
Naturally if you have ABIND(YES) then the auto Rebind jumps in and „Saves“ you - of course your carefully crafted access path may have gone south...
None of the places I visit has that setting in production- test no problem...
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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://urldefense.proofpoint.com/v2/url?u=https-3A__www.seg.de_corporate_rechtliche-2Dhinweise_datenschutz_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=Abt7OTwEnaSC6qMQxuHw8HIRtl_ZmtobOu42IZ6Mkuo&s=4MvpYmYgSaZuURGTAvFQlI0ArIiocR-35c4KAMKNslQ&e=

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

On 29 Jul 2020, at 15:30, Alan Gredell <[login to unmask email]<mailto:[login to unmask email]>> wrote:


My memory, and brief test, says that raising MAXPARTITIONS caused no outage, in fact, immediately cured it. I also just added 1 to a test table's value after observing the condition of all packages linked to the table in that tablespace, and there was no change in VALID or OPERATIVE columns. So RAISING (not initially creating a PBG) MAXPARTITIONS is a non-invasive change.

We had an outage in the ability to add rows to an On-Demand table. That product precisely calculates the needed size of a tablespace, and only created a value of 1 for the MAXPARTITION value. However, the calculation became obsolete when we moved to PBG, as the available space was slightly reduced. When the inserts started failing, we had an emergency fix put in to raise the MAXPARTITIONS value, and the issue was immediately corrected. So no worry about rebinding, as far as I can tell.

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

  • image001.png (12.9k)

Raymond Bell

MAXPARTITIONS and overhead (revisited)
(in response to Larry Jardine)
Now that would be handy, for a few reasons. :o)

From: Jardine, Lawrence J <[login to unmask email]>
Sent: 29 July 2020 15:38
To: [login to unmask email]
Subject: [DB2-L] - RE: MAXPARTITIONS and overhead (revisited)


*********************************************
"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"
*********************************************
Autorebind will operate with APREUSE soon. Or maybe it became available recently.

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: Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Wednesday, July 29, 2020 10:18 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: MAXPARTITIONS and overhead (revisited)

**** External Email - Use Caution ****
Naturally if you have ABIND(YES) then the auto Rebind jumps in and „Saves“ you - of course your carefully crafted access path may have gone south...
None of the places I visit has that setting in production- test no problem...
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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://urldefense.proofpoint.com/v2/url?u=https-3A__www.seg.de_corporate_rechtliche-2Dhinweise_datenschutz_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=Abt7OTwEnaSC6qMQxuHw8HIRtl_ZmtobOu42IZ6Mkuo&s=4MvpYmYgSaZuURGTAvFQlI0ArIiocR-35c4KAMKNslQ&e=

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

On 29 Jul 2020, at 15:30, Alan Gredell <[login to unmask email]<mailto:[login to unmask email]>> wrote:


My memory, and brief test, says that raising MAXPARTITIONS caused no outage, in fact, immediately cured it. I also just added 1 to a test table's value after observing the condition of all packages linked to the table in that tablespace, and there was no change in VALID or OPERATIVE columns. So RAISING (not initially creating a PBG) MAXPARTITIONS is a non-invasive change.

We had an outage in the ability to add rows to an On-Demand table. That product precisely calculates the needed size of a tablespace, and only created a value of 1 for the MAXPARTITION value. However, the calculation became obsolete when we moved to PBG, as the available space was slightly reduced. When the inserts started failing, we had an emergency fix put in to raise the MAXPARTITIONS value, and the issue was immediately corrected. So no worry about rebinding, as far as I can tell.

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


National Westminster Bank plc. Registered in England No. 929027. Registered Office: 250 Bishopsgate, London EC2M 4AA. 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. Registered in Scotland No. 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland 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, National Westminster Bank plc or any affiliated entity (“NatWest” or “us”) does not accept responsibility for changes made to this message after it was sent. NatWest may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

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 NatWest in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
Visit our website at www.natwestgroup.com https://www.natwestgroup.com

Attachments

  • image002.png (3.8k)

Jeff Gross

RE: MAXPARTITIONS and overhead (revisited)
(in response to Alan Gredell)

Alan,  thanks for confirming the suspected behavior.    I did see a warning about PLANs being invalidate due to the ACQUIRE ALLOCATE option, but the ALLOCATE option is deprecated in v12, so that should not be a worry.  I will verify with my users. 

In Reply to Alan Gredell:

My memory, and brief test, says that raising MAXPARTITIONS caused no outage, in fact, immediately cured it. 

 

Aurora, good to hear from you.   

Mein Deutsch ist sehr schlecht.   Ich habe einen Hintergrund. 

 

I did search for John Campbell's storage warning/calculation and could not find it.  If anyone has a link or search words, please pass along. 

Thanks,

Jeff

Walter Jani&#223;en

RE: MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)

Hi Roy,

The company I worked for had ABIND(YES) in production. I think it is better that a package remains executable than that the access path may goes south. And with statement level hints the access path is kept.

Regards

Roy Boxwell

MAXPARTITIONS and overhead (revisited)
(in response to Walter Janißen)
I also prefer it if you are Altering maxparts!


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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 29 Jul 2020, at 21:21, Walter Janißen <[login to unmask email]> wrote:



Hi Roy,

The company I worked for had ABIND(YES) in production. I think it is better that a package remains executable than that the access path may goes south. And with statement level hints the access path is kept.

Regards

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

Aurora Emanuela Dellanno

RE: MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)

Hey Jeff,

 

good to hear from you too.

 

I think you might find something in this GSE presentation by John, namely "Overuse of UTS PBG tablespaces and MAXPARTS":

 

https://www.gsebelux.com/system/files/Db2%20for%20zOS%20JC%20Greatest%20Hits%20War%20Stories%20and%20Best%20Practice%202019.pdf

 

 And as for the German, I was jokingly asking whether you were trying to entice Roy with a beer (but spelled the German way), or referring to a bier as in *ahem* the thing for corpses and funerals. sorry, black humour.

 



In Reply to Jeff Gross:

Aurora, good to hear from you.   

Mein Deutsch ist sehr schlecht.   Ich habe einen Hintergrund. 

 

I did search for John Campbell's storage warning/calculation and could not find it.  If anyone has a link or search words, please pass along. 

Thanks,

Jeff

Alan Gredell

IRE: MAXPARTITIONS and overhead (revisited)
(in response to Jeff Gross)

Jeff, I don't think that the warning came only or maybe even originally from John Campbell, just that, while we had heard it from several sources, we also could not find any documentation of how/why it's an issue, or how much of an issue. John didn't provide any calculation, just said to us that it was storage allocated that isn't needed, so for efficiency's sake, keep the number within reason.

And to everyone else, I don't believe there was any automatic rebind. If there was, in the production case, it occurred extremely quickly (yes, we had massive machine power!), and in my recent test, it's very very VERY unlikely that the packages were all executed so quickly. The dependent packages numbered in the vicinity of 20 or 25, and in a test system that is not used.

Roy Boxwell

I MAXPARTITIONS and overhead (revisited)
(in response to Alan Gredell)
Sorry to keep on banging the drum - it does invalidate all packages. When i test here the autorebind cuts in and works of course, Access path is then a lottery ticket...
Just create a pbg with maxparts 1. create a static package that just does an insert. Bind package and then alter tablespace.
I have done this loads of times...

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 30 Jul 2020, at 15:17, Alan Gredell <[login to unmask email]> wrote:



Jeff, I don't think that the warning came only or maybe even originally from John Campbell, just that, while we had heard it from several sources, we also could not find any documentation of how/why it's an issue, or how much of an issue. John didn't provide any calculation, just said to us that it was storage allocated that isn't needed, so for efficiency's sake, keep the number within reason.

And to everyone else, I don't believe there was any automatic rebind. If there was, in the production case, it occurred extremely quickly (yes, we had massive machine power!), and in my recent test, it's very very VERY unlikely that the packages were all executed so quickly. The dependent packages numbered in the vicinity of 20 or 25, and in a test system that is not used.

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

Adrian Collett

I MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)
Or just check the manual even ;-)

Db2 marks packages invalid when they depend on the target object, and sometimes on related objects that are affected by cascading effects, of the following actions:
...
Altering table spaces:Changing the SBCS CCSID attributeIncreasing the MAXPARTITIONS attributeChanging the SEGSIZE attribute to convert the table space to a partition-by-range (UTS) table spaceChanging the DSSIZE attribute of a partitioned table spaceApplying pending definition changesChanging the buffer pool page size
Ciao

Adrian Collett


On 30/07/2020 17:55, Boxwell, Roy wrote:
Sorry to keep on banging the drum - it does invalidate all packages. When i test here the autorebind cuts in and works of course, Access path is then a lottery ticket... Just create a pbg with maxparts 1. create a static package that just does an insert. Bind package and then alter tablespace.  I have done this loads of times...

Roy BoxwellSOFTWARE ENGINEERING GmbH and SEGUS Inc.-Product Development-Vagedesstrasse 1940479 Dusseldorf/GermanyTel. +49 (0)211 96149-675Fax +49 (0)211 96149-32Email: [login to unmask email]http://www.seg.deLink zur Datenschutzerklärung
Software Engineering GmbHAmtsgericht Düsseldorf, HRB 37894Geschäftsführung: Gerhard Schubert, Ulf Heinrich 
On 30 Jul 2020, at 15:17, Alan Gredell <[login to unmask email]> wrote:


Jeff, I don't think that the warning came only or maybe even originally from John Campbell, just that, while we had heard it from several sources, we also could not find any documentation of how/why it's an issue, or how much of an issue. John didn't provide any calculation, just said to us that it was storage allocated that isn't needed, so for efficiency's sake, keep the number within reason.
And to everyone else, I don't believe there was any automatic rebind. If there was, in the production case, it occurred extremely quickly (yes, we had massive machine power!), and in my recent test, it's very very VERY unlikely that the packages were all executed so quickly. The dependent packages numbered in the vicinity of 20 or 25, and in a test system that is not used.
-----End Original Message-----
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Virus-free. www.avast.com

Roy Boxwell

I MAXPARTITIONS and overhead (revisited)
(in response to Adrian Collett)
Adrian, you trust the Docu?!?!? ������

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: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 30 Jul 2020, at 18:10, DUGI <[login to unmask email]> wrote:

 Or just check the manual even ;-)

Db2 marks packages invalid when they depend on the target object, and sometimes on related objects that are affected by cascading effects, of the following actions:
...
Altering table spaces:

* Changing the SBCS CCSID attribute
* Increasing the MAXPARTITIONS attribute
* Changing the SEGSIZE attribute to convert the table space to a partition-by-range (UTS) table space
* Changing the DSSIZE attribute of a partitioned table space
* Applying pending definition changes
* Changing the buffer pool page size

Ciao

Adrian Collett


On 30/07/2020 17:55, Boxwell, Roy wrote:
Sorry to keep on banging the drum - it does invalidate all packages. When i test here the autorebind cuts in and works of course, Access path is then a lottery ticket...
Just create a pbg with maxparts 1. create a static package that just does an insert. Bind package and then alter tablespace.
I have done this loads of times...

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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 30 Jul 2020, at 15:17, Alan Gredell <[login to unmask email]><mailto:[login to unmask email]> wrote:



Jeff, I don't think that the warning came only or maybe even originally from John Campbell, just that, while we had heard it from several sources, we also could not find any documentation of how/why it's an issue, or how much of an issue. John didn't provide any calculation, just said to us that it was storage allocated that isn't needed, so for efficiency's sake, keep the number within reason.

And to everyone else, I don't believe there was any automatic rebind. If there was, in the production case, it occurred extremely quickly (yes, we had massive machine power!), and in my recent test, it's very very VERY unlikely that the packages were all executed so quickly. The dependent packages numbered in the vicinity of 20 or 25, and in a test system that is not used.

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

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


[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif] https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient Virus-free. www.avast.com https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient

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

Raymond Bell

I MAXPARTITIONS and overhead (revisited)
(in response to Roy Boxwell)
He’s an ex-pat; we’ll believe anything. ��

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/apsg/src/tpc/db2z_changesinvalidateplanspkgs.html My go-to KC page for stuff like this, which echo’s what Adrian said. Although we’ve seen cases where we’re making changes to objects that invalidate dependent packages and our change (mis)management ‘tool’ doesn’t generate the rebinds for you, as it should. But that’s another story.

Cheers,


Raymond

From: Boxwell, Roy <[login to unmask email]>
Sent: 30 July 2020 22:01
To: [login to unmask email]
Subject: [DB2-L] - RE: I MAXPARTITIONS and overhead (revisited)


*********************************************
"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"
*********************************************
Adrian, you trust the Docu?!?!? ������
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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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


On 30 Jul 2020, at 18:10, DUGI <[login to unmask email]<mailto:[login to unmask email]>> wrote:
 Or just check the manual even ;-)

Db2 marks packages invalid when they depend on the target object, and sometimes on related objects that are affected by cascading effects, of the following actions:
...
Altering table spaces:

* Changing the SBCS CCSID attribute
* Increasing the MAXPARTITIONS attribute
* Changing the SEGSIZE attribute to convert the table space to a partition-by-range (UTS) table space
* Changing the DSSIZE attribute of a partitioned table space
* Applying pending definition changes
* Changing the buffer pool page size

Ciao

Adrian Collett


On 30/07/2020 17:55, Boxwell, Roy wrote:
Sorry to keep on banging the drum - it does invalidate all packages. When i test here the autorebind cuts in and works of course, Access path is then a lottery ticket...
Just create a pbg with maxparts 1. create a static package that just does an insert. Bind package and then alter tablespace.
I have done this loads of times...
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: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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


On 30 Jul 2020, at 15:17, Alan Gredell <[login to unmask email]><mailto:[login to unmask email]> wrote:


Jeff, I don't think that the warning came only or maybe even originally from John Campbell, just that, while we had heard it from several sources, we also could not find any documentation of how/why it's an issue, or how much of an issue. John didn't provide any calculation, just said to us that it was storage allocated that isn't needed, so for efficiency's sake, keep the number within reason.

And to everyone else, I don't believe there was any automatic rebind. If there was, in the production case, it occurred extremely quickly (yes, we had massive machine power!), and in my recent test, it's very very VERY unlikely that the packages were all executed so quickly. The dependent packages numbered in the vicinity of 20 or 25, and in a test system that is not used.

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

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


[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif] https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient
Virus-free. www.avast.com https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient

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

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


National Westminster Bank plc. Registered in England No. 929027. Registered Office: 250 Bishopsgate, London EC2M 4AA. 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. Registered in Scotland No. 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland 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, National Westminster Bank plc or any affiliated entity (“NatWest” or “us”) does not accept responsibility for changes made to this message after it was sent. NatWest may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

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 NatWest in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
Visit our website at www.natwestgroup.com https://www.natwestgroup.com