All,

I would like to know why my db is generating so many logs that the disk space allocated for archival logs is getting filled up. It has generated more than 4000 logs in 5 days and this is the second time we are facing with such a issue. I am posting the information below from the snapshot and db2pd.

Commit statements attempted = 52232355
Rollback statements attempted = 1
Dynamic statements attempted = 52242605
Static statements attempted = 34826171
Failed statement operations = 1
Select SQL statements executed = 17419349
Xquery statements executed = 0
Update/Insert/Delete statements executed = 34813596
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0

Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 664
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Number of MDC table blocks pending cleanup = 0

Rows deleted = 496
Rows inserted = 497
Rows updated = 34812086
Rows selected = 17500725
Rows read = 52347875
Binds/precompiles attempted = 0

Log space available to the database (Bytes)= 516194841
Log space used by the database (Bytes) = 6045159
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 101372207
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000004
Log pages written = 27990499
Log write time (sec.ns) = 14991.000000004
Number write log IOs = 27990365
Number read log IOs = 0
Number partial page log IOs = 27012357
Number log buffer full = 0
Log data found in buffer = 0
Log to be redone for recovery (Bytes) = 69219752
Log accounted for by dirty pages (Bytes) = 69219752


Database Partition 0 -- Database ******* -- Active -- Up 0 days 00:52:47

Logs:
Current Log Number 6651
Pages Written 1625
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Log Chain ID 0
Current LSN 0x001961B61CDA

Address StartLSN State Size Pages Filename
0x07700000C8C7D2D8 0x00195E628000 0x00000000 4000 4000 S0006648.LOG
0x07700000C8E5E9D8 0x00195F5C8000 0x00000000 4000 4000 S0006649.LOG
0x07700000C8C7F118 0x001960568000 0x00000000 4000 4000 S0006650.LOG
0x07700000C8F929D8 0x001961508000 0x00000000 4000 4000 S0006651.LOG
0x07700000C02AFF38 0x0019624A8000 0x00000000 4000 4000 S0006652.LOG
0x07700000C8F95558 0x001963448000 0x00000000 4000 4000 S0006653.LOG
0x07700000C9064EF8 0x0019643E8000 0x00000000 4000 4000 S0006654.LOG
0x07700000C9068178 0x001965388000 0x00000000 4000 4000 S0006655.LOG
0x07700000C8D19958 0x001966328000 0x00000000 4000 4000 S0006656.LOG
0x07700000C005EF78 0x0019672C8000 0x00000000 4000 4000 S0006657.LOG
0x07700000C8D19BD8 0x001968268000 0x00000000 4000 4000 S0006658.LOG
0x07700000C8D1A798 0x001969208000 0x00000000 4000 4000 S0006659.LOG

Also I used a query which is posted in the forum and got the output as follows for log used and percentages in mb.

LOG_USED_MEG 2
LOG_SPACE_FREE_MEG 495
PERCENT_USED 0
MAX_LOG_USED_MEG 96
MAX_SECUNDARY_USED_MEG 0
SECONDARIES 0

Please help me and i am a nebie to db2.

DB2 writes data to the transaction log when data is changed (insert, update, delete, import, etc) or when DDL is submitted (create table, drop table, etc). How are we supposed to know what SQL has been submitted against your database?

DB2 does not just generate log records by itself. You would need to look at what your applications were doing at the time.

May be it is time to modify your backup/recovery strategy to deal with larger log volumes.

How do i know what processes were running during that time and also how can i know which one is filling up the logs. I will provide any additional information needed.

Please let me know the steps to approach this problem.

One way is to collect:
db2pd -d <db name> -log -app -tra -dyn

I checked for everything and still the issue prevails. the db is only 600mb in size and it has generated 10gb of logs since 4am till 1024am this morning.

Also can you please help me turn the event monitor on so that i can get some idea of what is filling up the space.

Also we backup the db every ni8 and move the archive logs twice everyday.

What did you check? Did you collect and check db2pd info?

I checked the db2pd and also did snapshot. But didnt find the root cause for this. When it happened today i was able to see an update statement running from a batch job and am sure that caused the problem. But the same batch jobs run in all the environments which are replica of this environment used as test/dev/uat and has never caused any problem even today. All the parameters for the dbm and db are same in all the env and log space is also same.

We push the archive logs every day at midnight,400am and 600pm.

Something must be different. Check the amount of log space being by this update statement in different environments (db2pd -tra). Is DDL/data exactly the same?

1) Looking in original post, many updates were done.

Although, the average length of log records(or row length) was not clear,
if assumed 100 byte for average length of log record,
total bytes logged would be about 7Gbyte(34.8M * 2 * 100).

So, I thought that logged 10Gbyte was not so unusually big.

2)
Compared those numbers, simple proportions could be seen.

(Commit stmts) : (Select stmts) : (U/I/D stmts) = 3 : 1 : 2
(Select stmts) : (Rows selected) = 1 : 1
(U/I/D stmts) : (Rows updated) = 1 : 1

These simple proportional relations made me thought of repeatedly execution of specific programs/transactions.

3) If guesses in 2) was sound,
you might want to see something like called administrative report or service level report, so on.
In those reports, you may find like top nn programs/transactions executed and/or heavy programs/transactions executed.

Thank You all for posting your quick replies. As asked the log spaces and the data and ddl are same in all the environments.