Example 3: Defining and running ad hoc migration jobs

For this example, assume that the auditing department has asked you to set up a table that contains data for a particular set of company invoices. The auditors want to report on new invoices for government sales for the past week.

To meet this request, you define a new table similar to table DB2DBA.GOVERNMENT_SALES_INVOICES that resides on your production subsystem. You want to populate the table with only the information that the auditors need. Therefore, you decide to generate MIGRATE SQL for only specific columns in the new table.

The following table provides a high-level task list for this example:

Task	References
Define a filter that selects specific transactions for government sales, and that selects only insert actions (new invoices).	Filter definitions Defining a filter in a log scan step
Define a time frame that starts from a specific date and time and ends at a specific date and time.	Time frame definitions Defining a time frame
Modify the output options for the default Summary report and SQL file (as needed).	SQL output files Defining a default report
Define MIGRATE SQL to include only certain columns for a specified table.
Save your selections in a work ID.	Overview of work IDs
Generate JCL and save it for batch submittal.	Creating a batch job from a work ID
Run the ongoing migration job.	Running the ad hoc migration job

To define the filter and time frame

Define the filter as instructed in To define a filter that selects specific table space transaction, and use the Structured or Free Form filter interface to define search criteria.
The filter for this example selects records for a specific object, the DB2DBA.GOVERNMENT_SALES_INVOICES table.

To define the time frame, complete the steps in To define a time frame for ongoing migration, and type Y in the Ongoing Process field.

For this example, define start and end points with appropriate date and time values. The following figure displays the values for this example:

 =========================== Time Frame Specification ============ DATA UPDATED
 Command ===> _________________________________________________________________
                                                                     SSID: DBAN
 Work ID . . : NCH2.$$WORKID0002
 Description : NCH2 2013-01-18 16.02.25 MIGRATE
                                                                                
 Start of Log Scan. . . . . : 2011-03-18 00.00.00.000000
 End of Log Scan. . . . . . : 2011-03-18 13.00.00.000000
 Log File Source. . . . . . : Subsystem BSDS
                                                                                
 Options . . .  _  1. Modify Start Point
                   2. Modify End Point
                   3. Reset Time Frame using Log File Selections
                                                                                
 Ongoing Process. . . . . . . Y      (Y=Yes, N=No)

To modify the output options

On the Generate MIGRATE SQL panel, select Define Report and File Outputs and press Enter.
Log Master displays the Report and File Outputs panel. A Summary report and MIGRATE SQL file are the default output for migrating data changes.
To edit the MIGRATE SQL data set, type E in the ACT column and press Enter.
Log Master displays the SQL Output panel.

Perform the following actions:

Specify MIGRATE SQL data set attributes for the SQL output data set and SQL template data set.

To specify column inclusion, type E in the Create Include/Exclude Columns field and press Enter.

Log Master displays the Column Include/Exclude Table List panel.

============================== SQL Output =============================
C .---------------------------------------------------------------------. ___
U |                 Column Include/Exclude Table List       Line 1 of 2 | DBAN
  |                                                                     |
  | Maintain the list. Then press Exit.                                 |
  |                                                                     |
  | Enter an action code. Then press Enter.                             |
  | C=Search Catalog  I=Insert  D=Delete  R=Repeat  E=Edit Columns      |
R | ACT INC/EXC OWNER     TABLE               COL ENTRIES               |
  | E   I       DB2DBA    GOVERNMENT_SALES>>  000                       |
  | ********************* End Of List **********************            |

To add a column to the list, type E in the ACT column, and type I in the INC/EXC column.
In the OWNER and TABLE columns, specify the table that contains the specific columns that you want to include in the output.
To enter the table name GOVERNMENT_SALES_INVOICES, press F4 in the TABLE column.
For more information, see Displaying long Db2 object names. The "Column Include/Exclude Table List panel" figure (see above) displays the panel after you specify the table. No columns have been included in, or excluded from, table DB2DBA.GOVERNMENT_SALES_INVOICES at this point.
Press Enter.
Log Master displays the Column Include List panel, as displayed in the following figure, "Column Include List panel."

Type the names of the columns that you want to include in the generated SQL Output data set, as displayed in the following figure, and then press F3 until you return to the Generate MIGRATE SQL panel.

============================== SQL Output ==============================
C .----------------------------------------------------------------------. ___
  | =========== Column Include/Exclude Table List ====================== | DBAN
U |   .-----------------------------------------------------------.      |
  | M | ============= Column Include List ======      Line 1 of 4 |      |
R |   |                                                           |      |
  | E | Table Name. . .: DB2DBA.GOVERNMENT_SALES>>                |      |
M | C |                                                           |      |
  | A | Maintain the list then press Exit:                        |      |
  | E |                                                           |      |
I | * | Enter an action code then press Enter:                    |      |
C |   | C=Search Catalog  I=Insert  D=Delete  R=Repeat            |      |
U |   | ACT  COLUMN                                               |      |
  |   | _    DEPT                                                 |      |
G |   | _    GOVT_INVOICE_NUM>>                                   |      |
  |   | _    SALESMAN                                             |      | mns,
  |   | _    INVOICE_TOTAL                                        |      |
  |   | ***** End Of List ******                                  |      |
C |   |                                                           |      |
  | F | F1=Help F3=Exit F4=Zoom F7=Forward F8=Backward F12=Cancel |      |
  '-- '-----------------------------------------------------------' -----'

To save the selections in a work ID

To save your selections under a work ID, complete the steps in To save the selections in a work ID.

To generate and save the JCL

To generate JCL for batch submittal, complete the steps in To generate and save the JCL.

Running the ad hoc migration job

To migrate the data that the auditors requested, apply the SQL that you generated on the production subsystem to the table that you created for the auditors.