You may have seen the recent video here on ChannelDB2 that introduces auditing of DB2 changed data. If you're interested in giving it a quick try, it's easy set up a simple configuration and see what you get. That's what I'm going to show you how to do in this post. It should take about 5-10 minutes. We'll use DB2's SQL Replication. It's provided with all editions of DB2 and InfoSphere Warehouse except DB2 Express-C.
Naturally, if you prefer a tutorial approach, I recommend the SQL Replication-to-DataStage tutorial found in the DB2 Information Center. It shows you how you can do two things at once - set up for auditing and have that data be an ETL feed as well. Otherwise, if you want the quick start, read on.
We're going to use a script to create a complete SQL Replication configuration. I've put a copy of the bottom of the post. If you've already done the very simple introduction called A Fast Way to Get Started with DB2's SQL Replication, you'll notice that the target table definition is the only difference between auditing and standard replication. As mentioned in the video, the target table is called a CCD or LiveAudit table.
Once you've run the script, you can start the Capture program to capture changed data from the source database (Sample) and the 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 (EmployeeCCD) for changes. Take a look at the data in the columns that start with the string 'IBMSNAP'.
Do It Yourself
Follow these instructions:
- Create the DB2 Sample database if you haven't done so already.
- Use the db2sampl command to do this.
- 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 sqlrepccd.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 sqlrepccd.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 EmployeeCCD table to see your change.
- Your change may take up to a minute to arrive.
- 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 comes with DB2 and InfoSphere Warehouse.
- 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 SET01 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:\TSTRGCCD.TS" SIZE 700 PAGES;
CREATE MEMBER IN SETNAME SET01 APPLYQUAL MYQUAL1 ACTIVATE YES
TARGET NAME EMPLOYEECCD
DEFINITION IN TSTRGCCD CREATE USING PROFILE TBSPROFILE
TYPE CCD JOIN CD UOW WITH UOW COLS ALL CONDENSED OFF EXTERNAL;
# Now, run the file through the asnclp command
# and you are ready to replicate data.