Question & Answer
How to temporarily turn logging off for heavy change operations, such as insert, delete, update, and changes in indexes?
Tables being modified can be altered to activate the 'NOT LOGGED INITIALLY' attribute, which deactivates logging for the current unit of work. Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged.
At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated for the table and all operations that are done on the table in subsequent units of work are logged.
To use this option properly, the application doing the changes should NOT have AUTOCOMMIT enabled. Having AUTOCOMMIT OFF also helps define the scope of the transactions:
- For CLP, you can turn AUTOCOMMIT OFF using the registry variable DB2OPTIONS:
- db2set DB2OPTIONS=+c
- If you execute a script containing update SQL statements, such as inserts, and DB2OPTIONS is not set, you can execute the script using:
- db2 +c -tvf input_script.sql -z output_script.out
To create table T1 with the 'not logged initially' attribute:
- db2 create table T1(id int) NOT LOGGED INITIALLY
or, if tables T1 and T2 are already created, create a transaction with all the operations that are not intended to be logged:
- db2 alter table T1 activate not logged initially
db2 alter table T2 activate not logged initially
db2 delete from T1 where ...
db2 update T2 set ...
db2 import from data_file.txt of del insert into T2
The updates in T1 and T2 within this unit of work will not be logged.
1. You can create more than one table with the NOT LOGGED INITIALLY parameter in the same unit of work.
2. Changes to the catalog tables and other user tables are still logged.
Because changes to the table are not logged, you should consider the following when deciding to use the NOT LOGGED INITIALLY table attribute:
* All changes to the table will be flushed out to disk at commit time. This means that the commit may take longer.
* If the NOT LOGGED INITIALLY attribute is activated and an activity occurs that is not logged, the entire unit of work will be rolled back if a statement fails or a ROLLBACK TO SAVEPOINT is executed (SQL1476N).
* You cannot recover these tables when rolling forward. If the rollforward operation encounters a table that was created or altered with the NOT LOGGED INITIALLY option, the table is marked as unavailable. After the database is recovered, any attempt to access the table returns SQL1477N.
- If using stored procedures, the whole stored procedure defines a transaction by default, unless an explicit COMMIT is called within the procedure code. So, the 'ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY' can be called at the beginning of the procedure, and a COMMIT statement at end of the procedure (or after the procedure has been called) will end the transaction.
- This document and suggestions given to prevent logging activity do not apply to LOAD utility, since LOAD does not do logging.
16 June 2018