Datacom

  • 1.  online reorgs

    Posted Apr 02, 2019 06:53 PM

    One of my tables in a multi table database has 210,000,000 records. Can I do an online reorg on that table alone and the rest of the tables separately as a batch job? 



  • 2.  Re: online reorgs

    Broadcom Employee
    Posted Apr 03, 2019 04:22 AM

    Hello Linda,
    as you can read on OLREORG (Reorganize a Database Online) section of Datacom DB 15.1 documentation, TABLE and DBID are required keywords for DBUTLTY OLREORG; this means that this function works at table level, not at area or database level. Being so, you can run the OLREORG for the other tables in separate steps or jobs.
    Umberto

    PS: I would suggest to carefully read the CA Datacom/DB Data Reorganization Green Book before proceeding



  • 3.  Re: online reorgs
    Best Answer

    Broadcom Employee
    Posted Apr 03, 2019 07:59 AM

    Online REORG is a possible answer for you to use, main requirement for Online REORG is that this tool will use the existing space you have for the database area you have the table in.  No ability to cause dynamic extend to occur.   Table will need some free space to  manage the movement of the rows.      When you review the doc, please look at REFGROUP 00 usage for Online REORG.

     

    If you are running 15.1 and are current on maintenance, you have another tool you can use called ONLINE_TABLE_MOVE you can use as well. This tool will allow you resize your data area for the table if you have that requirement, can make it bigger or smaller or  change device types that are using 3390 geometry.      Recommend you review the doc for ONLINE_TABLE_MOVE and DBUTLTY PREINIT function as well.   ONLINE_TABLE_MOVE will honor dynamic extend option if more space is needed assuming you have turned on dynamic extend for the database area in question.   Basically you will allocate a new data set that will eventually replace the existing data set as part of this process.   

     

    Both of these processes are row movers and will give you the REORG results you need.   Each tool has its part to play to be used for doing REORGS online.

     

    Both of these processes are table level functions.

     

    Ken Eaton



  • 4.  Re: online reorgs

    Posted Apr 03, 2019 08:07 AM

    Good post, Ken…

     

    Thanks,

    Clyde



  • 5.  Re: online reorgs

    Broadcom Employee
    Posted Apr 03, 2019 08:20 AM

    i need to make one more comment about ONLINE_TABLE_MOVE for clarification on my initial reply to you 

    Command for ONLINE_TABLE_MOVE is at the area level, and each of the tables are moved as part of the pile.

    ONLINE_TABLE_MOVE dbid,area 

     

    Ken

     

     



  • 6.  Re: online reorgs

    Posted Apr 03, 2019 08:43 AM

    I have a couple of follow-up comments about Ken's reply..

     

    1. Ken validly mentions REFGROUP 00 which is the current best practice for OLREORG. However REFGROUP 00 requires that the data area has been modified to use Data Space-Management Option (DSOP) 4. Many clients still have most of their data areas defined with DSOP 1. I have some JCL that we use to apply DSOP 4 and execute OLREORG with the current best practices that I could share if you wish. This does require a tiny outage (to apply the DSOP 4 setting) but it is only a few seconds.

     

    2. The original post states that the table has 210,000,000 rows. I would be wary of using ONLINE_TABLE_MOVE for such a large table. ONLINE_TABLE_MOVE is a row-level move, requiring that each row be logged with a MOVER record in the LXX. For such a large table the LXX will be flooded with MOVER and potentially cause many spills. If there is other long-running concurrent update processing it may cause other tasks to be force-checkpointed if the LXX is undersized for such a high volume of logging. ONLINE_TABLE_MOVE is a great feature and has many genuine benefits but I would start with small tables and monitor the LXX utilisation carefully as you ramp up to larger tables.