Designing Synchronized Applications
Page 6 of 7

Publication Design Issues

Making Targets Small and Efficient

By making each target as small as possible, you may reduce the time required to:

  • send the target or copy file through the network or Internet.
  • perform local queries and updates.

Targets without extraneous data are also more secure.

To make targets small, whenever possible:

  • Have the table's WHERE clauses restrict on primary keys or on indexed (and published) fields
  • Design your tables so that you can make each WHERE clause as restrictive as possible.

Another technique to make targets small is to put methods in the publication's WHERE clause. This is especially helpful when you don't know beforehand how many parameter values will be passed to the publication. Java collections like Arraylist and Vector provide contains, an especially useful method for this purpose.

For example, suppose you want to create a publication like the one defined in Step 4: Describing the Target Data, except that it contains data for multiple salespeople. If you know the number of salespeople whose data you want in the target, you can simply pass it more parameters. For example, if you know that there are three salespeople's data in the target, you can define the publication as follows:

CREATE PUBLICATION toySalesForThreeReps
    (salesRep1ID INT, salesRep2ID INT, salesRep3ID INT)
ADD TABLE toys
ADD TABLE orders
    WHERE (salesRepID IN (?salesRep1ID, ?salesRep2ID, ?salesRep3ID))
ADD TABLE leads
    WHERE (sentToRepID IN (?salesRep1ID, ?salesRep2ID, ?salesRep3ID))
ADD METHOD ALIAS getKey
ADD WORK ALIAS placeOrder
ADD WORK ALIAS sendLead

However, if you don't know beforehand how many salespeople will be included in the publication, you cannot know how many parameter to pass to the publication.

Using methods in the publication's WHERE clause can solve this problem. The following publication allows you to pass a vector containing any number of salesperson IDs as a parameter to the publication, and the publication will be optimally small:

CREATE PUBLICATION toySalesForMultipleReps
    (salesReps SERIALIZE(java.util.Vector))
ADD TABLE toys
ADD TABLE orders
    WHERE ?salesReps.contains(salesRepID)
ADD TABLE leads
    WHERE ?salesReps.contains(sentToRepID)
ADD METHOD ALIAS getKey
ADD WORK ALIAS placeOrder
ADD WORK ALIAS sendLead

NOTE: When using methods in publications, make sure that the method always returns the same results for the same data. A non-deterministic publication can cause incorrect data at a target. A method like contains is safe, but a method that, for instance, calls a random number generator is not. For the same reason, it is advisable not to include SQL-J statements in the method, since a query may return different results at different times.

NOTE: Using a method in the WHERE clause to reduce target size may reduce copy publication performance, since it may force a table scan on an indexed column.

Target-Side Validation

The target should "screen" the source from unnecessary transactions. A transaction expected to fail at the source should fail first at the target. Otherwise, a transaction that succeeds at the target may unexpectedly disappear from the target after the refresh completes.

For the most part, this screening happens automatically. A WHERE clause of a CREATE PUBLICATION statement becomes a check constraint on a target created from the publication. Therefore, any target transaction that violates the publication's WHERE clause fails at the target and never reaches the source.

However, there are cases in which a change at a target may unexpectedly fail at the source and may therefore disappear from the target's user tables after a refresh. Two situations that can cause this are:

  • A target change violates an unpublished unique BTREE index.

    Solution: Use unique constraints rather than unique BTREE indexes on published tables whenever possible, since unique constraints on published columns are automatically published. If you do use unique BTREE indexes, make sure to explicitly publish them.

  • A target change violates a constraint.

    For example, in the following scenario, an update to the maxBonus field of the compensation table at a target would be rolled back after a refresh:

// Compensation table.
// Everyone either is on commission or gets a year-end bonus
CREATE TABLE compensation
(
    employeeID INT,
    departmentID INT,
    baseSalary FLOAT,
    commissionPercent FLOAT,
    maxBonus FLOAT
    CHECK ( commissionPercent IS NOT NULL
        OR maxBonus IS NOT NULL )
)
...

CREATE PUBLICATION salaried_employees( departmentID INT )
    ADD TABLE employees WHERE departmentID = ?departmentID
    ADD TABLE compensation
        ( employeeID, departmentID, baseSalary, maxBonus )
            WHERE departmentID = ?departmentID ...

Solution: If you publish any column mentioned in a constraint, publish all other columns mentioned in the constraint as well. At the very least, if you do leave any columns that are mentioned in a constraint unpublished, make sure they evaluate to TRUE or NULL when default values are entered at the source.

Handling Published Foreign Keys

A foreign key involves two tables. A foreign key constraint may be satisfied on a database but may be violated if parts of the primary or foreign key tables are filtered out by a publication. Since a target database may be a subset of a source database, a foreign key constraint may succeed on the source but fail at the target.

If a publication contains a foreign key constraint and incompatible subsets of the two tables' data, it may become impossible to refresh the target without violating the target's foreign key constraint. If this happens, all future refreshes from this target will fail. Once the target is in this state, you must recreate the target with the proper subset of columns and data, or without the foreign key.

You can avoid the possibility of targets violating foreign key constraints by following these guidelines when creating publications:

  • place no WHERE restrictions on the primary key table

    or

  • have identical WHERE restrictions on the primary and foreign key tables.

If you create publications that violate these guidelines, be aware that target foreign key constraint violation may occur, and that you may lose the ability to successfully refresh the target.