DB2 Replication Guide and Reference

Defining a Replication Subscription with a User-Defined Target Table

The Control Center allows you to use a previously defined DB2 table as the target table in a replication subscription. This type of target table is known as a user-defined target table. With this feature, you can define a replication subscription with a target table that is defined outside of replication administration in the Control Center.

The following sections explain how to define a user-defined target table, the restrictions, and maintaining the target table.

To define a subscription with a user-defined target table:

1. Refer to Chapter 19. Table Structures to determine the structure for the target table type. For example, if you are defining a subscription for a base aggregate target table, refer to the table structure definition for base aggregate target tables.

2. Alter the target table to add any required columns, such as timestamp columns.

3. Create a unique index for condensed tables (point-in-time or condensed CCD tables). Define the index using the SQL statements provided in Chapter 19. Table Structures.

4. Define the subscription to match the user-defined target table structure. This task includes creating new columns, subsetting columns, changing column names, or renaming before-image columns to include the before-image column prefix.

   On the Define Subscription window:

   1. Clear the Create table check boxes for the table names for which you are providing the target tables.

   2. Type the user-defined target table name in the Target table field.

   3. If you want to subset columns or rows, enhance data, or specify a target table type other than user copy, click on Advanced to open the Advanced Subscription Definition notebook.

      1. If you want to select an alternate table type, see Choosing a Target Table Type.

      2. If you want to modify the target table columns to match the user-defined target table, see Defining the Target Table Structure: Columns and Rows.

      3. If you want to subselect the rows or use an aggregate expression, see Defining the Target Table Structure: Columns and Rows.

Restrictions

The following conditions cause run-time errors:

• The subscription definition contains fewer columns than exist in the user-defined target table.

• The new columns in the subscription definition do not allow nulls or have defined default values.

• The target table and unique index do not exist when you run the Apply program.

Maintaining a User-Defined Target Table

When you use a user-defined target table during subscription, you assume the responsibilities listed below. The Control Center does not check for inconsistencies between the subscription definition and a user-defined target table. You must complete the following activities:

• When running the Apply program:

  • Ensure that there is a target table that matches the subscription definition.

  • Ensure that there is a unique index for point-in-time, condensed CCD, user copy, and replica target tables.

• Debug any inconsistencies that exist between the target table and the subscription definition.

• For a user-defined CCD target table, define the subscription to match the attributes specified for the target table by specifying:
  • The target table type as a staging table
  • Whether the table has one or multiple changes per key

  For CCD target tables: When a subscription is to be defined on a base table having a user-defined internal CCD, ensure that the internal CCD table was created to match the subscription definition.

  Because an internal CCD table replaces the join of the CD and UOW tables as the source for updates to the target table, ensure that the CCD table exists before attempting to subscribe to the base table again. If the internal CCD target table is not created, the source table will not be available for another subscription to the base table.

[ DB2 List of Books | Search the DB2 Books ]