Using Advanced Features

This chapter describes advanced Replicators that are available using Visual DBA or the Replicator Manager, including the creation of lookup tables. The chapter also includes a discussion of deadlocks.

Lookup Tables

You can create lookup tables to establish:

• Horizontal partitioning of a CDDS
• Priority collision resolution

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.

   For more information, see Add a Database to the Database Summary List.

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.

3. Add database and Replicator server information for each new CDDS in the CDDS Databases and Servers window.

   For more information, see Add Database and Server Information.

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.

5. Register the base table to be partitioned from the Table Registration Summary window.

   For more information, see Register Tables.

   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.

   For instructions, see Assign a Table to a Different CDDS.

7. Specify the horizontal partitioning lookup table to the base table in the Table Registration Details window.

   For more information, see Assign Lookup Tables.

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.

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:

1. The DBA does not need to add any database information because all three databases participate in other CDDSs that were already configured.
2. 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.

3. 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

4. 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

5. 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.
6. The DBA accesses the registration information for whse_inventory in the Table Registration Details window and changes the CDDS number to 2.
7. 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.

8. 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."

Priority Collision Resolution

In CDDSs that use PriorityResolution collision mode, collisions are resolved by comparing the database numbers of the colliding databases; the database with the lowest number prevails. However, you can alter the priority of individual rows by creating and assigning a priority collision resolution lookup table that contains priority numbers; the row with the highest number prevails.

How You Create a Priority Collision Resolution Lookup Table

A priority collision resolution lookup table allows you to specify priority according to data values in a registered table. Follow these steps to create this table based on data values:

1. Create the priority collision resolution lookup table with two types of columns:
   • Columns with the same name and data type as the columns in the base table that determines priority. 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 dd_priority, with a data type of smallint not null, that contains the priority number; dd_priority must be an integer with the range 0-32767.

     Note: If many levels of priority are required, it is useful to establish a primary key on those lookup columns.

2. Populate the priority lookup table, assigning a priority number to each value so that the record with the highest priority has the largest priority number.
3. Once the table is populated in one database, use copydb to copy the lookup table to the other targets. Every database that contains the base table that is the basis for priority collision resolution must include the lookup table.

   Alternatively, repeat the preceding steps for every database that contains the CDDS to have priority collision resolution.

4. Register the base table for priority collision resolution in the CDDS Definition window in Visual DBA or from the Table Registration Summary window in the Replicator Manager.

   The lookup table appears in the Tables list in Visual DBA or in the Table Registration Summary window in Replicator Manager because it is a table in the database. However, it need not be registered for replication because it is not replicated.

5. Specify the priority collision resolution lookup table:

   Visual DBA: Specify the priority collision resolution lookup table to the base table in the CDDS Definition dialog. For more information, see the online help topics, Altering a CDDS and CDDS Definition dialog box.

   Replicator Manager: Specify the priority collision resolution lookup table to the base table in the Table Registration Detail window. For instructions, see Assign Lookup Tables.

Example: R.E.P.’s Priority Collision Resolution

The R.E.P. company wants to use PriorityResolution collision mode on their Inventory CDDS. The Inventory CDDS can be updated from each of the three full peer databases: New York (database 10), San Francisco (database 11), and London (database 20). Without a priority collision resolution lookup table, New York has priority over San Francisco and London, and San Francisco has priority over London. R.E.P. decides, however, that London must have a higher priority than San Francisco.

The Inventory CDDS has a table called book_inventory, which has a prim_whse_location column that indicates location of the inventory item and, presumably, the database from which its row originated.

The R.E.P. DBA implements priority collision resolution by completing the following tasks:

1. The DBA creates a priority collision resolution lookup table named book_inventory_priority in the nyc::hq database.

   Create the table by entering the following SQL statement with SQL Scratchpad in Visual DBA or, in Replicator Manager, with SQL/ISQL from the command line:

   create table book_inventory_priority (

       prim_whse_location    char(3)     not null,

       dd_priority           smallint    not null)

2. The DBA populates the table as follows:

   prim_whse_location      dd_priority

   NYC                     10

   LON                     8

   SFO                     5

3. The DBA copies the table to the lon::europe and sfo::west databases.
4. The DBA registers the book_inventory table in the nyc::hq database by clicking the check box in the Tables list in the CDDS Definition window in Visual DBA or in the Table Registration Summary window in Replicator Manager.
5. The DBA specifies the book_inventory_priority lookup table:

   Visual DBA: The DBA specifies the book_inventory_priority lookup table to book_inventory in the Priority Lookup Table field in the CDDS Definition window.

   Replicator Manager: The DBA specifies the book_inventory_priority lookup table to book_inventory in the Table Registration Details window by choosing the ListChoices option while the cursor is in the Priority Collision Resolution Lookup field.

6. 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."