Create the following directories to hold the consolidated database, the remote database, and the message server database:

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

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

Using Interactive SQL, connect to the consolidated database (cons) as a user with DBA authority, and ensure that you leave the database running when you disconnect by specifying AutoStop=NO for the AutoStop connection parameter:

dbisql -c "UID=DBA;PWD=sql;SERVER=cons;DBF=c:\tutorial\cons\cons.db;autostop=no"

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 that replicates, 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 not assigned passwords, so while the users exist in the database, you cannot connect to the database with these users. 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;
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: Creating the message server.