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;

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;

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;

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'

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'

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

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