Replication Guide and Reference

Defining replication subscriptions

To define a replication subscription using the DB2 Control Center:

1. Click the Replication Sources folder for the source database to show all tables and views defined as replication sources. The replication sources appear in the contents pane.

2. Select one or more tables or views that you want to define as sources for the subscription set, and right-click on one to show the pop-up menu and select Define subscription. The Define Subscription window opens.

3. Give the subscription a name, specify the target server, and specify the Apply qualifier for the subscription. You can also change the name of the target table and specify whether the Apply program should create the target table.

4. Click the Advanced push button to specify the target type and to specify specific columns and rows. See Choosing a target-table type and Defining the target-table structure: columns and rows for more information about these tasks.

5. Click the Timing push button to specify the frequency of replication and a data blocking value. See Specifying a data-blocking value for more information.

6. Click the SQL push button to add SQL statements or stored procedures that you want to run before or after a subscription cycle. For example, you can add a DELETE statement to prune the Apply trail control table.

7. Click the OK push button to complete the subscription definition. The Subscription Information window opens. In that window, specify the control server name.

To define a replication subscription using DJRA:

1. From the main window, click Create Empty Subscription Sets to open the Create Empty Subscription Sets window.

2. In this window, specify the source server, control server, target servers, the Apply qualifier, the subscription set name, the subscription timing, and blocking factor.

3. Add subscription-set members to the subscription set.

   1. From the main window, click Add a Member to Subscription Sets or Add Multiple Members to Subscription Sets to display either the Add a Member to Subscription Sets window or the Add Multiple Members to Subscription Sets window.

   2. In this window, specify the subscription sets to which you want to add a member, the tables and views to add to the subscription set, whether the target table should be a column or row subset of the source table (see Defining the target-table structure: columns and rows), the target table type (see Choosing a target-table type), and how the index for the target table should be created.

If you defined an event to start the Apply program, you must populate the event table. See Event timing for more information about this task. To begin replicating data to the target tables, start the Capture program at the source server, then start the Apply program using the name of the control server that you specified in the Subscription Information window.

Defining replication subscriptions for update-anywhere replication

To define a replication subscription for update-anywhere replication using the DB2 Control Center, define a subscription set and use the following selections:

1. Select the replication sources that you want to be in the subscription set. Include all sources affected by the replica tables being updated.

2. From the Subscription Definition window, select a target table to be defined as a replica table.

3. Click Advanced to open the Advanced Subscription notebook. The following selections are required on the Advanced Subscription notebook:

   1. From the Target Type page, click on Target table is replica.

   2. From the Target Columns page:

      1. Ensure that the Subscribe check boxes are selected for every column. Do not create new columns for the replica table.

      2. Specify a primary key for the replica table by clicking on the Primary Key check boxes next to the key column names.

         Make the primary key the same as the source-table primary key to prevent conflicts. Do not use before-image columns as primary-key columns for the target table.

         Important: For existing target tables, you must select the primary-key columns.

   3. If you want the replica to be a subset of the source table, type a row predicate in the WHERE field on the Rows page.

   4. Click OK to close the Advanced Subscription notebook.

4. Repeat step 3b for each target table.

5. Click Timing to open the Subscription Timing notebook:

   1. On the Source to Target page, fill in the subscription set timing information for copying the source-table's changed data to the target tables.

   2. On the Replica to Source page, fill in the subscription-set timing information for copying the replica-table's changed data to the source tables.

   3. Click OK to close the notebook.

6. If you want to define SQL or CALL procedures to run before or after the subscription set is processed, click SQL and define the processing statements.

To define a replication subscription for update-anywhere replication using DJRA, select the replica target structure when you add the member to the subscription set.

Choosing a target-table type

You can specify a target-table type if you do not want to accept the default target type of user copy.

To specify a target-table type using the DB2 Control Center:

1. From the Define Subscription window, select a source and target table combination, and click Advanced to open the Advanced Subscription Definition notebook.

2. From the Target Type page, select one of the following table types:

   • For read-only target tables, you can select:

     User copy

     A target table that matches the source table exactly at the time of the copy.

     Point-in-time

     A target table that matches the source table, with a timestamp column added.

     Staging table

     Also known as a CCD table. See CCD tables for more information about CCD tables. If you select this option without selecting either of the two options below, the DB2 Control Center creates a noncomplete, noncondensed CCD table.

     Used as source for future copies

     Select this option if you want a complete, condensed CCD table.

     Include Unit-of-Work (UOW) table columns

     Select this option if you want a noncomplete, condensed CCD table that includes extra columns from the UOW table.

     Base aggregate

     A target table that contains aggregated data for a user table appended at specified intervals.

     Change aggregate

     A target table that contains aggregated data based on changes recorded for a source table.

   • For updateable target tables, select Target table is replica to create an updateable target table used for update-anywhere replication.

3. If you are finished using the Advanced Subscription Definition notebook, select OK to close the notebook. Otherwise, use the other pages of the notebook to define the target table columns and rows, as needed.

To specify a target-table type using DJRA, click Add a Member to Subscription Sets or Add Multiple Members to Subscription Sets. Fill in the required information for the subscription-set member. You can specify the target-table type from the Table structure drop-down list. The available types include the same as those described for the DB2 Control Center, plus choices for CCD table types.

Defining the target-table structure: columns and rows

For some applications, the target table does not need all of the rows or columns that exist in the source table. You can define the target table to be a column or row subset of the source table using the Advanced Subscription Definition notebook. For more information on subsetting, see Subsetting columns and rows.

Restriction: Replica target tables must contain the same columns as the source table: you cannot create subsets for them; you cannot add columns; and you cannot rename columns.

Defining the target-table columns

To define the target-table columns using the DB2 Control Center:

1. From the Define Subscription window, select a source and target table combination and click Advanced to open the Advanced Subscription Definition notebook.

2. From the Target Columns page, specify which columns should be the primary-key columns for the target table; you can rename columns and you can change column definitions.

   If you want to specify a column as a primary-key column for the target table, select the Primary Key check boxes next to the column names.

   Attention: For the following target-table types you must select one or more columns as part of a primary key: user copy, point-in-time, replica, and condensed staging tables. If you do not select columns for the primary key, DB2 uses the primary-key definition of the source table. However, if the source table does not have a primary-key definition, the Apply program issues an error message.

   If you want to rename a column, select the column name and type over the existing column name. A column name can have up to 17 characters and can be an ordinary or delimited identifier.

   If you want to change a column definition for the target table, click Change to open the Change Column window. From this window, you can:

   • Change the name of the column.

   • Change the definition of the column by entering an SQL expression. For example: COUNT(*) or EMP_SALARY - EMP_COMM.

     The expression can contain up to 254 characters and can be any valid SQL expression. This expression can contain ordinary or delimited identifiers. Columns used in the expression must be valid after-image columns from the source table. These column names are listed in the Available columns box.

     See the DB2 SQL Reference for information on valid SQL expressions. Invalid SQL expressions cause an SQL error when the Apply program processes the subscription.

   • See examples of valid SQL expressions; click Examples.

   If you want to remove a column from the target table, clear the Subscribe check box next to the column name.

   If you want to create a new computed column or use aggregation for the target table:

   1. Click Create Column to open the Create Column window.

   2. Type the name of the column in the Column name field. The name can have up to 17 characters and can be an ordinary or delimited identifier.

   3. Type the SQL expression defining the new column.

   4. Click OK to close the window.

3. If you are finished using the Advanced Subscription Definition notebook, select OK to close the notebook. Otherwise, use the Rows page to define the target table rows, as needed.

To define the target-table columns using DJRA, click the Selected columns radio button from the Add a Member to Subscription Sets window. Then select the columns you want replicated to the target table.

Defining the target-table rows

To define the target-table rows using the DB2 Control Center:

1. From the Define Subscription window, select a source and target table combination and click Advanced to open the Advanced Subscription Definition notebook.

2. From the Rows page, specify a WHERE clause that defines the row subset.

   To specify which rows are copied to the target table, type an SQL predicate in the WHERE field. The predicate can contain ordinary or delimited identifiers. See the DB2 SQL Reference for more information about WHERE clauses.

   Row Predicate Restrictions:

   • Do not type WHERE in the clause; it is implied. Type WHERE in the clause only for subselect statements.

   • Do not end the clause with a semicolon (;).

   • You can use only those column names shown in the Target columns list on the Target Columns page; these names are also listed in the Available columns list.

   • Do not use before-image columns, computed columns, or IBMSNAP columns in the WHERE clause. Before-image columns are supported for CD tables but not for user tables, point-in-time tables, or replica tables. ¹⁴

   • If you added computed columns (on the Target Columns page), you must provide a GROUP BY clause. Both base aggregate and change aggregate tables must have a GROUP BY clause.

   • If your WHERE clause contains the Boolean expression OR, enclose the predicate in parentheses; for example, (COL1=X OR COL2=Y).

   • If the target table is a change aggregate table and contains before-image columns, you must include the before-image columns in a GROUP BY clause, even though these columns are not displayed in the Available columns box on the Target Columns page.

   • When both of the following conditions are true, you must provide a dummy WHERE clause:

     • You are creating an aggregate column that requires a GROUP BY clause.

     • You do not use any other predicate in the WHERE field.

     The Apply program issues error messages if you do not provide the dummy WHERE clause in this situation.

3. To see examples of SQL predicates, click the Examples button.

   WHERE clause examples:

   The following examples show WHERE clauses that you can use to filter rows of the target table. These examples are very general and designed for you to use as a model.

   • WHERE clause specifying rows with specific values

     To copy only the rows that contain a specific value, such as MGR for employees that are managers, use a WHERE clause like:

     EMPLOYEE = 'MGR'
     

   • WHERE clause specifying rows with a range of values

     To copy only the rows within a range, such as employee numbers between 5000 and 7000 to the target table, use a WHERE clause like:

     EMPID BETWEEN 5000 AND 7000
     

   • Dummy WHERE clause

     To support aggregation, use a WHERE clause like:

     1=1
     

4. If you are finished using the Advanced Subscription Definition notebook, click OK to close the notebook.

To define the target-table rows using DJRA, add a WHERE clause in the Where clause field of the Add a Member to Subscription Sets window.

Defining a subscription set with a user-defined table

DB2 DataPropagator allows you to use a previously-defined DB2 table as the target table in a subscription set. That is, you can define a subscription-set member to be a target table that is defined outside of the DB2 Control Center or DJRA. This type of target table is known as a user-defined target table.

Restrictions:

• The subscription-set definition must contain the same number of columns as exist in the user-defined target table.

• New columns in the subscription-set definition must allow nulls and must not have defined default values.

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

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

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

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

3. For point-in-time, user copy, replica, and condensed CCD tables, create a unique index.

4. Define the subscription-set member to match the user-defined target table structure, including new columns, subset columns, changed column names, and renamed before-image columns.

   From the DB2 Control Center, in the Define Subscription window:

   1. Clear the Create table check boxes next to 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 Advanced to open the Advanced Subscription Definition notebook.

      If you want to select an alternate table type, see Choosing a target-table type. If you want to modify the target-table columns to match the user-defined target table, or subselect the rows or use an aggregate expression, see Defining the target-table structure: columns and rows.

DJRA tolerates existing target tables, and checks that the columns in the target table match those defined for the subscription-set member.

DB2 DataPropagator does not check for inconsistencies between the subscription definition and a user-defined target table. You must:

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

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

• If you want to use a CCD table between the source table and the user-defined target table, define a subscription to match the attributes specified for the target table. Then define a subscription for the target table in which the CCD table is the source table.

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

Defining SQL statements or stored procedures for the subscription set

You can define SQL statements or stored procedures to be run before or after the Apply program copies the data from the source to the target table. For example, you can prune the Apply trail control table each day to remove older entries.

To specify SQL statements or stored procedures for the subscription set using the DB2 Control Center:

1. From the Define Subscription window, click SQL to open the SQL window.

   Use the SQL window to add or remove SQL statements or stored procedures that run at the target or source server either before or after the replication subscription is processed. The statements are processed in the order that they appear in the list.

2. Click Add to open the Add SQL window.

3. Type the SQL statement or stored procedure name in the SQL statement or Call procedure field. The stored procedure name must begin with CALL. This field can contain ordinary or delimited identifiers.

4. If you know that the SQL statement or stored procedure will generate SQLSTATEs that would otherwise terminate execution, specify these SQLSTATEs so that the Apply program can bypass them and treat them as successful execution. For example, a DELETE statement will generate a SQLSTATE 02000 when attempting to delete nonexistent rows, but for new tables you might not care about this error.

   Enter valid 5-byte SQLSTATE values in the SQLSTATE field and click Add. The value is added to the Acceptable SQLSTATE values box. You can add up to 10 values.

5. Specify whether you want to run the SQL statement or stored procedure at the source or target server before the subscription set is processed, or at the target server after the subscription set is processed by clicking the appropriate radio button in the Submit SQL statement field.

6. Click OK to add the statement to the box in the SQL window and close the Add SQL window.

To specify SQL statements or stored procedures for the subscription using DJRA, click Add Statements or Procedures to Subscriptions Sets. Fill in the required information to specify the source server and subscription sets to which you are adding SQL statements or stored procedures, then specify the SQL statement or stored procedure, along with acceptable SQLSTATE values, and when it should run within the subscription-set cycle.

Data-sharing considerations

You can implement replication in an S/390 data-sharing environment. In a data-sharing environment, you run one Capture program for each source data-sharing group and one or more Apply programs for each target data-sharing group.

The Capture program can read the data-sharing logs for DB2 for MVS/ESA V4, DB2 for OS/390 V5, or DB2 for OS/390 V6. That is, you can run different versions of DB2 in a data-sharing environment, for example during version-to-version migration, and have one Capture program continue to capture transaction-consistent data. However, this mixed-version environment is not recommended for long-term use, either for replication or for DB2. See the DB2 for OS/390 Administration Guide for information about data sharing with mixed versions of DB2.

Specifying a data-blocking value

To specify how many minutes worth of change data DB2 DataPropagator can replicate during a subscription cycle, use the Data Blocking page of the Subscription Timing notebook in the DB2 Control Center, or set the Blocking factor in the Create Empty Subscription Sets window in DJRA. The number of minutes that you specify determines the size of the data block. See Data blocking for large volumes of changes for more information about how to determine this value. DB2 DataPropagator saves this value in the MAX_SYNCH_MINUTES column of the subscription set control table.

Data currency requirements

How up to date do you want target tables to be? How out of date can they be without disrupting the application programs that use them? The answers to these questions reveal your data currency requirements. You can control how often the Apply program processes subscriptions and thereby control the currency of the data. You can set an interval (or relative timing) schedule for the Apply program, or define an event trigger that the Apply program uses to start processing a subscription set.

You define subscription timing with the Subscription Timing notebook in the DB2 Control Center or from the Subscription set timing field on the Create Empty Subscription Sets window in DJRA. You can control the timing using time-based or event-based scheduling, or you can use these timing options together. For example, you can set an interval of one day, and also specify an event that triggers the subscription cycle. For update-anywhere replication, you can also specify different timing for source-to-replica and replica-to-source replication.

Recommendation: When moving from a test environment to a production environment, set a mid-range timing value (such as 2 hours) and tune your system from there (to a more frequent or less frequent interval, as appropriate).

Interval timing (relative timing)

The simplest method of controlling subscription timing is to use interval timing. You determine a specific start time, date, and interval. The interval can be specific (from one minute to one year) or continuous, but time intervals are approximate. The Apply program begins processing a subscription set as soon as it can, based on its workload and the availability of resources. If you specify continuous timing, the Apply program replicates data as frequently as it can.

Choosing a timing interval does not guarantee that the frequency of replication will be exactly at that interval. Before specifying an interval, you should determine whether it is possible to refresh all tables in the subscription set within that interval: determine the amount of data that the Apply program is likely to select for each interval and estimate the time that it will take to copy the data.

You can set and change the interval using the DB2 Control Center, DJRA, or by executing SQL statements against the subscription-set control table.

Event timing

To replicate data using event timing, specify an event name when you define the subscription set in the DB2 Control Center or DJRA. You must also populate (using an application program or the DB2 Command Center) the subscription events table with a timestamp for the event name. When the Apply program detects the event, it begins replication.

The subscription events table has three columns, as shown in Table 6.

Table 6. The Subscription Events Table

EVENT_NAME	EVENT_TIME	END_OF_PERIOD
END_OF_DAY	1999-05-01-17.00.00.000000	1999-05-01-15.00.00.000000

EVENT_NAME is the name of the event that you specify while defining the subscription set. EVENT_TIME is the timestamp for when the Apply program begins processing the subscription set. END_OF_PERIOD is an optional value that indicates that updates that occur after the specified time should be deferred until a future time. Set EVENT_TIME using the clock at the control server, and set END_OF_PERIOD using the clock at the source server. This distinction is important if the two servers are in different time zones.

In Table 6, for the event named END_OF_DAY, the timestamp value (1999-05-01-17.00.00.000000) is the time when the Apply program is to begin processing the replication subscription. The END_OF_PERIOD timestamp value (1999-05-01-15.00.00.000000) is the time after which updates are not replicated and will be replicated on the next day's cycle. That is, the event replicates all outstanding updates made before three o'clock, and defers all subsequent updates.

Your application programs must post events to the subscription events table to tie your application programs to subscription activity. When you post an entry using CURRENT TIMESTAMP for EVENT_TIME, you trigger the event named by EVENT_NAME. Any subscription set tied to this event becomes eligible to run. You can post events in advance, such as next week, next year, or every Saturday. If the Apply program is running, it starts at approximately the time that you specify. If the Apply program is stopped at the time that you specify, when it restarts, it checks the subscription events table and begins processing the subscription set for the posted event.

Data consistency requirements

When planning and defining a subscription set, you need to be aware of the following rules and constraints:

• If any member of the subscription set requires full-refresh copying for any reason, the entire set is refreshed. For update-anywhere replication, full-refresh copying occurs only from the replication source to the replica, not from replica to source.

• A single synchpoint is maintained for the subscription set to indicate the copy progress for the entire subscription set.

• You must refresh target tables that have referential-integrity constraints using the ASNLOAD exit routine to bypass referential-integrity checking.

• Do not define referential constraints for read-only target tables.

• The first occurrence of any referential-integrity violation terminates the current replication cycle. The subscription cycle is automatically retried, after the transaction is rejected and compensated.

• For update-anywhere replication, you must specify a collision-detection level when you define a replication source.

• For update-anywhere replication, each replica table must be of the same generation as all other replicas in the subscription set, and they must all have the same source table.

• For update-anywhere replication, because no single application program updates both source and target tables, referential-integrity violations cannot be detected in application logic, so you must use declarative referential-integrity constraints.

• The administration tools do not copy referential-constraint definitions from a source table to target tables. For update-anywhere replication, you must include all referential constraints that exist among the source tables in the replica tables to prevent referential-integrity violations. If you omit some referential constraints, an update made to a replica table could cause an referential-integrity violation when it is replicated to the source table.

• For a subscription that includes external CCDs, all the external CCDs must have a common original source database.

If you maintain your own CCD table, you must update three columns in the register control table: CCD_OLD_SYNCHPOINT, SYNCHPOINT, and SYNCHTIME:

CCD_OLD_SYNCHPOINT

The synch point associated with the oldest row remaining in the CCD table.

Before a full refresh of the CCD table, set CCD_OLD_SYNCHPOINT to NULL.

After a full refresh of the CCD table, set the CCD_OLD_SYNCHPOINT to a value greater than the previous value of SYNCHPOINT. If SYNCHPOINT has no previous value (in the case of the initial load), set the CCD_OLD_SYNCHPOINT to X'00000000000000000000'.

SYNCHPOINT

A sequence value useful for maintaining the state of CCD copies, subscription states, and for controlling pruning.

Set the SYNCHPOINT for the CCD table to MAX(IBMSNAP_COMMITSEQ) whenever you commit new changes to the CCD table. Be sure also to set SYNCHTIME accordingly.

SYNCHTIME

The timestamp-equivalent of SYNCHPOINT.

¹⁴

If you use before-image columns or computed columns, for example, full refresh is no longer possible. You must also modify the register control table.

[ DB2 List of Books | Search the DB2 Books ]