User's Guide Part III. Using SQL Anywhere Chapter 28. SQL Remote AdministrationWhen a simple INSERT statement is entered at one database, it is sent to other databases in the SQL Remote setup as an INSERT statement. However, not all statements are replicated exactly as they are entered by the client application. This section describes how SQL Remote replicates SQL statements.
INSERT and DELETE statements are the simplest replication case. SQL Remote takes each INSERT or DELETE operation from the transaction log, and sends it to all sites that subscribe to the row being inserted or deleted.
If only a subset of the columns in the table is subscribed to, the INSERT statements sent to subscribers contains only those columns.
UPDATE statements are not replicated exactly as the client application enters them. This section describes two ways in which the replicated UPDATE statement may differ from the entered UPDATE statement.
SQL Remote replicates UPDATE statements including a VERIFY clause in the statement.
Consider the following UPDATE statement, entered by an application at a remote database.
UPDATE "dba".productSET quantity = 23WHERE id = 300
When this UPDATE is sent to the consolidated database, SQL Remote adds a VERIFY clause to the statement, as follows:
UPDATE "dba".productSET quantity = 23VERIFY ( quantity )VALUES ( 28 )WHERE id = 300
The VERIFY clause is a check to ensure that the values being updated at the consolidated database are the same as those that were updated at the remote database.
In the example, the UPDATE statement succeeds without a conflict at the consolidated database only if the quantity column has a value of 28 in the row being updated, before the update takes place. If the value is not 28, then any RESOLVE UPDATE trigger created for the table is fired before the UPDATE is applied. The VERIFY clause allows UPDATE conflicts to be detected and resolved by RESOLVE UPDATE triggers.
For more information on resolution of UPDATE conflicts, see "UPDATE conflict resolution examples".
You can include all columns in the VERIFY clause using the VERIFY_ALL_COLUMNS option, described in "RESOLVE UPDATE triggers".
If an UPDATE statement has the effect of removing a row from a given remote user's subscription, it is sent to that user as a DELETE statement. If an UPDATE statement has the effect of adding a row to a given remote user's subscription, it is sent to that user as an INSERT statement.
For example, the pub_orders publication is defined as follows:
CREATE PUBLICATION pub_orders (TABLE sales_order SUBSCRIBE BY sales_rep)
Samuel Singer, with user ID SamS, has a subscription to the pub_orders publication as follows:
CREATE SUBSCRIPTIONTO pub_orders ( '856' )FOR SamS
Pamela Savarino, with user ID PamS, has a subscription to the pub_orders publication as follows:
CREATE SUBSCRIPTIONTO pub_orders ( '949' )FOR PamS
An UPDATE to the sales_order table that changes the sales_rep value of a row from 856 to 949 is replicated to Samuel Singer as a DELETE statement, and to Pamela Savarino as an INSERT statement.
Any replication system is faced with two broad choices when replicating a procedure call. Either the call itself can be replicated (so that a procedure with the same name and parameters is called at the replicate site), or the procedure can be replicated by replicating the individual actions (INSERTs, UPDATEs, DELETEs) of the procedure.
When operations are applied by the Message Agent, the same triggers fired at the publishing database are fired at the subscriber database. If the actions taken by a trigger at the publishing database were included in replication messages, these actions would occur twice.
The current release of SQL Remote does not allow procedures to be included in publication definitions, and so implements the second of the two choices; to replicate the actions of the procedure, not the call. This is described in the following section.
SQL Remote replicates procedures by replicating the actions of a procedure. The call itself is not reproduced. Consequently, procedures that modify non-replicated data do not cause problems. If the call to a procedure were replicated, a version of the procedure would have to be written that does not carry out those actions that modify unreplicated data, for the replicate database.
SQL Remote does not replicate actions performed by triggers. SQL Remote replicates triggers by assuming the trigger is defined remotely. This avoids permissions issues and the possibility of each action occurring twice. There are some exceptions to this rule, described below.
The actions carried out by RESOLVE UPDATE triggers are replicated from a consolidated database to all remote databases, including the one that sent the message causing the conflict.
Some BEFORE triggers can produce undesirable results when using SQL Remote. For example, a BEFORE UPDATE that bumps a counter column in the row to keep track of the number of times a row is updated will double count, because the BEFORE UPDATE trigger will fire when the UPDATE is replicated. Also, a BEFORE UPDATE that sets a column to the time of the last update will get the time the UPDATE is replicated as well.
The Message Agent has a command-line switch that causes it to replicate all trigger actions. This is the DBREMOTE -t switch.
If you do use this switch, you must ensure that the trigger actions are not carried out twice at remote databases, once by the trigger being fired at the remote site, and once by the explicit application of the replicated actions from the consolidated database.
To ensure that trigger actions are not carried out twice, you can wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the triggers.
Only data manipulation statements are replicated to other databases. Data definition statements (CREATE, ALTER, DROP, and others that modify database objects) are not replicated by SQL Remote unless they are entered while in PASSTHROUGH mode.
For information about PASSTHROUGH mode, see "Using passthrough mode for administration".
SQL Remote includes special methods for replication of LONG VARCHAR, LONG BINARY, TEXT, and IMAGE data types: values that are longer than 256 characters.
The Message Agent uses a variable in place of the value in the INSERT or UPDATE statement that is being replicated. The value of the variable is built up by a sequence of statements of the form
SET vble = vble || 'more_stuff'
This makes the size of the SQL statements involving long values smaller, so that they fit within a single message. The SET statements are separate SQL statements, so that the "blob" is effectively split over several SQL Remote messages.
The Verify_threshold database option can prevent long values from being verified (in the VERIFY clause of a replicated UPDATE). The default value for the option is 1000. If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. This keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected.
There is a technique allowing detection of conflicts when verify_threshold is being used to reduce the size of messages. Whenever a "blob" is updated, a last_modified column in the same table should also be updated. Conflicts can then be detected because the old value of the last_modified column is verified.
Repeated updates to a blob should be done in a "work" table, and the final version should be assigned to the replicated table. For example, if a document in progress is updated 20 times throughout the day and the Message Agent is run once at the end of the day, all 20 updates are replicated. If the document is 200Kb in length, this causes 4Mb of messages to be sent. The better solution is to have a document in progress table. When the user is done revising a document, the application moves it from the document in progress table to the replicated table. The results in a single update (200Kb of messages).