Using REDO SQL

In contrast to an UNDO operation (which creates SQL statements to back out problem transactions) a REDO operation creates SQL statements that you can use to reapply the valid transactions after a recovery action while omitting the problem transactions defined by your time frame and filter.

Because a REDO operation omits the problem transactions, when you perform recovery and then execute REDO SQL, you can restore the table spaces to a current state that does not include the problem transactions.

Related topic

Backing-out-problem-transactions

For example, if undesirable changes are made to a database, to resolve the problem, the database administrator (DBA) can perform the follows actions:

  • Use BMC AMI Log Master to generate REDO SQL, defining the undesirable changes with a time frame and filter.
  • Use a utility program to recover the database to a point in time before the undesirable changes.
  • Execute the REDO SQL and re-apply all of the changes after that point in time, except the undesirable changes.

The following figure shows how BMC AMI Log Master uses the time frame and filter to generate REDO SQL: 

GUID-2F4C7DA6-2C69-4298-B7A2-304438E6459F-low.png

When you generate REDO SQL statements, BMC AMI Log Master starts by selecting all of the log records within the table spaces defined (directly or indirectly) by your filter. To generate REDO SQL, your filter must refer to at least one specific Db2 object, such as a table space, table, or column.

BMC AMI Log Master also selects all of the log records within the period between the REDO recovery point and the current time. To generate REDO SQL, you must define a REDO recovery point. From this set of log records, BMC AMI Log Master excludes all of the log records that are selected by your filter, and included within your time frame.

Ensure that you generate REDO SQL before you recover the database. When you generate REDO SQL, BMC AMI Log Master extends the end point of the log scan to the current date and time, which causes BMC AMI Log Master to process more log records than you might expect.

For an example of this procedure, see Example 2: Backing out transactions with REDO.

Scenario using REDO SQL

This scenario describes an example of how you can use REDO SQL. In this case, assume that the following series of events occurred:

  1. An image copy was taken at point 1.
  2. At point 2, Plan A began applying transactions.
  3. From point 3 to point 4, Plan B applied transactions that were subsequently discovered to be problem transactions. The problem transactions affected table space XYZ.

 REDO example

GUID-01179DD9-4F30-4A6A-8518-336621FE5418-low.png

To use REDO SQL to correct the state of the table space

  1. Generate a work ID that contains REDO SQL as output.

  2. Specify point 1 as the recovery point as you generate the SQL.This point is the starting RBA/LRSN of the image copy to be restored. Because the problem transactions occurred between points 3 and 4, the FROM date or RBA/LRSN should be no later than point 3, and the TO date or RBA/LRSN should be no earlier than point 4. The selection criteria are WHERE TABLESPACE ='XYZ' AND PLAN NAME ='B'.

    The SQL generated by the REDO procedure includes all changes that occurred to table space XYZ from point 1 (the recovery point) to the current time, except changes caused by Plan B. Any changes caused by Plan B between points 3 and 4 are excluded. For a REDO procedure, the FROM date or RBA/LRSN and the TO date or RBA/LRSN specify the time range of the problem transactions which you do not want to reapply. In this example, the REDO SQL contains only those transactions applied to table space XYZ by Plan A. 

Example 2: Backing out transactions with REDO

This example illustrates using BMC AMI Log Master to generate REDO SQL that excludes problem transactions.

For this example, assume that you are the DBA at your company. You have scheduled batch routines to run on the production Db2 subsystem every Sunday between 12:00 A.M. and 4:00 A.M. On Monday, you are alerted to transaction errors on the database. You discover that a batch job was mistakenly run at the wrong time and that the job has added unnecessary tax to all of the invoice records for the southern region.

In this example, you know the specific range of transactions to back out and the time range in which the batch transactions took place. Because you want to restore the table spaces involved to a state before the point when the wrong batch job was run, you decide to perform a recover and a REDO procedure.

To perform this procedure, you must generate REDO SQL that excludes the problem transactions. Then, you recover the table spaces involved in the problem transactions to a point of consistency that is before the problem. Then, you execute the generated REDO SQL.

Generating REDO SQL follows roughly the same procedures as generating UNDO SQL with the exception that you must generate REDO SQL before you perform a recovery.

The overall process for performing a REDO procedure is as follows:

Important

Note the following information:

  • Table spaces are offline from Step 3 to Step 5.
  • To maximize control of anomalies, avoid having other users update the spaces during Step 7.
  1. Identify and analyze problem transactions (see Analysis-of-problem-transactions).
  2. Identify all of the table spaces involved, and, for those table spaces, locate a point of consistency that is before the problem transactions.
  3. Place table spaces in Utility (UT) status (or STOP for the BMC AMI Recover for Db2 product).
  4. Generate REDO SQL:
    1. Define a filter to select the correct batch job.
    2. Specify the start and end points for the log scan.
    3. Specify the recovery point.
    4. Specify attributes for the Backout Integrity report and the SQL output file.
    5. Save all selections in a work ID.
    6. Generate and execute the JCL.
  5. Recover table spaces and related indexes to the previous point in time.
  6. Place table spaces in read/write (RW) status.
  7. Execute REDO SQL.

To perform a REDO procedure

For descriptions of the options on the panels referenced in this example, press F1 to access the online Help.

  1. Identify a point of recovery that exists before the point in time when the problem occurred.

  2. Define a filter to select the correct batch job by completing the following series of panels:

    On this panel

    Take this action

    Notes

    Main Menu

    Select Generate REDO SQL and press Enter.

    None

    Confirmation

    Press Enter to accept the default of Y.

    This step adds a Backout Integrity report as output.

    Generate REDO SQL

    Select Specify Log Filter Criteria and press Enter.

    The filter must qualify the table spaces involved in the recovery. For example, you may identify a particular plan in the problem transactions filter, but also include a list of table spaces, tables, or databases to give BMC AMI Log Master the qualification required to capture all transactions that must be preserved.

    For REDO SQL, BMC AMI Log Master selects log records that do not satisfy the filter.

  3.  Specify the start and end points for the log scan by completing the following series of panels:

    On this panel

    Take this action

    Notes

    Generate REDO SQL

    Select Specify Time Frame and/or Input Source and press Enter.

    Specify the start and end points to span the range of problem transactions.

    Time Frame Specification

    Type 1 and press Enter.

    Define Start Point

    Type 2 in the Options field.

    In the Date field, type 2022-01-20.

    In the Time field, type 22.00.00.000000.

    Press F3.

    Time Frame Specification

    Type 2 and press Enter.

    Modify End Point

    Type 2 in the Options field.

    In the Date field, type 2022-01-21.

    In the Time field, type 04.00.00.000000.

    Press F3 until you return to the Generate REDO SQL panel.

  4. Define the REDO recovery point, complete the following series of panels:

    On this panel

    Take this action

    Notes

    Generate REDO SQL

    Select Specify Run Time Options and press Enter.

    None

    Run Time Options

    Type E in the Specify REDO Recovery Point field and press Enter.

    By using the log mark information, you can select a log mark that meets your needs for use as a point of recovery

    REDO Recovery Point

    Type 2 in the Options field.

    In the Log Mark field, type the wild card pattern *.*.

    Press Enter.

    Mark List

    Type I beside the log mark that you want to display information about, and then press Enter.

    Type S beside the log mark that you want to use as the recovery point, and then press Enter.

    Press F3 until you return to the Generate REDO SQL panel.

  5.  To specify the destination and other attributes of the Backout Integrity report and the SQL output file, complete the following series of panels:

    On this panel

    Take this action

    Notes

    Generate REDO SQL

    Select Define Report and File Outputs and press Enter.

    None

    Report and File Outputs

    Type E in the ACT column beside the Backout Integrity report, and then press Enter.

    None

    Report Output

    Accept the default report destination and attributes, or edit them.

    Press F3 until you return to the Report and File Outputs panel.

    For more information, see Defining-a-default-report.

    Report and File Outputs

    Type E in the ACT column beside SQL File, and then press Enter.

    None

    SQL Output

    Type E in the REDO SQL field and press Enter.

    REDO SQL Output

    Type 1 and press Enter.

    Output Dataset Information

    Define and set attributes for the SQL output data set, as needed.

    Press F3 until you return to the REDO SQL Output panel.

    REDO SQL Output

    Type 2 and press Enter.

    Output Dataset Information

    Define and set attributes for the SQL output data set, as needed.

    Press F3 until you return to the Generate REDO SQL panel.

  6. Save your selections in a work ID and generate the JCL by completing the following series of panels:

    On this panel

    Take this action

    Notes

    Generate REDO SQL

    Select Work ID Options, JCL Generation, and press Enter.

    Work ID File Menu

    Save your selections in a work ID, and then generate JCL for batch submittal.

  7. Perform recovery of the selected table spaces.
  8. After you complete the recovery procedures, execute the REDO SQL.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*