Transcript Techwave_2001_EM418-_Reg_Domaratzki

EM418
SQL Remote for Adaptive
Server Anywhere Internals
Reg Domaratzki
Sustaining Engineering
iAnywhere Solutions
[email protected]
EM418 - SQL Remote for
Adaptive Server Anywhere
Internals
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
Pre-Requisites
For this talk, an assumption is made that the following
SQL Remote Concepts are understood
•RDBMS Concepts
•Publisher
•Remote User
•Consolidated User
•Publication
•Subscribe by Column
•Subscribe by Sub-query
•Subscriptions
•Update Publication
•Messaging System
•Resend Requests
•Passthrough
Assumptions
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
Where are We?
Pre-Requisites
Message Numbers
The Example Database
The Role of the Database Engine
Receiving Messages
Sending Messages
Understanding SQL Remote Comments in
DBTran Output
SYSREMOTEUSER Table
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
cons
user_id
log_sent
confirm_sent
resend_count
log_received
confirm_received
rereceive_count
rem1
250,000
250,000
2
130,000
130,000
1
rem1
user_id
log_sent
confirm_sent
resend_count
log_received
confirm_received
rereceive_count
cons
130,000
130,000
1
250,000
250,000
2
Message Numbers
I.
I.
I.
I.
I.
I.
I.
I.
06/26
06/26
06/26
06/26
06/26
06/26
06/26
06/26
17:25:12.
17:25:12.
17:25:13.
17:25:13.
17:25:13.
17:25:13.
17:25:13.
17:25:14.
Sybase SQL Remote Message Agent Version 7.0.2.1493
Scanning logs starting at offset 0000230000
Processing transactions from active transaction log
Sending message to "rem2" (0-0000230000-0)
Sending message to "rem1" (2-0000230000-0)
Sending message to "rem1" (2-0000230000-1)
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
A Simple Example
Consolidated Database
user_id
log_sent
confirm_sent
resend_count
log_received
confirm_received
rereceive_count
rem1
230,000
250,000
230,000
250,000
2
125,000
130,000
125,000
130,000
1
1) Application inserts data on consolidated
Modifies current log offset to 250,000
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)
Messages
cons
rem1
rem1.7
cons.10
cons.11
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)
Remote Database rem1
user_id
log_sent
confirm_sent
resend_count
log_received
confirm_received
rereceive_count
cons
125,000
130,000
125,000
130,000
1
230,000
250,000
230,000
250,000
2
Where are We?
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
The Example Database
Sales Representatives
Each salesrep is in
a certain region
Many expenses are
associated with
each salesrep
The Example Database
Customers
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
Link Table
There is a many-to-many relationship
between customers and salesreps
The Example Database
Publication Definition
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
)
Example Database
Update Publication Statements
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;
Where are We?
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
The Role of the Database Engine
Log File Writes without Replication
Extra Information Needed for Replication
Examples
Simple Insert, Update and Delete
Updating a Subscribe By Column
Update Publication Statement
Log File Writes without
Replication
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
Extra Information Needed for
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
System Tables Used
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
Example : Simple Inserts,
Updates and Deletes
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 : Updating a Subscribe
By Column
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 : Update Publication
Statement
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 (continued)
--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')
Where are We?
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
Receiving Messages
System Tables Used
Common Errors when Receiving
Messages
Receiving Messages
System Tables Used
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 (continued)
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
Common Errors when Receiving
Messages
“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
Where are We?
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
Sending Messages
System Tables Used
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 Message
System Tables Used
If the operation involved updating a
subscribe by value, or an update
publication, then an
an
insert is sent to a
a update
delete
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
in the
the sub_by
old sub_by
list, is
but
is in
ininthe
the
new
sub_by
list
written
ininthe
the
log
file
old
list, and
but
is
not
also
thenew
newsub_by
sub_bylist
listwritten
writtenin
thelog
logfile
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 Messages
Examples
SYSREMOTEUSERS
SYSSUBSCRIPTIONS
user_name
rem1
rem2
consolidate
N
N
type_name
FILE
FILE
address
rem1
rem2
frequency
A
A
send_time
(NULL)
(NULL)
next_send
(NULL)
(NULL)
log_send
200
200
time_sent
7/12/00 13:45 7/12/00 13:45
log_sent
200
200
confirm_sent
200
200
send_count
6
6
resend_count
0
0
time_received
7/12/00 13:44 7/12/00 13:44
log_received
100
100
confirm_received
100
100
receive_count
1
1
rereceive_count
0
0
publication_name user_name subscribe_by created started
SalesRepData
rem1
1
150
150
SalesRepData
rem2
2
150
150
TRANSACTION LOG
--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
user_name
rem1
rem2
consolidate
N
N
type_name
FILE
FILE
address
rem1
rem2
frequency
A
A
send_time
(NULL)
(NULL)
next_send
(NULL)
(NULL)
log_send
200
200
time_sent
7/12/00 13:45 7/12/00 13:45
log_sent
200
200
confirm_sent
200
200
send_count
6
6
resend_count
0
0
time_received
7/12/00 13:44 7/12/00 13:44
log_received
100
100
confirm_received
100
100
receive_count
1
1
rereceive_count
0
0
publication_name user_name subscribe_by created started
SalesRepData
rem1
1
150
150
SalesRepData
rem2
2
150
150
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
user_name
rem1
rem2
consolidate
N
N
type_name
FILE
FILE
address
rem1
rem2
frequency
A
A
send_time
(NULL)
(NULL)
next_send
(NULL)
(NULL)
log_send
200
200
time_sent
7/12/00 13:45 7/12/00 13:45
log_sent
200
200
confirm_sent
200
200
send_count
6
6
resend_count
0
0
time_received
7/12/00 13:44 7/12/00 13:44
log_received
100
100
confirm_received
100
100
receive_count
1
1
rereceive_count
0
0
publication_name user_name subscribe_by created started
SalesRepData
rem1
1
150
150
SalesRepData
rem2
2
150
150
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
user_name
rem1
rem2
consolidate
N
N
type_name
FILE
FILE
address
rem1
rem2
frequency
A
A
send_time
(NULL)
(NULL)
next_send
(NULL)
(NULL)
log_send
200
200
time_sent
7/12/00 13:45 7/12/00 13:45
log_sent
200
200
confirm_sent
200
200
send_count
6
6
resend_count
0
0
time_received
7/12/00 13:44 7/12/00 13:44
log_received
100
100
confirm_received
100
100
receive_count
1
1
rereceive_count
0
0
publication_name user_name subscribe_by created started
SalesRepData
rem1
1
150
150
SalesRepData
rem2
2
150
150
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')
Where are We?
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
Understanding SQL Remote
Comments in DBTran Output
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
Connect Operations
--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
Checkpoint 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
SQL Operations
--SQL-CON_ID-OFFSET
Operations that are executed against the
database that do not affect data, but
instead the definition of the database
structure are logged with SQL Operations
Examples :
--SQL-1001-0000175076
set option PUBLIC.Delete_old_logs = 'On'
--SQL-1002-0000175716
create remote type FILE address 'cons'
Commit, Rollback and
Transaction Operations
Transactions can be traced in the log file by
finding the begin transactions and associated
commits and rollbacks for a given connection
Examples :
--BEGIN TRANSACTION-1001-0000175075
--BEGIN TRANSACTION-1002-0000175695
--COMMIT-1001-0000175826
--ROLLBACK-1002-0000182180
Insert, Update and Delete
Operation
Insert, update and delete operations are
flagged with a special comment in the
translated log file to track which
connection ID performed the operation
Examples :
--INSERT-1001-0000175367
--UPDATE-1001-0000175702
--DELETE-1002-0000182022
Trigger Operations
A note is logged in the transaction log when an operation
occurs in a trigger, since these actions are handled
differently by SQL Remote
Example :
--CONNECT-1002-0000187185-dba-2001/jun/07 10:57
--BEGIN TRANSACTION-1002-0000187197
--BEGIN TRIGGER-1002-0000187223
--UPDATE-1002-0000187224
UPDATE region SET description=‘Waterloo_67’
WHERE region_id=67
--END TRIGGER-1002-0000187242
--INSERT-1002-0000187243
INSERT INTO region VALUES (100,‘Waterloo')
--COMMIT-1002-0000187274
Subscription Operations
--SUBSCRIPTION-CON_ID-OFFSET-PUB_ID-ACTION-SUB_BY
PUB_ID : The publication ID associated with this
subscription operation
ACTION : Always just says “action”. Because of
the way subscription information is logged, you
must look at the next operation in the log file
to determine what actually occurred.
SUB_BY : The subscribe by value associated with
the action (if needed)
Subscription Operations
Example:
--SUBSCRIPTION-1002-0000205972-0001-ACTION-2
--BEGIN TRANSACTION-1002-0000205989
BEGIN TRANSACTION
--SQL-1002-0000205990
start subscription to SalesRepData('2') for rem1
--COMMIT-1002-0000206042
COMMIT WORK
Publication Operations
--PUBLICATION-CON_ID-OFFSET-PUB_ID-TYPE[-SUB_BY]
Every time data is modified in a table that is a
member of a publication, a publication
comment(s) is written to the log file to identify
what actions need to be taken by dbremote
The type column indicates which users will be
interested in the operation to follow
Publication Operations
Type can be one of six values
SUBSCRIBE – Any remote user subscribed to the publication will
receive this operation
SUBSCRIBE_BY – Any remote user subscribed by the SUB_BY
value(s) will receive this operation
NEW_SUBSCRIBE_BY – The subscribe by list after the operation
on the subscribe by column completes
OLD_SUBSCRIBE_BY – The subscribe by list before the operation
on the subscribe by column completes
NEW_SUBSCRIBE – The subscribe by list after the operation that
modifies the condition in the WHERE clause on the publication
OLD_SUBSCRIBE - The subscribe by list before the operation that
modifies the condition in the WHERE clause on the publication
New and Old Operations
--NEW-CON_ID-OFFSET
--OLD-CON_ID-OFFSET
These statements are immediately followed by
commented SQL statements resulting from an
update operation causing an insert or delete
operation for this publication
The publication is identified by the most recent
PUBLICATION statement containing either
NEW_SUBSCRIBE, OLD_SUBSCRIBE,
NEW_SUBSCRIBE_BY or OLD_SUBSCRIBE_BY
in its type field
New and Old Operations
Example :
--PUBLICATION-1006-0000205694-0001-NEW_SUBSCRIBE_BY-2
--PUBLICATION-1006-0000205694-0001-OLD_SUBSCRIBE_BY-1
--NEW-1006-0000205694
--INSERT INTO expense
--VALUES (109,2,‘Golf Match’,109.80)
--OLD-1006-0000205694
--DELETE FROM expense
--WHERE expense_id=109
--UPDATE-1006-0000205694
UPDATE expense SET salesrep_id=2 WHERE expense_id=109
Update Publication Operations
--UPDATE PUBLICATION-CON_ID-OFFSET TABLE
TABLE : The table name affected by the update
publication
An update publication looks almost exactly the
same in a translated log as an update the
modifies the subscribe by column
The difference is that there is no actual update
statement in the log file, and an UPDATE
PUBLICATION comment is placed before the
two PUBLICATION comments
Update Publication Operations
Example :
--BEGIN TRIGGER-1006-0000205657
--UPDATE PUBLICATION-1006-0000205659 customer
--PUBLICATION-1006-0000205659-0001-NEW_SUBSCRIBE_BY-1,2
--PUBLICATION-1006-0000205659-0001-OLD_SUBSCRIBE_BY-1
--NEW-1006-0000205659
--INSERT INTO customer
--VALUES (123,’iAnywhere Solutions’,’415 Phillip’)
--OLD-1006-0000205659
--DELETE FROM customer
--WHERE customer_id = 123
--END TRIGGER-1006-0000205693
Distribute Operations
--DISTRIBUTE-CON_ID-OFFSET-USER_ID-TYPE
USER_ID : The user ID affected
TYPE : One of two values
USER : Used in combination with a SUBSCRIPTION
operation
PASSTHROUGH : Used when a passthrough session is
being executed
A distribute operation is used to add a user to
the distribution list for the next operation
Distribute Operations
Example:
--CONNECT-1001-0000209279-cons-2001/jun/12 11:04
--DISTRIBUTE-1001-0000209287-0102-PASSTHROUGH
--DISTRIBUTE-1001-0000209292-0103-PASSTHROUGH
--BEGIN TRANSACTION-1001-0000209297
--SQL-1001-0000209298
passthrough for rem1,rem2
--COMMIT-1001-0000209334
--BEGIN TRANSACTION-1001-0000209335
--SQL-1001-0000209336
create table NewTable(pkey integer null)
--COMMIT-1001-0000209397
--BEGIN TRANSACTION-1001-0000209403
--SQL-1001-0000209404
passthrough stop
--COMMIT-1001-0000209431
Distribute Operations
Example:
--DISTRIBUTE-1002-0000204212-0103-USER
--SUBSCRIPTION-1002-0000204212-0001-ACTION-2
--BEGIN TRANSACTION-1002-0000204229
--SQL-1002-0000204230
create subscription to SalesRepData('2') for rem2
--COMMIT-1002-0000204283
Remote Operations
--REMOTE-CON_ID-OFFSET-USER_ID-TYPE-LOCAL-REMOTE
Remote operations represents updates made to
the SYSREMOTEUSER table
USER_ID : The user_id being modified in the
SYSREMOTEUSER table
TYPE : Specifies which columns in the
SYSREMOTEUSER are to be updated
LOCAL : Offset relating to the local database
REMOTE : Offset relating to the remote database
Remote Operations
USER
--REMOTE-CON_ID-OFFSET-USER_ID-USER-NA-NA
The USER type is used to identify a new
incoming message from a particular remote
user
It does not modify the SYSREMOTEUSER table
Example:
--REMOTE-1001-0000205688-0102-USER-NA-NA
--COMMIT-1001-0000205694
Remote Operations
RESET
--REMOTE-CON_ID-OFFSET-USER_ID-RESET-NA-NA
The RESET type indicates that a remote reset
command has been executed for a particular
user_id
Example:
--REMOTE-1001-0000205567-0102-RESET-NA-NA
--COMMIT-1001-0000205573
Remote Operations
RECEIVED
--REMOTE-CON_ID-OFFSET-USER_ID-RECEIVED-NA-REMOTE
The received type indicates that a message from
the remote with log offset REMOTE has been
received and applied
The log_received value in the SYSREMOTEUSER
table is updated
Example:
--REMOTE-1003-0000211353-0101-RECEIVED-NA-0000205608
Remote Operations
RECEIVED_CONFIRM
--REMOTE-CON_ID-OFFSET-USER_ID-RECEIVED_CONFIRM-LOCAL-NA
The RECEIVED_CONFIRM type indicates that a
message has been received from a remote user
confirming that they have successfully applied the
log offset indicated in their database
The confirm_sent value in the SYSREMOTEUSER
table is updated
Example:
--REMOTE-1005-0000211442-0101-RECEIVED_CONFIRM-0000211368-NA
Remote Operations
SENT_CONFIRM
--REMOTE-CON_ID-OFFSET-USER_ID-SENT_CONFIRM-LOCAL-REMOTE
The SENT_CONFIRM type indicates that a message has
been sent to a remote user that contains messages
up to and including log offset LOCAL
The message also confirms that log offset REMOTE
from the remote database has been successfully
applied
The log_sent and confirm_received values in the
SYSREMOTEUSER table are updated
Example:
--REMOTE-1005-0000211381-0101-SENT_CONFIRM-000211368-000205608
Remote Operations
RERECEIVE
--REMOTE-CON_ID-OFFSET-USER_ID-RERECEIVE-NA-NA
The RERECEIVE type indicates that dbremote
has detected a lost message and has initiated
a resend request
The rereceive value in the SYSREMOTEUSER
table is increased by one
Example:
--REMOTE-1004-0000205866-0102-RERECEIVE-NA-NA
Remote Operations
RESEND
--REMOTE-CON_ID-OFFSET-USER_ID-RESEND-NA-REMOTE
The RESEND type indicates that a resend
request has been requested
The remote database is asking for all data since
log offset REMOTE to be resent
The log_sent value in the SYSREMOTEUSER table
is modified, and the resend value is increased
by one
Example:
--REMOTE-1006-0000256703-0101-RESEND-NA-0000211368
Summary
Pre-Requisites
SQL Remote System Tables
Understanding Message Numbers
The Example Database
The Role of the Database Engine
Receiving Messages
Sending Messages
Understanding SQL Remote Comments in
DBTran Output