Tutorial: Using Change Data Capture With VSAM

How to:

This tutorial shows how to use Change Data Capture for a VSAM cluster. Prior to following this tutorial, synonyms should be created for both the VSAM cluster and the log for that cluster, as described in the previous topic. The source cluster used in this example is called filea.

This tutorial has the following procedures:

  1. Create a target table and perform an initial load.
  2. Create a DataMigrator direct load flow.
  3. Update the source table.
  4. Run the flow and view the results.
  5. Schedule the flow.

Top of page

x
Procedure: How to Create a New Target Table and Perform an Initial Load

The first procedure is to create a data flow that creates the target table and loads it with data from the current contents of the source cluster.

  1. Create a new data flow in an application directory, like baseapp.
  2. Select a synonym for a VSAM cluster as a source.
  3. Select all the columns that are needed for the target.
  4. Add a new target, by selecting a database type, entering a name, and specifying one key.
  5. Save the flow.
  6. Run the flow.
  7. Open the newly created target and click Sample Data to confirm that the data was loaded correctly.

    Note: Key column(s) are required for loading a table to uniquely identify each row so that IUD processing can be performed.


Top of page

x
Procedure: How to Create a DataMigrator Data Direct Load Flow

In this procedure, we will create a DataMigrator flow to copy data from the VSAM log table to the target table. We will use a direct load flow, as that ensures proper checkpoint file handling.

  1. Right-click on an application directory, click New and click Direct Load Flow.
  2. Drag the synonym for the VSAM log to the left side of the flow.
  3. Drag the synonym for the target to the right side of the flow, as shown in the image below.

  4. Right-click the target and click Properties. The target properties window opens.
  5. On the Load Type pull-down menu, click IUD Processing. Close the properties window.
  6. Right-click the target and click Target Transformations. The Transformations window opens.
  7. Click the Automap button to map the source to the target columns. Click Ok.
  8. Click Save and enter a name. Click Save again.

Top of page

x
Procedure: How to Update the Source Table

To test the Change Data Capture process, make changes to the source VSAM cluster. In this procedure, three rows are updated.

  1. Right-click an application directory, click New, and then click Stored Procedure. A stored procedure window opens.
  2. Enter the commands to update the VSAM cluster.

    Note that if the source data set is dynamically allocated, for further updates the data set must be subsequently freed.

    In this procedure, icdc is the subsystem name and qualified.dataset.name is the name of the VSAM structure.

    DYNAM ALLOC FI filea DA qualified.dataset.name SHR REU SUBSYS icdc 
    SQL
    UPDATE filea
        SET NAME = ' JOHN ' WHERE NUMB = ' 000102 ' 
    END  
    DYNAM FREE FI filea
  3. Click the Run button to run the stored procedure. The console log shows the number of rows affected by each command.

Top of page

x
Procedure: How to Run the CDC Flow and View the Results
  1. With the CDC flow open, click the Run button and click Submit.
  2. Click the View Last Log button. The log report opens.
    Note the statistics that show the number of rows inserted, updated, and deleted. Close the report.
  3. In the open flow, double-click the target table and select the Sample Data tab. In this window you can confirm that the changes were applied correctly.

Top of page

x
Procedure: How to Schedule the CDC Flow

The final step in setting up Change Data Capture processing is scheduling the flow so that it runs periodically and processes transactions.

  1. Click the Process Flow tab to switch to the process flow view.
  2. Drag the Schedule button into the workflow and double-click it to open the Schedule Properties window.
  3. Select a Schedule type, Interval type, and Interval value to run the flow.
  4. Save the flow.

iWay Software