Hi,

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.

Thanx

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.

Andy

Thanks,

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? )

Thanks

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?

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.

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