EM418 SQL Remote for Adaptive Server Anywhere Internals

EM418SQL Remote for Adaptive Server Anywhere Internals • Reg Domaratzki • Sustaining Engineering • iAnywhere Solutions • Reg.Domaratzki@sybase.com

Pre-Requisites Understanding Message Numbers The Example Database The Role of the Database Engine Receiving Messages Sending Messages Understanding SQL Remote Comments in DBTran Output EM418 - SQL Remote for Adaptive Server Anywhere Internals

RDBMS Concepts Publisher Remote User Consolidated User Publication Subscribe by Column Subscribe by Sub-query Subscriptions Update Publication Messaging System Resend Requests Passthrough Pre-Requisites For this talk, an assumption is made that the following SQL Remote Concepts are understood

Everything discussed in this presentation related to using SQL Remote against an ASA database Many of the concepts relate directly to using SQL Remote against an ASE database, but not all There is also an assumption that dbremote is always running in send the close mode, not continuous Assumptions

Pre-Requisites Message Numbers The Example Database The Role of the Database Engine Receiving Messages Sending Messages Understanding SQL Remote Comments in DBTran Output Where are We?

When two remote users are in synch, then the values in the SYSREMOTEUSER table on each side will reflect this with identical values for certain fields rem1 cons SYSREMOTEUSER Table

I. 06/26 17:25:12. Sybase SQL Remote Message Agent Version 7.0.2.1493 I. 06/26 17:25:12. I. 06/26 17:25:13. Scanning logs starting at offset 0000230000 I. 06/26 17:25:13. Processing transactions from active transaction log I. 06/26 17:25:13. Sending message to "rem2" (0-0000230000-0) I. 06/26 17:25:13. Sending message to "rem1" (2-0000230000-0) I. 06/26 17:25:13. Sending message to "rem1" (2-0000230000-1) I. 06/26 17:25:14. Execution completed The first number represents the resend count for the user The second number is the starting log of operations in the message The third number is used for multi-part messages Message Numbers

A Simple Example 1) Application inserts data on consolidated Modifies current log offset to 250,000 Consolidated Database 2) Dbremote runs against consolidated Sending message to "rem1" (2-0000230000-0) 4) Dbremote runs against consolidated Received message from ”rem1" (1-0000125000-0) Applying message from ”rem1" (1-0000125000-0) Sending message to "rem1" (2-0000250000-0) Remote Database rem1 3) Dbremote runs against rem1 Received message from "cons" (2-0000230000-0) Applying message from "cons" (2-0000230000-0) Sending message to "cons" (1-0000125000-0) 5) Dbremote runs against rem1 Received message from "cons" (2-0000250000-0) Applying message from "cons" (2-0000250000-0)

Pre-Requisites Understanding Message Numbers The Example Database The Role of the Database Engine Receiving Messages Sending Messages Understanding SQL Remote Comments in DBTran Output Where are We?

Each salesrep is in a certain region Many expenses are associated with each salesrep The Example Database Sales Representatives

A customer can have many contacts A customer can have many orders Each order is for a single product Each order has a single status The Example Database Customers

There is a many-to-many relationship between customers and salesreps The Example Database Link Table

create publication SalesRepData( table DBA.Product, table DBA.Region, table DBA.Salesrep, table DBA.Expense subscribe by salesrep_id, table DBA.Link subscribe by salesrep_id, table DBA.Customer subscribe by (select salesrep_id from link where link.customer_id = customer.customer_id), table DBA.Contact subscribe by (select salesrep_id from link,customer where link.customer_id = customer.customer_id and customer.customer_id = contact.customer_id), table DBA."Order" subscribe by (select salesrep_id from link,customer where link.customer_id = customer.customer_id and customer.customer_id = "order".customer_id), table DBA.Order_Status ) The Example Database Publication Definition

create trigger BI_Link before insert on Link referencing new as new_row for each row begin declare local temporary table Old_List( salesrep_id integer null ) on commit delete rows; insert into Old_List select distinct salesrep_id from Link where Link.customer_id = new_row.customer_id; update Customer publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List union select new_row.salesrep_id ) where Customer.customer_id = new_row.customer_id; update Contact publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List union select new_row.salesrep_id ) where Contact.customer_id = new_row.customer_id; update "Order" publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List union select new_row.salesrep_id ) where "Order".customer_id = new_row.customer_id end Example Database Update Publication Statements

alter trigger BD_Link before delete on Link referencing old as old_row for each row begin declare local temporary table Old_List( salesrep_id integer null ) on commit delete rows; insert into Old_List select distinct salesrep_id from Link where Link.customer_id = old_row.customer_id; update Customer publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List where salesrep_id <> old_row.salesrep_id ) where Customer.customer_id = old_row.customer_id; update Contact publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List where salesrep_id <> old_row.salesrep_id ) where Contact.customer_id = old_row.customer_id; update "Order" publication SalesRepData old subscribe by (select salesrep_id from Old_List) new subscribe by (select salesrep_id from Old_List where salesrep_id <> old_row.salesrep_id ) where "Order".customer_id = old_row.customer_id; end; Example Database Update Publication Statements

Log File Writes without Replication Extra Information Needed for Replication Examples Simple Insert, Update and Delete Updating a Subscribe By Column Update Publication Statement The Role of the Database Engine

When an insert, update or delete is executed against the database, the engine will first write this change to the end of the current log file The only information that needs to be written if replication is not involved is Which user made that change Which connection the change was made on (to maintain transactional integrity during recovery) What the actual SQL was that was executed Log File Writes without Replication

In addition to the standard information needed, the database engine writes additional information to the log file when changes are made to tables involved in replication A list of publications that the table belongs to, and optionally, which subscribe by values to the publication are needed for this change to be of interest A list of old subscribe by values that used to satisfy the data before the change A list of new subscribe by values that satisfy the data after the change occurred Extra Information Needed for Replication

Note that for the database engine to calculate the subscribe by values that are written to the log file, the subscribe by subquery on the article in the publication is executed In the case of complex subscribe by subqueries that join many tables, this could be a heavy load on the database engine Extra Information Needed for Replication

To determine which subscribe by values satisfy a given query, the engine will need to access SYSARTICLE : To determine if the table modified is involved in any publications SYSARTICLECOL : To determine if the column modified is involved in any publications SYSPUBLICATION : To determine if a subscribe by column or sub-query is used for the publication design System Tables Used

Product Table Insert --PUBLICATION--SalesRep_Data INSERT INTO Product values (121,’Widget’,1.00,100); Expense Table Update --PUBLICATION--SalesRep_Data--SUB_BY 1 UPDATE Expense SET amount=11.50 WHERE expense_id=141; Product Table Delete --PUBLICATION--SalesRep_Data DELETE FROM Product where product_id = 121; Example : Simple Inserts, Updates and Deletes

Updating salesrep_id on Expense Table --PUBLICATION-SalesRep_Data-NEW_SUB_BY 2 --PUBLICATION-SalesRep_Data-OLD_SUB_BY 1 --NEW--INSERT INTO dba.Expense --(expense_id,salesrep_id,description,amount) --VALUES (141,2,'Dinner',10) --OLD--DELETE FROM dba.Expense --WHERE expense_id=141 UPDATE dba.Expense SET salesrep_id=2 WHERE expense_id=141 Example : Updating a Subscribe By Column

A insert or delete in the link table will need to have a trigger that fires update publication statements to make sure that the child records for the customer are properly handled. The following slides show the log file entries for an insert into the link table insert into Link (salesrep_id, customer_id, date_assigned) values (1,4,CURRENT DATE); Example : Update Publication Statement

--UPDATE PUBLICATION-Customer --PUBLICATION-SalesRep_Data-NEW_SUB_BY 1 --PUBLICATION-SalesRep_Data-OLD_SUB_BY --NEW--INSERT INTO dba.Customer --(customer_id,company_name,address) --VALUES (4,’Company',’Address’) --OLD--DELETE FROM dba.Customer --WHERE customer_id=4 Example : Update Publication Statement (continued)

--UPDATE PUBLICATION-Contact --PUBLICATION-SalesRep_Data-NEW_SUB_BY 1 --PUBLICATION-SalesRep_Data-OLD_SUB_BY --NEW--INSERT INTO dba.Contact (contact_id, --contact_name,phone,email,customer_id) --VALUES (6,'Anil Goel',’phone',’mail',4) --OLD--DELETE FROM dba.Contact --WHERE contact_id=6 --PUBLICATION-SalesRep_Data-SUB_BY 1 INSERT INTO dba.Link (salesrep_id,customer_id,date_assigned) VALUES (1,4,'2001/jul/05 00:00') Example : Update Publication Statement (continued)

System Tables Used Common Errors when Receiving Messages Receiving Messages

dbremote will first consult the SYSREMOTETYPE table to determine the address and message type for the publisher of the database In order to determine how to pick up messages through the messaging system, dbremote may have to consult SYSREMOTEOPTION and SYSREMOTEOPTIONTYPE Receiving Messages System Tables Used

For each message that is picked up during the receive stage, dbremote consults the SYSREMOTEUSER table to check the following The user that sent the message is in fact a remote user for this database The starting log offset of the message matches the log_received for the remote user The resend_count of the message matches the rereceive_count for the remote user Receiving Messages System Tables Used (continued)

For each transaction that is successfully applied in a message, the following occurs The log_received column for the remote user is increased to the offset that follows the successful commit The time_received column for the remote user is updated to the time that the last successful transaction was applied Receiving Messages System Tables Used (continued)

If a resend request is received from a remote user, then dbremote will set the value of log_sent in the SYSREMOTEUSER table to the value that the user has requested a resend from I’ll explain why this works when we get to the sending phase of dbremote Receiving Messages System Tables Used (continued)

“rem1” is not a remote user for this database A message has been received from a remote user that does not have an entry in the SYSREMOTEUSER table. Common Errors when Receiving Messages

Missing message(s) from “rem1” The log_received in the SYSREMOTEUSER table for remote user rem1 is 200,000 There are two possibilities There are messages in the inbox whose starting log offset is greater than 200,000, but there is no message that begins with offset 200,000 One part of a multi-part message is missing dbremote will increase the resend count by one and ask the remote user to resend the data Common Errors when Receiving Messages

Not applying messages with old resend count The rereceive_count in the SYSREMOTEUSER table is is higher than the resend count in the message that was just received This is most likely a lost message that has finally made it’s way to the right place, but a resend request has already been requested Common Errors when Receiving Messages

Not applying messages that have already been applied The confirm_received in the SYSREMOTEUSER table is greater than the log offset of the message being received Sometimes seen when dbremote is run after a user has been re-extracted and there were messages from the old remote user in the messaging system Common Errors when Receiving Messages

This message does not belong to me A confirmation message is being received from a remote user confirming a log offset of 250,000, but the current log offset is only 200,000 dbremote will assume that the message was meant for someone else, and reject the message Common Errors when Receiving Messages

If the message system for a given user is different than the message system used in the receiving stage, or if dbremote is running in send mode only, then dbremote will need to consult the SYSREMOTEOPTION and SYSREMOTEOPTIONTYPES tables to connect to the messaging system The message system used for each remote user is stored in SYSREMOTEUSER Sending Messages System Tables Used

dbremote will use the SYSREMOTEUSER table to determine what log offset to start sending messages from The minimum value of log_sent is used as the starting point log_send and log_sent are often confused When a message is generated and sent to a user, both the log_send and log_sent are set to the ending log offset of the message When a resend request is received the log_sent value is set to the log offset to resend from Sending Messages System Tables Used

For each operation in the log file, the operation is sent to a remote user if the following conditions are all true An entry exists in the SYSSUBSCRIPTION table stating that the user is subscribed to the publication that has been written to the log file for that operation The subscribe by value specified in the SYSSUBSCRIPTION table matches the subscribe by value (if it exists) written in the log file for that operation The created and started log offsets in SYSSUBSCRIPTION are less than the current log offset The log_sent value for the remote user in SYSREMOTEUSER is less than the current log offset Sending Messages System Tables Used

If the operation involved updating a subscribe by value, or an update publication, then an insert is sent to a remote user if the following are all true An entry exists in the SYSSUBSCRIPTION table stating that the user is subscribed to the publication that has been written to the log file The subscribe by value specified in the SYSSUBSCRIPTION table is not in the old sub_by list, but is in the new sub_by list written in the log file The created and started log offsets in SYSSUBSCRIPTION are less than the current log offset The log_sent value for the remote user in SYSREMOTEUSER is less than the current log offset Sending Message System Tables Used an update a delete The subscribe by value specified in the SYSSUBSCRIPTION table is in the old sub_by list, but is not in the new sub_by list written in the log file The subscribe by value specified in the SYSSUBSCRIPTION table is in the old sub_by list, and is also in the new sub_by list written in the log file

--201--PUBLICATION--SalesRep_Data INSERT INTO Product values (121,’Widget’,1.00,100); --204--PUBLICATION--SalesRep_Data--SUB_BY 1 UPDATE Expense SET amount=11.50 WHERE expense_id=141; --205--PUBLICATION--SalesRep_Data DELETE FROM Product where product_id = 121; Sending Messages Examples SYSREMOTEUSERS SYSSUBSCRIPTIONS TRANSACTION LOG

--207--PUBLICATION-SalesRep_Data-NEW_SUB_BY 2 --207--PUBLICATION-SalesRep_Data-OLD_SUB_BY 1 --207--NEW--INSERT INTO dba.Expense --VALUES (141,2,'Dinner',10) --207--OLD--DELETE FROM dba.Expense --WHERE expense_id=141 UPDATE dba.Expense SET salesrep_id=2 WHERE expense_id=141 Sending Messages Examples SYSREMOTEUSERS SYSSUBSCRIPTIONS TRANSACTION LOG

--208--UPDATE PUBLICATION-Customer --208--PUBLICATION-SalesRep_Data-NEW_SUB_BY 1 --208--PUBLICATION-SalesRep_Data-OLD_SUB_BY --208--NEW--INSERT INTO dba.Customer --(customer_id,company_name,address) --VALUES (4,’Company',’Address’) --208--OLD--DELETE FROM dba.Customer --WHERE customer_id=4 Sending Messages Examples SYSREMOTEUSERS SYSSUBSCRIPTIONS TRANSACTION LOG

--208--UPDATE PUBLICATION-Contact --208--PUBLICATION-SalesRep_Data-NEW_SUB_BY 1 --208--PUBLICATION-SalesRep_Data-OLD_SUB_BY --208-- NEW--INSERT INTO dba.Contact --VALUES (6,'Ani Goel',’phone',’mail',4) --208--OLD--DELETE FROM dba.Contact --WHERE contact_id=6 --208--PUBLICATION-SalesRep_Data-SUB_BY 1 INSERT INTO dba.Link VALUES (1,4,'2001/jul/05 00:00') Sending Messages Examples SYSREMOTEUSERS SYSSUBSCRIPTIONS TRANSACTION LOG

When DBTran is run with the –sr switch, extra comments are added into the output file Comments are of the general form --OP-CON_ID-OFFSET[-DATA] Some common short forms in the preceding section to conserve space will be OP = Operation Type CON_ID = Connection ID OFFSET = Current Transaction Log Offset Understanding SQL Remote Comments in DBTran Output

--CONNECT-CON_ID-OFFSET-USER-TIME CON_ID : Will hold the connection ID of this connection that will be used in subsequent entries in the translated log USER : The database user that connected at this time TIME : The time that the connection occurred, according to the local system clock Example : --CONNECT-1004-0000181569-cons-2001/may/30 16:02 Connect Operations

--CHECKPOINT-0000-OFFSET-TIME 0000 : The engine performs the checkpoint, and since it is not part of any transaction, is not associated with any current connection Example : --CHECKPOINT-0000-0000182054-2001/may/30 16:09 Checkpoint Operations

EM418 SQL Remote for Adaptive Server Anywhere Internals