MQT and too small transaction log size

[Toralf =?utf-8?q?F=C3=B6rster?=]

We tried to use MQT in our environment, but with the biggest MQT
we always run into this problem:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011

Because it is not sufficient to increase the transaction log over the
current (already big) size we are wondering whether DB2 provides the
possibility of something like an auto commit after N lines (or sth
similar) or a parameter to not use the transaction log size.
--
Toralf Förster
pgp key 0x7DB69DA3

[Ian]

Toralf Förster wrote:

Ian wrote:

>You can always disable logging for the MQT refresh (alter table ...
activate not logged initially), which will eliminate your problem
and speed up the refresh, at the expense of recoverability.

Ah thanks, we'll try it.

Just be aware that if the MQT refresh fails for any reason, your MQT
must be dropped and recreated. Not that this is necessarily a big deal
since you're already doing a full refresh, but just be aware.

>If you're on 9.1 or later, you can also "load from /dev/null of del
replace into your.mqt" before your refresh. This will physically
empty the MQT (eliminating the delete that occurs at the beginning
of the full MQT refresh), which will save on transaction log space
(and also speed up the refresh).

We run DB2/6000 8.2.8.
We used the load command for real tables already. Would it be possible
to use it for MQT too in version 8 ?

I am 99% certain that this was introduced in V9 as part of some other
changes. However, since you're on a very recent fixpack I suppose it's
possible that it could have appeared. You can always try and see what
happens.