Replicator User Guide > Replicator User Guide > Using Advanced Features > Lookup Tables > How You Set Up Horizontal Partitioning
Was this helpful?
How You Set Up Horizontal Partitioning
A horizontally partitioned table uses a base CDDS, which contains the whole replicated base table, and partitioned CDDSs that each contain only those rows that are associated with the particular value assigned to that CDDS in the lookup table.
To set up horizontal partitioning, you must complete two main tasks:
• Create and populate the lookup table in every participating database.
• Implement horizontal partitioning through Visual DBA or the Replicator Manager.
How You Create a Horizontal Partitioning Lookup Table
To set up horizontal partitioning, you must create and populate the lookup table in every participating database.
To create and populate a horizontal partitioning lookup table, follow these steps:
1. Create the horizontal partitioning lookup table with two types of columns:
• Columns with the same name and data type as the columns in the base table that determines the partitioning. You can have one column of this sort, for example, a location column; or you can have several columns, for example, group code, division code, and region code.
• A column called cdds_no, with a data type of smallint not null, that contains the CDDS number that corresponds to the partitioning value
2. Populate the horizontal partitioning lookup table using Visual DBA’s SQL Scratchpad or SQL Assistant, the SQL Terminal Monitor, or Query-By-Forms; enter the partitioning values and their corresponding CDDS numbers. You do not need to add a row for the base CDDS if the base values do not need to be replicated elsewhere.
3. Once the table is populated in one database, use copydb to copy the lookup table to the other targets. Each lookup table must be exactly the same in all databases.
Alternatively, repeat Steps 1 and 2 for every database that participates in the horizontal replication.
Note:  If you do not have a populated lookup table in every database that participates in horizontal partitioning, horizontal partitioning does not work properly.
How You Implement Horizontal Partitioning Using Visual DBA
After you create and populate the lookup table in every participating database, you must implement horizontal partitioning using one of the appropriate Ingres tools.
To implement horizontal partitioning using Visual DBA, follow these steps:
1. Define all the databases that participate in the partitioning by highlighting Databases in the Database Object Manager and clicking the Add Object toolbar button or choosing the Create command from the Edit menu. For more information, see the online help topic Creating a Database.
2. Add new CDDSs for horizontal partitioning with a different CDDS for every partition. Expand the Replication branch and highlight CDDS. For more information, see the online help topic CDDS Definition dialog box.Add propagation paths for each new CDDS in the expanded CDDS branch (of the expanded Replication branch) by selecting the desired CDDS number. For more information, see the online help topic CDDS Definition dialog box.
If the base CDDS is present only in a single database, it does not need any propagation paths.
3. Add database and Replicator server information for each new CDDS in the expanded Replication branch, expanding the CDDS branch, and selecting the desired CDDS number. For more information, see the online help topic CDDS Definition dialog boxRegister the base table to be partitioned by clicking the check box next to its name in the CDDS Definition window. For more information, see the online help topic CDDS Definition dialog box.
The lookup table appears in the Tables list because it is a table in the database. However, it need not be registered for replication.
Note:  The lookup tables are expected to be static; ordinarily they are not replicated. If desired, they can be registered. However, keep in mind that changes to the lookup table must be propagated ahead of rows in the base table or the partitioning scheme breaks down.
4. Attach the horizontal partitioning lookup table to the base table by specifying the Lookup Table in the CDDS Definition dialog. For more information, see the online help topic CDDS Definition dialog box.
5. Continue with configuration procedures—creating support objects, moving the configuration to other databases, and activating change recording—as explained in the chapter “Using Visual DBA for Configuration.”
How You Implement Horizontal Partitioning Using Replicator Manager
After you create and populate the lookup table in every participating database, you must implement horizontal partitioning using one of the appropriate Ingres tools.
To implement horizontal partitioning using Replicator Manager, follow these steps:
1. Define all the databases that participate in the partitioning in the Database Summary window.
2. Add new CDDSs for horizontal partitioning in the CDDS Summary window, with a different CDDS for every partition.
For more information, see Add a CDDS on page 14.
3. Add database and Replicator server information for each new CDDS in the CDDS Databases and Servers window.
4. Add propagation paths for each new CDDS in the Propagation Path Definition window. If the base CDDS is present in only a single database, it does not need any propagation paths.
For more information, see Add Propagation Paths on page 18.
5. Register the base table to be partitioned from the Table Registration Summary window.
For more information, see Register Tables on page 20.
The lookup table also appears in the Table Registration Summary window because it is a table in the database. However, it need not be registered for replication.
Note:  The lookup tables are expected to be static; ordinarily they are not replicated. If desired, they can be registered. However, keep in mind that changes to the lookup table must be propagated ahead of rows in the base table or the partitioning scheme breaks down.
6. Choose Edit from the Table Registration Summary window, and enter the new CDDS number for the base table in the Table Registration Details window.
7. Specify the horizontal partitioning lookup table to the base table in the Table Registration Details window.
For more information, see Assign Lookup Tables on page 23.
8. Continue with configuration procedures as explained in the chapter “Configuring Replication Using Replicator Manager.”
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 CDDS Example: Horizontal Partitioning on page 18.
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.”
Last modified date: 01/30/2023