Prev
Next
Forums S3 Systems Administration

how to speed up prtime dbreorg

Sort:
You are not authorized to post a reply.
Author
Messages
Aihui
Basic Member
Posts: 8
Basic Member
8/18/2014 7:33 PM
    We need to install two CTPs in the hope of fixing one program error. One of these two CTPs has lots of database changes, which also inlcudes dbreorg of PRTIME table. Due to the large size of this table, it took about 32 hours to finish the activate installation of this patch in our dev environment. To speed up the process when we did the installation in test environment we used "-F" option in order to skip the data dump/reload step of dbreorg. Unfortunately for certain table changes, dbreorg -F option is not working so the installation would still take long to finish. I am wondering whether anyone has ever encountered similar issue and had a workaround solution for this. Obviously we can't take our production system down for so long. Thanks.
    John Henley
    Senior Member
    Posts: 3348
    Senior Member
    8/18/2014 8:14 PM
      Off the top of my head, this is what I have done in the past (should cut down time but will still require more manual work).
      1. The key is to figure out what SQL is being generated for the dbreorg (i.e. I'm assuming it is creating a new index, new columns, etc.). There are several ways to do that depending on what database you are running)
      2. Rename the PRTIME indexes from PRTSETxxx to XRTSETxxx and the table from PRTIME to XRTIME via DBMS tools (i.e. SQL manager, etc.).
      3. Create a new PRTIME empty table via bldxxxddl
      4. Apply the CTP to do the dbreorg on the empty table.
      5. Rename the new empty table to ZRTIME (and indexes to ZRTSETxxx)
      6. Rename the XRTIME table back to PRTIME
      7. Rename the XRTSETxxx indexes back to PRTSETxxx
      8. Run the SQL captured in step 1 to create whatever new columns / indexes required
      9. reapply the CTP to ensure no dbreorg is not required
      Thanks for using the LawsonGuru.com forums!
      John
      Aihui
      Basic Member
      Posts: 8
      Basic Member
      8/19/2014 3:44 PM
        Thanks John, really appreciate your input.
        Jimmy Chiu
        Veteran Member
        Posts: 641
        Veteran Member
        9/4/2014 3:50 PM
          The long term solution probably is to upgrade your database server. 32 hours is @_@ regardless of your table size.

          It takes my database server 4 minutes to do a full production database backup @ 380GB size.
          You are not authorized to post a reply.