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