Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Huge data Update in production DB

    Hi All,

    I need to update huge data in production db Table that normally will take 2 hours to finish. We cannot afford holding tahe table that long.

    How can we use cursor to do the data in chunks. Still allowing most part of table available for on Line Gamers.


    Any good technique will update few rows and release the lock on table and accquire lock again and do small update again.

    DB2 ver 8.2 on windows server 2000


    Thanks in adv,

    DBFinder

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, open the cursor "with hold", and do a commit every 100 to 1000 updates. Normally a cursor is closed upon commit, but if you use "with hold" option it will stay open.

    Also, open the cursor with UR isolation level, and do the update separately (not update where current of cursor). This assumes the table has a primary key.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Marcus,

    I opened the cursor with hold and committed after 1000.

    The procedure ran like fully loaded and finished in few minutes.

    Cool,

    Thanks again.

    DBFinder

Posting Permissions

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