Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005

    Unanswered: RE: How to change log file size in Configuration Pannel ?


    I just want to know if I am proceeding right. The problem is with running or executing heavy sql scripts ( of cca 4 mil. lines ). When I executed such script this error msg poped up:

    SQL0964C: The transaction log for the database is full.

    Ho can I reset the logfilesize and through what DB2 ver. 8.1 application?
    I used DB2 - SetUp Tools - Configuration Assistant and then from top menu
    Configure - DBM Configuration, then Performance and I changed value of BACKBUFSZ from 1024 to 400000 .

    Did I turn to right solution????

    PLEASE, give me an idea.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    First of all, you should use COMMIT in your script as often as possible.
    This will shorten the length of your Units of Work and require smaller amounts of log space. If you do this and you still get the error, you need to look at the following database parameters: logfilsiz, logprimary, and logsecond. Refer to the Administration Guide: Performance manual for these.


  3. #3
    Join Date
    Feb 2005

    If I may bug you a little bit more, I have this permanent problem:

    I hopelessly keep on trying run .sql script which would create a page of 3 million lines ( table with data ). Each time the script ends with:

    SQL0964C: The ransaction log for the database is full

    The path of logs is on my drive D: which has 100Gb. I have increased the size of each logfile (4K pages) to 8000. Number of primary log files is 20, number of secondary log files is 200.
    So it seems changing of number of logfiles or actually the size of diskspace is not a problem, or?

    Can I turne the writing to log file temporarilly off? I mean by using this command:
    NOT LOGGED INITIALLY ( and how to use this command? )


  4. #4
    Join Date
    Aug 2004
    you can try using blk_log_dsk_ful set to yes. This means DB2 will attempt to create the log file every five minutes until it succeeds preventing diskfull errors

    Setting blk_log_dsk_ful to yes causes applications to hang when DB2 encounters a log disk full error, thus allowing you to resolve the error and allowing the transaction to complete. You can resolve a disk full situation by moving old log files to another file system or by enlarging the file system, so that hanging applications can complete.

    where are you archiving logs to ? tsm?

  5. #5
    Join Date
    May 2003
    If you are doing large imports, then there is an option to do intermediate commits at a specified frequency that will solve the problem. A commit frequency of about 1000 should be fine and will not impact the total execution time. See the IMPORT command in the Command Reference.

    You can use the NOT LOGGED INITIALLY feature by altering the table right before the long SQL statement and then doing an explicit commit right after the long SQL statement. The logging is turned off on that table until you do a commit. You must submit the script with the +c command to turn off auto-commit when doing this. See the command line processor options in the Command Reference for details on turning off auto-commit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2004
    Robert ,

    Your problem is more of application side. You have enough space in logs.
    May be you are running a huge batch job which is not being commited frequently

Posting Permissions

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