asked on

DB2 AIX 6.1 - how to reduce COMMIT time and increase TRANSACTION LOG?

hi guys,

(1) How do I reduce the intervals between COMMITs?
I want my database to COMMIT more often, therefore reducing the need to have a large transaction log because my database is very large... even clearing / deleting a few days data can cause the transaction log to fill up easily... as one day's data can have 50000 - 100000 records.

Also, (2) how do I increase the transaction log?

Please kindly advice. Esp if there are any other solutions... Thanks!

ASKER CERTIFIED SOLUTION

yongsing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yongsing

A fast way of deleting a very large number of records is:

(1) EXPORT (as an ixf file) the records you want to keep
(2) Do a LOAD REPLACE of this file into your table

This will not do any logging and will reclaim space.

Haho

ASKER

hi yong sing,

Thanks for your excellent advice but I have one question... does it mean that the COMMIT interval parameter is not configurable? I thought it was possible to configure this param but I just don't know how... :)

Thanks!

yongsing

I don't think that there is such a thing as a commit interval parameter. Commits are issued by applications to the DBMS. DB2 will commit a transaction only when it is told by the application program. If a transaction involves many rows, then the transaction log will fill up faster. If your application issues a DELETE which involves a lot of rows, then the log will fill up very fast. The log will only be cleared when a commit or rollback is issued after the delete.