ADM5530W - NOT LOGGED INITIALLY

Question: I was reviewing messages in db2diag.log and identified some recurring ADM5530W NOT LOGGED INITIALLY messages

ADM5530W The COMMIT processing of table "myschema.mytable"

          that used NOT LOGGED INITIALLY has been initiated.  It is recommended

          that you take a backup of this table's table space(s).

What do they mean?

Answer: The main purpose of NOT LOGGED INITIALLY is to restrict logging for a transaction.

If NOT LOGGED INITIALLY is initiated     and there is a : CREATE INDEX,DROP INDEX,ALTER TABLE,INSERT,DELETE or UPDATE  , these will not be logged.

It doesn’t mean there is an error, and is the expected behaviour for NOT LOGGED INITIALLY.

As a developer , you need to weigh up the benefits of using NOT LOGGED INITIALLY and the potential disadvantages.

One of the main benefits for NOT LOGGED INITIALLY is improved performance on certain operations and decreased amounts of transaction logs.

One of the main disadvantages is if the server crashes and there has not been a COMMIT on the transaction , then the ROLLBACK to the DB2 savepoint cannot work. Also , in the HADR environment where logs are passed from one server to another – then non logged operations will not be reflected on the STANDBY server

Another disadvantage is the Archival Logging situation

Some thought needs to be given as to when NOT LOGGED INITIALLY is appropriate. These are some suggestions:

1)There is a data load process with a range of sources

2) There are some complex calculations where temporary tables are required , and can be discarded

3)Limited space drive supporting DB2  database transaction logs .

Read More

How to report a DB2 performance problem (DBA DB2)