Log Analysis

The Db2 log is much more than just an important part of the Db2 recovery strategy; it is also a goldmine for a variety of other important purposes. Database administrators, application developers, and maintenance programmers can use the Db2 log to repair the results of faulty program executions, misscheduled jobs, or user errors. In many cases, a standard recovery based on image copies is inappropriate – because it resets too much – in fact, specific repairs to the data are required.


Related Products


ULT Logo
Provide REDO and UNDO SQL for data repair or data propagation. Find Quiet points for Recovery & support auditing tasks.

Log Analysis & Tracking supports you in the following areas:

As the Db2 Log records all data changes, it is ideally suited as a base for the provision of audit reports. Exploiting Db2’s log makes subsequent changes and supplements to established applications dispensable, also the known operational drawbacks of triggers are avoided. Therefore, compared to setting up a costly mechanism that continuously extracts and supervises all data changes, ongoing log analysis for the tables in question might be the better solution.

A log analyzer or log extractor program requires as control data at least the timestamps of start and end of the observation period and the name of the object to be observed. This sounds quite easy. In practice it might turn out that, as often, the devil is in the details. To specify and to execute a process that continuously delivers the changes to a set of tables or databases requires some efforts. It is preferable to deploy a tool with an on-line interface that supports the definition of the process and that facilitates operation and house-keeping.

The tool should offer a flexible way to specify the scope of the process, i. e. to select the set of objects to be observed, at best by generic declaration of databases, tablespaces, packages. For each of the monitored tables a file is needed to hold the extracted information either formatted as SQL or as LOAD input. Next, the target tables in that the detected changes are to be inserted/loaded must be created. Regular scheduled execution of a process that monitors a growing set of objects must consider naming conventions, possible restarts (dataset full), house-keeping (empty files), possible occurrence of new tables (monitor entire database), structural changes of the tables involved, etc. A log analysis tool that automates these things and for example automatically resumes at the end point of the previous run is ideally suited to usual data center requirements.

There are many occasions to forward data from one database to another: usage of more economic platforms for subordinate processing, improved accessibility, fault tolerance, conversion projects, etc. In general propagation is a well defined task; at the beginning the data must be entirely copied to the target system and afterwards periodically synchronized, in other words, the changes that occur at the source side need to be forwarded or propagated to the target from time to time. The process is of course asynchronous, the source does not wait for the target’s acknowledgement.

As the Db2 Log records all data changes, it is ideally suited as a base for the provision of the updates that occurred on the source side of the process to the tables in question. The challenge lies in the synchronization of the initial load and the subsequent ongoing propagation of the deltas, and, as always, in the operational requirements regarding a robust, easily restartable and flexible process. Flexible for example in respect of scope changes, i. e. additional or other tables and also in respect of table structure changes.

Most qualified for robust propagation is a tool that helps with automations for initial copy and synchronization, scope changes and structural alters, and last but not least, that ensures gap-less update of the target tables.

The information in the Db2 log can help you identify recovery issues. ULT4DB2™ log analysis can report log events, long running updaters, programs rolling back updates, activity for user, plan or object and point of no activity. It allows you to automate the execution of the reports. In addition, activity data is stored in a repository to allow you to query the data, investigate changes and identify log points for recovery.

Rollbacks Report
Changes are backed out if programs abend or issue explicit rollbacks. ULT4DB2 allows you to analyze the logs and identify stability issues in your applications. If programs are issuing too many rollbacks, it might be related to an application failure that can be resolved by fixing the program logic.

Identifying programs committing infrequently is an important task because these programs are impacting the recoverability and the availability of tables. Overlooking these programs may cause delays in recovery if a considerable log range must be read to reapply log changes. Also it can impact the Db2 restart time. ULT4DB2’s LONGRUNNERS report can help you identify these programs. You can then consider changing the programs and add logic to commit the changes more frequently. It might be needed to execute this report continuously on your critical tables to ensure that the recoverability and availability is not at risk.

ULT4DB2 can detect logical units of recovery and quiet points, periods of no activity against a group of objects that can be used as points of consistency. If you avoid taking QUIESCE, ULT4DB2 can be used to find a quiet point that can be used as point-in-time for the recovery process, so the recovery tool can create a consistent object without QUIESCE.

Activity reports can help in identifying the workload for objects, users, programs and jobs. ULT4DB2 allows the user to filter the log analysis on specific authorization ids, correlation names, plans and objects. The activity reports provide also statistics on the number of inserts, updates, deletes and log records recorded by the activity.

To undo committed changes you can use the RECOVER utility to recover the table to the point in time before the changes were made. However, in many cases, some other legitimate changes could have been made on the table. If you recover the entire table to a point in time, these legitimate changes might be lost. To ensure that no other legitimate changes were made after or during the incorrect changes that you wish to undo, you can run an activity report to confirm that recovery will not back out legitimate changes. If the activity shows that the table has more legitimate changes than incorrect changes, you can generate UNDO SQL to back out the incorrect changes. If the activity shows that the table has more incorrect changes than legitimate changes, it may make more sense to RECOVER the entire table to a point in time before the incorrect changes started and then generate REDO SQL to reapply all the legitimate changes. ULT4DB2 allows you to run an activity report and load the information into an ACTIVITY table. The information can then help to decide what is the best approach.

If you decide to UNDO the changes, you need to generate UNDO SQL only for the specific program, user and job that made the updates. Filtering should allow you also to limit the generated SQL to certain rows in the table by specifying a WHERE clause. The generated SQL can be executed immediately using the MODE EXEC option. Important is that the tool generates the UNDOs in the right order, for example, rows of child tables are to be deleted before the associated parent row can be deleted.

It makes sense to RECOVER and redo legitimate changes only if the number of changes you wish to undo is relatively high and the cost of undoing incorrect changes is higher than recovering the entire table. For example, if a table has million rows and 70 percent of the rows were deleted by mistake while at the same time 5 percent of the rows had legitimate changes, then it makes more sense to recover the entire table and then reapply the 50000 changes that were lost with recovery. The tool should allow you to identify exactly the log point that should be used to recover the table before the table was incorrectly changed. You can then generate REDO SQL for all the legitimate changes that were made since the selected point of recovery. The REDO SQL should exclude the unit of work that made the incorrect changes that you wish to undo. Also important is flexible handling of triggers, if the same triggers are implemented on both sides triggered changes must normally not be forwarded.

With image-copies, Db2 has a great instrument to restore tablespaces to any point-in-time when anything goes wrong with the data. But when the whole tablespace is dropped accidentally Db2 also deletes all the information about the image-copies. Db2 is not able to restore any of the dropped items. Here ULT4DB2 offers a solution to use the Db2 log and old image-copies to recreate dropped tablespaces and restore them to the point before the drop.