Sample ASNCLP script for setting up SQL Replication

This sample contains an ASNCLP script for setting up a basic SQL Replication environment.

The script uses the EMPLOYEE table in the DB2® for Linux, UNIX, and Windows SAMPLE database. To create the SAMPLE database, use the db2sampl command. The script generates SQL statements that create Capture and Apply control tables, a registration for the EMPLOYEE table, a subscription set and a subscription-set member.

You can copy the ASNCLP script to a text file and run it by using the ASNCLP -f filename command. First change all occurrences of DB2ADMIN to the schema of the EMPLOYEE table in your SAMPLE database. Within the code sample, details about each group of commands are preceded by a comment character (#).

ASNCLP script

The script performs the following actions:

1 Setting the RUN NOW option
2 Setting up the source server
3 Registering the EMPLOYEE table
4 Setting up the target server
5 Creating the subscription set
6 Creating a target object profile
7 Creating the subscription-set member
8 Ending the ASNCLP session

#  1  Setting the RUN NOW option
# This option prompts the ASNCLP to generate SQL scripts for creating replication
# objects and then run the scripts before generating the next SQL script. This option
# is required for this sample because, for example, the Capture control tables must be
# created before you can define a registration within them.

SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

#  2  Setting up the source server
# Specifies the SAMPLE database as the Capture server and creates the
# Capture control tables.

SET SERVER CAPTURE TO DB SAMPLE;
CREATE CONTROL TABLES FOR CAPTURE SERVER;

#  3  Registering the EMPLOYEE table
# This command registers the EMPLOYEE table in the SAMPLE database and specifies
# that a change-data (CD) table, CDEMPLOYEE, be created to "stage" or hold
# replicated rows until the Apply program fetches them. The DIFFERENTIAL
# REFRESH option prompts the Apply program to update the target table periodically
# as the source table changes.

CREATE REGISTRATION (DB2ADMIN.EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE;

#  4  Setting up the target server
# For this script we also use the SAMPLE database as the control server and
# target server.

SET SERVER CONTROL TO DB SAMPLE;
SET SERVER TARGET TO DB SAMPLE;
CREATE CONTROL TABLES FOR APPLY CONTROL SERVER;

#  5  Creating the subscription set
# The TIMING INTERVAL 1 option specifies that the Apply program process
# the set every minute.

CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00
ACTIVATE YES
TIMING INTERVAL 1 START DATE "2011-04-12" TIME "15:00:00.000000";

#  6  Creating a target object profile
# The profile specifies a container for the target table space that will be
# created for the target table. If you are running the script on Linux or
# UNIX, specify a Linux or UNIX filepath instead of c:\db2data\TSTRG.TS

SET PROFILE TBSPROFILE FOR OBJECT TARGET TABLESPACE OPTIONS UW USING
FILE "c:\db2data\TSTRG.TS" SIZE 700 PAGES;

#  7  Creating the subscription-set member
# The CREATE MEMBER command specifies the registered table EMPLOYEE
# as the replication source and creates a target table, TGTEMPLOYEE.
# It also specifies that a new table space, TSTRG00, be created. TGTEMPLOYEE
# is specified as a user copy table with all columns registered.

CREATE MEMBER IN SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES SOURCE
DB2ADMIN.EMPLOYEE
TARGET NAME DB2ADMIN.TGTEMPLOYEE DEFINITION IN TSTRG00 CREATE USING
PROFILE TBSPROFILE
TYPE USERCOPY COLS ALL REGISTERED;

#  8  Ending the ASNCLP session

QUIT;