Hi Donna,

This spring we did the same with a 33B million row table. It was a huge undertaking. My colleague will present our experiences at the Prague IDUG.

But my tips are 

• Use RECLUSTER NO that way there is no sort of the data. Only the indexes.
• Talk to your storage management people, The unload dataset will contain all the data at some time. Make sure you have enough space. Use zEDC compress. Ideally have a separate storage pool temporarily . With modern DfSMS the only real limit is the maximum number of volumes a dataset can reside on: 59.
• Also have enough space in the database pool. During the reorg there will be double space
• Put TIME=1440 in the jobcard . I know that's obvious, but we hit S322  after 3 days running.

Succes

Leo de Jong

Rabobank

Hello Leo, 

could you please let me know the name of your colleague or the name of the presentation?

I would like to attend if possible.

Thanks, 

Hi Donna,

This spring we did the same with a 33B million row table. It was a huge undertaking. My colleague will present our experiences at the Prague IDUG.

But my tips are 

• Use RECLUSTER NO that way there is no sort of the data. Only the indexes.
• Talk to your storage management people, The unload dataset will contain all the data at some time. Make sure you have enough space. Use zEDC compress. Ideally have a separate storage pool temporarily . With modern DfSMS the only real limit is the maximum number of volumes a dataset can reside on: 59.
• Also have enough space in the database pool. During the reorg there will be double space
• Put TIME=1440 in the jobcard . I know that's obvious, but we hit S322  after 3 days running.

Succes

Leo de Jong

Rabobank

------------------------------
Leo de Jong
------------------------------

Hi Donna,

If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg.  Since you are not doing that I assume your TS is "old-school partitioned".  If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.  

We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:

• How many indexes do you have on the table?  Ensure you have enough sort space to rebuild them during the reorg.
• If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.

Good luck!

Hi Steve

You are not entirely right. To change the DSSIZE for a single partition the tablespace has to be defined with PAGENUM RELATIVE. It is not enough that it is UTS PBR. On the other hand if it has PAGENUM RELATIVE you can increase the DSSIZE very smoothly with an ALTER. That saved me lots of work last night.

Regards Martin

Hi Donna,

If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg.  Since you are not doing that I assume your TS is "old-school partitioned".  If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.  

We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:

• How many indexes do you have on the table?  Ensure you have enough sort space to rebuild them during the reorg.
• If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.

Good luck!

Hi Martin,

Good catch thank you.  However with UTS PBR she could alter the DSSIZE at the tablespace level (it would apply to all partitions) and avoid a reorg.  That might give some immediate relief.

Thanks again,

Steve

Hi Steve

You are not entirely right. To change the DSSIZE for a single partition the tablespace has to be defined with PAGENUM RELATIVE. It is not enough that it is UTS PBR. On the other hand if it has PAGENUM RELATIVE you can increase the DSSIZE very smoothly with an ALTER. That saved me lots of work last night.

Regards Martin

Hi Donna,

If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg.  Since you are not doing that I assume your TS is "old-school partitioned".  If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.  

We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:

• How many indexes do you have on the table?  Ensure you have enough sort space to rebuild them during the reorg.
• If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.

Good luck!

Hi Steve - yes the tablespace is type R in systablespace .... UTS PBR.    However if I alter the DSSIZE at the tablespace level - the tablespace is put in advisory reorg pending - and I still need to reorg to materialize that new DSSIZE.     But this may avoid the pagenum relative alter - I'm attempting to alter as little as possible while still enlarging the partition size.

Hi Martin,

Good catch thank you.  However with UTS PBR she could alter the DSSIZE at the tablespace level (it would apply to all partitions) and avoid a reorg.  That might give some immediate relief.

Thanks again,

Steve

Hi Steve

You are not entirely right. To change the DSSIZE for a single partition the tablespace has to be defined with PAGENUM RELATIVE. It is not enough that it is UTS PBR. On the other hand if it has PAGENUM RELATIVE you can increase the DSSIZE very smoothly with an ALTER. That saved me lots of work last night.

Regards Martin

Hi Donna,

If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg.  Since you are not doing that I assume your TS is "old-school partitioned".  If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.  

We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:

• How many indexes do you have on the table?  Ensure you have enough sort space to rebuild them during the reorg.
• If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.

Good luck!

Hi Martin,

Good catch thank you.  However with UTS PBR she could alter the DSSIZE at the tablespace level (it would apply to all partitions) and avoid a reorg.  That might give some immediate relief.

Thanks again,

Steve

Hi Steve

You are not entirely right. To change the DSSIZE for a single partition the tablespace has to be defined with PAGENUM RELATIVE. It is not enough that it is UTS PBR. On the other hand if it has PAGENUM RELATIVE you can increase the DSSIZE very smoothly with an ALTER. That saved me lots of work last night.

Regards Martin

Hi Donna,

If the tablespace is UTS Partition by Range (PBR), you can alter the DSSIZE of an individual partition and do not have to reorg.  Since you are not doing that I assume your TS is "old-school partitioned".  If that is the case, I highly recommend converting it to UTS PBR at the same time so you can avoid another reorg of the entire TS later to do that conversion.  

We have reorged a few very large tablespaces using SHRLEVEL CHANGE and here are a couple things to keep in mind:

• How many indexes do you have on the table?  Ensure you have enough sort space to rebuild them during the reorg.
• If you don't have enough sort space, consider dropping non-unique indexes before the reorg, and recreating and rebuilding them with SHRLEVEL CHANGE after the reorg, and of course rebinding impacted packages.

Good luck!

 Alter the TS with required changes.  would never recommend anyone to use REORG SHRLEVEL NONE. I would suggest online Reorg with SHRLEVEL CHANGE at PART level . 

 Alter the TS with required changes.  would never recommend anyone to use REORG SHRLEVEL NONE. I would suggest online Reorg with SHRLEVEL CHANGE at PART level .