Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Remote » Tutorial: Setting up a replication system using the HTTP message system and the consolidated database as the message server via Relay Server

 

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

Add data to the consolidated and remote database, run SQL Remote to replicate the changes, and then confirm that the data is consistent in both databases.

Prérequis

You must have the SYS_REPLICATION_ADMIN_ROLE system role.

 Task
  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. 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;
  4. 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-04-12 09:33:03. Processing transactions from active transaction log
    I. 2011-04-12 09:33:03. Sending message to "rem" (0-0000000000-0000550994-0)
    I. 2011-04-12 09:33:03. sopen "c:\tutorial\messages\rem\cons.0"
    I. 2011-04-12 09:33:03. write " c:\tutorial\messages\rem\cons.0"
    I. 2011-04-12 09:33:03. close " c:\tutorial\messages\rem\cons.0"
  5. 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 message 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-04-12 09:34:03. Sending message to "cons" (0-0000000000-0000576448-0)
    I. 2011-04-12 09:34:03. HTTPWriteMessage "rem.0"
    I. 2011-04-12 09:34:03. HTTPWriteMessage: success -- filename  "rem.0"
    I. 2011-04-12 09:34:03. HTTPDisconnect
  6. You could also confirm that the request went through the Relay Server by looking at the output file generated by the RSOE and verifying that information is being printed to the log.

    I. 2011-04-12 09:34:03. <UpChannel-0000> PacketRead packet-len:257
    I. 2011-04-12 09:34:03. <UpChannel-0000> PacketRead packet-opcode:0xf004
    I. 2011-04-12 09:34:03. <UpChannel-0000> packet read..
    I. 2011-04-12 09:34:03. <UpChannel-0000> successful packet read.. processing it..
    I. 2011-04-12 09:34:03. <UpChannel-0000> 259 RS_CLI_SESSION_BEGIN(snum=0006 sfp=4e0e5291 ridx=0)
    I. 2011-04-12 09:34:03. <UpChannel-0000> Notifying worker thread
    
  7. 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.

  8. 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
  9. 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 data 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
  10. Disconnect from Interactive SQL.

Résultat

Data is added to the consolidated and remote database, the changes are replicated, and the data consistency has been verified.

Suivant

Proceed to Lesson 6: Cleaning up.