ADM5530W - NOT LOGGED INITIALLY

13 July,2016 by Jack Vamvas

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)

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on ADM5530W - NOT LOGGED INITIALLY

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright