We have a database with 3 million rows and we need to perform a update based on a selection criteria and the selection would update 450K rows out of these 3 million records. I get "The transaction log for database is full" error all the time when trying to update huge rows like this. my update command is like this
db2 update tablename set columnname1 = 'value1' where columnname2 = 'value2';

is there a problem in query? it works fine for small queries. I cannot extend 'where' with 'and' and some other column because the update queries will go in to hundreds. Any help is highly appreciated.

or reduce the update set or enlarge the log
update db cfg using logsecond... or logfilsiz...
or alter table .. initially notlogged .. and in the same unit of work the update, to prevent logging ..
or if log archiving is active - use infinit logging...

To expand on what Guy said, put the following commands in a script:

alter table table-name ACTIVATE NOT LOGGED INITIALLY;
update table-name set columnname1 = 'value1' where columnname2 = 'value2';
commit;

Run the script with the following options to turn off auto-commit:

db2 +c -tvf script-name

As soon as the commit happens, the logging will be turned back on.

Caution: If the script fails during execution then the table may be corrupted, so back it up first. If you have DB2 version 7, then the table must have been created with NOT LOGGED INITIALLY when it was first created.

A further possible solution:

• db2 update tablename set columnname1 = 'value1' where columnname2 = 'value2' and rand() < 0.1;

You have to increase the random count - so you have to run the script a few times - but because the amount of data to change is not so big, your log should'nt burst.

Be *very* careful with this... if the statement bombs DB2 has no way of rolling it back, and the table is marked as inaccessible! You will have to RESTORE to recover.

It's more meant for initial loads of data warehouse types of data than live and active databases.

jono