Designing Synchronized Applications
Page 7 of 7

Designing Universal Keys

One difficulty with occasionally connected distributed applications is that a key value that is unique at a single database may not be unique in all databases in the system.

You have two strategies for dealing with this issue:

  • Use a Cloudscape method to generate a universally unique key of type BIT VARYING(128) or CHAR(128).

    See Cloudsync Universal Keys

  • Create a composite key using an automatically generated serial number column combined with other columns, when that combination is guaranteed to be unique for your system.

    For example, target databases may contain IDs (such as sales rep IDs) that are unique to a database.

NOTE: Be sure to read Why Using Autoincrement Defaults with Cloudsync Is Problematic

Cloudsync Universal Keys

You may want to create a key that is unique to the whole synchronized system, even if key values get created at multiple targets. To do this, Cloudscape provides the COM.cloudscape.util.KeyGen.getUniversalKeyValue method. This method produces a random value of Java type byte[], whose corresponding SQL type is BIT VARYING(128). This method is virtually certain to produce a value unique to the whole synchronized system.

For example, the following SQL-J code produces a table containing one universal key column and inserts a unique key value into that column:

CREATE TABLE mytable(mycolumn BIT VARYING(128) PRIMARY KEY)

INSERT INTO mytable VALUES
    KeyGen.getUniversalKeyValue()

You can also create and publish a method alias for getUniversalKeyValue for work units to use. For example:

CREATE METHOD ALIAS getKey
    FOR KeyGen.getUniversalKeyValue

This way, to invoke a work unit whose parameters are a universal key value and an INT, for example, the user at the target need only enter something like:

CALL myWorkUnit(KeyGen.getKey(), 5)

NOTE: Some tools provided by vendors other than Cloudscape may not adequately display a column of type BIT VARYING. If this is a problem in your application, you can generate a universal key of type String by using the COM.cloudscape.util.KeyGen.getUniversalKeyStringValue method instead. The string generated by this method is also virtually certain to be unique to the whole synchronized system. However, it is twice as big as the BIT VARYING value produced by getUniversalKeyValue.

Why Using Autoincrement Defaults with Cloudsync Is Problematic

As stated in the Cloudscape Reference Manual, in a non-synchronized database, values in a column with an autoincrement default are guaranteed to be unique even when no uniqueness constraint exists on the column. However, the same is not true for synchronized systems. The following example illustrates why:

A source database contains a table called AutoValues with a column called AutoValue of type int, with a default value of AUTOINCREMENT. There is no constraint requiring that the values in this column be unique. The table is emtpy, and so the value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE for this table is 0. (SYS.SYSCOLUMNS.AUTOINCREMENTVALUE tracks the number most recently generated for the autoincrement column on disk and determines what the value of the item inserted next will be.)

A user creates a publication that includes all the columns of this table and tears off two target databases, A and B.

After the copy files are created, someone inserts two rows at the source and so the value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE for this table is now 2 at the source.

At target database A, the application inserts a row into the empty AutoValues table outside of a work unit. Cloudscape sets the value of the AutoValue column to 1. Cloudscape sets the value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE to 1.

At target database B, the application inserts three rows into the empty AutoValues table outside of a work unit. Cloudscape sets the value of the AutoValue column to 1 for the first row, 2 for the second row, and 3 for the third row. At the end of the second insert, Cloudscape sets the value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE to 3.

Target database A refreshes. At the source, a row is introduced into the AutoValues table with a value of 1 for column AutoValue. Cloudscape keeps the value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE to 2, because that is still the highest value inserted into the table (despite the most recent insert).

Target database B refreshes. At the source, a second row is introduced into the AutoValues table with a value of 1 for column AutoValue and second row is introduced with a value of 2 for the column. A row is also introduced into the AutoValues table with a value of 3 for column AutoValue. The value of SYS.SYSCOLUMNS.AUTOINCREMENTVALUE changes to 3.

NOTE: If there had been a constraint on the AutoValue column requiring unique values for this row, the first two statements of B's refresh would have failed.

If A refreshes again, when A and B are done refreshing, at each target there are now 6 rows in the AutoValues table. Three rows have a value of 1 for column AutoValue, two rows have a value of 2, and one row has a value of 3. The next time the application inserts a row into the table, the value for AutoValue will be 4.

However, it is much more likely (and recommended by Cloudscape) for an application to perform inserts within work units. In such a scenario, the inserts at the source will always produce unique keys. However, this means that the value inserted into the unique key column will change at the target after the target performs its refresh. This may be acceptable if the automatically generated value in this column is not used by the application outside the context of the database, or if the application knows to check the new value after the refresh. In either case, be sure to program the application so that all inserts into or updates to tables that use the autoincrement value as a part of a foreign key happen within the same work unit as the insert that initially generates the autoincrement value.