Generating UNDO and REDO SQL Statements

can generate executable SQL statements to undo or redo the data changes recorded in the DB2 logs. UNDO SQL statements let you recover from changes or inserts made by an errant application. REDO SQL statements let you apply changes made to one table to another table, perhaps after a production cycle has concluded. You can use these SQL statements to update tables across subsystems.
caladb2
CA Log Analyzer™ for DB2 for z/OS
can generate executable SQL statements to undo or redo the data changes recorded in the DB2 logs. UNDO SQL statements let you recover from changes or inserts made by an errant application. REDO SQL statements let you apply changes made to one table to another table, perhaps after a production cycle has concluded. You can use these SQL statements to update tables across subsystems.
  • The SQL statements generated by
    CA Log Analyzer™ for DB2 for z/OS
    provide a starting point for writing the SQL.
    CA Log Analyzer™ for DB2 for z/OS
    attempts to generate complete SQL statements but you might need to edit the statements before using them.
  • If you must generate multiple SQL statements, consider creating a load file and then using Log Apply to apply the load file to the target table instead of generating and executing individual SQL statements. Log Apply manages the prepared, dynamic SQL statements to minimize the number of PREPARE statements performed, unlike the individual REDO and UNDO SQL which perform a PREPARE for each statement.
Contents
Considerations for Generating UNDO and REDO SQL Statements
Review the following considerations before generating UNDO or REDO SQL statements.
Use of DATA CAPTURE CHANGES
Although 
CA Log Analyzer™ for DB2 for z/OS
 attempts to construct complete SQL statements, its ability to do so depends upon the amount of detail in the DB2 log files. The DATA CAPTURE attribute of a table dictates how much data is logged for an update transaction to that table. When DATA CAPTURE NONE (DCN) is used, the log record contains only the changed data. When DATA CAPTURE CHANGES (DCC) is used, the log record contains enough information to build a full before- and after-image. Best results are therefore achieved by enabling DATA CAPTURE CHANGES. (This parameter is specified in the CREATE TABLE or ALTER TABLE SQL statement for the table.) This consideration is important if you are generating SQL using the detail (D) option, because this option uses only the information that is present in the log.
If you are generating SQL for a table that does not have DATA CAPTURE CHANGES enabled, we recommend using the image copy (I) level of detail. This setting causes 
CA Log Analyzer™ for DB2 for z/OS
 to use both the log and any existing full and incremental image copies of the tables to construct a before- and after-image of the changed rows.
Using the image copy level of detail can significantly increase processing time depending on the number of tables selected, the amount of log data, the number of image copies, and the range of log data selected.
UNDO SQL for Unqualified Deletes and Truncates
An unqualified (or mass) delete occurs when a DELETE statement without a WHERE clause is applied to a table in a segmented or universal tablespace that was created with DATA CAPTURE NONE (DCN). In an unqualified delete, DB2 deletes the rows from the table without logging them. The deleted rows cannot be displayed or reconstructed because there are no log records.
If an unqualified DELETE or a TRUNCATE was issued against a table residing in a segmented or universal tablespace, 
CA Log Analyzer™ for DB2 for z/OS
 generates a partial statement with an error message. We do not recommend attempting to modify this statement to make it usable. Instead, undo the unqualified DELETE by recovering the tablespace.
If the table was created with DATA CAPTURE CHANGES, the mass delete record and each individual delete record are recorded in the log.
REDO SQL and Referential Integrity
By default, 
CA Log Analyzer™ for DB2 for z/OS
 does not generate SQL to redo changes made by DB2 to maintain RI (Referential Integrity). For example, if the deletion of a parent row causes DB2 to delete one or more dependent rows, 
CA Log Analyzer™ for DB2 for z/OS
 generates SQL to redo only the parent row deletion. You can have 
CA Log Analyzer™ for DB2 for z/OS
 generate SQL for each RI-related update by specifying Y in the Related Updates Options on the DML Activity Report Options panel.
REDO SQL and History Table Updates
When a SYSTEM TIME temporal table is processed, the updates to that table trigger a DB2 update to an associated history table. By default, 
CA Log Analyzer™ for DB2 for z/OS
 does not generate SQL to redo the updates that DB2 makes to its history tables. 
CA Log Analyzer™ for DB2 for z/OS
 generates SQL to redo only the temporal table update. You can generate SQL for the DB2 history table updates by specifying Y in the Related Updates Options field on the DML Activity Report Options panel.
DELETE and UPDATE Statements in SQL
If the generated SQL includes DELETE or UPDATE statements, the tables must have a unique index to generate a WHERE clause. 
CA Log Analyzer™ for DB2 for z/OS
 uses the Index Search Order parmlib variable to select an index with which to generate the predicate. If a unique index does not exist, 
CA Log Analyzer™ for DB2 for z/OS
 still generates SQL; however, executing the SQL can produce unintended results.
LOB Columns and SQL Generation
When INSERT or UPDATE statements are applied to a table with LOB columns, the LOB column data can cause an SQL statement to exceed its 2-MB limit. If the limit is exceeded, 
CA Log Analyzer™ for DB2 for z/OS
 generates multiple statements to represent the activity from a single INSERT or UPDATE statement.
When multiple statements are generated, an INSERT statement transforms into an INSERT statement with one or more UPDATE statements after it. An UPDATE statement transforms into one or more UPDATE statements. The INSERT statement (or initial UPDATE statement) sets the value of the LOB columns with the maximum amount of data that fits in the statement, possibly resulting in truncated data. For an INSERT statement, the subsequent LOB columns are set to a zero-length string (‘’) in the values clause. In both cases, the remaining UPDATE statements concatenate strings to finish building the truncated LOB data and subsequent LOB data.
Generate UNDO or REDO SQL Statements
CA Log Analyzer™ for DB2 for z/OS
 can generate executable SQL statements to undo or redo the data changes recorded in the DB2 logs. The DML Activity function provides options to generate UNDO and REDO SQL statements. UNDO SQL statements let you recover from erroneous changes or inserts without having to recover the entire tablespace. REDO SQL statements let you apply changes from one table to another table, perhaps after a production cycle has concluded. You can update tables across subsystems.
Follow these steps:
  1. Select 
    (Process Log) from the 
    CA Log Analyzer™ for DB2 for z/OS
     Main Menu and press Enter.
    The Report Specification panel appears.
  2. Select DML Activity and press Enter.
    The DML Activity Report Options panel appears.
  3. Select the appropriate options. The following settings are required for SQL generation:
    • Output Format: 
      S
       (RedoSQL) or 
      U
       (UndoSQL).
    • Level of Detail: 
      D
       (Detail) or 
      I
       (Image copy). If the tables do not have DATA CAPTURE CHANGES enabled, we recommend specifying 
      I
      .
    If your tables include LOB columns, also specify 
    (Include) in the LOB Column Data field.
  4. Press PF3 (End) repeatedly until the Report Source Specification panel appears.
  5. Specify the log data to analyze by entering the starting and ending parameters under LOG PROCESSING RANGE and the data sources to use under SOURCE OPTIONS.
    To enter a log label name or a date/time expression that exceeds the field length in the panel, enter a slash (
    /
    ) in the field and press Enter. The expanded field pops up. Enter the value in the field and press PF3 (End).
    Press PF3 (End).
    The Output Specifications panel appears.
  6. Define the specifications for the generated output.
    Press PF3 (End).
    A second Output Specifications panel appears.
  7. (Optional) Enter a data set name for the LOB file. This step is necessary only when you process LOB columns.
    Press PF3 (End).
    The Report Submission panel appears.
  8. Submit the job using 
    one 
    of the following options:
    • O
       (online mode)
    • B
       (batch mode)
    The SQL statements are generated. We recommend that you edit the statements before using them. 
    CA Log Analyzer™ for DB2 for z/OS
     generates complete SQL statements whenever possible. However, it can create skeleton SQL due to incomplete log data or data for which the format cannot be determined.