SQL type/output definition

Directs Log Master to generate REDO SQL, so that you can reapply a set of transactions after a recovery action, while omitting a set of 'problem' transactions defined by your Range definition and WHERE clause. For example, assume some changes have been made to a set of table spaces in error. You can

1. Define the problem changes with a Range definition and WHERE clause

2. Generate REDO SQL

3. Recover the set of table spaces to a point in time before the problem changes

4. Execute the REDO SQL and reapply all of the changes after that point in time, except the problem changes.

The following diagram shows how Log Master uses the Range definition and WHERE clause to generate REDO SQL.

For REDO SQL, Log Master starts by selecting all of the log records that are

• Within the table spaces defined (directly or indirectly) by your WHERE clause

  To generate REDO SQL, your WHERE clause must refer to at least one specific DB2 object, such as a table space, table, or column.

• Within the period between the REDO recovery point and the current time

  To generate REDO SQL, you must define a REDO recovery point. For more information, see REDO FROM.

  
  

From this set of log records, Log Master excludes all of the log records

• Defined by your WHERE clause

• Defined in your Range definition (the FROM and TO values)

BMC Software strongly recommends that you generate REDO SQL before you perform the recovery. You might be able to generate the SQL after the recovery by using the PROCESS PITS keyword of the OPTION statement, but this practice is not recommended. For more information, see PROCESS PITS.

Also remember that when you generate REDO SQL, Log Master extends the end point of the log scan to the current date and time. This action causes Log Master to process more log records than you might expect.

Specifies the characteristics for the output data set that contains the template information for the generated SQL. For more information, see LOGSCAN output definition.

The template file is optional, but BMC Software recommends that you create it if you intend to execute the generated SQL with Log Master (either by using an EXECSQL statement or by using a separate job or job step to run the High-speed Apply Engine). The template file

• Improves performance when the High-speed Apply executes the generated SQL

• Contains one entry for each distinct statement type in the generated SQL

• Provides information that High-speed Apply uses to generate a static plan for the SQL statements

• Can be used with a version of the High-speed Apply Engine against a DB2 for LUW target on distributed systems platforms (download both the SQL file and the template file, edit the first line of the SQL file, replace the previous name with the fully qualified path name of the template file, and execute the SQL file with High-speed Apply)

For more information about the High-speed Apply Engine that is distributed with Log Master, see the Log Master for DB2 documentation .

Indicates whether Log Master is to include log records that reflect changes resulting from referential integrity (RI) constraints when generating SQL output. This keyword does not cause Log Master to add tables to the filter; it only determines whether Log Master includes RI changes (in addition to normal changes) for the tables that are selected by the filter. To include a complete set of RI changes, you must specify all of the tables involved in the RI constraints in your WHERE clause or filter.

The default value for this keyword depends on the type of SQL that you are generating:

This keyword can be important when you migrate data from a source database environment to a target environment. During migration, your decision to include RI changes depends on how the RI constraints are defined in the target environment, as follows:

Determines whether Log Master includes information from compensated log records in the output SQL file. Use the default value of NO for normal processing. In this context, compensated log records are the log records of database changes that DB2 subsequently 'compensates for' or 'reverses' (for example, log records of changes that are later reversed by a ROLLBACK statement). Depending on the value of this keyword, Log Master includes or excludes compensated log records. For more information, see INCLUDE ROLLBACK in LOGSCAN report definition.

If you do not specify INCLUDE ROLLBACK, Log Master ignores compensated log records. If you specify INCLUDE ROLLBACK without specifying YES or NO, the default value is YES (Log Master includes both compensated and noncompensated log records).

Determines whether Log Master includes the data from large object (LOB) columns in the output SQL file. When the value of this keyword is YES, Log Master writes temporary LOB VSAM files during processing, but does not save them permanently to disk unless the log scan includes an output logical log file.

Log Master generates one LOB VSAM file for each LOB column (or each partition of a LOB column) that occurs in the selected log records. Log Master allocates an additional LOB VSAM file only when it has filled the initial data set and all possible extents, but more LOB column data remains to be written. Depending on your environment and your LOB data, the additional disk I/O required to process LOB VSAM files can slow Log Master performance.

Log Master includes the LOB data as part of the generated SQL statements (by using character representation of hexadecimal values, for example x'407AC2' ). If the length of a statement exceeds the limit imposed by your version of DB2, Log Master issues a warning message, omits the statement, and continues processing.

Log Master cannot include LOB column data in the UNDO SQL that is generated for delete and some insert actions. For more information, see Large volume columns and SQL.

For more information about how Log Master processes LOB columns and data, see the Log Master for DB2 documentation .

If you do not specify INCLUDE LOBS, Log Master does not include LOB data. If you specify INCLUDE LOBS without specifying YES, NO, or INLINE, the default value is YES (Log Master includes LOB data).

Indicates whether Log Master includes changes to history tables that are associated with system-maintained temporal tables in the generated SQL. This keyword does not cause Log Master to search additional tables for changes; it only determines whether Log Master includes the history changes that exist in the log records that are selected by your log scan.

DB2 does not have a flag in the history log record to indicate whether the record was logged as a result of activity performed on the associated system-maintained table, or as a result of DML activities performed on the history table by a user. Therefore, Log Master includes all history records in the generated SQL when the value of this keyword is YES, and the history tables are explicitly or implicitly specified in the WHERE clause or filter.

When you generate MIGRATE or REDO SQL, the default value for this keyword is NO. When you generate UNDO SQL, the default value is YES.

Considerations for including history changes

When you work with system-maintained temporal tables, be aware of the following points:

• Because the SYSTEM_TIME start and end columns are defined as GENERATED ALWAYS, any SQL output that includes activities that were performed on the system-maintained table might not be executable. To exclude the GENERATED ALWAYS columns from the SQL output, use the EXCLUDE COLUMNS clause in the log scan statement. For more information, see LOGSCAN column include/exclude definition.

• When you generate MIGRATE SQL, if you include history changes, the SQL might not be executable, or might not keep the system-maintained and the history tables in sync. For instance, when applying the generated SQL, the versioning relationship might result in duplicate INSERTS to the history table. In addition, DB2 generates new timestamp values for the GENERATED ALWAYS columns when the migrated SQL is applied.

• When you perform an UNDO or drop recovery, unpredictable results can occur if you attempt to recover the base table and its history table as a set, and also attempt to maintain history information.

• The INCLUDE HISTORY keyword can be important when you migrate data from a source database environment to a target environment. During migration, your decision to include history table changes depends on how the tables are defined in the target environment:

  • If the history tables are the same in the target environment as in the source environment, you probably do not want to include the additional history changes because the constraints can cause duplicate changes. Set INCLUDE HISTORY to NO.

  • If the history tables in the target environment are not set up the same as the source environment, you might want to include the history tables. If so, include all of the affected tables in the WHERE clause or filter, and set INCLUDE HISTORY to YES.

    Note

    You might need to disable the versioning relationship between the target system-maintained and history tables. Doing so ensures that, when updates are applied to the base table, the history table does not record the wrong history information. After the migration is complete, enable versioning again.

• The INCLUDE HISTORY keyword is not valid for report and logical log outputs; however, if the filter includes a history table, Log Master includes all history records in the output.