SQL output files

• UNDO SQL to reverse the effects of problem transactions.

• REDO SQL to reapply a set of changes (usually after a database recovery), omitting the subset of changes specified by the filter and the time frame. When you generate REDO SQL, be aware of the following points:

  • You must define a recovery point.

  • Your filter must refer to at least one specific Db2 object, such as a table name or a column name. This action ensures that you define (either directly or indirectly) a set of table spaces.

  • You cannot execute REDO SQL in the same run that generates the REDO SQL statements.

    Best practice

    We strongly recommend that you generate REDO SQL before you perform the recovery action.

• MIGRATE SQL to move data to shadow tables, or migrate specific updates to another system for test or audit.

After the SQL output file exists and is cataloged, to view the contents of the SQL output file or to view information about the SQL output file, from the Main Menu, select Previously Created Outputs.

The following figure shows a sample of the information displayed for an SQL output data set. 

                                SQL Dataset List                               
.---------------------------------------------------------------------------------------.
|============================SQL Dataset Information==================================  |
|                                                                              			|
| Dataset . . . .  . : DB2DBA.OM701.SQL                                        			|
|    Status . . .  . : Cataloged                                              			|
| SQL Type  . . .  . : MIGRATE                                                 			|
| Create Timestamp : 2013-01-18 11.14.38                                       			|
| SQL Dataset Statistics                                                       			|
|   Insert Count . . : 329700                           Trigger . . : 0        			|
|   Update Count . . : 30600       RI . . : 0           Trigger . . : 0        			|
|   Delete Count . . : 29700       RI . . : 0           Trigger . . : 0        			|
|   Where Clause Mode: Primary Key                                             			|
|   Date Format  . . : ISO                                                     			|
| Creation Statistics                                                          			|
|   Work ID/Run Num  : DB2DBA.OM701LLOGSQL          /  2                       			|
|   Step Number  . . : 1                                                       			|
|   Job Name/Num . . : DB2DBA    /  3304                                       			|
|                                                                              			|
|   F1=Help   F12=Cancel                                                       			|
.----------------------------------------------------------------------------------------

SQL generation

Log Master provides an SQL generator, with exclusive features, that generates SQL statements that undo or redo specific transactions. WHERE clauses are intelligently generated based on available index information. You can control whether updates resulting from referential integrity are a part of the correction process.

When executing generated SQL, the product accommodates and automatically reports any errors (SQL codes) that it encounters. The product can report specific errors, and then bypass them to continue processing.  

Table and column name translation during SQL generation

Log Master enables you to change the names of tables or columns during SQL generation. This feature provides a way to generate SQL for migrating changes to similar objects on other DBMS platforms, or to other Db2 systems with different naming conventions. You can choose to translate names during SQL execution or SQL generation:

• To translate names during SQL execution, use High-speed Apply configuration parameters. For an example, see Example 1: Using the online interface to generate High-speed Apply JCL.

• To translate names during SQL generation, you must edit your JCL to add an SQLXLAT DD statement to the job or job step that generates the SQL. The DD statement defines either a data set containing translation specifications, or contains specifications 'in stream' in the JCL. Specify how to translate table and column names by using the following format:

   TABLE tbowner.tbname -> newowner.newname 
  COLUMN colname -> newname

The COLUMN lines are always associated with the preceding TABLE line. The table names may contain an asterisk (*) to indicate a match on all possibilities.

The following figure shows an example of an in stream SQXLXLAT DD statement: 

//SQLXLAT DD *
TABLE USER1.PAYROLL -> USER2.PYRLL
COLUMN ATT1 -> NEWCOL1
TABLE MYUSER.* -> NEWUSER.*
TABLE X.Y -> X.Y
COLUMN UNITCOST -> OURCOST
COLUMN SUPPLIER -> CONTRACTOR
/*

In this example, the product would take the following actions:

• Generate SQL with the name USER2.PYRLL instead of USER1.PAYROLL

• Convert references to column ATT1 to NEWCOL1

• Generate all of the tables owned by MYUSER using the owner name of NEWUSER

• Convert references to table X.Y columns UNITCOST and SUPPLIER to OURCOST and CONTRACTOR, respectively. However, the product would not translate the name of table X.Y.

SQL output and the SQL template

To execute SQL output with the High-speed Apply Engine that is distributed with Log Master, specify the following forms of output:

• SQL output

• SQL template

The SQL template file contains descriptions of all distinct statement types contained in the SQL output. The template file is optional:

• If you execute the generated SQL with High-speed Apply, BMC recommends that you create the template file.

• If you execute the SQL with a program other than High-speed Apply, you do not need the SQL template file.

When you allocate space for these data sets, remember that the SQL output data set must accommodate the total number of SQL statements, while the SQL template data set must accommodate the total number of distinct statement types.  

SQL output data set options

The following figure shows the SQL Output panel, which you access from the Output Options panel. Define an output SQL file by specifying values on this panel. For descriptions of the options on this panel, press F1 to access the online Help. 

SQL Output
Command ===>
UNDO SQL . . . . . . . . . . . . . .   (E=Edit)             SSID : DXW

   Include RI Recs . . . . . . . . . Y    (Y=Yes, N=No)
   Include DDL . . . . . . . . . . . N    (Y=Yes, N=No)
   Include Trigger Recs. . . . . . . Y    (Y=Yes, N=No)
   Include Rollback. . . . . . . . . N    (Y=Yes, O=Only, N=No)
   Include XML . . . . . . . . . . . N    (Y=Yes, N=No)
   Execute SQL . . . . . . . . . . . N    (Y=Yes, N=No)

MIGRATE SQL . . . . . . . . . . . .       (E=Edit)

   Include RI Recs . . . . . . . . . N    (Y=Yes, N=No)
   Include DDL . . . . . . . . . . . N    (Y=Yes, N=No)
   Include Trigger Recs. . . . . . . Y    (Y=Yes, N=No)
   Include Rollback. . . . . . . . . N    (Y=Yes, O=Only, N=No)
   Include LOBS. . . . . . . . . . . N    (Y=YES, N=No)
   Include XML . . . . . . . . . . . N    (Y=YES, N=No)

REDO SQL (used with PIT recovery). .      (E=Edit)

   Include RI Recs . . . . . . . . . N    (Y=Yes, N=No)
   Include DDL . . . . . . . . . . . N    (Y=Yes, N=No)
   Include Trigger Recs. . . . . . . Y    (Y=Yes, N=No)
   Include Rollback. . . . . . . . . N    (Y=Yes, O=Only, N=No)
   Include LOBS. . . . . . . . . . . N    (Y=YES, N=No)
   Include XML . . . . . . . . . . . N    (Y=YES, N=No)

SQL Options  . . . . . . . . . . . .      (E=Edit)
Create Include/Exclude Columns . . .      (E=Edit)

For more information about the topics in this section, see the following references: