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 DBb 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:
- Identify and analyze problem transactions. (See Analysis-of-problem-transactions).
- Generate UNDO SQL:
- Define a filter to select the correct batch job.
- Specify the start and end points for the log scan.
- Specify attributes for the Backout Integrity report and the SQL output file.
- Save all selections in a work ID.
- Generate and execute the JCL.
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.
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.
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.
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.
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.
Related topic