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). | |
Define a time frame that starts from a specific date and time and ends at a specific date and time. | |
Modify the output options for the default Summary report and SQL file (as needed). | |
Define MIGRATE SQL to include only certain columns for a specified table. | |
Save your selections in a work ID. | |
Generate JCL and save it for batch submittal. | |
Run the ongoing 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.
Related topic