Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: The Trasaction Log for database is full

    We have a database with 3 million rows and we need to perform a update based on a selection criteria and the selection would update 450K rows out of these 3 million records. I get "The transaction log for database is full" error all the time when trying to update huge rows like this. my update command is like this
    db2 update tablename set columnname1 = 'value1' where columnname2 = 'value2';

    is there a problem in query? it works fine for small queries. I cannot extend 'where' with 'and' and some other column because the update queries will go in to hundreds. Any help is highly appreciated.

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    log full

    or reduce the update set or enlarge the log
    update db cfg using logsecond... or logfilsiz...
    or alter table .. initially notlogged .. and in the same unit of work the update, to prevent logging ..
    or if log archiving is active - use infinit logging...
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To expand on what Guy said, put the following commands in a script:

    alter table table-name ACTIVATE NOT LOGGED INITIALLY;
    update table-name set columnname1 = 'value1' where columnname2 = 'value2';
    commit;


    Run the script with the following options to turn off auto-commit:

    db2 +c -tvf script-name

    As soon as the commit happens, the logging will be turned back on.

    Caution: If the script fails during execution then the table may be corrupted, so back it up first. If you have DB2 version 7, then the table must have been created with NOT LOGGED INITIALLY when it was first created.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jul 2005
    Posts
    3
    A further possible solution:
    • db2 update tablename set columnname1 = 'value1' where columnname2 = 'value2' and rand() < 0.1;

    You have to increase the random count - so you have to run the script a few times - but because the amount of data to change is not so big, your log should'nt burst.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by przytula
    or alter table .. initially notlogged .. and in the same unit of work the update, to prevent logging ..
    Be *very* careful with this... if the statement bombs DB2 has no way of rolling it back, and the table is marked as inaccessible! You will have to RESTORE to recover.

    It's more meant for initial loads of data warehouse types of data than live and active databases.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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