<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="11. Data Replication, Synchronization and Transformation Services" />
<meta name="dc.subject" content="11. Data Replication, Synchronization and Transformation Services" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="repl.html" title="Chapter Contents" />
<link rel="prev" href="SNAPSHOT.html" title="Snapshot Replication" />
<link rel="next" href="SCHEDULER.html" title="Virtuoso scheduler" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>11. Data Replication, Synchronization and Transformation Services</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="proctransrepl" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>11. Data Replication, Synchronization and Transformation Services</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="repl.html">Chapter Contents</a> | <a class="link" href="SNAPSHOT.html" title="Snapshot Replication">Prev</a> | <a class="link" href="SCHEDULER.html" title="Virtuoso scheduler">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="repl.html">Data Replication, Synchronization and Transformation Services</a>
</div>
<br />
<div>
<a href="replintro.html">Introduction</a>
</div>
<div>
<a href="SNAPSHOT.html">Snapshot Replication</a>
</div>
<div class="selected">
<a href="proctransrepl.html">Transactional Replication </a>
<div>
<a href="#pubitems" title="Publishable Items">Publishable Items</a>
<a href="#errorsinreplication" title="Errors in Replication">Errors in Replication</a>
<a href="#TransReplPublishFunctions" title="Publisher Transactional Replication Functions">Publisher Transactional Replication Functions</a>
<a href="#SubscriberFunctions" title="Subscriber Functions">Subscriber Functions</a>
<a href="#ReplStatusFunctions" title="Common Status Functions">Common Status Functions</a>
<a href="#bidirtransrepl" title="Bi-Directional Transactional Replication">Bi-Directional Transactional Replication</a>
<a href="#trxlogpurger" title="Purging replication logs">Purging replication logs</a>
<a href="#trxobj" title="Objects created by transactional replication">Objects created by transactional replication</a>
</div>
</div>
<div>
<a href="SCHEDULER.html">Virtuoso scheduler</a>
</div>
<div>
<a href="replexamples.html">Transactional Replication Example</a>
</div>
<div>
<a href="replsample.html">Replication Logger Sample</a>
</div>
<br />
</div>
<div id="text">
<a name="proctransrepl" />
<h2>11.3. Transactional Replication </h2>
<p>
The unit of replication is a publication. A publication is an ordered sequence
of transaction entries. One database transaction can add data to
zero or more publications. The data contributed to a publication by a
transaction is appended to the publication at the time of commit.
Because commits are serialized database wide, items in a publication
have a well defined order.
</p>
<p>
Each transaction entry in a publication has a unique sequence number
within the publication. Each subscriber of a publication has a level of
synchronization, which is the serial number of the last transaction from the publication which this
subscriber has processed.
</p>
<p>
Each publication has exactly one publisher and zero or more subscribers. Any
multi-master merge replication schemes will be based on this notion, with data to be
merged back into the original source regarded as a separate publication and the merge
regarded as a process between publications.
</p>
<p>
In order to publish data for replication by others a server must have a unique
name within the group of servers participating in the replication. This server name is
assigned to the server in its virtuoso.ini file in the DBName setting.
</p>
<p>
To publish data the publishing server initializes a publication with the repl_publish function, where
it names the publication and assigns a log file name for it. The server can then
start adding transactions to the publication, which can happen either under
application control or implicitly.
</p>
<div class="tip">
<div class="tiptitle">Tip</div>
<p>See the <a href="proctransrepl.html#TransReplPublishFunctions">repl_text</a> function.</p>
</div>
<p>
To subscribe to publications a server must also have a distinct DBName. It identifies
the publishing server by associating a host name and port number to its logical
name with the repl_server function. It can then call
<span class="computeroutput">repl_subscribe()</span>. Replication feeds from publisher
are replayed by 'dba' user by default. The default can be changed
(see <a href="fn_repl_subscribe.html">repl_subscribe()</a> function).
for each of the publications it subscribes to. A publication is uniquely
identified on the subscriber with the publishing server name and the
publication name. Note that several servers in a network may publish like named
publications and these will be logically distinct, having each their own distinct publisher.
</p>
<p>
A subscriber may or may not be connected to the publisher at any point in time.
If a subscriber is connected to the publisher it may either be 'in sync' or syncing'. In the
syncing state it is receiving transaction entries with numbers consecutive from its sync
level up until the last committed serial number committed on the server.
</p>
<p>
At the start of the sync communication the subscriber indicates the level of the last successfully
processed transaction in the publication. The sync exchange terminates when the subscriber reaches
the last committed item on the publication. At this point the subscriber is said to be 'in sync'.
The connection to the publisher is then maintained by default and is used to send sync information as it
becomes available. This means that once an entry is appended to the publication by a committing
a transaction it is sent to the 'in sync' subscribers without separate request.
</p>
<p>
The publisher can terminate the replication feed by unilateral decision. It will do it
if the sending of the message times out for too long or if the queue of 'to be sent' replication
records exceeds a settable threshold. This essentially happens with communication failures
or if the subscriber continuously processes the feed at a speed lower than the
feed production speed of the publisher. A disconnected subscriber can reconnect at will, in
which case it enters the 'syncing' state and will receive transactions from the point where the feed
was cut.
</p>
<p>
A subscriber can disconnect from the publisher at any time without ill effect.
</p>
<p>A table</p>
<div>
<pre class="programlisting">
SYS_REPL_ACCOUNTS (
SERVER varchar,
ACCOUNT varchar,
NTH integer,
LEVEL integer,
IS_MANDATORY integer,
IS_UPDATEABLE integer,
SYNC_USER varchar,
P_MONTH integer,
P_DAY integer,
P_WDAY integer,
P_TIME time,
primary key (SERVER, ACCOUNT))
</pre>
</div>
<p>is used to store information about published accounts and accounts
this server is subscribed to.</p>
<p>A table</p>
<div>
<pre class="programlisting">
SYS_REPL_SUBSCRIBERS (
RS_SERVER varchar,
RS_ACCOUNT varchar,
RS_SUBSCRIBER varchar not null,
RS_LEVEL integer NOT NULL,
RS_VALID integer NOT NULL,
primary key (RS_SERVER, RS_ACCOUNT, RS_SUBSCRIBER))
</pre>
</div>
<p>is used to store subscribers' status (pushback
accounts for updateable subscriptions are there too).
Subscribers for an account are added to this table automatically on each
request to sync an account from subscriber or manually from Admin UI.</p>
<p>SYS_REPL_SUBSCRIBERS.RS_VALID column is be used to designate
subscribers whose replication account level is valid (lags not more than
REPL_MAX_DELTA behind the publisher's level).</p>
<p>RS_VALID state of subscriber is checked and updated on every sync request
from subscriber. If subscriber is found to be invalid
all further sync requests from it are ignored. Such subscriber need to
be reinitialized manually and marked as valid using Admin UI.</p>
<a name="pubitems" />
<h3>11.3.1. Publishable Items</h3>
<p>
Tables, stored procedures and DAV collections may be added to a
transactional publication. When a table is added, triggers are
created for capturing changes to the table. When a procedure is
added, all calls to this procedure will be recorded in the
publication's log and the same procedure will be called on the
subscriber. When a procedure is published in this manner, actions
performed inside the procedure are not themselves recorded even if
they touch on items that are part of a publication. It is assumed
that the procedure on the subscriber will produce the equivalent
effect. When a DAV collection is published, operations on direct and
indirect members of the collection are logged into the publication's
log. When this is replayed on the subscriber, the operations are
repeated on like-named DAV resources, creating collections and
resources as needed.
</p>
<p>
When a table or procedure is added to a publication, the creating
statement is added also, so that the subscribers come to create the
table or procedure before receiving any replication operations on said
table or procedure. Also when the table or procedure is altered, the
altering statements are added to the publication so s to be reflected
on all subscribers. This can be overridden for procedures, since in
some cases it is desirable to have a different definition on the
subscriber. Table constraints are also replicated, except for foreign
key constraints with tables outside the publication, since these would
not be meaningful on the subscriber, as there is no knowledge on what
tables may exist there outside of the ones in the publication.
Changes to schema on subscribers are never replicated to the
publisher, even if we had bidirectional replication. Identity and
timestamp columns are replicated so that the values on the subscriber
are assigned by the publisher.
</p>
<p>
When a procedure is published, it is possible to specify whether the
definitions, calls or both are replicated. Usually specifying both
calls and definition is reasonable, sometimes only calls are to be
replicated if the procedure intentionally has a different definition
in the subscriber. This is useful for example when the subscriber
gathers statistics or maintains a data warehouse where the storage
schema is not identical with that of the publisher. Only procedures
with input parameters can be replicated. The rationale is that
replication is a one way stream and no return values, result sets or
output parameters can be captured by either publisher or subscriber.
Procedure calls cannot be replicated bidirectionally, these go from publisher to subscriber exclusively.
</p>
<p>
Even though replication can carry schema changes, it is in no way a means of keeping software installations in sync. Many schema elements such as triggers are not covered and a software upgrade is more complex than can be represented by replication alone.
</p>
<br />
<a name="errorsinreplication" />
<h3>11.3.2. Errors in Replication</h3>
<p>
A statement received for replication from a publisher may encounter an
error. All Such errors are logged into the subscriber's error log
file. If the error is retryable, like a deadlock, retries are made
until the operation succeeds. If Other errors are simply skipped and
replication proceeds. A replication subscription can be permanently
broken and out of sync if for example columns are dropped from the
subscriber copy of the tables or if the subscriber runs out of disk and can replay some transactions but not all. Note that in such situations gaps may be formed into the received transaction sequence. In such cases, it is best to drop the
subscription, drop the tables and remake the subscription.
</p>
<br />
<a name="TransReplPublishFunctions" />
<h3>11.3.3. Publisher Transactional Replication Functions</h3>
<p>
The most generic form of the replication element is a stored procedure call. This associates
a procedure name and set of parameters to a publication inside a transaction. When the transaction commits, all the
publication entries are appended to the publication, forming a replication entry with
its unique number.
</p>
<p>These functions are available to the publishing Virtuoso server:</p>
<ul>
<li>
<a href="fn_repl_publish.html">repl_publish()</a>
</li>
<li>
<a href="fn_repl_unpublish.html">repl_unpublish()</a>
</li>
<li>
<a href="fn_repl_pub_add.html">repl_pub_add()</a>
</li>
<li>
<a href="fn_repl_pub_remove.html">repl_pub_remove()</a>
</li>
<li>
<a href="fn_repl_pub_init_image.html">repl_pub_init_image()</a>
</li>
<li>
<a href="fn_repl_new_log.html">repl_new_log()</a>
</li>
<li>
<a href="fn_repl_text.html">repl_text()</a>
</li>
<li>
<a href="fn_repl_grant.html">repl_grant()</a>
</li>
<li>
<a href="fn_repl_revoke.html">repl_revoke()</a>
</li>
</ul>
<br />
<a name="SubscriberFunctions" />
<h3>11.3.4. Subscriber Functions</h3>
<p>These are the functions that are available to the subscribing Virtuoso server:</p>
<ul>
<li>
<a href="fn_repl_server.html">repl_server()</a>
</li>
<li>
<a href="fn_repl_subscribe.html">repl_subscribe()</a>
</li>
<li>
<a href="fn_repl_unsubscribe.html">repl_unsubscribe()</a>
</li>
<li>
<a href="fn_repl_init_copy.html">repl_init_copy()</a>
</li>
<li>
<a href="fn_repl_sync.html">repl_sync()</a>
</li>
<li>
<a href="fn_repl_sync_all.html">repl_sync_all()</a>
</li>
<li>
<a href="fn_repl_sched_init.html">repl_sched_init()</a>
</li>
<li>
<a href="fn_sub_schedule.html">sub_schedule()</a>
</li>
<li>
<a href="fn_repl_disconnect.html">repl_disconnect()</a>
</li>
<li>
<a href="fn_repl_this_server.html">repl_this_server()</a>
</li>
<li>
<a href="fn_repl_purge.html">repl_purge()</a>
</li>
</ul>
<br />
<a name="ReplStatusFunctions" />
<h3>11.3.5. Common Status Functions</h3>
<ul>
<li>
<a href="fn_repl_stat.html">repl_stat()</a>
</li>
<li>
<a href="fn_repl_status.html">repl_status()</a>
</li>
</ul>
<p>
The status () function shows a replication status summary.
The same data can be obtained with the repl_stat and repl_status procedures.
</p>
<br />
<a name="bidirtransrepl" />
<h3>11.3.6. Bi-Directional Transactional Replication</h3>
<p>Virtuoso supports bi-directional transactional replication via a mechanism
of updateable subscriptions. The following rules and conditions must be observed:</p>
<ul>
<li>Every table has only one publisher.</li>
<li>Only direct subscribers are considered.</li>
<li>Only replication of tables is allowed.</li>
</ul>
<p>It is assumed that all the tables within a publication have primary keys
and that the primary key columns are never modified.</p>
<p>Every transaction has an origin, i.e. the originating server on which
the transaction was performed.</p>
<p>Modifications to a subscriber come from publisher only using the ordinary
transactional replication technique: the subscriber initiates an update and
pulls (requests) replication logs from publisher. The publisher sends
the replication log from the replication log files and then places the subscriber
into "synced" (or "online") mode. In this mode the replication logs are sent
to subscriber immediately after each COMMIT.</p>
<p>Data flow from a subscriber to the publisher is very similar: the
subscriber initiates the update and pushes replication logs to the publisher.
After all replication log data has been sent to the publisher it is put into
"synced" mode and will receive modifications immediately after each COMMIT
on subscriber.</p>
<a name="bidirtransreplcreate" />
<h4>11.3.6.1. Creating Publications for Updateable Subscriptions</h4>
<p>In order to create a publication that allows transaction-based
replication and updateable subscriptions use the
<span class="computeroutput">repl_publish()</span> with a non-zero third argument.
Replication feeds from subscribers can be replayed by user different from
'dba' user.</p>
<div>
<pre class="programlisting">repl_publish('foo', 'foo.log', 1, 'demo');</pre>
</div>
<p>This will create updateable publication 'foo'. Replication feeds
from subscribers will be replayed as user 'demo'.</p>
<br />
<a name="bidirtransrepladdtable" />
<h4>11.3.6.2. Adding Tables to a Publication</h4>
<p>When a table is added to an updateable publication a new
'ROWGUID varchar' column is automatically added to the table.
This column will be used for conflict resolution (described later).
If the table already has column with such a name, an existing column
will be used (with checking for appropriate data type and width).
ROWGUID columns hold globally unique identifiers of a row and are
modified after each UPDATE of a row. ROWGUID column values
are OSF DCE 1.1 compliant Universally Unique Identifiers (UUID).</p>
<p>ROWGUID columns are used for conflict resolution for
INSERT/UPDATE/DELETE DML operations. Basically, if a ROWGUID column
that came from a subscriber does not differ from the ROWGUID column
of the publisher's table then it is assumed that there is no conflict,
otherwise conflict resolution must take place.</p>
<br />
<a name="bidirtransreplconflictres" />
<h4>11.3.6.3. Conflict Resolution</h4>
<p>Since every table may have only one publisher, conflicts resolution
will always take place on the publisher.</p>
<p>Assume some DML operation that occurred on a subscriber is being replayed
on publisher. There may be three types of conflicts:</p>
<ol>
<li>
<div class="formalpara">
<strong>uniqueness conflict (insert conflict)</strong>
<p>occurs when the row with some primary key <PK> already exists
in publisher's table.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>update conflict</strong>
<p>occurs when an UPDATE modifies a row which has already been
modified on publisher (by the publisher or another subscriber).</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>delete conflict</strong>
<p>occurs when an UPDATE modifies a row or a DELETE removes a row
that does not exist on publisher anymore.</p>
</div>
</li>
</ol>
<p>Every table has a number of conflict resolvers that are used for
conflict resolution. These are stored in DB.DBA.SYS_REPL_CR system table.
Each conflict resolver has a type ('I', 'U', or 'D') and an order. Conflict
resolvers are applied in ascending order.</p>
<p>The conflict resolver is a Virtuoso/PL procedure that receives a
conflicting row from a subscriber and some other arguments. The conflict
resolver can modify the row, which is passed as an 'inout' argument.
The conflict resolver should return an integer value, which will be used
for conflict resolution.</p>
Conflict resolvers of different types have different signatures:
<ul>
<li>
<p>
<strong>'I' - Insert conflict resolvers</strong>
</p>
<p>(<ALLCOLS>, inout _origin varchar)</p>
</li>
<li>
<p>
<strong>'U' - Update conflict resolvers</strong>
</p>
<p>(<ALLCOLS>, , <ALLOLDCOLS>, inout _origin varchar)</p>
</li>
<li>
<p>
<strong>'D' - Deletion conflict resolvers</strong>
</p>
<p>(<ALLOLDCOLS>, inout _origin varchar)</p>
</li>
</ul>
<p>where</p>
<p><ALLCOLS> are new values of all columns including the ROWGUID
column, <ALLOLDCOLS> are old values of all columns, and _origin
is transaction originator.</p>
<p>Conflict resolvers can return the following integer values;
The conflict resolver types concerned for each are listed in parentheses:</p>
<ul>
<li>
<div class="formalpara">
<strong>0 - can't decide (I, U, D)</strong>
<p>next conflict resolver will be fired.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>1 - subscriber wins (I, U, D)</strong>
<p>DML operation will be applied with <ALLCOLS>
All the subscribers except originator will receive modifications
(originator already has them).</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>2 - subscriber wins, change origin (I, U)</strong>
<p>DML operation will be applied with <ALLCOLS> and origin
of transaction will be changed to publisher's server name.
All the subscribers (including originator) will receive modifications.
This return value is useful when conflict resolver changed some of
the columns of the row that were passed in.
Although all parameters of conflict resolver are inout
only changing of <ALLCOLS> (non-PK columns) parameters
makes sense.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>3 - publisher wins (U)</strong>
<p>DML operation will be applied with <ALLCOLS> taken from
publisher's table. All the subscribers will receive
modifications.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>4 - reserved</strong>
<p />
</div>
</li>
<li>
<div class="formalpara">
<strong>5 - ignore (D)</strong>
<p>DML operation is ignored.</p>
</div>
</li>
</ul>
<p>Conflict resolution stops when conflict resolvers return a non-zero
value meaning that it has made a decision.</p>
<a name="ex_conflictreslntrans" />
<div class="example">
<div class="exampletitle">Conflict Resolution</div>
<p>Suppose we have the following table:</p>
<div>
<pre class="programlisting">
create table items(
item_id integer primary key,
name varchar,
price decimal
);
</pre>
</div>
<p>"Publisher wins" 'I' conflict resolver will look like:</p>
<div>
<pre class="programlisting">
create procedure items_cr(
inout _item_id integer,
inout _name varchar,
inout _price decimal,
inout _origin varchar)
returns integer
{
return 3;
}
</pre>
</div>
<p>The conflict resolver that will make a decision based on the
minimal price column will look like:</p>
<div>
<pre class="programlisting">
create procedure items_cr(
inout _item_id integer,
inout _name varchar,
inout _price decimal,
inout _rowguid varchar,
inout _old_item_id integer,
inout _old_name varchar,
inout _old_price decimal,
inout _old_rowguid varchar,
inout _origin varchar)
returns integer
{
declare p decimal;
-- get current price value
select price into p from items where item_id = _item_id;
if (p < _price)
return 3; -- publisher wins
else if (p > _price)
return 1; -- subscriber wins
return 0; -- can't decide
}
</pre>
</div>
<p>Conflict resolver that will change the price to the minimal
value will look like:</p>
<div>
<pre class="programlisting">
create procedure items_cr(
inout _item_id integer,
inout _name varchar,
inout _price decimal,
inout _rowguid varchar,
inout _old_item_id integer,
inout _old_name varchar,
inout _old_price decimal,
inout _old_rowguid varchar,
inout _origin varchar)
returns integer
{
declare p decimal;
-- get current price value
select price into p from items where item_id = _item_id;
if (p < _price)
{
_price := p;
return 2; -- publisher wins, change origin
}
return 1; -- subscriber wins
}
</pre>
</div>
</div>
<p>Conflict resolution occurs differently for each kind of DML operation:</p>
<ul>
<li>
<div class="formalpara">
<strong>INSERT</strong>
<p>When INSERT of some row with primary key <PK> is replayed,
the row in the publisher's table with such <PK> is looked-up.
If the row does not exist then there is no conflict, conflict
resolution stops and the INSERT is replayed.
If the row exists then we have a "uniqueness conflict". In this case 'I'
conflict resolvers are fired-up.
If none of the 'I' conflict resolvers were able to make a decision
(return non-zero value) the default action is 'publisher wins'.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>UPDATE</strong>
<p>When there is an UPDATE of some row with primary
key <PK> is replayed, the row (and its ROWGUID) in
publisher's table with such <PK> is looked-up.
If the row does not exist then we have a "delete conflict",
'D' conflict resolvers are fired up. If none of the 'D' conflict
resolvers were able to make a decision the default action will be
to 'ignore'.
If the row exists in the publisher's table and its ROWGUID is the same
as that from the subscriber then there is no conflict. Conflict
resolution stops and the UPDATE is replayed.
If the row exists and its ROWGUID differs from the one that came
from subscriber then we have an "update conflict". In this case the
'U' conflict resolvers are fired-up.
If none of the 'U' conflict resolvers were able to make a decision
(return non-zero value) the default action will be 'publisher wins'.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>DELETE</strong>
<p>When DELETE of some row with primary key <PK> is replayed,
the row in the publisher's table with such <PK> is looked-up.
If the row does not exist or if the row exists but its
ROWGUID differs from the one that came from subscriber then
we have "delete conflict". The 'D' conflict resolvers are fired-up.
If none of the 'D' conflict resolvers were able to make a decision then the
default action will be taken to 'ignore'.
Otherwise it is assumed that there is no conflict and DELETE statement
is replayed.</p>
</div>
</li>
</ul>
<br />
<a name="bidirtransreplautoconres" />
<h4>11.3.6.4. Automatically Generated Conflict Resolvers</h4>
<p>Simple conflict resolvers can be generated automatically.
This can be done by calling REPL_ADD_CR function.</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="fn_REPL_ADD_CR.html">REPL_ADD_CR()</a>
</p>
</div>
<br />
<a name="bidirtransrepllogdata" />
<h4>11.3.6.5. Replication Log Data</h4>
<p>Replication log data is different for each kind of DML operation:</p>
<ul>
<li>
<div class="formalpara">
<strong>INSERT</strong>
<p>(stmt, <ALLCOLS>)</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>UPDATE</strong>
<p>(stmt, <ALLCOLS>, <OLDPK>, <ALLOLDCOLS>, ncols)</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>DELETE</strong>
<p>(stmt, <OLDPK>, <ALLOLDCOLS>, ncols)</p>
</div>
</li>
</ul>
<p>where</p>
<p>stmt is DML statement (varchar), <ALLCOLS> is new values of
all columns, <OLDPK> is primary key, specifying a row for which
(UPDATE or DELETE) DML statement is executed, <ALLOLDCOLS> is old
values of all columns, ncols is number of columns in table (integer).</p>
<p>The format of the log replication data is the same as in simple transactional
replication with addition of <ALLOLDCOLS> and ncols for logging UPDATE and
DELETE statements.</p>
<br />
<br />
<a name="trxlogpurger" />
<h3>11.3.7. Purging replication logs</h3>
<p>Every replication account has an associated sequence which holds
replication account level (basically, transaction number).
Each subscriber also maintains a sequence where it stores
its replication account level.</p>
<p>When subscriber issues a sync request for an account it submits its
replication account level so publisher can find a point in time from where
it should start to submit replication logs to publisher.</p>
<p>Replication account level can roll over REPL_WRAPAROUND (0x7fffffff)
to 1. Old replication logs need to be purged to allow this to work
correctly. Procedure <a href="fn_repl_purge.html">repl_purge()</a> purges
replication logs for a specified account.</p>
<p>Columns needed to store purger configuration in SYS_REPL_ACCOUNTS table
are:
<ul>
<li>P_MONTH integer (month, nullable)</li>
<li>P_DAY integer (day of month, nullable)</li>
<li>P_TIME time (time, nullable)</li>
</ul>
</p>
<p>
If P_TIME is NULL log purger will not be scheduled to run at all.
If P_TIME is not NULL there can be the following combinations of P_MONTH,
P_DAY and P_WDAY ('*' means NULL value, 'x' means any value):
<table class="data">
<caption>Table: 11.3.7.1. Purger config settings</caption>
<tr>
<td class="data">
<strong>P_MONTH</strong>
</td>
<td class="data">
<strong>P_DAY</strong>
</td>
<td class="data">
<strong>P_WDAY</strong>
</td>
<td class="data">
<strong>meaning</strong>
</td>
</tr>
<tr>
<td class="data">month</td>
<td class="data">day</td>
<td class="data">x</td>
<td class="data">purger is run yearly on specified month and day</td>
</tr>
<tr>
<td class="data">month</td>
<td class="data">*</td>
<td class="data">x</td>
<td class="data">purger is run yearly on 1st of month</td>
</tr>
<tr>
<td class="data">*</td>
<td class="data">day</td>
<td class="data">x</td>
<td class="data">purger is run monthly on specified day of month</td>
</tr>
<tr>
<td class="data">*</td>
<td class="data">*</td>
<td class="data">wday</td>
<td class="data">purger is run weekly on specified day of week</td>
</tr>
<tr>
<td class="data">*</td>
<td class="data">*</td>
<td class="data">*</td>
<td class="data">purger is run daily</td>
</tr>
</table>
<br />
</p>
<p>
An entry to call purger is inserted (or updated) into SYS_SCHEDULED_EVENT
after each modification of purger settings for an account (from Admin UI)
or after each successful run of repl_purge() for this account.
</p>
<br />
<a name="trxobj" />
<h3>11.3.8. Objects created by transactional replication</h3>
<p>Virtuoso creates triggers "<name>_I",
"<name>_U" and "<name>_D" for every published table.
On subscriber "DB"."DBA"."SYS_REPL_ACCOUNTS" table and
"DB"."DBA"."TP_ITEM" view are attached from publisher as
"DB"."<dsn>"."SYS_REPL_ACCCOUNTS" and "DB"."<dsn>"."TP_ITEM"
respectively.</p>
<p>If publication is updateable Virtuoso additionally creates
the following objects:
<ul>
<li>
<p>"<qual>"."<owner>"."replcr_<name>_I",
"<qual>"."<owner>"."replcr_<name>_U" and
"<qual>"."<owner>"."replcr_<name>_D" procedures
for every published table. These procedures are used for conflict
resolution.</p>
</li>
<li>
<p>Triggers "<name>_I", "<name>_U" and "<name>_D"
for every subscribed table.</p>
</li>
</ul>
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="SNAPSHOT.html" title="Snapshot Replication">Previous</a>
<br />Snapshot Replication</td>
<td align="center" width="34%">
<a href="repl.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="SCHEDULER.html" title="Virtuoso scheduler">Next</a>
<br />Virtuoso scheduler</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
827
