Introduction

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

Introduction

This chapter presents a tutorial to lead you through setting up a SQL Remote installation. The installation replicates data between an Adaptive Server Enterprise database (the consolidated database) and an Adaptive Server Anywhere database (the remote database).

Tutorial goals

In the tutorial you act as the system administrator of a consolidated Adaptive Server Enterprise database, and set up a simple replication system. The replication system consists of a simple sales database, with two tables.

The consolidated database holds all of the database, while the remote database has all of one table, but only some of the rows in the other table.

The tutorial takes you through the following steps:

Creating a consolidated database on your Adaptive Server Enterprise server.
Creating a file-sharing replication system with a single Adaptive Server Anywhere remote database.
Replicating data between the two databases.

The database

The tutorial uses a simple two-table database. One table holds information about sales representatives, and the other about customers. The tables are much simpler than you would use in a real database; this allows us to focus just on those issues important for replication.

Database schema

The database schema for the tutorial is illustrated in the figure.

Features to note include the following:

Each sales representative is represented by one row in the SalesRep table.
Each customer is represented by one row in the customer table.
Each customer is assigned to a single Sales representative, and this assignment is built in to the database as a foreign key from the Customer table to the SalesRep table. The relationship between the Customer table and the SalesRep table is many-to-one.

The tables in the database

The tables are described in more detail as follows:

Table

Description

SalesRep

One row for each sales representative that works for the company. The SalesRep table has the following columns:

rep_key     An identifier for each sales representative. This is the primary key.

name     The name of each sales representative.

The SQL statement creating this table is as follows:

CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) )

Customer

One row for each customer that does business with the company. The Customer table includes the following columns:

cust_key     An identifier for each customer. This is the primary key.

name     The name of each customer.

rep_key     An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table.

The SQL statement creating this table is as follows:

CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY ( rep_key ) REFERENCES SalesRep (rep_key ), PRIMARY KEY (cust_key) )

Table	Description
SalesRep	One row for each sales representative that works for the company. The SalesRep table has the following columns: rep_key An identifier for each sales representative. This is the primary key. name The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) )
Customer	One row for each customer that does business with the company. The Customer table includes the following columns: cust_key An identifier for each customer. This is the primary key. name The name of each customer. rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table. The SQL statement creating this table is as follows: CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY ( rep_key ) REFERENCES SalesRep (rep_key ), PRIMARY KEY (cust_key) )

Replication goals

The goals of the replication design are to provide each sales representative with the following information:

The complete SalesRep table.
Those customers assigned to them.

The tutorial describes how to meet this goal using SQL Remote.

Use Sybase Central or the command line

The tutorial material is presented twice. One section describes how to set up the installation using the Sybase Central management utility. The second section describes how to set up the installation using the Adaptive Server Enterprise and Adaptive Server Anywhere interactive SQL utilities: this requires typing commands individually.

Where next?

To work through the tutorial using Sybase Central, go to Setting up SQL Remote using Sybase Central.
To work through the tutorial entering commands explicitly, go to A tutorial using isql and ssxtract.