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

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

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.

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?

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

How is recovery logging going to help?

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.