Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    6

    Unanswered: DB2 Transaction log is full. How to flush / clear it?

    Hi,
    I’m working on a experiment regarding to a course I’m taking about tuning DB2. I’m using the EC2 from Amazon (aws) to conduct the experiment.

    My problem is, however, that I have to test a non-compression against row-compression in DB2 and to do that I’ve created a bsh file that run those experiments. But when I reach to my compression part I get the error ”Transaction log is full”; and no matter how low I set the inserts for it is complaining about my transaction log.

    I’ve scouted Google for a day now trying to find some way to flush / clear the log or just get rit of it, i don’t need it. I’ve tried to increase the size but nothing has helped.

    Please, I hope someone has an answer to solve this frustrating problem

    Thanks
    - Mestika
    Last edited by Mestika; 04-08-10 at 08:32.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot flush, clear, or just get rid of the transaction log for DB2. You really hove do direct control over it. And for good reason. This is where DB2 ensures that your data is consistent. The reason you log is getting full, is that you are doing too much work in one single transaction. You need to either commit more frequently and/or make smaller transactions.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are using circular logging (the default) then you need to increase the number of secondary logs to about 200, and increase the logfilesz to about 20000 pages. These changes will probably solve your problem.

    If you are using recovery logging (sometimes known a log retain), then you need to make the same changes as above, plus you need to archive the logs (LOGARCHMETH1), and also periodically prune (delete) the older logs on the log archive path.

    All these changes are set up with the "db2 update db cfg for db-name using XXXXXXXX XX" command, and then terminate all connections for them to take effect.
    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
    Apr 2010
    Posts
    3
    Tranaction log full error pops up when the DML transactions are not commited frequently.
    Use COMMITS frequently, say for every 1000 records.
    If you want to delete huge data from the tables, better drop them and recreate before inserting the data.

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Satyenderm View Post
    Tranaction log full error pops up when the DML transactions are not commited frequently.
    Use COMMITS frequently, say for every 1000 records.
    If you want to delete huge data from the tables, better drop them and recreate before inserting the data.
    or can load replace null file, right?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    I am agree with Marcus_A.

    If you are not sure about how big your transaction would be then better to go for the recovery logging.

    for this some changes required in your DB configuration
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by singhipst View Post
    If you are not sure about how big your transaction would be then better to go for the recovery logging.
    How is recovery logging going to help?

  8. #8
    Join Date
    Jun 2009
    Posts
    272
    As you are doing an experiment, I would suggest you to go for infinite logging where in you set logsecond = -1 and it allows you to use as much active log space as you want provided you have enough filesystem space for the logpath and the overflow logpath.

    This cannot be accomplished by turning off logarchmeth1.

    Don't forget the fact that rollback and crashrecovery will be pretty slow by enabling infinite logging.
    Last edited by blazer789; 04-09-10 at 17:42.

Tags for this Thread

Posting Permissions

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