Lesson 2: Prepare the consolidated database

When connecting to the pubs2 database, this tutorial uses the default sa account. When Adaptive Server Enterprise is installed, the sa account has a null password. This tutorial assumes you have changed the null password to a valid password. For more information about changing the null password in Adaptive Server Enterprise, see [external link] http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1615.htm.

In this lesson, you increase the size of the consolidated database for MobiLink synchronization and create unique primary keys.

Increasing the size of the consolidated database

MobiLink needs to add system tables and other objects to the pubs2 database for synchronization. To do this, the size of the pubs2 database must be increased.

To increase the size of the consolidated database

Connect to the pubs2 database as sa, using the isql utility in Adaptive Server Enterprise. At a command prompt, run the following command, all on one line:
isql -U sa -P Your password for sa account -D pubs2
If you are accessing Adaptive Server Enterprise remotely, use the -S parameter to specify the server name.
To have proper permission for increasing the size of a database, you must access the master database. Run the following command in isql:
use master
In Adaptive Server Enterprise, a database is stored on a disk or a portion of a disk. To increase the pubs2 database, run the following command (you must specify the disk where pubs2 is stored):
ALTER DATABASE pubs2 ON disk name = 33

Adding unique primary keys

In a synchronization system, the primary key of a table is the only way to uniquely identify a row in different databases and the only way to detect conflicts. Every table that is being mobilized must have a primary key. The primary key must never be updated. You must also guarantee that a primary key value inserted at one database is not inserted in another database.

There are several ways to generate unique primary keys. For simplicity, the method of composite primary keys is used in this tutorial. This method creates primary keys with multiple columns that are unique across consolidated and remote databases.

To add unique primary keys to the consolidated database

Connect to the pubs2 database as sa, using the isql utility in Adaptive Server Enterprise. At a command prompt, run the following command, all on one line:
isql -U sa -P Your password for sa account -D pubs2
If you are accessing Adaptive Server Enterprise remotely, use the -S parameter to specify the server name.

The following rows are not unique based on the composite primary key created for the salesdetail table in step 5. For simplicity, drop the rows by running the following commands:

DELETE FROM salesdetail 
WHERE stor_id = '5023' 
AND ord_num = 'NF-123-ADS-642-9G3' 
AND title_id = 'PC8888'

DELETE FROM salesdetail 
WHERE stor_id = '5023' 
AND ord_num = 'ZS-645-CAT-415-1B2' 
AND title_id = 'BU2075'

The following indexes interfere with the creation of primary keys in step 5. To drop the indexes, run the following commands:

DROP INDEX authors.auidind
DROP INDEX titleauthor.taind
DROP INDEX titles.titleidind
DROP INDEX sales.salesind

Add unique primary keys.

ALTER TABLE au_pix ADD PRIMARY KEY (au_id)
ALTER TABLE authors ADD PRIMARY KEY (au_id)
ALTER TABLE titleauthor ADD PRIMARY KEY (au_id, title_id)
ALTER TABLE titles ADD PRIMARY KEY (title_id)
ALTER TABLE discounts ADD PRIMARY KEY (discounttype)
ALTER TABLE stores ADD PRIMARY KEY (stor_id)
ALTER TABLE sales ADD PRIMARY KEY (stor_id, ord_num)
ALTER TABLE salesdetail ADD PRIMARY KEY (stor_id, ord_num, title_id)

After running these commands, the MobiLink server should have no trouble connecting to the consolidated database and setting it up for synchronization with any number of remotes.

Note

It is possible to synchronize data with consolidated databases that do not have primary keys. However, you must write you own synchronization events that act on shadow tables that are designed to identify rows uniquely in other tables.

Unique primary keys across all databases

In Lesson 4, the remote schema is created from the consolidated schema. This means that the remote schema has the same primary keys as the consolidated schema.

Columns were specifically chosen to ensure unique primary keys for all databases. For the sales table, the primary key consists of the stor_id and ord_num columns. Any inserted value into the remote sales table must have an unique order number (the stor_id value is always the same). This ensures uniqueness in each remote sales table. The primary key in the consolidated sales table prevents conflicts if multiple stores upload data. Each upload from one store is unique to another store because their stor_id values are different.

For the salesdetail table, the primary key consists of the stor_id, ord_num and title_id columns. There may be multiple book titles in an order. For the remote sales tables, rows may have the same values for stor_id and ord_num, but they must have different title_id values. This ensures uniqueness in each remote salesdetail table. Similar to sales table, each upload to the consolidated database from a store is unique to another store because their stor_id values are different.