Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Log Master for Db2 13.1.

Example 4: Building and applying a load file to migrate historical data

For this example, assume that the auditors for your company have requested that you create a table that shows the changes to all payroll records for employees that work at your largest office.

The original changes affect a table named PAYROLL in your production database. On the receiving subsystem, you define a new table similar to a table named PAYROLL that resides on your production system. However, the new table has a primary key that was defined as nonunique.

Because you will be adding data directly into the new historical table, you create a load file as your Log Master output. The load file contains separate before and after images for each record. You use the load file to populate the new table, and to audit changes in data. You must add columns to the new table to accommodate the informational columns of data that Log Master adds to the records in the output load file.

Auditors can query historical data from the new table. For example, if one of the columns in the table is an employee number, they can request all log records where that column is changed, and verify all insert, update, delete, and exchange actions that affected employee numbers. They can select log records relating to a specific employee or group of employees.

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

Task

References

Define a filter that selects transactions for employees at the site requested.

Define a time frame.

Define data set attributes for the default report, and optionally, specify an additional Audit report.

Define a load output data set that includes two separate before and after images of the selected transactions.

Warning

Important

In this example, do not generate a MIGRATE SQL output file.

Save your selections in a work ID.

Generate JCL and save it for batch submittal.

Apply the load file.

To define the filter and time frame

  1. To define the filter, complete the steps in To define a filter that selects specific table space transaction, and use the Structured or Free Form filter interface to define search criteria, as shown in the following figure:

     FILTERS ==================== Structured Filter Maintenance ======= Line 1 of 2
     Command ===> ________________________________________________ Scroll ===> HALF
                                                                         SSID: DBAN
     Work ID . . : NCH2.$$WORKID0003
     Description : NCH2 2013-01-18 13.44.05 MIGRATE
     Updated Version:V11.01.00
                                                                                   
     Type one or more action codes then press Enter:
          C=Catalog Search  I=Insert  D=Delete  E=Edit  O=Logical OR                   
     ACT       SELECTABLE FIELD    OPERATOR  VALUE
     _         NETPAY              CHANGED
     _    AND  SITE                =         LARGE_OFFICE
    ********************************* End Of List *********************************

    The filter for this example selects records with any changes to the ABC.PAYROLL.NETPAY column. (Log Master does not display the fully qualified column name.)

  2. Press F3 to return to the Generate MIGRATE SQL panel.
  3. To define the time frame, complete the steps in To define a time frame for ongoing migration.For this example, specify a time frame range for the day that you run the payroll batch job.
  4. Press F3 to return to the Generate MIGRATE SQL panel.

To modify the output options

  1. On the Generate MIGRATE SQL panel, select Define Report and File Outputs, and then press Enter.Log Master displays the Report and File Outputs panel.
  2. Type I in the ACT column and press Enter.Log Master displays the Output Options panel.

  3. Select Load File Outputs and press Enter.Log Master displays the Load Output panel. 

                                       Load Output                                  
     Command ===>                                                                   
                                                                        SSID : DJV  
                                                                        More:     +
     Load File . . . . . . . . . . . . .    (E=Edit)                                
         FILE: &SYSUID..D&DATE..T&TIME..LOAD.DATA                                   
     Load Control File . . . . . . . . .    (E=Edit)                                
         FILE: &SYSUID..D&DATE..T&TIME..LOAD.CNTL                                   
     Record Format . . . . . . . . . . . V  (V=VB, S=VBS)                           
     Spanned . . . . . . . . . . . . . . N  (N=No, Y=Yes  LOB/XML for LOADPLUS)     
     Format of Load File Output. . . . . L  (L=Log Master, U=Unload Plus,           
                                             C=CSV, S=Standard Format, E=EDIT CSV)  
     Separate Dataset per Table  . . . . N  (Y=Yes, N=No)                           
     Generate Empty Files  . . . . . . . N  (Y=Yes, N=No)                           
     Expand VARCHAR columns  . . . . . . Y  (Y=Yes, N=No)                           
     Include INFO Columns with Data. . . N  (A=All, U=Urid Only, N=Urid None,       
                                             E=Edit, S=User Prev Specified)         
     Separate Unit of Recovery Info  . . N  (Y=Yes, N=No)                           
     Update Record Options . . . . . . . S  (A=After Image Only,B=Before Image Only,
                                             S=Both Separately, T=Both Together)    
     Create Include/Exclude Columns  . .    (E=Edit)                                
     Sort  . . . . . . . . . . . . . . . Y  (Y=Yes, N=No)                           
     Include Rollback. . . . . . . . . . N  (Y=Yes, O=Only, N=No)                   
     Include XML . . . . . . . . . . . . Y (Y=Yes, N=No, T=Template, E=Edit)                 
     Include LOBS. . . . . . . . . . . . N (Y=Yes, N=No, I=Inline, T=Template, E=Edit)
     PeriodOverride . . N IgnoreFields . N (Y=Yes, N=No)
     Delimit Column Name . . . . . . . . O  (O=Only, N=None, A=ALL)
  4. Type E in the Load File and Load Control File fields and press Enter.Log Master displays the Output Dataset Information panel.
  5. Specify the name and attributes for each data set, and then press F3 to return to the Load Output panel.

  6. To specify that the load file is to contain two separate sets of before and after log record images, type S (for Both Images Separately) in the Update Record Options field, and then press F3 to return to the Report and File Outputs panel. 

                                     Report and File Outputs
    Command ===>                                                  Scroll ===> CSR
                                                                       SSID : DBAM
    Work ID . . : NCH2.$WORKID0003                                              
    Description : NCH2 2013-01-18 13.44.05 MIGRATE                                
                                                                                 
    Enter an action code. Then press Enter.                                       
      I=Insert  D=Delete  E=Edit                                                  
    ACT TYPE         DESCRIPTION                                                
    _    Report      SUMMARY                                                    
                     Sysout: Class(*)                                           
    _    Load File   Data File: &SYSUID..D&DATE..T&TIME..LOAD.DATA              
                     Cntl File: &SYSUID..D&DATE..T&TIME..LOAD.CNTL              
    _    SQL File    Terse                                                      
                     Migrate File: &SYSUID..D&DATE..T&TIME..MIGRATE.SQL       
                     Include Trigger                                            
                     Include ROLLBACK No                                        
    ******************************** End of List *******************************
  7. To delete the default MIGRATE SQL output file, type D in the ACT column beside the SQL file and press Enter.
  8. (optional) To add an Audit report, type I in the ACT column.For more information, see Audit-report.
  9. Press F3 until you return to the Generate MIGRATE SQL panel.

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.

Applying the load file

This topic explains how to apply the load file.

To apply the load file

Take the load file generated on the production subsystem and load it into the new table that you created for the auditors.

To change the names of the columns

Edit the load control file by selecting Previously Created Outputs on the Main Menu.

Related topic

Using BMC AMI Log Master for data migration


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Log Master for Db2 12.1