Space announcements

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Using UNDO SQL

Generate UNDO SQL to reverse the effects of a problem transaction.

For example, applying UNDO SQL can change INSERT statements to DELETE statements. When you generate UNDO SQL, you are also preparing transactions to be backed out in the correct order.

The default forms of output for UNDO SQL are a Backout Integrity report and an UNDO SQL output file. The report compares the set of changes that your filter specifies against subsequent updates. Use the Backout Integrity report to determine which updates will be invalidated if you apply the generated UNDO SQL. To generate a Backout Integrity report, Log Master extends the range of your log scan to the current time. This extended range often causes Log Master to read more DB2 log files than you expect.

For an example of this procedure, see Example 1: Backing out transactions with UNDO.

Log Master cannot generate UNDO SQL to reverse the effects of a mass delete action (similar to a DELETE statement with no WHERE clause or a TRUNCATE statement) within a segmented or universal table space. Because of the way that DB2 logs the mass delete, Log Master can generate REDO or MIGRATE SQL in this situation, but not UNDO SQL. Similarly, the log records that DB2 creates for a LOAD REPLACE LOG NO action also prevent Log Master from generating UNDO SQL.

Example 1: Backing out transactions with UNDO

This example shows the use of Log Master to generate UNDO SQL to back out problem transactions.

For this example, assume that you are the database administrator at your company. You have scheduled batch routines to run on the production DB2 subsystem every Saturday between 10:00 P.M. and 4:00 A.M. These runs include batch jobs to:

  • Terminate employees

  • Record employee sick and vacation days

  • Record salary increases

  • Record new employees

At 2:00 A.M., you are alerted to errors in the batch job that records sick and vacation days. All other batch jobs ran without error. You are scheduled to begin payroll processing. Because data for vacation and sick days does not affect payroll processing, you allow the processing to continue as scheduled.

In this example, you know the specific range of transactions to back out, and the time range in which the batch transactions took place. Therefore, you are ready to use Log Master to generate UNDO SQL.

The overall process for performing an UNDO procedure is as follows:

Best Practice

To maximize control of anomalies, BMC recommends that the table spaces involved remain unavailable to other applications while generating and executing the UNDO SQL.

  1. Identify and analyze problem transactions. (See Analysis of problem transactions).

  2. Generate UNDO SQL:

    1. Define a filter to select the correct batch job.

    2. Specify the start and end points for the log scan.

    3. Specify attributes for the Backout Integrity report and the SQL output file.

    4. Save all selections in a work ID.

    5. Generate and execute the JCL.

  3. Execute UNDO SQL.

    You have the option of executing the SQL statements in the same run that generates them. If you choose not to execute the UNDO SQL in the same run, you must build a High-speed Apply job to execute the SQL. For more information, see Running SQL.

To perform an UNDO procedure

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

  1. 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 UNDO SQL and press Enter.

    None

    Confirmation

    Press Enter to accept the default of Y.

    This step adds a Backout Integrity report as output.

    Generate UNDO SQL

    Select Specify Log Filter Criteria and press Enter.

    These steps define a filter to select the correct batch job; in this example, a filter where PLAN NAME = TIMEOFF.

    Selectable Fields

    Type 3 in Field and press Enter.

    Conditional Operators

    Type 1 and press Enter.

    Plan Name Value

    Type TIMEOFF, and then press F3 until you return to the Generate UNDO SQL panel.

  2. 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 UNDO SQL

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

    Specify the start point as one hour before the batch job was scheduled to run.

    Log Master sets the end point to CURRENT.

    Specifying the start and end points in this way safeguards against not scanning enough log to retrieve all of the bad transactions.

    Time Frame Specification

    Type 1 and press Enter.

    Define Start Point

    Type 2 in the Options field.

    In the Date field, type 2013-01-19.

    In the Time field, type 09.00.00.000000.

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

  3. Specify the destination and other attributes of the Backout Integrity report and the SQL output file by completing the following series of panels:

    On this panel

    Take this action

    Notes

    Generate UNDO 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, or edit the destination.

    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

    In the UNDO SQL section:

    If necessary, type E in the UNDO SQL field to edit the SQL Output and SQL Template data set attributes.

    If you specify N in the Execute SQL field, to execute the UNDO SQL, you must build and run a High-speed Apply job to execute the SQL.

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

    For more information, see

    Generate UNDO SQL

    Select Work ID Options, JCL Generation and press Enter.

    For more information, see

    Work ID File Menu

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

  4. 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 UNDO SQL

    Select Work ID Options, JCL Generation and press Enter.

    For more information, see

    Work ID File Menu

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


Was this page helpful? Yes No Submitting... Thank you

Comments