Set up the consolidated database

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

Set up the consolidated database

This section of the tutorial describes how to set up the consolidated database of a simple replication system.

You require DBA authority to carry out this task.

Create a SQL Remote message type

All messages sent as part of replication use a message type. A message type description has two parts:

A message link supported by SQL Remote. In this tutorial, we use the FILE link.
An address for this message link, to identify the source of outgoing messages.

To create the message type:

Create the file message type using the following statement:

CREATE REMOTE MESSAGE 
TYPE file 
ADDRESS 'hq'

The address (hq) for a file link is a directory in which files containing the message are placed. It is taken relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

For information about setting the SQLRemote value, see Setting message type control parameters.

Grant PUBLISH and REMOTE at the consolidated database

In the hierarchical replication system supported by SQL Remote, each database may have one consolidated database immediately above it in the hierarchy and many databases immediately below it on the hierarchy (remote databases).

PUBLISH permission identifies the current database for outgoing messages, and the REMOTE permission identifies each database receiving messages from the current database.

Permissions can only be granted by a user with DBA authority. To carry out these examples you should connect using the Interactive SQL utility to hq as user ID DBA, with password SQL.

GRANT PUBLISH to identify outgoing messages

Each database that distributes its changes to other databases in the replication system is a publisher database. Each database in the replication system that publishes changes to a database is identified by a single user ID. You set that ID for your database using the GRANT PUBLISH statement. This section describes setting permissions for the consolidated database (hq.db).

To create a publisher for the database:

Connect to the database using Interactive SQL, and type the following statement:
```
GRANT CONNECT 
TO hq_user 
IDENTIFIED BY hq_pwd ;

GRANT PUBLISH TO hq_user ;
```

You can check the publishing user ID of a database at any time using the CURRENT PUBLISHER special constant:

SELECT CURRENT PUBLISHER

GRANT REMOTE for each database to which you send messages

Each remote database is identified using the GRANT REMOTE statement. Whether the remote database is a personal server or a network server with many users, it needs a single user ID to represent it to the consolidated database.

In a mobile workgroup setting, remote users may already be users of the consolidated database, and so this would require no extra action on the part of the DBA.

The GRANT REMOTE statement identifies the message system to be used when sending messages to the recipient, as well as the address.

To add a remote user:

Connect to the database using Interactive SQL, and enter the following statements:
```
GRANT CONNECT TO field_user 
IDENTIFIED BY field_pwd ;

GRANT REMOTE TO field_user 
TYPE file ADDRESS 'field' ;
```
The address string is the directory used to hold messages for field_user, enclosed in single quotes. It is taken relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

For information about setting the SQLRemote value, see Setting message type control parameters.

Create publications and subscriptions

A publication is created using a CREATE PUBLICATION statement. This is a data definition language statement, and requires DBA authority. For the tutorial, you should connect to the hq database as user ID DBA, password SQL, to create a publication.

Set up a publication at the consolidated database

Create a publication named SalesRepData, which replicates all rows of the table SalesRep, and some of the rows of the table Customer.

To create the publication:

Connect to the database from Interactive SQL, and enter the following statement:

CREATE PUBLICATION SalesRepData (
   TABLE SalesRep,
   TABLE Customer SUBSCRIBE BY rep_key 
)

Set up a subscription

Each user ID that is to receive changes to the publication must have a subscription. The subscription can only be created for a user who has REMOTE permissions. The GRANT REMOTE statement contains the address to use when sending the messages.

To create the subscription:

Connect to the database from Interactive SQL, and enter the following statement:
```
CREATE SUBSCRIPTION 
TO SalesRepData ('rep1')
FOR field_user ;
```
The value rep1 is the rep_key value we will give to the user field_user in the SalesRep table.

The full CREATE SUBSCRIPTION statement allows control over the data in subscriptions; allowing users to receive only some of the rows in the publication. For more information, see CREATE SUBSCRIPTION statement.

The CREATE SUBSCRIPTION statement identifies the subscriber and defines what they receive. However, it does not synchronize data, or start the sending of messages.