In my previous post
, I showed how federated database technology can make PostgreSQL tables appear as though they're DB2 tables to DB2 users. Naturally, one of the first follow-on questions I get about this is, if these look like DB2 tables, can they be used with DB2's built-in change data capture technology called SQL Replication
Good question. As always, I think the best answer is to give it a try. So, I did :) In this post, I'm going to show how I was able to replicate from DB2 to PostgreSQL.
Before we start, understand that PostgreSQL tables show up as 'logical' tables in a federated database. They are 'logical' in the sense that the physical tables remain in your PostgreSQL database while the federated database just maintains 'pointers' to the physical tables. Applications can issue DB2 SQL Selects, Inserts, Updates, and Deletes against these logical tables (called nicknames) and the federated database handles the rest.
How does this relate to SQL Replication? Well, with DB2, SQL Replication retrieves changed data for DB2 tables and applies it to target tables using DB2 SQL Inserts, Updates, and Deletes. That's a good match with the function provided by a federated database.
To get started, I needed one copy of InfoSphere Replication Server
. This is a heterogeneous data replication product from IBM that has IBM's federation technology integrated with it.
Once I had this, I went through the following steps:
2. Configure federated access to PostgreSQL.
3. Create SQL Replication subscriptions between DB2 and PostgreSQL.
4. Start the SQL Replication Capture and Apply programs.
5. Run applications against my source tables.
The remaining sections of this post talk about each of these and provide small scripts to help you reproduce my results as well as my conclusion.
I installed InfoSphere Replication Server 9.7 on the same system as my PostgreSQL server.
However, 9.7 doesn't automatically install the JDBC component ('wrapper') you need. Instead, you need to install Replication Server 9.7, then download and install the latest DB2 Universal Fix Pack
on top of Replication Server 9.7.
Next, find the "Relational Wrappers" installer in the DB2 Universal Fix Pack. This installer works with both Federation Server and Replication Server. Once you've launched this installer, select and install the "JDBC data source support". If you're not sure where to find this installer, look for a directory and file with names similar to the following from my Windows system:
C:\DownloadDirector\97FP1\UNIVERSAL\db2\Windows\InfoSphere Federation Server Relational Wrappers.msi
Note that, if your source database is DB2 LUW and it is on a different system from your PostgreSQL server, you do not need to install Replication Server on that system. The SQL Replication Capture program is built into DB2 LUW.
2. Configure Access to PostgreSQL
Simply follow the instructions and run the script found in my post about Federating DB2 and PostgreSQL
3. Create SQL Replication Subscriptions
If you're familiar with SQL Replication, you'll recognize the following steps as standard SQL Replication configuration. However, if you're new to SQL Replication, I recommend you start by checking out an older post of mine called A Fast Way to Get Started with DB2's SQL Replication
I'm going to assume your source is DB2 LUW, but it could just as easily be DB2 z/OS or DB2 on i5/OS. I'm also assuming you're using the DB2 Sample database and that it's on the same system as your PostgreSQL server.
If you have not enabled DB2 archive logging
for your source database, do so now from a DB2 command window:
- db2 update database configuration for sample using logarchmeth1 logretain
- db2 backup database sample
Copy and paste the script at the bottom of this post into a file.
- Name the file postgsubs.asnclp.
Edit the script and make the following changes:
- Change all occurances of the 'db2admin' to the user you use to connect to your DB2 Sample database.
- Change all occurances of 'mypw' to your DB2 user's password.
- Save the file.
Process the script by running DB2's asnclp
command in a DB2 command window. You may want to redirect the output to stdout.
- asnclp -f postgsubs.asnclp
You're now ready to replicate data.
4. Start the SQL Replication Programs
Start the SQL Replication Capture program from a DB2 command window:
- asncap capture_server=sample
Start the SQL Replication Apply program from a different DB2 command window:
- asnapply control_server=sample apply_qual=MYQUAL1
5. Run Applications
Update a row in the DB2 Employee table.
- Any number of rows will do. So will an insert or delete.
Query the PostgreSQL Employee table to see your change. This may take up to a minute. By default, the Apply program sleeps one minute when it finds no data to replicate.
Yes, it is possible to replicate data from DB2 to PostgreSQL. However, in all fairness, you will find a few limitations. Some examples:
1. The replication administration tools will not create target tables in PostgreSQL.
- You have to do this yourself.
- However, this is a one-time activity.
2. Some PostgreSQL datatype may not map to DB2 datatypes, or, at least not easily.
# Identify databases involved.
# ('Control' and 'Target' are usually the same)
SET SERVER CAPTURE TO DB SAMPLE ID db2admin PASSWORD "mypw";
SET SERVER CONTROL TO DB SAMPLE ID db2admin PASSWORD "mypw";
SET SERVER TARGET TO DB SAMPLE ID db2admin PASSWORD "mypw";
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
# Add meta data tables to hold information about
# your source and target tables.
CREATE CONTROL TABLES FOR CAPTURE SERVER;
CREATE CONTROL TABLES FOR APPLY CONTROL SERVER;
# Identify source table(s).
# Changed data will be 'staged' (stored) in a 'CD' table
CREATE REGISTRATION (EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE;
CREATE REGISTRATION (DEPARTMENT) DIFFERENTIAL REFRESH STAGE CDDEPARTMENT;
# A subscription (a 'member') maps a source table to a target table.
# Subscription members are grouped in sets and
# every member must be in a set, so we make a set here:
CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES
TIMING INTERVAL 1 START DATE "2008-01-01" TIME "01:00:00.000000";
# Identify our target tables and create members in the set.
# This script assume the target table(s) and nickname(s) already exist
CREATE MEMBER IN SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES
TARGET NAME MYEMP
(EXPRESSION "EMPNO" TARGET "empno"
,EXPRESSION "FIRSTNME" TARGET "firstnme"
,EXPRESSION "MIDINIT" TARGET "midinit"
,EXPRESSION "LASTNAME" TARGET "lastname"
,EXPRESSION "WORKDEPT" TARGET "workdept"
,EXPRESSION "PHONENO" TARGET "phoneno"
,EXPRESSION "HIREDATE" TARGET "hiredate"
,EXPRESSION "JOB" TARGET "job"
,EXPRESSION "EDLEVEL" TARGET "edlevel"
,EXPRESSION "SEX" TARGET "sex"
,EXPRESSION "BIRTHDATE" TARGET "birthdate"
,EXPRESSION "SALARY" TARGET "salary"
,EXPRESSION "BONUS" TARGET "bonus"
,EXPRESSION "COMM" TARGET "comm"
CREATE MEMBER IN SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES
TARGET NAME MYDEPT
(EXPRESSION "DEPTNO" TARGET "deptno"
,EXPRESSION "DEPTNAME" TARGET "deptname"
,EXPRESSION "MGRNO" TARGET "mgrno"
,EXPRESSION "ADMRDEPT" TARGET "admrdept"
,EXPRESSION "LOCATION" TARGET "location"
# Now, run the file through the asnclp command
# and you are ready to replicate data.