Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

314 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

session data for the databases monitored are maintained in a local datastore.

Core components are installed on each monitored database.

Use DB2 Recovery Expert in a data warehouse environment when:

򐂰 Determining the most efficient technique for a recovery situation.

򐂰 Rolling back specific unwanted data changes while the system remains

online.

򐂰 Analyzing database logs, providing text reports, and generating optional

executable redo or undo SQL using user-specified filters and report range

parameters.

Avoid using DB2 Recovery Expert to offset ETL application shortcomings. Log

analysis of extended time periods where high volumes of data have been

ingested into the data warehouse is not advisable.

8.6.1 Schema level repository

DB2 Recovery Expert uses a schema level repository to maintain details of

changes made to database objects. This repository is referenced to generate

DDL and DML for the recovery of objects.

You create and refresh the schema level repository through the schema level

repository web interface. The initial build of this repository is based on a user

selected backup image. You can then update this repository based on

subsequent backups; DB2 Recovery Expert keeps a record of changes which

can be thought of as a history of your system catalog.

The schema level repository “data store” can be located in a separate DB2

database or can be placed within an existing database as a separate schema.

8.6.2 Log analysis

DB2 Recovery Expert analyzes each archived transaction log to generate Log

Analysis reports detailing changes that have occurred within specified time

periods. Report filters allow you to include tables, table spaces, database

partition groups, or schemas. Log analysis can use the schema level repository

to reference the structure of objects that might have been dropped to build a

recovery plan for the dropped table.

Redo or Undo SQL statements can be generated from the Log Analysis report

and used to replicate or reverse changes that were previously made to database

objects.

Chapter 8. Building a corporate backup and recovery strategy 315

Log Analysis Report

Data warehouse environments, and fact tables in particular, are typically

characterized by volumes of INSERT statements. UPDATE statements against

fact table entries, although not unusual in some industries, can be considered

rare. The scenario in this section shows how the Log analysis feature can be

used to determine where an UPDATE statement was issued against the

CSTINSIGHT.CUSTOMER_TXN table in error.

Figure 8-2 shows the objects page where the CUSTOMER_TXN table has been

specified for inclusion in the report. In the next step, the Filters page, we only

checked the box for Updates as we simply want to report on UPDATE

statements issued against the fact table.

Figure 8-2 Selecting objects to be included in the Log Analysis report

When the filter is selected, click Run to start the Log Analysis report for the

criteria entered. Figure 8-3 on page 316 shows the completed Log Analysis

statistics. Two update statements were identified for the CUSTOMER_TXN table.

Click Report to see the time each update occurred, the database partition the

Tip: Avoid performing a Log Analysis for INSERT statements during a high

volume ETL time period. Instead, choose the time period and specific

statement you want to report on.

316 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

updated rows resided on, and other related details. The Log Analysis results can

help determine the type of recovery scenario and plan you need implement to

restore or Undo the UPDATE statements.

Figure 8-3 Log Analysis report filtered to report on update statements in a data warehouse environment

To UNDO the two UPDATE statements identified, we can run the Log Analysis

Summary+Detail report with a SQL Type of Undo SQL as shown in Figure 8-4.

Figure 8-4 Run the Log analysis report with the Undo SQL parameter set to generate

Undo SQL for the statements identified