Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    14

    Unanswered: Trasaction logs full while delete

    Hello,

    This is very basic error that we face many times.

    Last time I had faced it while deleting all rows from table and the table had millions of rows.
    I went on increasing the LOGSECOND parameter & reach to the maximum limit, still i couldn't empty the table.

    Finally i need to drop the table and recreated it to make it empty.

    Ok. My question is... is there any other way to delete all data from table without making transaction logs full?

    Thanks!


    P.S. -- db2 v9.1 on AIX 5
    Last edited by DB Guide; 12-29-09 at 11:02.

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    load of an empty file with replace option might do the trick
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    you may want to use import instead of load if your db is using archival logging

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Quote Originally Posted by DB Guide View Post
    Hello,

    This is very basic error that we face many times.

    Last time I had faced it while deleting all rows from table and the table had millions of rows.
    I went on increasing the LOGSECOND parameter & reach to the maximum limit, still i couldn't empty the table.

    Finally i need to drop the table and recreated it to make it empty.

    Ok. My question is... is there any other way to delete all data from table without making transaction logs full?

    Thanks!


    P.S. -- db2 v9.1 on AIX 5

    set logsecond to -1.

    or increase your logfilsiz/logprimary

    or load from null or other truncate tricks
    Last edited by yhangw; 12-29-09 at 21:21.

  5. #5
    Join Date
    Oct 2009
    Posts
    14
    Thank you all for your replies.

    "Load/Import" is really a good option to empty the table.

    Thanks a lot.

  6. #6
    Join Date
    Apr 2007
    Posts
    21
    Simple method to empty the table is

    ALTER TABLE x ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by DB Guide View Post
    Hello,

    This is very basic error that we face many times.

    Last time I had faced it while deleting all rows from table and the table had millions of rows.
    I went on increasing the LOGSECOND parameter & reach to the maximum limit, still i couldn't empty the table.

    Finally i need to drop the table and recreated it to make it empty.

    Ok. My question is... is there any other way to delete all data from table without making transaction logs full?

    Thanks!


    P.S. -- db2 v9.1 on AIX 5
    You can use COMMITS after some number of deleted records, or better
    use LOAD table with REPLACE, using DUMMY input file.

    Lenny

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Lenny mentioned commits, which is very important when you are trying to delete millions and less of rows, but not the entire table. If you are attempting to delete some subset of rows, you would want to do the deletes within a loop and commit after a particular count of records.

    Dave

  9. #9
    Join Date
    Jun 2009
    Posts
    272
    If you are in 9.7 you can also use truncate table command.

    db2 "truncate table tablename IGNORE DELETE TRIGGERS drop storage immediate"

Posting Permissions

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