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

A tutorial using Interactive SQL and DBXTRACT

The following sections are a tutorial describing how to set up a simple SQL Remote replication system for users without Windows 95 or Windows NT 3.51 or later, who cannot run Sybase Central. It may also be useful to users of Sybase Central who prefer to use command-line tools or who want to know what Sybase Central is doing behind the scenes.

This tutorial describes the SQL statements for managing SQL Remote, which can be run from Interactive SQL. It also describes how to run the dbxtract command-line utility to extract remote databases from a consolidated database.

In this tutorial you act as the DBA of the consolidated database, and set up a simple replication system using the file-sharing message link. The simple example is a primitive model for a sales-force automation system, with two tables. One contains a list of sales representatives, and another a list of customers. The tables are replicated in a setup with one consolidated database and one remote database. You can install this example on one computer.

  Preparing for the replication tutorial

This section describes the steps you need to take to prepare for the tutorial. These steps include the following:

• Create the directories and databases required for the tutorial.

• Add a table to the consolidated database.

  To create the databases and directories for the tutorial:

1. Create a directory to hold the files you make during this tutorial; for example c:\tutorial.

   mkdir c:\tutorial

2. The tutorial uses two databases: a consolidated database named hq.db and a remote database named field.db. Change to the tutorial directory and create these databases using the following statements at a command line:

   dbinit hq.db
   dbinit field.db

   The database initialization tool for Windows 3.x is dbinitw rather than dbinit. Under Windows 3.x, you should execute the commands from the Program Manager File->Run menu or you could make an icon for each command.

3. Create a subdirectory for each of the two user IDs in the replication system. Create these subdirectories using the following statements at a command line:

   mkdir c:\tutorial\hq
   mkdir c:\tutorial\field

The next step is to add a pair of tables to the consolidated database.

  To add the tables to the consolidated database:

1. Connect to hq.db from Interactive SQL as user ID DBA, using password SQL.

   • Type CONNECT in the Interactive SQL Command window, and click Execute.

   • Enter the user ID DBA and the password SQL, and click OK.

   • If the hq database is already running, type hq in the Database Name box. If it is not running, enter the Database File c:\tutorial\hq.db. Then click OK to connect.

2. Enter the following CREATE TABLE statement to create the SalesRep table:

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

3. Enter the following CREATE TABLE statement to create the Customer table:

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

You are now ready for the rest of the tutorial.