INSERT...SELECT -> Transaction log full

[ShowMeBO]

Hi!

I was getting "transaction log full" -error message while running INSERT.. SELECT statement. The SELECT part of the query isn't that complicated but it returns about 10M rows, which I then INSERT. Both with one statement as told.

I'm running DB2 version 9 on Linux.
DB LOG CFG:
FILSIZ 8000
LOGPRIMARY 40
LOGSECONDARY 20

Any suggestions what are the appropriate values for these?
Is there any way (commit?) to make it work so that transaction logs are emptied after certain number of rows are loaded, or what to do?

I will have even more rows in the future "INSERT.. SELECT" -statements so it would be nice to get some other option than increasing the log file size and number.

 

[blom0344]

An alternative might be to perform a load instead of inserting the data. If you use the regular import facilities you can also set a commitcount..

Ties Blom