Example: R.E.P.’s Horizontal Partitioning
The R.E.P. company is partitioning its warehouse information: the New York location must contain information for all warehouses, and the San Francisco and London locations must only contain information for their respective sites. This example is also discussed in (see page
CDDS Example: Horizontal Partitioning.
The whse_inventory table contains the location column used to determine horizontal partitioning. The new CDDSs and their contents are illustrated in the following figure:
The CDDS Diagrams portion of the CDDS Worksheet is illustrated in the following figure:
The R.E.P. DBA creates, populates, and distributes the lookup table whse_inventory_cdds.
1. Create the table by entering the following SQL statement with SQL Scratchpad in Visual DBA, or enter SQL/ISQL from the command line:
create table whse_inventory_cdds (
location char(3) not null,
cdds_no smallint not null)
The location column contains the location values that are used to partition the data.
2. The DBA populates whse_inventory_cdds with the following values:
location cdds_no
LON 3
SFO 4
No value is added for NYC because the New York data is not replicated to other sites.
3. The DBA copies whse_inventory_cdds to the lon::europe and sfo::west databases.
The R.E.P. DBA implements horizontal partitioning by completing the following tasks on nyc::hq:
4. The DBA does not need to add any database information because all three databases participate in other CDDSs that were already configured.
5. The DBA adds the following CDDSs to the CDDS branch under Replication in Visual DBA or to the CDDS Summary window in Replicator Manager:
• CDDS 2, NYC Warehouse
• CDDS 3, LON Warehouse
• CDDS 4, SFO Warehouse
The DBA uses the default collision and error modes.
6. The DBA adds database and Replicator Server information for each CDDS in the CDDS Definition window in Visual DBA or in the CDDS Databases and Server window in Replicator Manager.
Note: If you are using Visual DBA, Step 4 is performed before Step 3.
CDDS 2 does not need any propagation paths because the NYC data is not replicated to any other locations.
The Database Summary portion of the CDDS Worksheet for each CDDS is as follows:
CDDS No. Database No./Name Target Type Server Number
2 (NYC) 10, nyc::hq full peer 11
3 (LON) 10, nyc::hq full peer 11
20, lon::europe full peer 12
4 (SFO) 10, nyc::hq full peer
11, sfo::west full peer
7. The DBA adds the propagation paths for each CDDS in the CDDS Definition dialog in Visual DBA or the Propagation Path Definition window in Replicator Manager.
The Propagation Paths portion of the CDDS Worksheet for CDDS 3 is as follows:
Originator Local Target Comment
10 (nyc::hq) 10 (nyc::hq) 20 (lon::europe) NYC to LON
20 (lon::europe) 20 (lon::europe) 10 (nyc::hq) LON to NYC
The Propagation Paths portion of the CDDS Worksheet for CDDS 4 is as follows:
Originator Local Target Comment
10 (nyc::hq) 10 (nyc::hq) 11 (sfo::west) NYC to SFO
11 (sfo::west) 11 (sfo::west) 10 (nyc::hq) SFO to NYC
8. The DBA registers the whse_inventory table in the CDDS Definition window in Visual DBA or in the Table Registration Summary window in Replicator Manager. In Replicator Manager, at this point, the CDDS assigned to whse_inventory is still the Default CDDS.
9. The DBA accesses the registration information for whse_inventory in the Table Registration Details window and changes the CDDS number to 2.
10. The DBA specifies the whse_inventory_cdds lookup table:
Replicator Manager: The DBA specifies the whse_inventory_cdds lookup table to whse_inventory in the Table Registration Details window by choosing the ListChoices option while the cursor is in the Horizontal Partition Lookup field.
Visual DBA: The DBA specifies the whse_inventory_cdds lookup table to whse_inventory in the CDDS Definition window by typing the lookup table name into the CDDS Lookup field.
11. Continue with configuration procedures—creating support objects, moving the configuration to other databases, and activating change recording—as explained in the chapters “Configuring Replication Using Visual DBA“ and “Configuring Replication Using Replicator Manager.”
