Create the following directories to hold the consolidated database and the remote database:

• c:\tutorial
• c:\tutorial\cons
• c:\tutorial\rem

Create the following directories to hold the message files generated by the consolidated database and the remote database:

• c:\tutorial\messages
• c:\tutorial\messages\cons
• c:\tutorial\messages\rem

From the c:\tutorial\cons directory, run the following command to create the consolidated database (cons):

dbinit cons.db

Start the consolidated database:

dbsrv12 -n cons c:\tutorial\cons\cons.db -xs http(port=8033)

-xs http(8033) is required on the command line, because this is the database server that will be accepting HTTP requests from the remote database and accessing the messages files that exist in the c:\tutorial\messages directory. While no web services are defined at the time you start the database server, they are created in the next lesson. In this lesson, you only start the personal database server, so only SQL Remote processes on this computer are able to communicate with the message server using HTTP. In a production environment, you would typically use the network server so that SQL Remote processes on other computers would also have access to the web services. You have started a network server in this lesson and named it cons. If there is another database server in your network already running with this name, you must choose a different name for the network server and modify the connection strings in the remainder of this tutorial to use the alternative name. For more information about using -xs, see -xs dbeng12/dbsrv12 server option.

Using Interactive SQL, connect to the consolidated database (cons) as a user with DBA authority:

dbisql -c "UID=DBA;PWD=sql;SERVER=cons;DBN"

To set the global database ID for the consolidated database (cons), execute the following statement (the global database ID is needed so that distinct primary keys are chosen for all databases when using the GLOBAL AUTOINCREMENT default):

SET OPTION public.global_database_id=0;

The schema for the database in this tutorial consists of a single table and all the columns and rows from the table replicate to every remote user. Execute the following statements on the consolidated database (cons) to create the single table in the database:

CREATE TABLE employees (
     employee_id BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000) PRIMARY KEY,
     first_name VARCHAR(128) NOT NULL,
     last_name VARCHAR(128) NOT NULL,
     hire_date TIMESTAMP NOT NULL DEFAULT TIMESTAMP
);

Execute the following statements on the consolidated database (cons) to add sample data to the employees table:

INSERT INTO employees (first_name, last_name) VALUES ('Kelly', 'Meloy');
INSERT INTO employees (first_name, last_name) VALUES ('Melisa', 'Boysen');
COMMIT;

Execute the following statement on the consolidated database (cons) to confirm that the table was created and populated with data:

SELECT * FROM employees;

The query returns the following data from the employees table, although the hire_date column contains the time you inserted the row, and not the values you see in the following table:

In this tutorial, the publisher and remote users are assigned passwords because the consolidated database acts as the message server for the HTTP message system. Execute the following statements to create the user cons that has CONNECT and PUBLISH permissions:

GRANT CONNECT TO cons;
GRANT PUBLISH TO cons;

For performance reasons, the HTTP message system can only be used at the remote database, and not at the consolidated. The following statements configure the use of the FILE-based message system at the consolidated database:

CREATE REMOTE MESSAGE TYPE FILE ADDRESS 'cons';
SET REMOTE FILE OPTION public.directory='c:\\tutorial\\messages';
SET REMOTE FILE OPTION public.debug='yes'; 

Execute the following statements to create the remote user rem without a password, and then grant REMOTE permissions, while defining the user's address in the FILE message system:

GRANT CONNECT TO rem IDENTIFIED BY rem;
GRANT REMOTE TO rem TYPE FILE ADDRESS 'rem';

A publication describes the set of data to be replicated. Create a publication named pub_employees that replicates all rows of the employees table. You subscribe a user to a publication by creating a subscription.

CREATE PUBLICATION pub_employees ( TABLE employees );
CREATE SUBSCRIPTION TO pub_employees FOR rem;

Disconnect from Interactive SQL.

Proceed to Lesson 2: Configuring the Relay Server.