Set up the remote database

Data Replication with SQL Remote
PART 1. Introduction to SQL Remote
CHAPTER 4. A Tutorial for Adaptive Server Anywhere Users

Set up the remote database

The remote database needs to be configured in order to send and receive messages and participate in a SQL Remote setup. Like the consolidated database, the remote database needs a CURRENT PUBLISHER to identify the source of outgoing messages, and it needs to have the consolidated database identified as a subscriber. The remote database also needs the publication to be created and needs a subscription created for the consolidated database. The remote database also needs to be synchronized with the consolidated database; that is, it needs to have a current copy of the data in order for the replication to start.

The dbxtract utility enables you to carry out all the steps needed to create a remote database complete with subscriptions and required user IDs.

Extract the remote database information

Leave the hq database running, and change to the tutorial directory.

Type the following command at the system command line (all on one line) to extract a database for the user field_user from the consolidated database:

dbxtract -v -c "dbn=hq;uid=dba;pwd=sql" c:\tutorial field_user

In Windows 3.x, you should run the following command either from File->Run in Program Manager or from an icon:

dbxtracw -v -c "dbn=hq;uid=dba;pwd=sql" c:\tutorial field_user

The -v command-line switch produces more verbose output. This is useful during development.

This command assumes the hq database is currently running on the default server. If the database is not running, you should enter a database file parameter in the connection string:

dbf=hq.db

instead of the dbn database name parameter.

For Info For details of the dbxtract utility and its options, see The extraction command-line utility.

The dbxtract command creates a SQL command file named reload.sql in the current directory and a data file in the c:\tutorial directory. It also starts the subscriptions to the remote user.

The next step is to load these files into the remote database.

Load the remote database information

To load the database information:

From the tutorial directory, connect to the remote database field.db from Interactive SQL as user ID DBA and password SQL.

You can do this by typing CONNECT in the Interactive SQL command window, and then entering DBA, SQL, and the database file c:\tutorial\field.db in the connection dialog box.
Once connected, run the reload.sql command file:
```
READ C:\tutorial\reload.sql
```

The reload.sql command file carries out the following tasks:

Creates a message type at the remote database.
Grants PUBLISH and REMOTE permissions to the remote and consolidated database, respectively.
Creates the table in the database. If the table had contained any data before extraction, the command file would fill the replicated table with a copy of the data.
Creates a publication to identify the data being replicated.
Creates the subscription for the consolidated database, and starts the subscription.

While connected to the field database as DBA, confirm that the tables are created by typing

SELECT * FROM SalesRep ;

SELECT * FROM Customer ;

What next?

The system is now ready for replication.

For Info For the next step, inserting and replicating data, see the section Start replicating data .