how do you do a very large reorg (to convert to PBR)

We have a table that is currently residing on a large tablespace (but not a PBR).  It stores 5.4 billion rows and uses 91 million pages.  Normally, we reorg this by partition since it has only one partitioned index.  We wish to convert this to be using a PBR tablespace.

I attended John Cambell's webinar last week and had hopes that this could be reorganized using:

"SORTDATA NO RECLUSTER NO"

And while this dramatically reduces the need for sort work space, it also then requires a "SYSREC" dataset (or at least something defined with the UNLDDN clause.

So, I used a SYSREC dataset that was defined with 255 volumes (on tape - we really do not have the disk space required for this).  The reorg then failed because it ran through all 255 volumes and was attempting to go to the 256th volume. 

Is there any solution to this (and I know that third party DB2 reorg utilities can come to the rescue here, but I was hoping that I could do this with the IBM reorg.

 

Larry 

Hi Larry,
I have had this problem and at sites that don't have third party tools and it is a problem in that everything has to be done at the tablespace level.
My workaround in such cases is to define a new PBR tablespace tsnameN and tableNEW .  Then unload from the current table and then load data up to tableNEW.
Next rename the existing table to tableOLD and then rename tableNEW to table.
This approach can get a bit fiddly where the table in question is in constant use but I have got around this in various ways which usually included shifting most of the data beforehand and then moving the current data (if that can be identified) during a small outage slot.
The above approach can be done by partition and it is also a good idea to replace NPIs with DPSIs at the same time if possible.
One gotcha is to check that you scan your housekeeping jobs for instances where the original tablespace name is explicitly coded.
Regards,Paul Ogborne


-----Original Message-----
From: Larry Kirkpatrick <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Mon, 4 Feb 2019 13:27
Subject: [DB2-L] - how do you do a very large reorg (to convert to PBR)

We have a table that is currently residing on a large tablespace (but not a PBR).  It stores 5.4 billion rows and uses 91 million pages.  Normally, we reorg this by partition since it has only one partitioned index.  We wish to convert this to be using a PBR tablespace.I attended John Cambell's webinar last week and had hopes that this could be reorganized using:"SORTDATA NO RECLUSTER NO"And while this dramatically reduces the need for sort work space, it also then requires a "SYSREC" dataset (or at least something defined with the UNLDDN clause.So, I used a SYSREC dataset that was defined with 255 volumes (on tape - we really do not have the disk space required for this).  The reorg then failed because it ran through all 255 volumes and was attempting to go to the 256th volume. Is there any solution to this (and I know that third party DB2 reorg utilities can come to the rescue here, but I was hoping that I could do this with the IBM reorg. Larry 
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] has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
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

Larry,

Sorry to just drop in here – but I saw the 255-volumes issue and thought I’d ask. Has your z/OS system been upgraded to accept the LBI (Large Block Interface), the maximum size of 262xxx bytes per block for a tape file?

5.4 billion records is pretty huge, I understand, but I’m wondering that it took more than 255 volumes. That’s hard to swallow – how many blocks and bytes on each tape? (I asked about LBI because it radically increases the storage capacity of a single tape volume; the increase is a factor between 8x and 9x.)

The other question, of course, is: can you, instead of running a REORG, run two or three multi-volume DSNUTILB unloads of a range of the table, with the ranges collectively capturing all rows? And then run them as LOAD/REPLACE on the current or a new table definition?

--Phil Sevetson

From: Paul Ogborne [mailto:[login to unmask email]
Sent: Monday, February 04, 2019 9:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: how do you do a very large reorg (to convert to PBR)

Hi Larry,

I have had this problem and at sites that don't have third party tools and it is a problem in that everything has to be done at the tablespace level.

My workaround in such cases is to define a new PBR tablespace tsnameN and tableNEW . Then unload from the current table and then load data up to tableNEW.

Next rename the existing table to tableOLD and then rename tableNEW to table.

This approach can get a bit fiddly where the table in question is in constant use but I have got around this in various ways which usually included shifting most of the data beforehand and then moving the current data (if that can be identified) during a small outage slot.

The above approach can be done by partition and it is also a good idea to replace NPIs with DPSIs at the same time if possible.

One gotcha is to check that you scan your housekeeping jobs for instances where the original tablespace name is explicitly coded.

Regards,
Paul Ogborne

-----Original Message-----
From: Larry Kirkpatrick <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Mon, 4 Feb 2019 13:27
Subject: [DB2-L] - how do you do a very large reorg (to convert to PBR)
We have a table that is currently residing on a large tablespace (but not a PBR). It stores 5.4 billion rows and uses 91 million pages. Normally, we reorg this by partition since it has only one partitioned index. We wish to convert this to be using a PBR tablespace.
I attended John Cambell's webinar last week and had hopes that this could be reorganized using:
"SORTDATA NO RECLUSTER NO"
And while this dramatically reduces the need for sort work space, it also then requires a "SYSREC" dataset (or at least something defined with the UNLDDN clause.
So, I used a SYSREC dataset that was defined with 255 volumes (on tape - we really do not have the disk space required for this). The reorg then failed because it ran through all 255 volumes and was attempting to go to the 256th volume.
Is there any solution to this (and I know that third party DB2 reorg utilities can come to the rescue here, but I was hoping that I could do this with the IBM reorg.

Larry

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

Given the challenges you are facing getting this table converted, maybe it’s an opportunity to revisit the need?

You say “We wish to convert this to be using a PBR tablespace.” but you don’t say WHY

Maybe there are other solutions that don’t require a wholesale conversion to PBR?? What problem(s) are you hoping to solve?

Do you have other tablespaces that will need converting that may present similar challenges???

Just trying to figure out if the pain you are trying oo alleviate is enough to justify investigation of ISV tools
________________________________





[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Phil Grainger
Principal Enablement Manager

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]























-----Original Message-----
From: Larry Kirkpatrick <[login to unmask email]<mailto:[login to unmask email]>>
To: DB2-L <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Mon, 4 Feb 2019 13:27
Subject: [DB2-L] - how do you do a very large reorg (to convert to PBR)
We have a table that is currently residing on a large tablespace (but not a PBR). It stores 5.4 billion rows and uses 91 million pages. Normally, we reorg this by partition since it has only one partitioned index. We wish to convert this to be using a PBR tablespace.
I attended John Cambell's webinar last week and had hopes that this could be reorganized using:
"SORTDATA NO RECLUSTER NO"
And while this dramatically reduces the need for sort work space, it also then requires a "SYSREC" dataset (or at least something defined with the UNLDDN clause.
So, I used a SYSREC dataset that was defined with 255 volumes (on tape - we really do not have the disk space required for this). The reorg then failed because it ran through all 255 volumes and was attempting to go to the 256th volume.
Is there any solution to this (and I know that third party DB2 reorg utilities can come to the rescue here, but I was hoping that I could do this with the IBM reorg.

Larry

-----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.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

I have appreciated all the insight on this.

I also have a couple of observations (as I have worked with the responses).

I agree with Phil that you need to evaluate whether or not you really need to convert your large tablespaces to PBR.  IBM has given a direction to shops to move in this direction, but so far, there is no deadline.  That being said, we have already encountered an ALTER that we wanted to do that could not be done because the tablespace was large and not PBR.  So, you need to be aware that you may cause a two step process for some of your future changes (should you decide to defer this conversion).

Also, I noted the advice to change the blocksize.  I did a test of a reorg on 100 partitions (from my monster table) and used a 32K blocksize and then did it again with the "DCB=BLKSIZE=262000" in my JCL.  While the TOTALBLOCKS put to tape was reduced by a wow factor (1766940 for 32K and 220623 for BLKSIZE=262000), the total number of tapes was unchanged.

Then I spoke with my storage guys and asked if we had some tape units available to us that had higher capacity.  And, the answer was yes.  So, I do have a solution (never try to do something unilaterally, you will find quite a bit of help discussing your problem with others).

 

Larry 

Good news Larry – all you need now is the TIME to do the reorg

Would be interesting if you could report back how long the process actually took

Remember though, that IBM are suggesting PBR or PBG for the future – so I was really wondering why you chose PBR? (Not that going to a PBG would be any easier!)
________________________________





[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Phil Grainger
Principal Enablement Manager

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]










































From: Larry Kirkpatrick [mailto:[login to unmask email]
Sent: 05 February 2019 15:38
To: [login to unmask email]
Subject: [DB2-L] - RE: how do you do a very large reorg (to convert to PBR)


I have appreciated all the insight on this.

I also have a couple of observations (as I have worked with the responses).

I agree with Phil that you need to evaluate whether or not you really need to convert your large tablespaces to PBR. IBM has given a direction to shops to move in this direction, but so far, there is no deadline. That being said, we have already encountered an ALTER that we wanted to do that could not be done because the tablespace was large and not PBR. So, you need to be aware that you may cause a two step process for some of your future changes (should you decide to defer this conversion).

Also, I noted the advice to change the blocksize. I did a test of a reorg on 100 partitions (from my monster table) and used a 32K blocksize and then did it again with the "DCB=BLKSIZE=262000" in my JCL. While the TOTALBLOCKS put to tape was reduced by a wow factor (1766940 for 32K and 220623 for BLKSIZE=262000), the total number of tapes was unchanged.

Then I spoke with my storage guys and asked if we had some tape units available to us that had higher capacity. And, the answer was yes. So, I do have a solution (never try to do something unilaterally, you will find quite a bit of help discussing your problem with others).



Larry

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

I liked the advice given by John Campbell in a webinar regarding PBR vs PBG last week.  And, basically, we have been using his advise for several years (before I heard that webinar).

What I heard from that webinar is that many DBAs are attracted to PBG because it looks like a "no maintenance" or "low maintenance" option, it actually has several side effects that you may not want (like no ability to set up partitioned indexes not to mention several other things).  The table that I am working with right now has one index that is partitioned.  It is a very easy table to work with for our normal reorganizations because we can reorganize by partition and it works very well.  It would be a disaster for this table to be converted to PBG.

Larry

Thanks, Larry and Phil
For sharing thoughtful knowledge, could please share the detail of last week John Campbell webinar. Thanks

Regards,
Anguraj




> On Feb 5, 2019, at 12:06 PM, Larry Kirkpatrick <[login to unmask email]> wrote:
>
> John Campbell in a webinar regarding PBR vs PBG last week

One thing we found to speed up the conversion process elapsed time, if using an online Reorg, is to limit the level of inline stats collection. It looks like you can't totally turn off all stats collection, but if you can live without it temporarily, don't collect the stats.

We've got a Large tablespace with 300m 8K pages and 5 indexes on 3 billion rows that we may need to convert to a PBR at some point. It's a critical table in it, always in use, so that could be "interesting".

Anguraj Rathinasamy,

You were asking for details from last week John Campbell webinar.
The URL is https://www.worldofdb2.com/events/jc-greatest-hits-war-stories-and-best-practices-2019-part-2
Beware that John Campbell is talking about Db2 12; version 11 could be a (slightly) different story ...

Here is a copy/paste from one of his slides (slides 14+15 of part 2) where John Campbell states that PBG is "over-used":

         Overuse of UTS PBG tablespace and MAXPARTS

• Primary driver for the developing UTS PBG tablespace was the removal of the 64GB limit for classic segmented tablespace and avoid the disruptive migration to classic partitioned tablespace
• Some considerations
  – All indexes are going to be NPIs
  – Limited partition independence for utilities (REORG, LOAD)
  – Partitioning not used for query parallelism
  – Degraded insert performance (free space search) as the number of partitions grow
  – If REORG a partition list/range, it may encounter undetected deadlock between applications and REORG during the SWITCH phase (i.e. drain and claim in different order)
  – REORG PART will fail for a full UTS PBG partition if FREEPAGE or PCTFREE are non-zero
  – Setting system parameter REORG_DROP_PBG_PARTS = ENABLE could lead to operational issues if the number of PARTs are pruned back
         • No point-in-time recovery prior to the REORG that prunes partitions
         • Cannot use DSN1COPY to move data between Db2 systems
• Should not be using UTS PBG as the design default for all tables (with large number of partitions)

• General recommendations for use of UTS PBG tablespace
  – Only use UTS PBG tablespace as the alternative and replacement for classic segmented tablespace
  – A table greater than 60GB in size should be created as a UTS PBR tablespace
  – Good reasons to limit number of partitions - should have as few partitions as possible - ideally only 1
  – DSSIZE and SEGSIZE should be consistent with the target size of the object e.g.
          • Small size object: DSSIZE = 2GB and SEGSIZE = 4
          • Medium size object: DSSIZE = 4GB and SEGSIZE = 32
          • Large size object: DSSIZE = 64GB and SEGSIZE = 64
  – REORG at the table space level unless do not have sufficient DASD space for sort
  – Setting system parameter REORG_DROP_PBG_PARTS = DISABLE?
          • If required to prune back the number of partitions
             – Use online system parameter to temporarily enable for controlled use
          • Better still, in Db2 12, use the DROP_PART YES option of REORG

 

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Much appreciate - Peter for the detailed mail

Regards,
Anguraj Rathinasamy

> On Feb 8, 2019, at 5:33 AM, Peter Vanroose <[login to unmask email]> wrote:
>
> Anguraj Rathinasamy,
>
> You were asking for details from last week John Campbell webinar.
> The URL is https://www.worldofdb2.com/events/jc-greatest-hits-war-stories-and-best-practices-2019-part-2
> Beware that John Campbell is talking about Db2 12; version 11 could be a (slightly) different story ...
>
> Here is a copy/paste from one of his slides (slides 14+15 of part 2) where John Campbell states that PBG is "over-used":
>
> Overuse of UTS PBG tablespace and MAXPARTS
>
> • Primary driver for the developing UTS PBG tablespace was the removal of the 64GB limit for classic segmented tablespace and avoid the disruptive migration to classic partitioned tablespace
> • Some considerations
> – All indexes are going to be NPIs
> – Limited partition independence for utilities (REORG, LOAD)
> – Partitioning not used for query parallelism
> – Degraded insert performance (free space search) as the number of partitions grow
> – If REORG a partition list/range, it may encounter undetected deadlock between applications and REORG during the SWITCH phase (i.e. drain and claim in different order)
> – REORG PART will fail for a full UTS PBG partition if FREEPAGE or PCTFREE are non-zero
> – Setting system parameter REORG_DROP_PBG_PARTS = ENABLE could lead to operational issues if the number of PARTs are pruned back
> • No point-in-time recovery prior to the REORG that prunes partitions
> • Cannot use DSN1COPY to move data between Db2 systems
> • Should not be using UTS PBG as the design default for all tables (with large number of partitions)
>
> • General recommendations for use of UTS PBG tablespace
> – Only use UTS PBG tablespace as the alternative and replacement for classic segmented tablespace
> – A table greater than 60GB in size should be created as a UTS PBR tablespace
> – Good reasons to limit number of partitions - should have as few partitions as possible - ideally only 1
> – DSSIZE and SEGSIZE should be consistent with the target size of the object e.g.
> • Small size object: DSSIZE = 2GB and SEGSIZE = 4
> • Medium size object: DSSIZE = 4GB and SEGSIZE = 32
> • Large size object: DSSIZE = 64GB and SEGSIZE = 64
> – REORG at the table space level unless do not have sufficient DASD space for sort
> – Setting system parameter REORG_DROP_PBG_PARTS = DISABLE?
> • If required to prune back the number of partitions
> – Use online system parameter to temporarily enable for controlled use
> • Better still, in Db2 12, use the DROP_PART YES option of REORG
>
>
>
> -- Peter Vanroose
> ABIS Training & Consulting,
> Leuven, Belgium.
> https://www.abis.be/
>
>
> 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]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> 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
>