Lesson 5: Extracting the remote database

In this lesson you create a database for a remote user by extracting the remote database from the consolidated database (hq).

The remote database must be configured to send and receive messages and participate in a SQL Remote system. Like the consolidated database (hq), the remote database needs a CURRENT PUBLISHER to identify the source of outgoing messages. It also needs to have the consolidated database (hq) identified as a subscriber.

Run the dbxtract utility to create a remote database that contains:

  • a subscription to the consolidated database

  • a publication

  • a current copy of the data

 Extract the remote database

  1. Extract the remote database schema from the consolidated database (hq) for the user field_user by running the following command from the c:\tutorial directory: 

    dbxtract -v -c "UID=DBA;PWD=sql;SERVER=server_hq;DBF=C:\tutorial\hq.db" c:\tutorial field_user

    This command:

    • Creates a SQL script file named reload.sql in the current directory. The reload.sql file contains the schema and instructions to load it into a new database.

    • Creates a data file in the c:\tutorial directory.

    • Starts the subscriptions to the remote user.

  2. From the c:\tutorial directory, run the following command to create the remote database (field): 

    dbinit field.db
    Caution

    In a production environment, do not store two replicating databases in the same directory.

  3. Load the database information into the remote database (field).

    Connect to the remote database (field) from Interactive SQL as a user with DBA authority.

    dbisql -c "UID=DBA;PWD=sql;SERVER=server_field;DBF=c:\tutorial\field.db"

  4. Execute the following statement to read the reload.sql file: 

    READ C:\tutorial\reload.sql;

    The reload.sql script file:

    • Creates a message type at the remote database (field).

    • Grants PUBLISH permission to the remote database (field).

    • Creates the SalesReps and Customers tables in the remote database (field). These tables contain the same data as in the consolidated database (hq).

    • Creates a publication to identify the data being replicated.

    • Creates the subscription for the consolidated database (hq), and starts the subscription.

  5. Execute the following statements to confirm that the tables were created:

    SELECT * FROM SalesReps;

    The above query returns the following data from the SalesReps table:

    rep_key name
    rep1 Field User
    rep2 Another User 
    SELECT * FROM Customers;

    The above query returns the following data from the Customers table:

    cust_key name rep_key
    cust1 Ocean Sports rep1
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1