Unexpected SQL0964C Transaction log full

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bernd Hohmann - 23 May 2006 22:54 GMT

I have a table with CHAR(8), CHAR(5), DOUBLE, DOUBLE, VARCHAR(40),
DOUBLE, CHAR(1). This should be roundabout less than 100 bytes per row.

The primary key is the CHAR(8)+CHAR(5).

The table holds 413896 rows so the pure byte count should be about 42MB.

This table holds a 1:1 backup of another table.

LOGFILSIZ=2500, LOGPRIMARY=5, LOGSECOND=25 - I think this is plenty for
this task (about 50mb Primary + 250MB secondary).

The task: delete all +400.000 rows (DELETE FROM backup) and insert the
data from the master again (INSERT INTO backup (SELECT * FROM master) in
ONE transaction - gives SQL0964C.

Another strange thing: after restarting the database it works. But I
don't see any uncommitted statements (thanks to Pierre for your hint
where to look). (OS/2, DB2 7.2)

Bernd

Reply to this Message

Artur - 23 May 2006 23:48 GMT

Bernd,
Not obvious, but I think you may have not enough log space.

Total log space you have is: 300 MB (50 MB + 250 MB).
If, depending on logging overhead (also index logging?) and possibly
other activities, your transaction fills 150MB -- this means
"Transaction log full" (keep in mind that you have to log deletes
~50 MB and inserts ~50 MB).

Why 150 MB?

Probably because most manuals are not exactly correct saying that the
log full condition happens when primary and secondary logs are full.
This is not true. The truth is: it happens lot earlier.

You probably know, that transaction should fit available log space.
What happens if, after inserting and deleting (let's assume 150 MB
of log space) you issue rollback? To complete the transition DB2 has to
log all compensation statements (undo) which will take another 150 MB
from log space. In another words, DB2 must reserve a space in log for
possible rollback. DB2 is monitoring available log space and stops
processing earlier to guarantee enough log space for possible undo
records.

-- Artur Wronski

Reply to this Message