Chapter 6: Generating SQL
This section contains the following topics:
SQL Generation (see page 115)
Generate UNDO or REDO SQL Statements (see page 117)
SQL Generation
CA Log Analyzer 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.
Notes:
■ The SQL statements generated by CA Log Analyzer provide a starting point for writing the SQL. CA Log Analyzer 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.
More information:
Load File Overview (see page 67)
Use of DATA CAPTURE CHANGES
Although CA Log Analyzer 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.
SQL Generation
116 User Guide
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 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.
Note: 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
CA Log Analyzer cannot generate UNDO SQL for data that was deleted from a
segmented tablespace using an unqualified DELETE. If a DELETE without a WHERE clause was issued against a table residing in a segmented tablespace, CA Log Analyzer
generates a partial statement with a warning message. We do not recommend attempting to modify this statement to make it usable. Instead, undo the unqualified DELETE by recovering the tablespace.
REDO SQL and Referential Integrity
By default, CA Log Analyzer 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 generates SQL to redo only the parent row deletion. You can have CA Log Analyzer generate SQL for each RI-related update by specifying Y in the Related Updates Options on the DML Activity Report Options panel.
More information:
Specify Related Updates Options (see page 65)
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 does not generate SQL to redo the updates that DB2 makes to its history tables. CA Log Analyzer 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.
Chapter 6: Generating SQL 117
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 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 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 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 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 1 (Process Log) from the CA Log Analyzer Main Menu and press Enter.
The Report Specification panel appears.
2. Select DML Activity and press Enter.
The DML Activity Report Options panel appears.
Generate UNDO or REDO SQL Statements
118 User Guide
3. Select the appropriate options (see page 45). 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 (see page 115), we recommend specifying I.
If your tables include LOB columns, also specify I (Include) in the LOB Column Data field.
Press PF3 (End).
The Report Specification panel appears.
4. Press PF3 (End) again.
The Report Source Specifications panel appears.
5. Specify the log data to analyze by entering the following information:
■ Starting and ending parameters under LOG PROCESSING RANGE
■ The data sources to use under SOURCE OPTIONS Press PF3 (End).
The Output Specifications panel appears.
6. Define the specifications for the generated output (see page 38).
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) (see page 39)
■ B (batch mode) (see page 41)
The SQL statements are generated. We recommend that you edit the statements before using them. CA Log Analyzer 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.
More information:
DML Activity Output Types (see page 45)