Replication Guide and Reference

Defining a Subscription

To define a subscription to copy the changes from DEPARTMENT, the source table, to the target table named DEPARTCOPY:

1. From the Replication Sources list displayed in 8 above, click mouse button 2 on the DEPARTMENT object on the right pane of the Control Center, then select Define subscription. The Define Subscription window opens.

2. Set up the target table and subscription definition.

   1. Type DEPTSUB in the Subscription name field.

   2. Type COPYDB in the Target server field. This database is where the target table will reside.

   3. Type DEPTQUAL in the Apply qualifier field. The character string identifies the subscription definitions unique to each instance of the Apply program that will run this subscription.

   4. Type DEPARTCOPY over the default name of the target table. The Control Center adds a table qualifier prefix to the target table name.

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

3. Click on the Advanced push button. The Target Type page of the Advanced Subscription Definition notebook opens.

   1. Select the User Copy radio button.

   2. Click on the Target Columns tab.

      1. Select the check box under Subscribe for XDEPTNO.

      2. Check the Primary key check box next to DEPTNO.

   3. Click on the Rows tab and type the following WHERE clause:

      DEPTNO >='A00'
      

   4. Click on OK to save these settings and return to the Define Subscription window.

4. Define SQL statements to be processed during subscription run time:

   1. Click on the SQL push button. The SQL window opens.

   2. Click on the Add push button. The Add SQL window opens.

   3. Type the following processing statement in the SQL statement field:

      DELETE FROM ASN.IBMSNAP_APPLYTRAIL
      WHERE LASTRUN < (CURRENT TIMESTAMP - 1 DAY)
      

   4. Type the SQLSTATE value 02000 in the SQLSTATE field and click on Add. This value is added to the Acceptable SQLSTATE values list box.

   5. Click on the radio button that indicates you want to submit the statement before the subscription is processed.

   6. Click on OK. The statement is added to the list box in the SQL window and the Add SQL window closes.

   7. Click on OK to return to the Define Subscription window.

5. Specify when and how often to replicate the subscription by clicking on the Timing push button. The Source to Target page of the Subscription Timing notebook opens.

   1. Keep the current date in the Start date field.

   2. Keep the current time in the Start time field.

   3. Select the Time-based check box and the Using relative timing radio button.

   4. Use the spin buttons on the Minutes field to select 1-minute intervals.

   5. Use the spin buttons on the Hours field to change the default number to 0.

   6. Click on the Data Blocking tab.

   7. Click on the spin buttons and select 5 as the number of minutes at a time that Apply will copy committed data.

   8. Click on OK to save these values and return to the Define Subscription window.

6. Click on OK to submit the subscription. The Subscription Information window opens.

   1. Type in COPYDB.

   2. Select the Save to SQL file and run later radio button and click on OK.

   3. Type the file name where you want to save the file in the Run SQL files window and click on OK.

   4. Click on OK in the DB2 message window to save the values and close the Define Subscription window.

7. Click mouse button 2 on the Replication Subscriptions folder under the SAMPLE database and select Run SQL files.

   1. Run the SQL file you named above.

   2. Click on OK in the DB2 message window when the message appears that this file has run successfully.

8. Click mouse button 1 on the Replication Subscriptions folder under the SAMPLE database. The DEPTSUB subscription appears as an instance on the contents pane of the Control Center.

[ DB2 List of Books | Search the DB2 Books ]