Tutorial: Using Change Data Capture With VSAM
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:
- Create a target table and perform an initial load.
- Create a DataMigrator direct load flow.
- Update the source table.
- Run the flow and view the results.
- Schedule the flow.
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.
-
Create a new data flow in an application directory,
like baseapp.
-
Select a synonym for a VSAM cluster as a source.
-
Select all the columns that are needed for the target.
-
Add a new target, by selecting a database type, entering
a name, and specifying one key.
-
Save the flow.
-
Run the flow.
-
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.
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.
-
Right-click on an application directory, click New and
click Direct Load Flow.
-
Drag the synonym for the VSAM log to the left side of
the flow.
-
Drag the synonym for the target to the right side of
the flow, as shown in the image below.
-
Right-click the target and click Properties.
The target properties window opens.
-
On the Load Type pull-down menu, click IUD Processing.
Close the properties window.
-
Right-click the target and click Target Transformations.
The Transformations window opens.
-
Click the Automap button to map
the source to the target columns. Click Ok.
-
Click Save and enter a name. Click Save again.
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.
-
Right-click an application directory, click New,
and then click Stored Procedure. A stored
procedure window opens.
-
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
-
Click the Run button to run the
stored procedure. The console log shows the number of rows affected
by each command.
x
Procedure: How to Run the CDC Flow and View the Results
-
With the CDC flow open, click the Run button
and click Submit.
-
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.
-
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.
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.
-
Click the Process Flow tab
to switch to the process flow view.
-
Drag the Schedule button into
the workflow and double-click it to open the Schedule Properties
window.
-
Select a Schedule type, Interval type, and Interval
value to run the flow.
-
Save the flow.