DB2-CA Log Analyzer – Generate UNDO /REDO SQL

Problem : Application team run a update query without a where clause by mistake which resulted in mass update to around 600,000 records in a production table. This happened during online up time and users were also making their updates. Application owner was not ready to bring down the table and recover the table to status before wrong mass update. Suggested to work after online are down.

Initially opted to recovery the table to point in time just before the mass update happened and redo all SQL changes happened during the online time, which would be approx 30,000 records.

Used CA Platinum Log analyzer to generate REDO/UNDO SQL during the online time but for UPDATE queries failed with below message.

LAE0038E Unable to format data. A partial SQL stmt will be generated.
LAE0037E Unable to generate a full UPDATE type SQL statement for:

Fixed the production problem by providing Clone table with data before mass update happened and asked application team to write program to fix the data problem that occurred due to wrong mass update.

Later contacted CA to find why the LOG analyzer was not able to produce the UPDATE UNDO/REDO SQL.


CA Response

The usage of DATA CAPTURE CHANGES is recommended at the table level but is not a requirement of PLA. PLA can read the most recent full image copy prior to the log record and then read the log forward from the image copy to create the row image. Image Copy Reporting must be specified when generating the REDO or UNDO file if DATA CAPTURE CHANGES is not enabled for the table being reported against and UPDATE statements are found.

From the PLA DML Activity Report Options panel specify:
Level of Detail ==> I for I - ImageCopy

When generating a DML Activity report with Detail Level of Detail and DATA CAPTURE NONE, the following messages are issued:

Since DATA CAPTURE CHANGES is not being used, a Level of Detail of Image Copy would need to be used. A complete row does not exist in the log and Log Analyzer needs to have a complete row to generate complete statements.


Final Conclusion

DATA CAPTURE NONE is the default and default is used in all table definitions. If we change it to DATA CAPTURE CHANGES log over head will be more . So better solution will be to generate REDO or UNDO SQL for mass updates using IMAGECOPY LEVEL option.


Solution:
Using Load Format it generated a load input file and load control card.

Load file – The type of records included in the output files is controlled by the REDO and UNDO Data option on the Load Options screen. Each record includes a Statement Type indicator, depending on the type of update the record describes. The following is a list of valid record types:
. UA (update after image)
. UB (update before image) Important!
. DM (mass delete)
. I (insert)
. D (delete)


Load Control card - This output data set contains control cards for use with a LOAD utility or your own programs


Utility program
Indicates how you intend to use the load format file.
->A (Log Apply)—Generates a load format file and control cards to run the Log Apply utility. The cards can be executed to redo or undo the changes in the file.
->L (Default) DB2 Load—Generates a load format file and control cards that can be edited to run a DB2 LOAD utility. The cards can be used to populate a historical table against which you can make requests.
Note: Cards from the DB2 Load option cannot be run against the
original table without editing.


Key Points
Load Format is much faster than the REDO or UNDO SQL method. It uses Load utility for applying the changes from the log .

The benefit of Log Apply through Load Format option is that it manages the prepared, dynamic SQL statements to minimize the number of PREPAREs performed, unlike the individual REDO/UNDO SQL which performs a PREPARE for each statement.