SQL Replication is a database replication technology built into DB2 and InfoSphere Warehouse. People often ask me what's the fastest way to get started with it. I think it's best just to set up a simple configuration and go from there. That's what I'm going to show in this post. It should take about 5-10 minutes. You can then extend that configuration using the documentation in DB2's Information Center.
Naturally, if you prefer an approach where you learn concepts while you set it up, you can use the SQL Replication tutorial at ibm.com. Otherwise, read on.
SQL Replication maintains a little bit of meta data for each table you replicate. This data can be created through a DB2 user interface or a script and is stored in tables. We're going to use a script. I've put a copy of the bottom of the post.
Once you've run the script, you can start a 'Capture' program to capture changed data from the source database (Sample) and an 'Apply' program to put changes in the target database (also Sample). After that, change a row or more in the source table (Employee) and check the target table (TrgEmployee) for changes. It may take up to a minute depending on what the Apply program is doing.
After you finish, if you want to try more :) see one of the following:
o Transforming Data as You Replicate
o A Look at Distributing Data
o Compare the Rows of Two Tables
o Auditing DB2 changed data
Do It Yourself
Follow these instructions:
- Create the DB2 Sample database if you haven't done so already.
- Use the db2sampl command to do so.
- If you have not enabled DB2 log archiving for the Sample 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 from this post into a file.
- Name the file sqlrep.asnclp.
- Edit the script and make the following changes:
- Change all occurrences of the 'DB2ADMIN' to the user you use to connect to your DB2 Sample database.
- Change all occurrences of 'mypw' to your DB2 user's password.
- If you are working from a UNIX system, change the table space directory from 'c:\' to a UNIX directory.
- Save the file.
- Process the script by running the DB2 asnclp command in a DB2 command window.
- asnclp -f sqlrep.asnclp
- You may want to redirect the output to stdout.
- 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
- Update a row in the Employee table.
- Any number of rows will do. So will an insert or delete.
- Query the TrgEmployee 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.
Here are a few other things you may want to try:
- Start the Replication Center to see your replication meta data in a graphical user interface
- The Replication Center is installed with any DB2 product on Windows or Linux for Intel/AMD.
- It's part of the DB2 Control Center.
- Tell the Replication Center about your database by using the 'Add' function off of the Capture and Apply folders.
- Use the asnanalyze command to view historical data that is stored in the meta data (control) tables.
- This data includes information such as how many rows were replicated and the times at which replication occurred.
The asnclp script you need is as follows:
# 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 (DB2ADMIN.EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE;
# A subscription maps a source table to a target table.
# Subscriptions are grouped in sets.
# Every subscription 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";
# Each subscription is a member of a set.
# If needed, you can create the target table as we do here:
SET PROFILE TBSPROFILE FOR OBJECT TARGET TABLESPACE OPTIONS UW USING
FILE "c:\TSTRG.TS" SIZE 700 PAGES;
CREATE MEMBER IN SETNAME SET00 APPLYQUAL MYQUAL1 ACTIVATE YES
TARGET NAME TRGEMPLOYEE
DEFINITION IN TSTRG00 CREATE USING PROFILE TBSPROFILE
TYPE USERCOPY COLS ALL REGISTERED;
# Now, run the file through the asnclp command
# and you are ready to replicate data.