# 11. Data Replication, Synchronization and Transformation Services

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Data Replication, Synchronization and Transformation Services
Introduction
Snapshot Replication
Non incremental snapshot replication Incremental snapshot replication Command reference Bi-Directional Snapshot Replication Registry variables Heterogeneous snapshot replication Data type mappings Objects created by incremental snapshot replication Objects created by bi-directional snapshot replication Replication system tables Table snapshot logs
Transactional Replication
Virtuoso scheduler
Transactional Replication Example
Replication Logger Sample

## 11.2. Snapshot Replication

### 11.2.1. Non incremental snapshot replication

The Virtuoso Server periodically evaluates a query and inserts the result into a table, replacing the content. The source and target can be anywhere but typically will be on different databases of which at least one is remote.

This makes a two party distributed transaction. Only one of the transaction branches is writing, hence this goes with a one phase commit.

Note:

This can be used to replicate between two non-Virtuoso databases.

The prerequisite of this mode of replication is that all tables exist. The schema is never replicated.

### 11.2.2. Incremental snapshot replication

A table can be replicated from a generic, possibly non-Virtuoso source in an incremental fashion if the data source meets certain criteria:

The data should come from a single table.

The source table should have an explicit primary key.

There source query should have the form

    SELECT fields FROM source_table WHERE scalar_conditions

A snapshot log on the source table should exist.

There should be insert, delete and update triggers on the source table to update the snapshot log.

The incremental update is done in the following way:

All the changed records with a snaptime greater or equal from REPL_START_TIME(SN_LAST_TS) are processed. If the record to insert already exists, then it is updated. If the record to update does not exist in the destination table, then it is inserted. This contributes for conflict resolving.

### 11.2.3. Command reference

#### 11.2.3.1. CREATE SNAPSHOT LOG FOR source_table

    CREATE SNAPSHOT LOG FOR source_table

    repl_create_snapshot_log(in source_table varchar)

Creates a snapshot log of a native (non VDB) table and the appropriate log update triggers.

Note that when replicating data from a remote Virtuoso a snapshot log table can be created on the remote virtuoso and then the source and log tables can be attached into the local Virtuoso. However the snapshot log table and the update triggers should be created manually on a non-Virtuoso data source.

Examples (based on Virtuoso demo database):

    create snapshot log for Orders;

#### 11.2.3.2. DROP SNAPSHOT LOG FOR source_table

    DROP SNAPSHOT LOG FOR source_table

    repl_drop_snapshot_log(in source_table varchar)

Drops a snapshot log of a table and the log update triggers.

This results in 4 SQL commands :

    drop trigger xxx_I_log;
    drop trigger xxx_U_log;
    drop trigger xxx_D_log;
    drop table RLOG_xxx;

where xxx is the source table name (fully qualified - DB.DBA.Orders becomes DB_DBA_Orders).

Examples (based on Virtuoso demo database):

    drop snapshot log for Orders;

#### 11.2.3.3. PURGE SNAPSHOT LOG FOR source_table

    PURGE SNAPSHOT LOG FOR source_table

    repl_purge_snapshot_log(in source_table varchar)

There can be old snapshot log rows which have been replayed in all the snapshots of a given source table. These rows are no longer needed and can be purged from the snapshot log for faster operation.

This command checks if there are any rows in SYS_REPLICATION referring to that source table and if there are, then it deletes all the rows in the log table with SNAPTIME earlier then REPL_START_TIME(MIN(SN_LAST_TS)).

If there are no rows in SYS_SNAPSHOT it assumes that the log is used for "pull" replication and does not do anything.

Examples (based on Virtuoso demo database):

    purge snapshot log for Orders;

#### 11.2.3.4. CREATE NONINCREMENTAL SNAPSHOT

    CREATE NONINCREMENTAL SNAPSHOT dest_table as 'query_def'

    repl_create_snapshot(in query_def varchar, in dest_table varchar)

Creates a non-incremental snapshot log of the data returned by the query query_def.

The server first checks for the existence of a table with the same name. If it does not exist, then a table with a layout to accommodate the resultset produced by query_def is created.

Then a entry containing query_def and tablename is added to SYS_SNAPSHOT and the destination table is filled in with an insert into tablename query_def.

Examples (based on Virtuoso demo database):

    create nonincremental snapshot sub_orders as
    		'select * from Orders where OrderID < 5'

#### 11.2.3.5. CREATE INCREMENTAL SNAPSHOT

    CREATE SNAPSHOT dest_table FROM source_table [ 'source_column_list' ]
    		[ WHERE 'condition' ]

    repl_create_inc_snapshot(
        in source_column_list varchar,
        in source_table varchar,
        in condition varchar,
        in dest_table varchar)

Creates a incremental snapshot log using source query like that :

    SELECT source_table_pk_cols, source_column_list FROM source_table WHERE condition

If the dest table already exists, then it's column count is checked to be greater than the primary key parts count of the source table.

If the source_column_list is omitted, then the destination table is created to have the same columns as the source table.

Examples (based on Virtuoso demo database):

    CREATE SNAPSHOT sub_orders FROM Orders WHERE 'OrderID < 5'

#### 11.2.3.6. UPDATE SNAPSHOT

    UPDATE SNAPSHOT snapshot_table_name [ AS NONINCREMENTAL ]

    repl_refresh_inc_snapshot(
        in snapshot_table_name varchar)

    repl_refresh_noninc_snapshot(
        in snapshot_table_name varchar)

Without the optional argument updates the snapshot according to it's type. If an incremental snapshot needs to be updated as a nonincremental, then the full form should be used.

Examples (based on Virtuoso demo database):

    UPDATE SNAPSHOT sub_orders

#### 11.2.3.7. DROP SNAPSHOT

    DROP SNAPSHOT snapshot_table_name [ WITH DELETE ]

    repl_drop_snapshot(
        in snapshot_table_name varchar,
        in delete integer)

Removes a snapshot definition from SYS_SNAPSHOT and optionally drops the snapshot destination table.

Examples (based on Virtuoso demo database):

    DROP SNAPSHOT sub_orders WITH DELETE

### 11.2.4. Bi-Directional Snapshot Replication

Bi-directional snapshot replication allows you to set up snapshot replication between multiple servers where updates can be performed on all servers. Bidirectional snapshot replication uses the publisher-subscriber model where each table or DAV collection has only one publisher and when an update is performed on subscriber it goes to publisher first, and then to all other subscribers. Conflict resolution may need to take place on the publisher when data coming from a subscriber is processed. Bi-directional snapshot replication uses snapshot logs on publisher and subscribers to track changes in published table and its replicas.

It is assumed that all tables published using bi-directional snapshot replication have primary keys columns that are never modified.

To perform conflict resolution a ROWGUID column is added to every published table. This column should never be modified manually. All INSERT statements should specify exact column-select-lists that do not include the ROWGUID column. Likewise the ROWGUID column should never feature in any UPDATE statements.

Each server participating in bi-directional snapshot replication must have unique name (replication name). For Virtuoso servers replication name is assigned to the server in its virtuoso.ini file in the DBName setting. For other RDBMS servers replication name is the name of instance or the name of database. Replication name of remote server can be obtained using REPL_SERVER_NAME() (after this server is defined, see below).

REPL_CREATE_SNAPSHOT_PUB() function should be used to create bi-directional snapshot publication.

To create subscription for the publication, replication subscriber server should be defined first using REPL_SNP_SERVER() function. The name of the server can be obtained later using REPL_SERVER_NAME() function. After this REPL_CREATE_SNAPSHOT_SUB() function should be used to create a subscription (and create replicated table on subscriber if table replication takes place).

To load initial data on subscriber REPL_INIT_SNAPSHOT() should be used after subscription is created. Loading initial data can take some time so REPL_INIT_SNAPSHOT() performs commits after every 100 rows are copied from source table to the table on subscriber to prevent running out of transaction log or deadlocks. It is possible to specify an alternative value for number of rows per transaction (last parameter of REPL_INIT_SNAPSHOT() function). In DAV case commits are performed per copied resource.

REPL_UPDATE_SNAPSHOT() should be called periodically after initial data is loaded on subscriber to sync published items (tables or DAV collections) on publisher and subscribers. This function reads snapshot logs attached from subscribers and replays them with possible conflict resolution. After all snapshot logs from subscribers are processed an updating procedure reads snapshot log on publisher and replays it on all subscribers.

Note:

Please note that all operations in bi-directional snapshot replication (publication, subscription, doing initial copy, syncing) should be performed on publisher.

Creating bi-directional snapshot publication

This example demonstrates creating bi-directional snapshot publication of table 'Demo.demo.Shippers'.

    SQL> REPL_CREATE_SNAPSHOT_PUB ('Demo.demo.Shippers', 2);

Creating bi-directional snapshot subscription

This example demonstrates creating bi-directional snapshot subscription for table 'Demo.demo.Shippers' and loading initial data on subscriber with DSN 'localhost:1121'.

    SQL> REPL_SNP_SERVER ('localhost:1121', 'dba', 'dba');
    SQL> REPL_CREATE_SNAPSHOT_SUB (REPL_SERVER_NAME ('localhost:1121'), 'Demo.demo.Shippers', 2);
    SQL> REPL_INIT_SNAPSHOT (REPL_SERVER_NAME ('localhost:1121'), 'Demo.demo.Shippers', 2);

Syncing bi-directional snapshot publication

This example demonstrates syncing bi-directional snapshot publication of table 'Demo.demo.Shippers'.

    SQL> REPL_UPDATE_SNAPSHOT ('Demo.demo.Shippers', 2);

See Also:

The following functions are used for creating, dropping and updating publications and subscriptions to them:

REPL_CREATE_SNAPSHOT_SUB()

REPL_CREATE_SNAPSHOT_PUB()

REPL_DROP_SNAPSHOT_SUB()

REPL_DROP_SNAPSHOT_PUB()

REPL_INIT_SNAPSHOT()

REPL_UPDATE_SNAPSHOT()

REPL_SNP_SERVER()

REPL_SERVER_NAME()

#### 11.2.4.1. Conflict Resolution

Since every table can have only one publisher, conflicts can only occur on the publisher when modifications from a subscriber are attempted. When DML operations originating on a subscriber are being replayed on the publisher, three types of conflicts can arise:

1. uniqueness conflict (insert conflict)

   occurs when the row with some primary key <PK> already exists in publisher's table.

2. update conflict

   occurs when UPDATE modifies a row which has already been modified on publisher (by the publisher or another subscriber)

3. delete conflict

   occurs when DELETE deletes a row that does not exist on publisher anymore.

Delete conflicts when UPDATE modifies a row that does not exist on publisher can't be detected in snapshot replication case.

Every table has a number of conflict resolvers which are used for conflict resolution which are enlisted in the DB.DBA.SYS_SNAPSHOT_CR system table. Each conflict resolver has a type, one of ('I', 'U', or 'D'), and an order. Conflict resolvers are applied in ascending order.

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.

Conflict resolvers of different types have different signatures:

- 'I' - Insert conflict resolvers

  (<ALLCOLS>, inout _origin varchar)

- 'U' - Update conflict resolvers

  (<ALLCOLS>, inout _origin varchar)

- 'D' - Deletion conflict resolvers

  (<PK>, inout _origin varchar)

where

<ALLCOLS> are the new values of all columns (including the ROWGUID column), <PK> are the values of primary key columns, and _origin is transaction originator.

Conflict resolvers can return the following integer values; The conflict resolver types concerned for each are listed in parentheses:

- 0 - un-decide (I, U, D)

  next conflict resolver will be fired.

- 1 - subscriber wins (I, U, D)

  DML operation will be applied with <ALLCOLS> All the subscribers except originator will receive modifications (originator already has them).

- 2 - subscriber wins, change origin (I, U)

  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.

- 3 - publisher wins (U)

  DML operation will be applied with <ALLCOLS> taken from publisher's table. All the subscribers will receive modifications.

- 4 - reserved

- 5 - ignore (D)

  DML operation is ignored.

Conflict resolution stops when some conflict resolver returns a non-zero value meaning that it has made a decision.

Conflict Resolution

Suppose we have the following table:

    create table items(
      item_id integer primary key,

      name varchar,
      price decimal
    );

"Publisher wins" 'I' conflict resolver will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _origin varchar)
      returns integer
    {
      return 3;
    }

The conflict resolver that will make a decision based on the minimal price column will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _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
    }

The conflict resolver that will change the price to the minimal value will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _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
    }

Conflict resolution occurs differently for each kind of DML operation:

- INSERT

  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 descision (return non-zero value) the default action is 'publisher wins'.

- UPDATE

  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'.

- DELETE

  When DELETE operation 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.

#### 11.2.4.2. Conflict Resolution in WebDAV

Conflict resolvers in DAV are found based on the collection. The closest collection that specifies resolvers will be the one providing the resolver set. Resolvers from any enclosing collection will not be invoked.

There is special subcollection in each replicated collection named '_SYS_REPL_BACKUP'. This subcollection is never replicated and is used to store backup copies of resources that lose conflict resolution.

If a resource is locked during an update it will be stored in temporary location. Conflict resolution will take place after the lock is released. If the update was performed on the publisher then no conflict resolution will be performed on the subscribers and the resource from temporary location will simply replace an existing resource after the lock is released.

Default conflict resolution is 'publisher wins'.

Delete conflicts are not handled when replicating DAV because updates are the same as inserts -- if the resource that was updated does not exist it will be created. INSERT or uniqueness conflict for the same reasons.

Subcollections are not considered in conflict resolution. Subcollections are always created as needed during an update. This means that if a resource is updated locally but collection that holds this resource does not exist on remote peer it will be created.

#### 11.2.4.3. Automatically Generated Conflict Resolvers

Simple table conflict resolvers can be generated automatically by calling the REPL_ADD_SNAPSHOT_CR() function. DAV conflict resolvers be generated by calling REPL_ADD_DAV_CR() function.

See Also:

REPL_ADD_DAV_CR()

REPL_ADD_SNAPSHOT_CR()

The generated procedures can be modified afterwards. In particular it is possible to change the notification e-mail address by setting the _notify_email parameter, and the notification text by setting the _notify_text parameter.

The default behaviour for generated procedures is 'pub_wins', making a backup and notifying the owner by e-mail.

### 11.2.5. Registry variables

- snp_repl_tolerance_offset (default 15)

  In incremental and bi-directional snapshot time stamp of last replayed snapshot log entry (LAST_TS) is kept in system tables. LAST_TS is used to determine the starting point of the next update. This value can't be used as is for the following reasons:

  - Local time may be adjusted occasionally on the servers which participate in replication.

  - Virtuoso snapshot replication triggers use now() function to get snapshot log entry time stamp. If transaction is started before updating snapshot log and ended after updating snapshot log is finished, data modifications made in this transaction will be missed.

  In order to resolve the issues mentioned above Virtuoso reads snapshot logs starting from LAST_TS minus some tolerance offset. The value of tolerance offset (in minutes) is kept in "snp_repl_tolerance_offset" registry variable. For the reasons mentioned above tolerance offset should be longer than length of any transaction which modifies published tables.

- snp_repl_purge_offset (default 30)

  In order to prevent snapshot log entries to be replayed more than once Virtuoso keeps log of replayed snapshot log entries (rplog). Rplog entries need to be purged periodically. Virtuoso automatically schedules rplog purger when snapshot log for some table is created. All the records in rplog with time stamp less than MIN(LAST_TS) minus some offset are purged. The value of purge offset (in minutes) is kept in "snp_repl_purge_offset" registry variable. Normally, purge offset should be greater than tolerance offset.

### 11.2.6. Heterogeneous snapshot replication

Virtuoso allows incremental and bi-directional snapshot replication flavors to be used with non-Virtuoso databases.

The following databases are supported in incremental snapshot replication:

- SQL Server 2000 and later

- Oracle 8i and later

- IBM DB2 8.1 and later

- Informix IDS 9.40 and later

The following databases are supported in bi-directional snapshot replication:

- SQL Server 2000 and later

- Oracle 8i and later

- IBM DB2 8.1 and later

DBMS-specific notes:

- SQL Server 2000 and later

  Bi-directional snapshot triggers for SQL Server require "nested triggers" server option to be "On" (which is "On" by default).

- Oracle 8i

  - Oracle 8i does not have support for "localtimestamp" data type out of box. Required functionality can be enabled by editing $ORACLE_HOME/rdbms/admin/standard.sql script. The following statement

        pragma new_names('8.1.6',
                         dbtimezone, sessiontimezone, localtimestamp,
                         localtime, to_local_tz, to_db_tz,
                         cube, rollup, grouping, "TIMESTAMP WITH LOCAL TIME ZONE");

    should be changed to

        --pragma new_names('8.1.6',
        --                 dbtimezone, sessiontimezone, localtimestamp,
        --                 localtime, to_local_tz, to_db_tz,
        --                 cube, rollup, grouping, "TIMESTAMP WITH LOCAL TIME ZONE");

    After $ORACLE_HOME/rdbms/admin/standard.sql is edited it should be executed with SYS user privileges:

        $sqlplus SYS/CHANGE_ON_INSTALL @$ORACLE_HOME/rdbms/admin/standard.sql

    where "CHANGE_ON_INSTALL" is password for user SYS.

    Oracle 9i and later has this type in the default installation and this step is not necessary.

  - ODBC driver from Oracle 8.1.6 (and earlier versions) is known to be buggy and may cause Virtuoso server to crash when snapshot replication with Oracle database is set up. ODBC driver from Oracle 8.1.7 and later is recommended.

  - Oracle ODBC driver uses Oracle NLS settings by default for determining decimal separator. If decimal separator in the locale is not '.' (period) there will be interoperability errors with Virtuoso. Oracle ODBC driver can be forced to use US Settings for numerics: open Oracle ODBC driver configuration dialog, select "Application" tab and set "Numeric Settings" to "Use US Settings".

- DB2 8.1 ESE

  - FixPak 3 is recommended because ODBC driver from earlier DB2 8 versions may cause Virtuoso server to crash when BLOB columns exist in replicated tables.

  - DB2 CLI/ODBC driver uses default locale's decimal separator. If decimal separator in the locale is not '.' (period) Virtuoso fails to parse numeric values. The default behavior of the DB2 CLI/ODBC driver can be modified by specifying PATCH2 CLI/ODBC configuration keyword in the db2cli.ini initialization file. Add the following line to your DSN config in db2cli.ini:

        PATCH2=15

    This will force DB2 CLI/ODBC driver to always use period ('.') as decimal separator.

- Informix IDS 9.40

  Informix IDS can't report current time in UTC. This may cause snapshot replication to fail when timezone change occurs. In order to set up snapshot replication with Informix server properly any of the following steps must be taken:

  - Informix server must be run in UTC time zone.
  - "snp_repl_time_offset" registry variable should be set to value greater than 60.
  The second method may cause additional system load on updates and will cause transactions to be replayed out of order when timezone change occurs.

### 11.2.7. Data type mappings

Heterogeneous replication requires data type mapping to be performed when Virtuoso table is created on replica.

Table: 11.2.7.1. Data type mappings
Virtuoso | SQL Server | Oracle | DB2 | Informix
varchar | varchar(8000) | VARCHAR2(4000) | VARCHAR(32000) | VARCHAR(254)
varchar(n) | varchar(n) | VARCHAR2(n) | VARCHAR(n) | VARCHAR(n)
integer | int | INTEGER | INTEGER | INTEGER
smallint | smallint | SMALLINT | SMALLINT | SMALLINT
real | real | FLOAT | REAL | SMALLFLOAT
double precision | float | DOUBLE PRECISION | DOUBLE | FLOAT
numeric | numeric(38, 15) | NUMERIC(38, 15) | NUMERIC(32, 15) | DECIMAL(31, 15)
numeric(p, s) | numeric(p, s) | NUMERIC(p, s) | NUMERIC(p, s) | DECIMAL(p, s)
date | datetime | DATE | DATE | DATE
time | datetime | DATE | TIME | DATETIME HOUR TO SECOND
datetime, timestamp | datetime | DATE | TIMESTAMP | DATETIME YEAR TO FRACTION(5)
varbinary | varbinary(8000) | RAW(2000) | VARCHAR(32000) FOR BIT DATA | BYTE
varbinary(n) | varbinary(n) | RAW(n) | VARCHAR(n) FOR BIT DATA | BYTE
long varbinary | image | BLOB | BLOB | BYTE
long varchar | text | CLOB | CLOB | TEXT
nvarchar | nvarchar(4000) | NVARCHAR2(4000) | VARCHAR(32000) FOR MIXED DATA | NVARCHAR(254)
nvarchar(n) | nvarchar(n) | NVARCHAR2(n) | VARCHAR(n) FOR MIXED DATA | NVARCHAR(n)
long nvarchar | ntext | NCLOB | DBCLOB | TEXT

### 11.2.8. Objects created by incremental snapshot replication

Table "DB.DBA.RPLOG_<name>" (replay log) is created in Virtuoso database for replayed snapshot log entries.

Table "RLOG_<name>" (snapshot log) is created in the source DSN. This table is attached as "<qual>"."<dsn>"."RLOG_<name>" if source table is an attached table. Other objects created by incremental snapshot replication in the source DSN are:

- Virtuoso

  Triggers "<qual>_<owner>_<name>_I_log", "<qual>_<owner>_<name>_U_log" and "<qual>_<owner>_<name>_D_log" on replicated table.

- SQL Server

  Triggers "<name>_I_log", "<name>_U_log" and "<name>_D_log" on replicated table.

- Oracle

  Triggers "<name>_I_log", "<name>_U_log" and "<name>_D_log" on replicated table.

  Function OPL_GETUTCDATE().

- DB2

  Triggers "<name>_I", "<name>_U" and "<name>_D" on replicated table.

  Sequence opl_seq_rowguid.

- Informix

  Triggers "<name>_I_log", "<name>_U_log" and "<name>_D_log" on replicated table.

  Sequence opl_seq_rowguid.

  Stored procedures "<name>_R_proc" and "<name>_U_proc".

### 11.2.9. Objects created by bi-directional snapshot replication

Table "DB"."DBA"."RPLOG_<name>" (replay log) is created in Virtuoso database for replayed snapshot log entries.

Table "RLOG_<name>" (snapshot log) is created on the publisher and on subscribers. "RLOG_<name>" table and replicated table itself are attached from subscribers as "<qual>"."<dsn>"."RLOG_<name>" and "<qual>"."<dsn>"."<name>" respectively. Other objects created by bi-directional snapshot replication on the publisher and on subscribers are:

- Virtuoso

  Triggers "<qual>_<owner>_<name>_I_log", "<qual>_<owner>_<name>_U_log" and "<qual>_<owner>_<name>_D_log" on replicated table.

- SQL Server

  Triggers "<name>_I_log", "<name>_U_log" and "<name>_D_log" on replicated table.

- Oracle

  Triggers "<name>_I_log", "<name>_IR_log", "<name>_U_log", "<name>_UR_log", "<name>_UD_log", "<name>_UDR_log", and "<name>_D_log" on replicated table.

  Function OPL_GETUTCDATE().

  Global temporary table "DLOG_<name>".

- DB2

  Triggers "<name>_I", "<name>_U", "<name>_UD" and "<name>_D" on replicated table.

  Sequence opl_seq_rowguid.

### 11.2.10. Replication system tables

#### 11.2.10.1. SYS_SNAPSHOT

    CREATE TABLE SYS_SNAPSHOT (
        SN_NAME	VARCHAR(255),
        SN_QUERY    VARCHAR(1024),
        SN_LAST_TS	VARBINARY(30),
        SN_IS_INCREMENTAL	INTEGER,
        SN_SOURCE_TABLE	VARCHAR(255),
        PRIMARY KEY (SN_NAME))

This table describes all defined snapshot replication relations. SN_NAME is the snapshot's name (destination table name). SN_QUERY is the query to get the data from. SN_LAST_TS is the time of the last update reflected for incremental snapshots (from the snapshot log's SNAPTIME). SN_IS_INCREMENTAL is null for nonincremental snapshots and non-null for the incremental ones. SN_SOURCE_TABLE is the source table for the incremental snapshots, NULL otherwise.

This implies that an incremental snapshot can be updated in two ways - incrementally and nonincrementally as the nonincremental definition is a subset of the incremental definition.

#### 11.2.10.2. SYS_SNAPSHOT_LOG

    CREATE TABLE SYS_SNAPSHOT_LOG (
        SNL_SOURCE	varchar (320) NOT NULL,
        SNL_RLOG	varchar (320) NOT NULL,
        SNL_RPLOG	varchar (320) NOT NULL,
        primary key (SNL_SOURCE)
    )

This table describes all defined snapshot replication relation logs. SNL_SOURCE is the base table for witch a snapshot log is defined. SNL_RLOG is the name of the RLOG snapshot log auxiliary table. SNL_RPLOG is the name of the RPLOG snapshot log auxiliary table.

### 11.2.11. Table snapshot logs

In order to be able to create incremental snapshots on a table there should be a snapshot log table defined in the following fashion:

    create table RLOG_sourcetable (
    RLOG_sourcetable_primarykey_parts,
    ...
    SNAPTIME DATETIME,
    DMLTYPE	 CHAR(1),
    RLOG_ROWGUID VARCHAR(255),
    PRIMARY KEY (RLOG_*));

The name of the snapshot log table is constructed from the name of the source table by prefixing it with RLOG_ .

Snapshot log table contains all the primary key parts of the source table prefixed with RLOG_ .

Snapshot log table contains at most one row per updated source table row with the update time and update type in DMLTYPE (I - for insert, U - for update, D - for delete).

Snapshot log table contains RLOG_ROWGUID column which uniquely identifies this snapshow log row.

An updating procedure for incremental snapshot or bi-directional snapshot (table case) reads records from snapshot log table, ordered by SNAPTIME. Commits are performed each time an updating procedure notices that SNAPTIME has changed. In Virtuoso case (when native table is snapshot-replicated) all snapshot log records with equal SNAPTIME belong to the same transaction and all such records constitute almost a transaction (it is not exactly a transaction because only the last DML operation for each primary key in the source table is recorded in snapshot log table). So in Virtuoso case an updating procedure commits almost per transaction on source table. Snapshot log records are not transaction-bound in heterogeneous case however. Nevertheless, the technique used to determine when to commit described above prevents running out of transaction log or deadlocks even in heterogeneous case.

Replay log is created on the server which performs sync (server on which snapshot log was create in ordinary case, publisher in bi-directional snapshot case) to handle correct snapshot log replaying. Replay log is purged periodically using REPL_PURGE_RPLOGS() function in ordinary case and REPL_PURGE_URPLOGS() in bi-directional case. The calls to this functions are automatically scheduled when snapshot log or bi-directional snapshot publication is created and there is no need to call them manually.

There should be triggers on the source table to update the log like that:

    create trigger xxx_I_log after insert on xxx
    {
      insert replacing RLOG_xxx values (xxx_pk, now(), 'I', uuid());
    };

    create trigger xxx_D_log after delete on xxx
    {
      insert replacing RLOG_xxx values (xxx_pk, now(), 'D', uuid());
    };

    create trigger xxx_U_log after update on xxx
    {
      if (OLD.xxx_pk differs from NEW.xxx_pk)
        {
          insert replacing RLOG_xxx values (OLD.xxx_pk, now(), 'D', uuid());
          insert replacing RLOG_xxx values (NEW.xxx_pk, now(), 'I', uuid());
        }
      else
        insert replacing RLOG_xxx values (xxx_pk, now(), 'U', uuid());
    };

where xxx is the source table name, RLOG_xxx is the snapshot log table name, xxx_pk is a comma separated list of the source table's primary key parts. The names of the triggers are constructed from the fully qualified name (DB.DBA.Orders makes DB_DBA_Orders_I_log).

Table snapshot logs and triggers are created automatically for Virtuoso and databases listed in Heterogeneous Snapshot Replication section.

Previous Introduction | Chapter Contents | Next Transactional Replication