Replication Guide and Reference

Setting up the scenario replication environment

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 (table space, locking) that are suitable for testing purposes but not for production environments. To customize the control tables for your production environment, you must edit the dpcntl.udb file before you perform any other replication task.

To customize control tables:

1. Go to the sqllib\samples\repl\ directory.

2. Open the dpcntl.udb file. If you were in your production environment, you would edit 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. From the Define as Replication Source window, specify that you want to use standard conflict detection. By default, all columns are available for replication with their before-image values so you don't need to change anything else in this window for this exercise. Click OK.

4. You have the option to run SQL now or later. Save the SQL to a file, as though you wanted to change the name of the CD table. Use these steps from the Run Now or Save SQL window:

   1. Accept the default, which is to save the SQL to a file and run it later, by clicking OK. The Save SQL file window opens.

      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. To create a file in which to save the SQL, in the Save SQL file window, type replsrc.sql as the name for the file and C:\scripts as the directory in which you want it stored; and then click OK.

      Tip:	By default, the SQL is saved in the sqllib\bin 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 with all other SQL executable programs.

   3. 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.

5. Run the file to define the replication source:

   1. Right-click the Replication Sources folder and select Run SQL files.

   2. Specify the SQL file that you saved in step 4b, replsrc.sql, and click OK.

6. Verify that DEPARTMENT is defined as a replication source by 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 Subscription window opens.

2. Set up the target table and subscription set:

   1. Name the subscription set that you are about to define 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 each 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 a name for the target table by typing DEPTCOPY over the default name.

   5. Specify that you want the Control Center to create the target table by selecting the Create table check box for the DEPTCOPY target table.

   6. Click Advanced. The Target Type page of the Advanced Subscription Definition notebook opens.

   7. Because you want to create a user copy type of target table, leave the User Copy radio button selected.

   8. Configure the columns in the target table by clicking the Target Columns tab and making DEPTNO the primary key of the target table. Select the Primary key check box next to DEPTNO.

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

   9. Indicate that you want to replicate rows that meet certain criteria by clicking the Rows tab and typing the following WHERE clause:

      DEPTNO >='A00'
      

   10. Click OK to save these settings and return to the Define 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 all of the SQL states that you want to ignore.

   5. To run the SQL before the subscription set is processed, click 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 colocated 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 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 Subscription window.

5. Submit the subscription set.

   1. Click OK in the Define 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 typing COPYDB. This server is the database in which you want to store the subscription control information.

   3. Accept the default option, which is to save the SQL file and run it later, by clicking OK. The Save SQL file window opens.

   4. Type the file name, replsub.sql, and the directory in which you want to store it, C:\scripts; and then click OK. The Save SQL file 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 5d, 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 Capture program

To configure the Capture program:

1. Right-click the SAMPLE database object and select Configure. The Configure Database -- SAMPLE window opens.

2. Go to the Logs page, select the Retain log files for roll-forward recovery parameter from the list, and select the Yes 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. Click No so that you don't need to type your user ID to connect to the database again.

5. Right-click on the SAMPLE database object and select Back-up -> Database. Follow the instructions in the window to back up to a directory on your system using the default options.

   Tip:	You must perform the back-up action to make the database accessible. The database was put in back-up 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. Check that you are still connected to the source server. If you are disconnected, type the following command before going to the next step:

   DB2 CONNECT TO SAMPLE 
   

3. Go to sqllib\bnd. 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
   

   Tip:	Most systems support UR (uncommitted read) format. If your system does not support it, substitute CS (cursor stability format) for UR.

   The CAPTURE.LST file contains a list of the packages created.

To manually bind the Apply program:

1. Check that you are still connected to the source server. If you disconnected after you configured the Capture program, type the following command before going to the next step:

   DB2 CONNECT TO SAMPLE 
   

2. 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.

3. Connect to the target server by typing this command:

   DB2 CONNECT TO COPYDB 
   

4. 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 directory from which you will want to start the Apply program.

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

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. Only add 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 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 the previous release of DB2 DataPropagator is also supported: <applyqual><instance_name><control_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 window, go to the directory in the source server where you want to store the replication control files associated with the Capture program.

2. Type the following command to start the Capture program using the cold start option, and 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 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 window, go to the directory on the target server where you stored the password file.

2. Type the following command to start the Apply program and to call the ASNLOAD program:

   ASNAPPLY DEPTQUAL COPYDB LOADX
   

   Tip:	The LOADX invocation parameter calls the ASNLOAD program. 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 in the background. You can check the Apply trail table (ASN.IBMSNAP_APPLYTRAIL) 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 4.

Table 4. 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	-

[ DB2 List of Books | Search the DB2 Books ]