Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

Reorg 1.1B row table shrlevel reference

  • 1.  Reorg 1.1B row table shrlevel reference

    Posted Mon September 25, 2023 03:58 PM

    Hello IBM Community !    I have a 1.1B row table with 21 partitions.   Each partition is 8 gig and some are dangerously close to hitting that 8 gig limit.

    Last month one partition hit the limit and I altered the limit keys to fix that issue, however it's only a matter of days/weeks before this happens again.

    I want to alter the dssize at the partition level to 16 gig, however firstly I have to alter to turn on pagenum relative at the tablespace level.

    This will force me to reorg the entire tablespace.   If I reorg shrlevel none – and it fails – this will put me in a recovery scenario – I would like to avoid that if at all possible.

    Therefore after the pagenum relative alter – I'd like to reorg shrlevel reference – after I ensure the SMS Pool has enough dasd available for the shadow table.

    Just wondering if anyone sees any issues with this approach, and have you ever reorged a 1.1B row table shrlevel reference?

    It's more the reorg that is giving me pause.   ��   Thanks for any advice !

     

     

    ______________________________________________

    Donna Stinson

    Sr. DBA

    Payment & Registration I&IT Solutions Branch

    Health Services Cluster

    613-539-1722

     



  • 2.  RE: Reorg 1.1B row table shrlevel reference

    Posted Tue September 26, 2023 02:12 AM

    Hi Donna

    We have a few tablespaces that big and we have reorged them with SHRLEVEL REFERENCE and SHRLEVEL CHANGE. You cannot use SHRLEVEL NONE to materialize a pending definition change, for example converting to pagenum relative. I would never recommend anyone to use REORG SHRLEVEL NONE by the way. You can do a partlevel reorg, but you have to reorg all partitions in the same reorg statement. That may make it a little bit easier. Make sure you use a TEMPLATE for the inline image copies with &PART specified.

    REORG TABLESPACE abc PARTLEVEL.

    Regards Martin Ålund

    Db2 Systems Programmer

    Handelsbanken, Sweden



    ------------------------------
    Martin Ålund
    ------------------------------

    Original Message


  • 3.  RE: Reorg 1.1B row table shrlevel reference

    Posted Tue September 26, 2023 04:10 AM

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

    Original Message


  • 4.  RE: Reorg 1.1B row table shrlevel reference

    Posted Wed September 27, 2023 03:45 AM

    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, 



    ------------------------------
    Soledad Martinez
    ------------------------------

    Original Message


  • 5.  RE: Reorg 1.1B row table shrlevel reference

    IBM Champion
    Posted Wed September 27, 2023 10:27 AM
    Instead of DASD use virtual tape, that way you shouldn't have to worry about running out of disk space.   


    Sent from my iPhone



    Original Message


  • 6.  RE: Reorg 1.1B row table shrlevel reference

    Posted 29 days ago

    hi Donna,

    Add SORTDATA NO along with RECLUSTER NO option to the online REORG as that will help with the elapsed time along with less resources used for the job.

    Hope the Reorg went ok and if not yet done all the best.

    Thanks

    Dhiren Chaudhary

    Natwest Group 



    ------------------------------
    Dhirendra Chaudhary
    ------------------------------

    Original Message


  • 7.  RE: Reorg 1.1B row table shrlevel reference

    Posted Tue September 26, 2023 08:35 AM

    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!



    ------------------------------
    Steve Johnson
    ------------------------------

    Original Message


  • 8.  RE: Reorg 1.1B row table shrlevel reference

    Posted Tue September 26, 2023 08:54 AM

    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



    ------------------------------
    Martin Ålund
    ------------------------------

    Original Message