db2 rollforward log

[v***@imaxsoft.com]

I'm a developer, and I need this crucial information to support one of my customers.

How does db2 rollforward log work? Is there a standard API or tool that can be used to access/read this log? Are there any 3rd party tools available that IBM certified?

[Kelly Schlamb]

Hi,

You can use the db2ReadLog() API (and there is also a "no connect" version) to read DB2's log records. More information can be found in the manuals:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001673.htm
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001910.htm

There are 3rd party tools that do read the logs for the purposes of replication, auditing, etc. For instance, the DB2 Recovery Expert product has the ability to read the logs and show redo/undo SQL for the work that has been logged.

Regards,
Kelly Schlamb

[Knut Stolze]

Post by v***@imaxsoft.com
I'm a developer, and I need this crucial information to support one of my customers.
How does db2 rollforward log work? Is there a standard API or tool that
can be used to access/read this log? Are there any 3rd party tools
available that IBM certified?

I'm curious: what do you need the logs for?

You can use them (as I did here: http://tinyurl.com/2k4xey), but DB2 may
change the log record structure anytime. This happened with the mentioned
article, which doesn't work anymore in V9. So you will be bound to play
catch-up with any changes.

How DB2 logging works is rather straight-forward: each change made on a page
in DB2 causes a corresponding log record to be written. When a transaction
commits, this results in a commit log record. However, if a rollback
occurs (for whichever reason), DB2 will scan all log records of the
transaction being rolled back and write "compensation" log records, which
effectively negate the changes made before. Now, if you need to apply the
logs because you restored a backup or because DB2 crashed and not all data
on the pages was written to disk yet (crash recovery), DB2 will scan
through the logs and (conceptually) apply all changes in the normal and
compensation log records. It also keeps track of any transactions not yet
completed (commit/rollback) and rolls them back automatically after the log
apply.

p.s: This is a rather simple view. Of course, there are improvements like
not applying log records if the transaction has been rolled back or
starting only after a checkpoint, etc.