Replication Guide and Reference

Data replication scenario

Use the scenario in this chapter to get some experience using the DB2 Control Center and the Capture and Apply programs. Follow the steps in this simple scenario to copy changes from a DB2 replication source to a target table in a database on DB2 for Windows NT Enterprise Edition (EE) or Workgroup Edition (WE).

The scenario consists of the following parts:

1. Before you begin
2. Planning this scenario
3. Setting up the replication environment for this scenario
4. Operating in a replication environment

Before you begin

If you want to work through this scenario on your computer, set up your system using these steps:

1. Make sure that you have DB2 for Windows NT installed on your computer.
2. Make sure that your DB2 Control Center uses the default settings. If you explicitly changed the default settings, some of the steps described in this scenario will not match what you see on your display.
3. Create the C:\scripts directory in which you will store the SQL files for replication.
4. Use the DB2 Control Center to create a new database called COPYDB, which you will use as the target and control server. To create the database, right-click the Database folder and follow the instructions for creating a new database using the wizard with default options.
5. Use the First Steps icon in DB2 Universal Database (or select Start -> Programs -> DB2 for Windows NT -> First Steps) to create the SAMPLE database. After the database is created, close the First Steps window. If you did not install First Steps when you installed DB2 for Windows NT, go to a DB2 command window and issue the db2sampl command to create the SAMPLE database.

The steps in this chapter use the data in the DEPARTMENT table from the SAMPLE database. The fully qualified name is userID.Department; where userID is the user ID that created the table. Table 1 shows the DEPARTMENT table.

Table 1. DEPARTMENT table

DEPTNO	DEPTNAME	MGRNO	ADMRDEPT	LOCATION
A00	SPIFFY COMPUTER SERVICE	000010	A00	-
B01	PLANNING	000020	A00	-
C01	INFORMATION CENTER	000030	A00	-
D01	DEVELOPMENT CENTER	-	A00	-
D11	MANUFACTURING SYSTEMS	000060	D01	-
D21	ADMINISTRATION SYSTEMS	000070	D01	-
E01	SUPPORT SERVICES	000050	A00	-
E11	OPERATIONS	000090	E01	-
E21	SOFTWARE SUPPORT	000100	E01	-

For the remainder of this exercise, use the user ID with which you created the SAMPLE and COPYDB databases. Because you created the databases, you have the authority (DBADM or SYSADM) to perform replication tasks.

Planning this scenario

Assume that an application that generates reports needs information that exists in the DEPARTMENT table of the SAMPLE database. Instead of using the data directly from the source table, you want to copy the changes to a target table that can be read only by the report-generating application. For ease of administration, you want to keep the target table on the same machine as the source server.

You require a simple data distribution configuration, with changes from one replication source being replicated to a single read-only copy. This section describes the design and planning issues that you need to consider before you perform any replication tasks.

Replication source

You already know that the replication source is the userID.DEPARTMENT table in the SAMPLE database. Before you set up your environment, you must decide what you want to replicate from that table. You decide to make all columns available for replication; and you want to save before-image values for each of them so that you can see what is changed.

Tip:	You might want to always include before-image values when you define a replication source. If you later change an update-anywhere configuration, you won't have to redefine your replication source.

Replication target

You decide that you want your replication target to be the COPYDB database, which you created using DB2 for Windows NT earlier in this chapter. Currently there is no target table in that database; you want the Control Center to create the target table according to your specifications.

Using existing target tables: When you use the Control Center, the target table is created if it doesn't exist. This method of automatically generating a target table is preferred because it ensures correct mapping to the replication source. You can use existing target tables if they were created by any DB2 product.

Assume that you want the target table in COPYDB to contain the following columns of information:

DEPTNO

Information from the DEPTNO column in the replication source (this column will be the primary key of the target table)

DEPTNAME

Information from the DEPTNAME column in the replication source

MGRNO

Information from the MGRNO column in the replication source

ADMRDEPT

Information from the ADMRDEPT column in the replication source

LOCATION

Information from the LOCATION column in the replication source

Because the columns in the target table simply reflect the data from the source table, and there is to be only one record in the target table for each record in the source table, you can use a user copy type of target table.

Replication options

For the purpose of this exercise, you decide to store the target table and the replication control tables in the default table space, USERSPACE1.

Logical server	Table space	Contents
Source server: SAMPLE	USERSPACE1	Source replication control tables, including the CD table
Control server and target server: COPYDB	USERSPACE1	Replication control tables and the target table

Typically you will want to put the UOW table and the CD tables (and CCD tables if you are using them) in their own table spaces, with table or table space locking. You can put all other replication control tables together in one table space with row-level locking.

For scheduling replication, assume that you want DB2 replication to check for any changes from the source table every minute and replicate them to the target table. Although a report-generating application doesn't require that kind of turnaround, you want to test the replication environment that you set up to make sure that everything is working correctly.

Also, you decide that after each replication cycle, you want to delete any records from the Apply audit trail table that are older than one week (seven days). This pruning prevents the table from growing too large.

You won't need to set constraints because you have a read-only target. Constraints are needed only when applications are updating a target table. In this scenario, the updates are committed at the replication source, and they must satisfy the constraints defined on that system. There is no reason for you to reevaluate the same constraints at the target.

Setting up the replication environment for this scenario

After planning the replication model, you are ready to set up the replication environment.

Step 1: Customize control tables

The Control Center automatically creates control tables at the source server and at the target server. By default, it builds the control tables with default settings (locking, table space) that are suitable for testing purposes but not for production environments. To customize the control tables for your production environment, you must edit and run the dpcntl.udb file before you perform any other replication task.

To customize control tables:

1. On the drive where you installed the DB2 Universal Database, change to the sqllib\samples\repl\ directory.
2. Open the dpcntl.udb file. If you were in your production environment, you would edit and run this file to customize the control tables for your needs. For the purpose of this exercise, do not edit this file.
3. Close the dpcntl.udb file.

Step 2: Define a replication source

After you customize the control tables, go to the Control Center to define the DEPARTMENT table as a replication source.

To define a replication source:

1. In the object tree, click the Tables folder under the SAMPLE database. All of the tables that exist in SAMPLE appear in the contents pane.
2. Right-click the DEPARTMENT table and select Define as Replication Source -> Custom. A custom replication is one that lets you manipulate the data before it is applied to the source. The Define as Replication Source window opens.
3. For this exercise, don't change anything on the Define as Replication Source window because you want to use the default settings. Click OK.
4. The Run Now or Save SQL window opens. Use these steps from the Run Now or Save SQL window:
   1. You can run SQL now or later. Accept the default, which is to save the SQL to a file and run it later, by clicking OK.

      Tip:	Most of the time you will want to use the default. By saving the SQL to a file, you can look at the SQL to understand what it will do, make any modifications that you require, save the file, and run it after you are confident that it will do what you expect it to do.

   2. The System name window opens. Click OK.
   3. Use the File browser window to create a file in which to save the SQL:
      1. In the Drives field, select C:.
      2. In the Directories list, select scripts by double-clicking it. (To move up one directory level, double-click the two dots (..) at the top of the list.)
      3. In the Path field, type replsrc.sql.
      4. Click OK.

      Tip:	By default, the SQL file is saved in the sqllib directory. When you work in your own replication environment, you will want to keep all the files in a separate directory instead of storing them in sqllib.

   4. View the file that you created. Go to the C:\scripts directory and open the replsrc.sql file using an editor. For the purpose of this exercise, don't change anything in the file. Close the file.

      Tip:

      When you set up your own replication environment, be careful how you edit this file. If you change the name of the CD table or the table space in which the CD table will be put, you must also modify the CREATE INDEX statement for the CD table and update the CD table entry in the ASN.IBMSNAP_REGISTER row.

5. Run the file to define the replication source:
   1. Right-click the Replication Sources folder and select Run SQL Files.
   2. Use the File browser window to select the SQL file that you saved in step 4c:
      1. In the Drives field, select C:.
      2. In the Directories list, select scripts by double-clicking it.
      3. In the Files field, select replsrc.sql.
      4. Click OK.
6. Verify that DEPARTMENT is defined as a replication source by right-clicking Replication Sources -> Refresh. The table name, DEPARTMENT, appears in the contents pane of the Control Center.

The table DEPARTMENT is now defined as a replication source. When you ran the SQL file, the Control Center created the change data (CD) table for this replication source and it created the replication control tables in the default table space (USERSPACE1) for the SAMPLE database.

Step 3: Define a subscription set and a subscription-set member

After you define the source, you need to define a subscription set. A subscription set defines a relationship between the replication source (DEPARTMENT in this scenario) and a target table (that you will call DEPTCOPY in this scenario). It also defines some replication parameters.

To define a subscription set and a subscription-set member:

1. Select the Replication Sources object in the object tree, then right-click the DEPARTMENT object that appears on the right pane of the Control Center and select Define Subscription. The Define replication subscription window opens.
2. Set up the target table and subscription set:
   1. Name the subscription set that you are defining by typing DEPTSUB in the Subscription name field.
   2. Identify the database where the target table will reside by selecting COPYDB in the Target server field.
   3. Type DEPTQUAL in the Apply qualifier field. This string identifies the definitions unique to the instance of the Apply program that will run this subscription set.

      Tip:	The Apply qualifier is case-sensitive. If you want the Apply qualifier to be in lowercase characters, you must delimit it when you type it; for example "deptqual". If you simply type deptqual, the Control Center converts the value to uppercase characters by default.

   4. Specify that you want the Control Center to create the target table by selecting the Create table check box.
   5. Click Change. The Change window opens.
      1. Change the default target table from userid.DEPARTMENT to userid.DEPTCOPY by typing over it.
      2. Click OK to return to the Define replication subscription window.
   6. Click Advanced. The Advanced Subscription Definition notebook opens. Do the following steps:
      1. Leave the default selections on the Target Type page because you want to create a user copy target table.
      2. On the Target Columns page, make DEPTNO the primary key of the target table by selecting the Primary key check box next to DEPTNO.

         Tip:	You might want to expand the window to view all of the columns. Also, some rows have names beginning with the letter X (for example, XDEPTNO). These rows store the before-image column values that you requested.

      3. On the Rows page, indicate that you want to replicate rows that meet certain criteria by typing the following for the WHERE clause:

         DEPTNO >='A00'
         

      4. Click OK to save these settings and return to the Define replication subscription window.
3. Define the SQL statements that will be processed when the subscription set is run:
   1. Click SQL to open the SQL window.
   2. Click Add to open the Add SQL window.
   3. Indicate that you want to delete any records in the Apply audit trail table that are older than seven days by typing the following processing statement in the SQL statement or Call procedure field:

      DELETE FROM ASN.IBMSNAP_APPLYTRAIL WHERE LASTRUN
      < (CURRENT TIMESTAMP - 7 DAYS)
      

   4. Indicate that "row not found" is an acceptable SQL state by typing the value 02000 in the SQLSTATE field and clicking Add. This value is added to the Acceptable SQLSTATE values list box.

      Tip:	You can define up to ten SQL states that you want to ignore for this subscription.

   5. Specify that the SQL is to run before the subscription set is processed by selecting the At the target server before subscription is processed radio button. In this case, you must run the SQL at the target server because the control server and target server are collocated and the Apply trail table is at the control server.
   6. Click OK. The SQL statement is added to the list box in the SQL window and the Add SQL window closes.
   7. Click OK in the SQL window to return to the Define replication subscription window.
4. Click Timing and use the Source to Target page of the Subscription Timing notebook to specify when and how often to replicate the subscription set.
   1. Keep the default values for Start date, Start time, Time-based, and Using relative timing.
   2. Specify that you want the subscription set to run in 1-minute intervals:
      1. Use the spin button on the Minutes field to select 1-minute intervals (or type 1 in the field).
      2. Use the spin button on the Hours field to change the default number to 0 (or type 0 in the field).
   3. Click the Data Blocking tab, and use the spin buttons to select 1 as the number of minutes at a time that Apply will copy committed data.

      Tip:	The value that you set for data blocking depends on how much free space you have on the workstation that runs the Apply program. Typically, you would use a number from 5 to 20. If you want to be very conservative, use 1 minute.

   4. Click OK to save these values, close the Subscription Timing notebook, and return to the Define replication subscription window.
5. Submit the subscription set.
   1. Click OK in the Define replication subscription window. The Run Now or Save SQL window opens.
   2. Specify the control server, which is the database that will contain the subscription set control information, by selecting COPYDB. This server is the database in which you want to store the subscription control information.
   3. On the Run Now or Save SQL window, accept the default option, which is to save the SQL file and run it later, by clicking OK.
   4. The System name window opens. Click OK.
   5. Use the File browser window to create a file in which to save the SQL:
      1. In the Drives field, select C:.
      2. In the Directories list, select scripts by double-clicking it.
      3. In the Path field, type replsub.sql.
      4. Click OK. The File browser window closes.
6. Run the file to define the subscription set:
   1. Right-click the Replication Subscriptions object under the SAMPLE database and select Run SQL Files.
   2. Specify the SQL file, replsub.sql, which you named in step 5e, and click OK.
7. Right-click the Replication Subscriptions object under the SAMPLE database and select Refresh. The DEPTSUB subscription set appears as an object on the contents pane of the Control Center.

Step 4: Configure the source database

To configure the Capture program:

1. Right-click the SAMPLE database object and select Configure. The Configure Database notebook opens.
2. Go to the Logs page, select the Retain log files for roll-forward recovery and/or Capture parameter from the list. In the Value box, select the CAPTURE radio button. By retaining the log, you ensure that DB2 won't overwrite log entries before the Capture program reads them.
3. Click OK to save the values.
4. Right-click the SAMPLE database object and select Disconnect.
5. Right-click on the SAMPLE database object and select Backup -> Database. Follow the instructions in the window to back up now to a directory on your system using the default options.

   Tip:	You must perform the backup action to make the database accessible. The database was put in backup pending mode when you specified that you want to retain log files for roll-forward recovery.

Step 5: Bind the Capture and Apply programs

To manually bind the Capture program

1. Select Start -> Programs -> DB2 for Windows NT -> Command Window to open a DB2 command window.
2. Connect to the source server:

   DB2 CONNECT TO SAMPLE 
   

3. Go to the sqllib\bnd directory. All the bind files are located in that directory.
4. Create and bind the Capture program package to the source server database by typing the following command:

   DB2 BIND @CAPTURE.LST ISOLATION UR BLOCKING ALL
   

   The capture.lst file contains a list of the packages created.

   Continue with the steps listed below.

To manually bind the Apply program:

1. Create and bind the Apply program package to the source server by typing both of the following commands:

   DB2 BIND @APPLYUR.LST ISOLATION UR BLOCKING ALL 
   

   DB2 BIND @APPLYCS.LST ISOLATION CS BLOCKING ALL
   

   The applyur.lst and applycs.lst files contain a list of the packages that were created.

2. Connect to the target server:

   DB2 CONNECT TO COPYDB 
   

3. Create and bind the Apply package to the target server database by typing both of the following commands:

   DB2 BIND @APPLYUR.LST ISOLATION UR BLOCKING ALL 
   

   DB2 BIND @APPLYCS.LST ISOLATION CS BLOCKING ALL
   

   The applyur.lst and applycs.lst files contain a list of the packages that were created.

Step 6: Create a password file

For end-user authentication to occur at the source server, you must create a password file with an AUTH=SERVER scheme. The Apply program uses this file when connecting to the source server. Make sure that the user ID that will run the Apply program can read the password file.

To create a password file:

1. Go to the C:\scripts directory.
2. Open a file editing session for a new file.
3. Type the following records in the empty file:

   SERVER=SAMPLE USER=userid  PWD=password 
   SERVER=COPYDB USER=userid  PWD=password 
   

   Where:

   server

   The name of the source, target, or control server, exactly as it appears in the subscription set table. (In this example, SAMPLE and COPYDB.)

   userid

   The user ID that you plan to use to administer that particular server. This value is case-sensitive on Windows NT and UNIX operating systems.

   password

   The password that is associated with that user ID. This value is case-sensitive on Windows NT and UNIX operating systems.

   Password file format: Do not put blank lines or comment lines in this file. Add only the server-name, user ID, and password information. This information enables you to use different passwords or the same password for each server.

4. Save and close the file as deptqual.pwd.

   Password file naming convention:

   The password file name is applyqual.pwd; where applyqual is a case-sensitive string that must match the case and value of the Apply qualifier (APPLY_QUAL) in the subscription set table. The file naming convention from Version 5 of DB2 DataPropagator is also supported: ApplyqualInstance_nameControl_server.pwd; which includes the case-sensitive Apply qualifier, the instance name that the Apply program runs under (the default name is DB2, in uppercase), and the name of the control server in uppercase (for example, COPYDB).

For more information about authentication and security, refer to the IBM DB2 Administration Guide.

Step 7: Replicate the scenario data

After defining the replication source and the subscription set, you can submit the copy request by starting the Capture and Apply programs.

To start the Capture program:

1. In a Windows NT command prompt window, go to the C:\scripts directory.
2. Type the following command to start the Capture program using the cold start option without automatic pruning:

   ASNCCP SAMPLE COLD NOPRUNE
   

   Tip:	Usually you would not specify the cold start option; you would let the Capture program determine whether it should cold start or warm start. For this exercise, you are forcing the Capture program to cold start to clean up the records in the CD and UOW tables.

The Capture program starts running but no new command prompt appears. This action creates a *.ccp file. The Capture program is initialized but it does not start capturing changes for the defined replication source until you start the Apply program and it completes its initial full-refresh copy.

To start the Apply program:

1. From another Windows NT command prompt window, go to the C:\scripts directory on the target server where you stored the password file.

   Tip:	You must start the Apply program in the same directory in which you stored the password file. If you try to start the Apply program in another directory, you will get an error message.

2. Type the following command to start the Apply program:

   ASNAPPLY DEPTQUAL COPYDB
   

   Tip:	The Apply qualifier, DEPTQUAL, is case-sensitive and must be entered in uppercase for this exercise.

   Tip:

   You can use the LOADX invocation parameter to call the ASNLOAD program. Type the LOADX parameter after the database name (COPYDB) in the command statement above. The default ASNLOAD program uses the EXPORT utility to export data from the source table and uses the LOAD utility to fully refresh the target table. You can modify ASNLOAD to call any IBM or vendor utility.

The Apply program starts running, but no new command prompt appears. You can check the Apply trail table (ASN.IBMSNAP_APPLYTRAIL) in COPYDB for status information.

If you view the DEPTCOPY target table after one replication cycle, you should see results that match the data shown in Table 2.

Table 2. DEPTCOPY table

DEPTNO	DEPTNAME	MGRNO	ADMRDEPT	LOCATION
A00	SPIFFY COMPUTER SERVICE	000010	A00	-
B01	PLANNING	000020	A00	-
C01	INFORMATION CENTER	000030	A00	-
D01	DEVELOPMENT CENTER	-	A00	-
D11	MANUFACTURING SYSTEMS	000060	D01	-
D21	ADMINISTRATION SYSTEMS	000070	D01	-
E01	SUPPORT SERVICES	000050	A00	-
E11	OPERATIONS	000090	E01	-
E21	SOFTWARE SUPPORT	000100	E01	-

Operating in a replication environment

After the replication environment is up and running, changes that are made to the replication source will be replicated to the target table. You must prune the control tables periodically to prevent them from growing too large. Although the Capture and Apply programs can run continuously, there are times when you will want to stop them (for example, to run utilities that use the table spaces that contain the control tables).

Step 1: Update the source table

Assume that two new departments were created at the Spiffy Computer Service: a technical writing department and a public relations department.

To update the source table:

1. Select Start -> Programs -> DB2 for Windows NT -> Command Window to open a DB2 command window.
2. Connect to the source server:

   DB2 CONNECT TO SAMPLE
   

3. Add two new rows, one for each department, by typing both of the following commands:

   DB2 INSERT INTO DEPARTMENT VALUES ('F01','TECHNICAL WRITING','000110','F01',NULL)
   DB2 INSERT INTO DEPARTMENT VALUES ('G01','PUBLIC RELATIONS','000120','G01',NULL)
   

4. Connect to the target server:

   DB2 CONNECT TO COPYDB
   

5. Verify that the new rows are replicated to the target database by typing the following command:

   DB2 SELECT * FROM DEPTCOPY
   

   Tip:	The replication process does not occur immediately. You should wait approximately five minutes before checking the table.

Table 3 shows the results of the replication, with two new rows appended to the table.

Table 3. DEPTCOPY table after changes are replicated

DEPTNO	DEPTNAME	MGRNO	ADMRDEPT	LOCATION
F01	TECHNICAL WRITING	000110	F01	-
G01	PUBLIC RELATIONS	000120	G01	-
A00	SPIFFY COMPUTER SERVICE	000010	A00	-
B01	PLANNING	000020	A00	-
C01	INFORMATION CENTER	000030	A00	-
D01	DEVELOPMENT CENTER	-	A00	-
D11	MANUFACTURING SYSTEMS	000060	D01	-
D21	ADMINISTRATION SYSTEMS	000070	D01	-
E01	SUPPORT SERVICES	000050	A00	-
E11	OPERATIONS	000090	E01	-
E21	SOFTWARE SUPPORT	000100	E01	-

Step 2: Prune the control tables

The following steps assume that the Capture program is running. If the Capture program is not running, the prune command will not work.

Tip:	On Windows NT, you can use the Task Manager to determine if the Capture program (ASNCCP) is running.

To verify that there is something to prune:

1. Open a DB2 command window. Use this window for the remainder of the steps in this exercise.
2. Type the following command to connect to the source server:

   DB2 CONNECT TO SAMPLE
   

3. Check that there are some rows in the unit-of-work table by typing the following command:

   DB2 SELECT COUNT(*) FROM ASN.IBMSNAP_UOW
   

   There should be two rows in the unit-of-work table from the previous replication.

To run the prune command:

1. Type the prune command; and include the name of the source server:

   ASNCMD SAMPLE PRUNE
   

   Tip:	Usually you should prune during off-peak hours.

2. Verify that the prune command worked and the unit-of-work table is empty by typing the following command:

   DB2 SELECT COUNT(*) FROM ASN.IBMSNAP_UOW
   

   There should be no rows in the table.

Step 3: Stop the Capture and Apply programs

An important part of maintaining your replication environment is regular database maintenance. Sometimes that maintenance will require you to stop the Capture and Apply programs. For example, you must stop the Capture and Apply programs before you run utilities that directly use the table spaces that are used by these programs.

Perform the following steps in the DB2 command window that you opened in the previous step.

To stop the Capture program:

Type the following command: ASNCMD SAMPLE STOP

To stop the Apply program:

Type the following command: ASNASTOP DEPTQUAL (where DEPTQUAL is the case-sensitive Apply qualifier).

You can run DB2 utilities on your database now that you stopped the Capture and Apply programs. (Running the utilities is beyond the scope of this exercise.)