Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: Log size and HADR

    Hi, again I may need your suggestions.
    DB2 V9.7 FP3 / Win 2008 R2
    HADR is in place with near sync mode - bandwidth is 8mbps beteen PR and SB
    Primary log = 40
    Secondary log=60
    LOGFILSZ=10MB

    We have to remove around 450K rows from three tables (each table 150K).
    On the forth table, we need to update one column with "BAD DATA", again this table contain 150K rows.

    The first three tables are small. The size wise around 25GB (Contain 40million rows).
    The fourth table is around 30GB containing around 300 million rows.

    We are going to use a simple SP (using cursor with hold) to delete and update the tables.
    My questions is....
    1. Do I need to increase the logsize ?
    2. Do I need to increase the logbufsiz?
    3. The default COMMIT COUNT IS defined as 1 in the SP. Is it advisable to increase to 1000
    4. The HADR will handle with this volume?
    5. Temp or User TBL space need to be any size?
    finally....any other considerations??

    Please share with me your experience/knowledge.
    Thanks.

  2. #2
    Join Date
    Nov 2011
    Posts
    87

    any Volunteers

    Any Volunteers !

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is this a one-off activity ? If it is, then I will keep the changes to database configs to a minimum. And if your SLAs will permit, disable hadr on standby and transport logs from primary and do rollforward on standby.

    if you want to change configs, yes, i would increase the log file size to atleast 1 gb and increase the log buffer size to. 10MB is small. Definitely increase the commitcount. how much, depends on what other activities going on the system. if this activity is on its own, then I would increase much higher than 1000 - 10s of 1000s. make sure your log filesystem and your pri+sec logs can cope.

    8mbps does not sound a fast line. using hadr is very almost certain to be a bottleneck.





    Quote Originally Posted by db2mtrk View Post
    Hi, again I may need your suggestions.
    DB2 V9.7 FP3 / Win 2008 R2
    HADR is in place with near sync mode - bandwidth is 8mbps beteen PR and SB
    Primary log = 40
    Secondary log=60
    LOGFILSZ=10MB

    We have to remove around 450K rows from three tables (each table 150K).
    On the forth table, we need to update one column with "BAD DATA", again this table contain 150K rows.

    The first three tables are small. The size wise around 25GB (Contain 40million rows).
    The fourth table is around 30GB containing around 300 million rows.

    We are going to use a simple SP (using cursor with hold) to delete and update the tables.
    My questions is....
    1. Do I need to increase the logsize ?
    2. Do I need to increase the logbufsiz?
    3. The default COMMIT COUNT IS defined as 1 in the SP. Is it advisable to increase to 1000
    4. The HADR will handle with this volume?
    5. Temp or User TBL space need to be any size?
    finally....any other considerations??

    Please share with me your experience/knowledge.
    Thanks.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2011
    Posts
    87
    Thanks Sathyaram.
    Really helpfull tips. Yes, this is a one off activity. I am doing this within our 12 maintenance window. So no other activity will happen on the system.

    Disabling the HADR is a very good idea. I did not think about this. Also I never did this in the past.

    Can you see these steps arecorrect pls.

    On Standby server,
    1. Deactivate db mydb
    2. Copy active logs from PRImary server to SB server
    3. Rollforward db mydb with end of logs
    4. Activate db mydb
    5. Start hadr on db mydb as standby




    Best Regards,

Posting Permissions

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