Hi,
I'm using Db2V8.1 on AIX.

I've a shell script that contains delete operations on 5 tables.
each table has around 1 million records on average to delete.
I applied commit statement on each db2 delete statement.

After executing 4 statements, there is an error "Transacton log for the database is full".
I've seen the path by using command "db2 get db cfg" and found the below path for transaction log
"/u02/db2inst1/NODE0000/SQL00001/SQLOGDIR/"

I asked DBA either to increase the log filesize or increase number of logs or clean up old log files.
He said there is no error in the file db2diag.log.

Am I looking in the correct path? How to fix this issue?
Is there any option to have auto commit after x number of records deleted?

You have two options.

1) beat you DBA over the head and tell him to increase the size of number of logs or both.

2) change your script to break up the statements into smaller data chunks. This will not use as much log space, and also perform faster.

I personally would go with number 2.

Andy

Thanks for information.

Below is the Logfile configuration.

Log file size (4KB) (LOGFILSIZ) = 10000
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 50
Changed path to log files (NEWLOGPATH) =
Path to log files =/u02/db2inst1/NODE0000/SQL00001/SQLOGDIR/

I went in to the location and below are the logs

-rw------- 1 db2inst1 db2iadm1 512 Jul 23 15:42 SQLLPATH.TAG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 03:41 S0133167.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133170.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133169.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133168.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:03 S0133171.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:05 S0133172.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:07 S0133173.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 04:10 S0133174.LOG
-rw------- 1 db2inst1 db2iadm1 40968192 Aug 28 08:18 S0133175.LOG
-rw------- 1 db2inst1 db2iadm1 38760448 Aug 28 08:18 S0133166.LOG


It shows that only 10 log files are there. There are no secondary logs are created. DBA tells that only 10% of the space is used. But we are still getting transaction log full error.

How to know whether all the space is used when there is only 10 log files are created?

We will be splitting the data to delete but how to rectify the space is used 100%?

Look at the monitor elements sec_log_used_top, tot_log_used_top, sec_logs_allocated, uow_log_space_used. This might give you more info.

Also look here for log related config parameters:

http://publib.boulder.ibm.com/infoce...n/r0006082.htm

Andy