Lesson 4: Adding and replicating data in the consolidated and remote databases

In this lesson, you add data to the consolidated and remote databases, run SQL Remote to replicate the changes, and then confirm that the data is consistent in both databases.

 Add data to the consolidated database
  1. If you are not currently connected to the consolidated database (cons), run the following command:

    dbisql -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql"
  2. Execute the following statements on the consolidated database (cons) to add additional sample data to the employees table:

    INSERT INTO employees (first_name, last_name) VALUES ('Javier', 'Spoor');
    COMMIT;
  3. Disconnect from Interactive SQL.

 Add data to the remote database
  1. If you are not currently connected to the remote database (rem), run the following command:

    dbisql -c "SERVER=rem;DBN=rem;UID=DBA;PWD=sql"
  2. Execute the following statements on the remote database (rem) to add additional sample data to the employees table:

    INSERT INTO employees (first_name, last_name) VALUES ('Nelson', 'Kreitzer');
    COMMIT;
  3. Disconnect from Interactive SQL.

 Replicate changes between the consolidated and remote databases
  1. At the consolidated database (cons), run the Message Agent:

    dbremote -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql" -qc -v -o c:\tutorial\cons1.txt

    This scans the transaction log of the consolidated database (cons) and generates a message for the remote database (rem) using the FILE message system. Since the debug message system parameter has been set for the FILE message system in the consolidated database, you can look at the c:\tutorial\cons1.txt file and verify that you see debug messages indicating that messages are being written to the c:\tutorial\messages\rem directory. For example:

    I. 2011-03-25 11:03:31. Processing transactions from active transaction log
    I. 2011-03-25 11:03:31. Sending message to "rem" (0-0000000000-0000550994-0)
    I. 2011-03-25 11:03:31. sopen "c:\tutorial\messages\rem\cons.0"
    I. 2011-03-25 11:03:31. write " c:\tutorial\messages\rem\cons.0"
    I. 2011-03-25 11:03:31. close " c:\tutorial\messages\rem\cons.0"
  2. At the remote database (rem), run the Message Agent:

    dbremote -c "SERVER=rem;DBN=rem;UID=DBA;PWD=sql" -qc -v -o c:\tutorial\rem.txt

    Using the HTTP messaging system, this command receives and applies the message that was just generated by the consolidated database. It then scans the transaction log and sends a message back to the consolidated database with the new row that was added in the remote database. Since the debug message system parameter has been set for the HTTP message system in the remote database, you can look at the c:\tutorial\rem.txt file and verify that you see debug messages indicating that the HTTP message system is being used. For example:

    I. 2011-03-25 11:10:02. Sending message to "cons" (0-0000000000-0000557411-0)
    I. 2011-03-25 11:10:02. HTTPWriteMessage "rem.0"
    I. 2011-03-25 11:10:02. HTTPWriteMessage: success -- filename  "rem.0"
    I. 2011-03-25 11:10:02. HTTPDisconnect
  3. At the consolidated database (cons), run the Message Agent:

    dbremote -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql" -qc -v -o c:\tutorial\cons2.txt

    This command receives and applies the message that was just generated by the remote database using the FILE-based message system.

 Verify the data at the consolidated and remote databases
  1. If you are not currently connected to the consolidated database (cons), run the following command:

    dbisql -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql"
  2. To verify that the consolidated database contains all four rows of data, execute the following statement to view the contents of the employees table:

     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:

    employee_id first_name last_name hire_date
    1 Kelly Meloy 2011-03-25 08:27:56.310
    2 Melisa Boysen 2011-03-25 08:27:56.310
    3 Javier Spoor 2011-03-25 08:30:26.110
    102000001 Nelson Kreitzer 2011-03-25 08:31:51.970
  3. Disconnect from Interactive SQL.

  4. If you are not currently connected to the remote database (rem), run the following command:

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

    Verify that the remote database (rem) contains all four rows of data by executing the following statement to view the contents of the employees table:

    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:

    employee_id first_name last_name hire_date
    1 Kelly Meloy 2011-03-25 08:27:56.310
    2 Melisa Boysen 2011-03-25 08:27:56.310
    3 Javier Spoor 2011-03-25 08:30:26.110
    102000001 Nelson Kreitzer 2011-03-25 08:31:51.970
  5. Disconnect from Interactive SQL.

  6. Proceed to Lesson 5: Cleaning up.