Generating SQL

SQL for UNDO/REDO purposes can be generated from the details level report. It will be included as a JCL step only if requested specifically from the details level report panel, or from the 'Generate Details' panel if generating a combination general and detail level report.

Each SQL statement is numbered within comments, and the original associated URID and date/time fields are included in the comments as well. This SQL can be used as input to Db2's SPUFI, or cut and paste into other applications (for example, QMF) for execution. The SQL is written to the file associated with the DD named SQLOUT in the last step of the detail report JCL.

REDO SQL is, by definition, SQL that will redo an original action. In other words, REDO SQL re-applies changes made by an original action. UNDO SQL is, by definition, SQL that will undo an original action. In other words, UNDO SQL backs out changes made by an original action.

For REDO SQL, the SQL generated is identical to the original SQL. That is, for REDO SQL, an original UPDATE, INSERT, or DELETE action will be generated as an identical UPDATE, INSERT, or DELETE action, respectively.

UNDO SQL is slightly more complex in that the SQL generated is the opposite of the original SQL. That is, for UNDO SQL, an original UPDATE, INSERT, or DELETE action will be generated as an UPDATE, DELETE, or INSERT, respectively. For example, to undo an INSERTed row, you need to DELETE the row. To undo a DELETEd row, you need to INSERT the row. To undo an UPDATEd row, you also need to UPDATE the row, but the originally changed values need to be backed out.

Both REDO and UNDO SQL require that your affected tables are in the proper state in order to run successfully. Running either type of SQL requires that your tables looked exactly as they did at the time of their original execution. That is, the table data must be identical.

For REDO SQL, this means that some type of recovery has been performed. For example, the RECOVER utility may have been run to restore your data to a previous image copy. The REDO SQL could then be used to re-apply changes to a certain point-in-time.

For UNDO SQL, this means (most likely) that the data must not have changed at all since the reported changes here. That is, the actions reported on by this run of Log Analysis Tool are the most recent actions against the affected tables. Therefore, the table data is already in its proper state, without any needed modifications.

If the preceding conditions are not fully met, execution of the generated SQL will likely fail. Keep the following in mind as a general rule: it is most likely that you will require UNDO SQL generation for log activity that occurred very recently, and REDO SQL for that which occurred longer ago. It makes sense that you would want to UNDO actions that happened recently in that the current state of your tables involved likely matches the post-change row images for the log activity. Similarly, REDO SQL would be desired for events that occurred longer ago because it will likely be easier to recover to a point-in-time prior to the log activity and apply the REDO SQL.

The general idea here is that you need a point of consistency so that the WHERE clauses for both UNDO and REDO will be met when this SQL is run. The fastest way to get to that point should be the method chosen.

You should analyze each SQL statement and the order of execution of these statements to fully understand how running this SQL affects your Db2 tables. Modification of the SQL may be desired prior to execution. It is unlikely you'd need to modify the contents of any given SQL statement, but the omission/inclusion of any given SQL statement should be scrutinized to attain the desired results, especially on those tables where unique keys do not exist.

WHERE clauses are built using unique index keys, unless otherwise directed (the user may override this behavior using detail report specifications). If there exists more than one unique index on the table, the product will use only one set of unique index keys. It determines which index will provide a path of 'least resistance' for SQL execution. For example, suppose TABLEX has two unique set of keys; the first on column EMP-ID, an INTEGER field, and the other index on EMP-NO and EMP-ROWID, defined as INTEGER and ROWID GENERATED ALWAYS, respectively. The product will only use EMP-ID in the WHERE clause, to prevent possible problems with the GENERATED ALWAYS aspect of the other key. If only a single unique index is available, that set of keys must be used regardless of any column attributes. If no unique index is available, all columns must be used in the WHERE clause. It is strongly not recommended to generate SQL for any such table without a unique index.

A COMMIT statement will be added every x number of SQL statements, depending on the value assigned to COMMIT SCOPE field on the details level report panel. For example, if x=100, after 100 SQL statements, a COMMIT is performed. If x=000, no COMMITs are added to the SQL.

Rolled back units of recovery are never included in SQL generation. Since all the work was already backed out, there would be nothing to UNDO or REDO.

Uncommitted units of work will be included in all SQL if you requested its inclusion in the general or detail reports. Execution of such SQL requires extra consideration and evaluation if requested as applying such could leave your data in an inconsistent state. All SQL included as part of an uncommitted unit of work can be identified by doing a find on 'UURID' (meaning 'uncommitted unit of recovery ID') located in the commented header of each SQL statement.

In cases of very long column data (> 254 bytes), it is necessary to generate multiple SQL statements from one action. For example, assume a row with a long varchar column is deleted, and you wish to UNDO this action. This will result in an INSERT statement which will recreate the row. Since only 254 bytes can be included in the INSERT statement for the long varchar column, it is initially set to a subset of its true data value. Immediately after the INSERT, subsequent UPDATE statement(s) will concatenate the long varchar column with its current contents and the remaining data. As many UPDATE statements are generated as needed to completely reproduce the columns value.

For similar situations occurring with an original UPDATE statement, the product will attempt to minimize these extra SQL statements by determining if a long data column has actually been modified. If the long data has not changed, the column itself will be omitted from the SQL statement avoiding the need for these extra SQL statements.

Some log records are generated due to referential integrity definitions. These log records are no different from any other, but are considered the results of referential constraints (see your Db2 manuals for full descriptions of referential integrity/constraints). The SQL generated from these referential constraints must be treated differently when applying UNDO or REDO SQL.

No user action is necessary to handle the issues of referential integrity, the product automatically takes care of it. The explanation here is just to aid the user in understanding the proper handling of this type of SQL. Basically, for REDO purposes, all referential-constraint-type records will be omitted. That is, only the originating SQL statement will be included, and this will activate all other referential-constraint-type activity (exactly as it originally occurred). For UNDO purposes, the order of the SQL is slightly modified to ensure any parent key rows are restored before issuing any SQL against dependent key rows.

Referential constraints are noted in the generated SQL output as being either UPD-RI or DEL-RI, meaning an UPDATE or a DELETE due to referential integrity definitions, respectively. INSERTs cannot be the result of referential constraints.

As previously mentioned, for REDO purposes, no records marked UPD-RI or DEL-RI will be included in the SQL. This is because the originating action (the original SQL which caused the referential constraint SQL) is to be re-done, which will once again activate those referential constraints, so no extra work is needed. For UNDO purposes, those records marked as UPD-RI and DEL-RI will be included, but only after the originating SQL is undone. This is necessary because referential integrity would otherwise prohibit the activated actions without the existence of the associated parental key values.

Log records are also generated due to triggers firing. These log records are no different than any other, but are considered the results of triggered SQL statements (see your Db2 manuals for full descriptions of triggers). The SQL generated from these triggers firing must be treated differently when applying UNDO or REDO SQL.

Some user action is necessary to handle the issues of triggers firing, as the product cannot interpret the logic behind the triggers or if there is a trigger in place that will act as the opposite of the originating trigger. The explanation here is to aid the user in understanding the proper handling of this type of SQL. Db2 allows for two different types of triggers. The user will need to know which type is present in the tables that REDO / UNDO SQL is being generated for. If Traditional Triggers are present and REDO SQL is requested then the user will probably want to choose X to exclude the actions that originated from the trigger. This is because when the original statement is redone, the trigger will fire again doing all the necessary work. If Instead Of Triggers are present and REDO SQL is requested then the user will probably want to choose Y to include the statements that were caused by triggers because the original statement was against a view and Db2 transformed the original statement into the triggered statement(s). In this case the original statement will no longer be available. For UNDO purposes, the user has the option of excluding the triggered SQL statements or having them treated the same as the other SQL statements that are candidates for UNDO.

Triggered SQL statements are noted in the generated SQL output as being UPD-TR, INS-TR or DEL-TR, meaning an UPDATE, INSERT, or DELETE due to a trigger firing, respectively.

As previously mentioned, the user can opt to include or exclude the records marked UPD-TR, INS-TR, or DEL-TR from the generated SQL by choosing Y to include these actions or X to exclude these actions.

If impact report was requested at the detail report level, SQL is not included pertaining to any impact data. An impact report is for analyzing the impact of what occurred after your requested time range, not for generating extra SQL. If the SQL is desired after your requested time range, the proper method to handle that is to increase your time range, not via an impact report. See the tutorial topic on "Impact Report" for more information.