Chapter 4
Empress Replication Details

A replication table has some additional features, minor overheads and restrictions regarding to a normal table. For replication purposes, two system attributes (namely EMPRESS_TIMESTAMP and EMPRESS_LOGIC_DELETED) have been added. See [New System Attributes] for more information.

Database containing a Replication Table also contains information about the relationship of this table with its Replication Replicates and with its Replication Masters. This information is in the form of entries in system tables related to Replication. See [New System Tables for Replication Tables, Replication Relations and Synchronization Status] for more information about these system tables,

For a list of Command Restrictions on Replication Tables see: [Restrictions on DDL and DML commands for Replication].

4.2.1 Master Table

A Replicate Table inherits the table structure and indices of its Replication Master Table when it is created. For the sake of performance, creating a Replicate Table provides an option for the initial loading of the Replication Master Table data.

4.2.3 Original Master Table

Only two Replication Tables that have the same Original Master Table Information can participate in a Replication Relation with each other, i.e. can perform Replication Synchronization with each other.

4.2.4 Replication Table Switch

When a replicate table is switched into master table, the updates will be made to the new Master Table, and the updated data will be propagated to other Replicate Tables in the Replication World. After a successful switch to a master table, a new master table is created, which is called Current Master Table. The timestamp which the switching is occurred is called Current Master Table Start Timestamp (CMTS).

Existence of more than one update-able Master Tables in a Replication World causes confusion. It is the responsibility of the creator of the old Master Table to alter it to a Replicate Table. It is also the responsibility of the creator of the new Master Table to authorize the candidate RRTs.

When a Replication World is created, there exists only one Master Table which is the Original Master Table. The CMTS is the creation timestamp of this Master Table. When a Replicate Table is switched into a Master Table, CMTS is changed to the switching time of the new Master Table.

When a Replicate Table is created, it automatically inherits CMTS from its RMT. Consider an example where an RRT having CMTS1 tries to synchronize with its RMT having CMTS2. The following situations might occur:

• if CMTS1 = CMTS2: the synchronization is a Normal Synchronization

• if CMTS1 < CMTS2: it means that a new current master table is produced, i.e. a replicate table is switched to a master table. In this case the synchronization process might need to do some recovery to rollback the unnecessary data on RRT. After this synchronization and required recovery, RRT's CMTS is changed to CMTS2 of its RMT.

• if CMTS2 > CMTS2: then RRT has more recent data than its RMT, and the synchronization will be refused.

4.3 Replication Relation

For a Replication Table T, the candidate tables that are "authorized" to be its RRTs are specified. The information to access each "authorized" RRT is added as a Replication Replicate Entry to a list of Replication Replicates. Adding a Replication Replicate for a Table is described in [Creating Replication Replicate ].

For a Replication Table T, the candidate tables that are "claimed" to be its RMTs are specified. The information to access each "claimed" RMT is added as a Replication Master Entry to a list of Replication Masters. Adding a Replication Master Entry for a Table is described in [Creating Replication Master].

A Replication Relation is formed when data between an RMT and a corresponding RRT is exchanged. A Replication Relation is a relation between an RMT and an RRT. RRT is "authorized" to be Replication Replicate Table for RMT, by having a Replication Replicate Entry accessing to RRT in a list of Replication Replicate entries for RMT. RMT is "claimed" to be Replication Master Table for RRT by having a Replication Master Entry referring to RMT in a list of Replication Replicate entries for RRT. This is shown in [Figure: Replication Relation].

4.3.1 Replication World

In normal case, there is only one master table and multiple replicate tables in a Replication World. However it is possible that there are multiple master tables in a replication world because of a Replication Table Switch. A Replication World can have an inter-related Model in which:

• a Replication Table (usually a Replicate Table) can have more than one RMTs participating in different Replication Relations.
• A Replicate Table can simultaneously be RRT in one Replication Relation, and be the RMT in another Replication Relation.
• a Replication Table can have more than one RRTs participating in different Replication Relations.

Figure 4-2
Replication World

There are cases where a Replicate Table needs to become an RMT for another Replicate Table. In this case, the Replicate Tables that will do synchronization from this table must be authorized. Then either the replicate tables that are authorized to do synchronization shall be created, or if the replicate table is already created, it needs to claim a new RMT for itself.

Each Replication Table might have many Replication Replicates, as well as many Replication Masters. Talking about a certain Replication Relation, one specific Replication Master and one specific Replication Replicate are considered. That Replication Master will be in RMT-Side position and that Replication Replicate will be in RRT-Side position.

A Replicate Table can serve as an RRT in one Replication Relation and serve as an RMT in another Replication Relation. This allows the scenario where the Master Table lives in the corporate Head Quarter, a set of replicate tables lives in the regional offices, and further set of replicate tables lives in the local offices and the entire set of tables represents a replication world. Note that being an RMT does not necessarily allow a Replication Table to be update-able by the users of that Replicate Table. A Replication Table must be a Master Table to be update-able.

4.3.2 RMT-Side and RRT-Side

In Empress Replication an RMT-Side and an RRT-Side are relative terms related to a certain replication relationship and the elements that form that relationship.

For example consider two replication tables T1 and T2, where T2 is a replicate of T1. A Replication Relation between a table T1 and a table T2 is established when both of the following conditions satisfy:

1. T1 has a Replication Replicate Entry that authorizes T2 to be its Replication Replicate table
2. T2 has a Replication Master Entry that claims T1 be its Replication Master Table.

Figure 4-1
Replication Relation

In the next two sections we describe Replication Replicate Entry and Replication Master Entry. These entries contain information about Replication Relations. See [Viewing Replication tables and synchronization status] for viewing information of Replication Replicate Entry and Replication Master Entry.

4.3.3 Replication Replicate Entry

Each Replication Table has a list of Replication Replicate Entries. Only an authorized table can be a candidate RRT of a certain RMT. This authorization is necessary because RMT is a replication origin only for replicas that are specified as its RRTs. No other non-authorized table should be able to copy the data of an RMT. These authorities are specified by the Replication Replicate Entries.

An RRT can be either a Replicate Table, or a Master Table. If RRT is a Master Table, it can not get data from its RMT. If this table is switched to a Replicate Table, it can synchronize from its RMT.

Figure 4-3
Replication Replicate Entry

4.3.4 Replication Master Entry

A Replication Master Entry can be defined for both a Master Table and a Replicate Table, but a Master Table having a Replication Master Entry is not very common, and occurs usually because of a Replication Table Switch.

When a Replicate Table is created, a Replication Master Entry with order number "0", and "Enabled" status is created implicitly. Creating an explicit Replication Master Entry is described in [Creating Replication Master].

Figure 4-4
Replication Master Entry

4.3.6 RMT Switch

When selecting the RMT for synchronization, a different RMT from the previous one might be selected. Choosing a different RMT from the previous one, is called RMT Switch for that Table. Automatic RMT Switch is done when a problem in connection between RRT-Side and an RMT-Side occurs, as explained in [Connection between an RMT-Side and an RRT-Side]. RMT Switch can also happen when the list of Replication Master Entries is manually altered. The alteration to the list of Replication Master Entries can be because of one or more of the followings:

• Adding new Replication Master Entries
• Removing some of the existing Replication Master Entries
• Enabling or Disabling the RMTs
• Changing the Replication Master Order of some of the RMTs

1. [Defining Master Table] which is changing the type of an existing table (either a Normal Table or a Replicate Table) to MASTER,
2. [Defining Replicate Table] which is either creating a Replicate Table or changing the type of a Master Table which has a Replication Master Entry, to REPLICATE,
3. [Undefining a Master Table or a Replicate Table] which is changing the type of a Master Table or a Replicate Table to NORMAL, and
4. [Dropping a Master Table or a Replicate Table].

4.4.1 Defining a Master Table

In order to have a data replication, a Master Table must be defined. "Defining a Master Table" is changing an existing table's type to MASTER. This is done using the SQL command:

   *  ALTER TABLE table_name CHANGE TABLE TYPE TO MASTER;

When a normal table is converted to a Master Table, a Replication World is created. When a Replicate Table is converted to a Master Table, it is a Replication Table Switch. Note that A Replicate Table that is created from a [Subset Replication] can not be switched to a Master Table. Also a View can not be converted to a Master Table.

4.4.2 Defining a Replicate Table

A Replicate Table inherits table structure and index definition from its related RMT. The command for creating a Replicate Table is:

   * CREATE [AND INSERT INTO] REPLICATE replicate_table 
      FROM replication_master_info;

Where:
   replication_master_info ::= server_name:database_name:table_name

If the option [AND INSERT INTO] is specified, after creating the replicate_table, the data from the RMT will be loaded into Replicate Table. This is similar to an initial Synchronization with the RMT.

Before creating a Replicate Table:

1. Replicate Table must have been authorized to be an RRT for RMT accessed by replication_master_info. That is to say there should exist a Replication Replicate Entry accessing replicate_table for the assigned RMT. This is explained in [Creating Replication Replicate].
2. There should be a connection established between RRT-Side and RMT-Side. See [Connection between an RMT-Side and an RRT-Side] for more information on this connection requirement.

Defining a Replicate Table is also possible by switching a Master Table to a Replicate Table, which is changing an existing master table's type to REPLICATE. For a table to be a replicate table, replication master entries must exist on it. Changing type of a table to REPLICATE is allowed only for a Master Table that was originally created as a Replicate Table, and later had changed to a Master Table, still keeping its Replication Master Entries. Changing type of a table to REPLICATE is done using the SQL command:

 * ALTER TABLE table_name CHANGE TABLE TYPE TO REPLICATE;

Undefining a master table or a replicate table are done using the SQL command:

   * ALTER TABLE replication_table CHANGE TABLE TYPE TO NORMAL;

Empress RDBMS does not allow undefining a Replication Table (changing its type to NORMAL), when Replication Replicate entries or Replication Master entries exist on that table. Prior to changing type of a table to NORMAL, the user has to remove the replication relationships from that table; that is removing Replication Replicate Entries and Replication Master Entries of that Replication Table. Additionally, if replication_table has Replication Master Entries, and a connection between RRT-Side (containing replication_table) and RMT-Side can not be established, dropping the table is refused. For undefining a Replication Table without removing replication relationships see [Overriding Regular Empress Replication Controls].

When a replication table is converted back to normal table, the applicable restrictions and overheads for a replication table are not applied to the normal table anymore.

4.4.4 Dropping a Master Table or a Replicate Table

Dropping a Replication Table, is like dropping a normal table. Use the SQL command

   * DROP TABLE table_name;

• If Replication Replicate Entries exist on table, the drop operation will be refused.
• If Replication Master Entries exist on table, and a connection with any of RMT-Sides can not established, the dropping operation will be refused.

4.5 Operations on Replication Relation

1. Creating and Dropping Replication Replicate,
2. Creating and Dropping Replication Master,
3. Changing Replication Master Order, and
4. Enabling or Disabling Replication Masters.

"Creating Replication Replicate" is giving an authorization to a Replication Table to have a table as its candidate RRT. Creating Replication Replicate is done by using the following SQL command:

   * CREATE REPLICATION REPLICATE replication_replicate_info ON replication_table;

Where:
   replication_replicate_info ::= host_name:database_name:table_name

"Dropping Replication Replicate" is taking away the authorization from a Replication Table to have a table as its candidate RRT. Dropping Replication Replicate is done using the following SQL command:

  * DROP REPLICATION REPLICATE replication_replicate_info ON replication_table;

Where:
   replication_replicate_info ::= host_name:database_name:table_name

To "Drop all Replication Replicates" of a Replication Table, use the following SQL command:

 * DROP ALL REPLICATION REPLICATES ON replication_table;

Note that execution of these two commands does not drop any database tables.

4.5.3 Creating Replication Master

Whenever a replicate table is created, it has a default RMT associated with it. This means that CREATE REPLICATE TABLE replicate_table command implicitly creates a Replication Master Entry for replicate_table. Empress RDBMS can support multiple RMTs for a Replication Table. This can be done by explicitly creating Replication Master Tables.

"Creating Replication Master" is claiming another RMT for the assigned Replication Table (mostly a Replicate Table). The command to create replication master table is:

   * CREATE REPLICATION MASTER replication_master_info ON replication_table;

Where:
   replication_master_info::= server_name:database_name:table_name  

For a successful execution of this command:

1. A Connection between RMT-Side and RRT-Side must be established.
2. replication_table must be authorized to be a candidate RRT for the RMT accessed by replication_master_info. This means that a corresponding Replication Replicate Entry must exist for RMT accessed by replication_master_info.
3. In special case, if a connection between RRT-Side and RMT-Side cannot be established, a Replication Master Entry can still be added for replication_table. See [ Overriding Regular Empress Replication Controls] for information on forcing the command.

"Dropping Replication Master" is unclaiming a table from being a candidate RMT of a Replication Table (mostly a Replicate Table). Dropping Replication Master is done using the following command:

   * DROP REPLICATION MASTER replication_master_info ON replication_table; 

Where:
   replication_master_info::= server_name:database_name:table_name  

To "Drop all the Replication Masters" of a Replication Table, use the following command:

   * DROP ALL REPLICATION MASTERS ON replication_table;

Successful execution of these two commands requires a connection between RRT-Side and RMT-Side. If a connection between RRT-Side and RMT-Side can not be established, Replication Master Entry can still be dropped from replication_table. See [ Overriding Regular Empress Replication Controls] for information on forcing the command.

Note that execution of these two commands does not drop any database tables.

4.5.5 Changing Replication Master Order

When a Replication Master Entry is added for a replication table, it is given a default order number. The order number starts with "0" for the first Replication Master Entry and is increased with step of "1" for each added Replication Master Entry. The order number of a Replication Master Entry specifies the Replication Master Order. The command to change the Replication Master Order is:

   * ALTER replication_table CHANGE REPLICATION MASTER 
        ORDER replication_master_info order {,replication_master_info order };
Where:
  replication_master_info::= server_name:database_name:table_name
  order ::= A real number

A Replication Master Entry is enabled by default and it can be manually enabled or disabled. When an RMT is disabled manually, the replication table accessed by that entry is not considered as an RMT. So the synchronization utility does not consider a disabled Replication Master Entry as a source for synchronization. Disabling a Replication Master neither removes the replication relationship between a Replication Table and its RMT, nor changes its Replication Master Order.

The SQL command to disable a Replication Master Entry for a Replication Table is:

   * ALTER replication_table DISABLE REPLICATION MASTER 
       replication_master_info;
Where:
   replication_master_info::= server_name:database_name:table_name

The SQL command to disable all the Replication Master Entries of a Replication Table is:

   * ALTER replication_table DISABLE ALL REPLICATION MASTERS;

The SQL command to enable Replication Master Entry of a Replication Table is:

   * ALTER replication_table ENABLE REPLICATION MASTER 
        replication_master_info ;
Where:
   replication_master_info::= server_name:database_name:table_name

To Enable all the Replication Master Entries of a Replication table:

   * ALTER replication_table ENABLE ALL REPLICATION MASTERS;

1. Existence of a connection problem between RMT-Side and RRT-Side
2. Recovery purpose
3. Need for applying too many pre-steps before regular execution of a command

General form of overriding the replication commands is applying the option WITH FORCE to the end of some replication SQL commands. The results of applying force option on these commands are explained in [References: SQL Commands]. The commands that accept WITH FORCE option are:

• ALTER table_name CHANGE TABLE TYPE TO NORMAL WITH FORCE;

  When changing a Master Table or a Replicate Table table_name to a normal table, if [WITH FORCE] option is not used, Empress RDBMS checks whether there exists any Replication Master Entries and/or Replication Replicate Entries for that table, and refuses to apply the change in case of existence of entries. If [WITH FORCE] option is used, the Replication Replicate entries and/or Replication Master entries for table_name will be removed, and the table type will be changed to NORMAL.

• CREATE REPLICATION MASTER replication_master_info ON replication_table WITH FORCE;

  IF [WITH FORCE] option is used, a replication master entry replication_master_info for replication_table is created, even if a connection between RMT-Side and RRT-Side can not be established, or if replication_table is not authorized to be a RRT, or if replication_master_info is not a valid replication table.

• DROP REPLICATION MASTER replication_master_info ON replication_table WITH FORCE;

  By using the option: [WITH FORCE], Replication Master Entry replication_master_info of replication_table is dropped, even if connection between RRT-Side and RMT-Side can not be established.

• DROP ALL REPLICATION MASTERS ON replication_table WITH FORCE;

  By using the option: [WITH FORCE], all Replication Master Entries on replication_table are dropped, even if connection between RRT-Side and RMT-Side can not be established .

• DROP TABLE table_name WITH FORCE;

  When the table table_name is a Master or a Replicate table, Empress RDBMS refuses to drop the table if Replication Replicate entries exist on the table table_name. Using the option [WITH FORCE] directly drops the table and removes all of its Replication Master Entries and Replication Replicate Entries.

• emprepsync -f replicate_database replicate_table

  Usually an RRT shall not contain more recent data than its RMT during Synchronization. The [-f] option forces emprepsync to synchronize replicate_table with the RMT accessed by successfully connected Replication Master Server, even if the contents of replicate_table are more recent than that of its RMT. Note that [-f] option works only when Current Master Table Start Timestamp (CMTS) of replicate_table is equal to CMTS of its chosen Replication Master Table participating in the Replication Synchronization. The CMTS of a replicate table might be larger than CMTS of its Replication Master Table because of a Replication Table Switch.

• empsvadm server_name stop force

  The Empress Server will stop only after all the services serving its Clients are terminated. If used with [force] option, the busy servers will be forced to terminate.

The SQL command to view this information is:

   * DISPLAY TABLE table_name ALL;

Examples for applying this command to a Master Table and to a Replicate Table are given in: [ References: Examples of Viewing Replication Tables and Synchronization Status]

4.8 Subset Replication

Figure 4-5:
Full set versus Subset Replication

Full set Replication Master                  Replicate

Subset Replication Master                      Replicate

Subset replication duplicates rows from the master that satisfy a predetermined condition. To put it more technically, The subset condition is a restricted SQL WHERE clause, which we call Subset Replication Search Condition (SRSC). To support a Subset Replication, the Master Table must have a primary key, and must assign a list of attributes for SRSC. This list of attributes is called Subset Attribute List and is a list of attributes from the Master Table on which the SRSC will be defined. SRSC does not allow explicit database and table references. All the attributes that are referred in an SRSC must be specified in the Subset Attribute List when creating a Master Table. This restriction prevents joins and sub-query statements.

During Subset Replication, similar to the full set replication, new data values are entered into the master table then propagated to an arbitrary number of replicate tables. The command for specifying the Subset Attribute List is:

   * ALTER TABLE table_name CHANGE TABLE TYPE TO MASTER 
       FOR SUBSET ON (attr [, attr ...]);
Where:
   attr ::= attribute(s) in Subset Attribute List. 

 * ALTER TABLE TorontoPList CHANGE TABLE TYPE to MASTER
    FOR SUBSET ON (prod_lang, prod_price);

When creating replication replicates, the optional SRSC must be defined by a WHERE clause using the following command:

   * CREATE REPLICATION REPLICATE replication_replicate_info 
      ON replication_table WHERE srsc;

Where:
   replication_replicate_info ::= host_name:database_name:table_name
   srsc ::= Subset Replication Search Condition 

 * CREATE REPLICATION REPLICATE NewYorkHost:NewYorkDB:NewYorkPList
     ON TorontoPList WHERE prod_lang='english';

Beside a Master Table, a Replicate Table can also authorize RRTs using SRSC. This SRSC can only refer to attributes that are defined in Subset Attribute List of the Master Table. In this case, the defined SRSC will implicitly combine all the SRSCs set by its replication masters. For example if BrooklynPList is authorized to be an RRT of NewYorkPList using the command:

 * CREATE REPLICATION REPLICATE BrooklynHost:BrooklynDB:BrooklynPList
  ON NewYorkPList WHERE prod_price < 5000;

  prod_lang='english' AND prod_price < 5000

It is useful to note that a full set replication model offers higher performance than subset replication models. Empress Replication does not allow a subset of attributes of a Master Table to be replicated. This means that the whole table structure of a Master Table must be replicated to any RRT. The difference is that when using a subset replication, only selected rows chosen upon SRSC will be replicated.

4.9 System Attributes and System tables related to Replication

New System attributes and System Tables are introduced for Replication purposes. These are:

1. New System Attributes
2. New System Tables for Replication Tables, Replication Relation and Synchronization Status

The deleted records of a Replication Table are only marked as "Deleted" by setting attribute EMPRESS_LOGIC_DELETED of that record. Purging is physically deleting a record from a database table. These marked records are "purged" only after their data is synchronized with the replicates of their table. Note that the records marked as "Deleted" are considered as deleted records, and are only used internally for Replication purposes, so users can not retrieve "Deleted" records.

4.9.2 New System Tables for Replication Tables, Replication Relations and Synchronization Status

• Defining or Undefining any Replication Table (Changing the type of tables)
• Creating or Dropping any Replication Table Master Entry
• Adding or Removing Replication Replicate Entries or Replication Master Entries
• Changing the Replication Order or Enable/Disable status of a Replication
• Synchronization

1. Table sys_rep_tables

   One record is kept in this table for each Replication Table in the database.

2. Table sys_rep_masters

   One record is kept in this table for each Replication Master Entry.

3. Table sys_rep_replicates

   One record is kept in this table for each Replication Replicate Entry.