Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Reorgs + LOG FULL error

    DB2 LUW 8.2, AIX 5.3 + TSM/TDP (For SAP) also using HADR

    Hey all,

    I've just emerged from 24 hours of hell where a reorg on a large table managed to fill the log files, abort and mark the indexes bad. Then it continued to try and recreate the indexes in the background (holding a Z table lock the whole time) and obviously continued loop the same behavior until we intervened, each time filling the logs and grinding the system to a halt

    Soooo we dropped the secondary indexes and we increased the # secondary logs several times in the end reaching 20GB of logs (vs 4GB normally defined) and after many retries finally managed to get the Primary index rebuilt then we rebuilt the secondaries one at a time.

    My questions are:

    Are we doing something wrong? The table in question is 46GB and in the end it generated 14GB of logs to rebuild the Primary, yet on another DB I can reorg a 38GB table without issue using our standard 4GB of logs defined.

    Is it normal to have such huge UOWs on a reorg?

    Do we need to substantially increase my Logging settings? Is there a rule of thumb like "(largest table / 2)GBs of logging space"?

    Can we run reorgs unlogged? If so are there implications for HADR?

    Does anyone have any other suggestions or tips on Reorgs, logging and HADR?

    Cheers

  2. #2
    Join Date
    Jul 2004
    Posts
    306
    You don't have to answer ALL the questions, any feedback at all is welcome here :P

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    No input out there?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is usually recommended that LOGINDEXBUILD be set to ON if you are using HADR to facilitate takeover speed, so I suspect you have it ON. However, this will log many more pages during the index build phase of an inplace (online) reorg compared to an offline reorg.

    You should either turn off LOGINDEXBUILD or do an offline (classic) reorgs. LOGINDEXBUILD can also be set at a table-by-table basis to override the database configuration setting via the ALTER TABLE command with the LOG INDEX BUILD OFF option.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    Cool, I'll look into that. Luckily I've managed to replicate the problem on a test system so I should be able to try a few things....

    THANKS!

  6. #6
    Join Date
    Jul 2004
    Posts
    306
    Cool, I'll look into that. Luckily I've managed to replicate the problem on a test system so I should be able to try a few things....

    THANKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •