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.