| Chapter X Replication Internals

## X.1. Introduction

This chapter explains the Empress Replication Internal Concepts. These include but are not limited to algorithms, ideas and technical details of Empress Replication. Behind the scene of Replication story. These are details from Research & Development Group.

## X.2. Replication Tables

The minor overheads to a replication table are:

- A replication table occupies a larger space in table space, because the update timestamp and logical delete attributes for records of a replicate table are also kept.
- Updating a Replication Table is slower than updating a Normal Table, because there is a need to update two additional attributes for each record of a Replication Table.
- An index is created implicitly for any Replication Table.

### Master Table

- Update-able

### Replicate Table

- Read-Only

## X.3. SQL Commands

- ALTER TABLE table_name CHANGE TABLE TYPE TO MASTER; Successful execution of this command inserts a line in the system table sys_rep_tables.
- ALTER TABLE table_name CHANGE TABLE TYPE TO NORMAL; Successful execution of this command removes the record corresponding to table_name from the system table sys_rep_tables of the database containing the master table.
- DROP TABLE table_name; Successful execution of this command removes a replication table table_name and all of the replication master entries defined for table_name. It also deletes all the records from system table sys_rep_masters related to `table_name`.
- CREATE [AND INSERT INTO] REPLICATE TABLE replicate_table FROM master_side_access; If this command terminates successfully, a replication entry row will be added to the system table sys_rep_masters in the database containing the replicate table.
- CREATE REPLICATION REPLICATE replicate_side_access ON replication_table; Successful execution of this command inserts an entry to the system table sys_rep_replicates having `replicate_side_access` as a replicate of `master_table`.
- DROP REPLICATION REPLICATE replicate_side_access ON replication_table; Successful execution of this command removes an entry from the system table sys_rep_replicates having table accessed by `replicate_side_access` as replicate table of `replication_table`. Execution of this command does not drop any database tables.
- CREATE REPLICATION MASTER master_side_access ON replicate_table; Successful execution of this command inserts a new entry into sys_rep_masters table.
- DROP REPLICATION MASTER master_side_access ON replicate_table; It deletes `replicate_table` entries from sys_rep_masters table in Replicate-Side having `master_side_access` as its Master-Side table.
- ALTER replicate_table ENABLE REPLICATION MASTER master_side_access ; Successful execution of these commands alters the repm_order field of sys_rep_masters system table related to `master_side_access` in the Replicate-Side.

## Synchronization

Replication Master Entry Successful synchronization updates attributes of system table sys_rep_masters in the database replicate_db and attributes of sys_rep_replicates tables in Master-Side database, for records related to updated target tables and source Master-Side tables.

## C.4. System Tables Related to Replication

Information about Replication Tables, Replication Relations and Synchronization Status are stored in three system tables. These tables are created upon creation of an Empress Database and are updated upon:

- Defining or Undefining any Replication Table (Changing the type of tables)
- Creating or Dropping any Replication Table
- Any Replication Synchronization Request
- Any Successful Replication Synchronization
- Changing the Replication Master Order or Availability (Enabling or Disabling) of a Replication Master Table
- Adding or Dropping Replication Replicate Entries or Replication Master Entries. (These are Authorizing / Unauthorizing any candidate Replication Replicate Table or Claiming / Unclaiming any candidate Replication Master Table.)

Beside these, there are some internal operations that update the values of system tables related to replication.

The following system tables are added for Replication purpose:

1. sys_rep_tables
     One record is kept in this table for each Replication Table in the database.
2. sys_rep_masters
     One record is kept in this table for any Replication Master Entry.
3. sys_rep_replicates
     One record is kept in this table for any Replication Replicate Entry.

The information in these System Tables are for Internal Usage. Most of the useful information extracted from these system tables can be seen using the command:

       * DISPLAY TABLE table_name ALL;

So the user doesn't have to look at the contents of these system tables directly.

### C.4.1. Table sys_rep_tables

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

Note:
  In the explanation (Meaning) of the attributes of system table sys_rep_tables, we refer to the specific Replication Table for which the record of this system table is kept, as "T".

System Table sys_rep_tables
Attribute | Data Type | Meaning
--- | --- | ---
`rep_tabnum` | integer Not Null | Table number the replication table T
`rep_tabtype` | integer Not Null | Type of the replication table T (replicate or master)
`rep_tab_condition` | nlstext(32,0,64,1) | A string used for Subset Replication. This string is set only in records related to Replicate Tables that participate in Subset Replication.
`rep_tab_timestamp` | microtimestamp(0) | Standard time that a Replicate Table has the snapshot of the Master Table of the Replication World. This attribute is not meaningful for a Master Table. If a Replicate Table R1 synchronizes with a Master Table at timestamp TS1, rep_tab_timestamp value of the record kept related to that Replicate Table is TS1. Imagine that R1, has a Replication Replicate Table called R2, such that R2 synchronizes its information with R1 at timestamp TS2, where TS2>TS1. In this case, value of rep_tab_timestamp for the record related to R2, is TS1, not TS2. Because rep_tab_timestamp contains the snapshot time of the "Master Table" of the Replication World.
`rep_recov_timestamp` | microtimestamp(0) | Standard Recovery Time of the replication table. This timestamp is used to recover the more recent data of a Replicate Table to the data of its current Master Table. This value is used internally when during Master Table Switch. In this case the data of all the other Replicate Tables in that Replication World come to the state of the data of their current Master Table when doing synchronization.
`rep_purge_timestamp` | microtimestamp(0) | Standard Purge Time of the replication table. This timestamp is used for physically deleting (purging) the logically deleted records of Replication tables. This value is used internally, and equals to the minimum of Table Timestamp (rep_tab_timestamp) of the directly related Replication Replicate tables of T. Whenever the Record Timestamp (EMPRESS_TIMESTAMP) of a logically deleted record is less than or equal to Purge Timestamp of T, then the logically "deleted" record can be purged.
`rep_orig_mashost` | character(32,1) Not Null | Host Identifier of the original master of T. This is the IP address of the host.
`rep_orig_masdb` | nlstext(32,32,32,1) Not Null | Physical name and path to the database of the original master of T. If the table itself is the original master table, this value shows its own database name.
`rep_orig_mastab` | nlscharacter(32,1,0) Not Null | Table Name of the original master of T. If the table itself is the original master table, this value shows its own table name.
`rep_orig_mas_start_timestamp` | microtimestamp(0) Not Null | Standard time that the original master table of this replication table is defined as Master. This timestamp never changes, and is constant during the life of a replication world.
`rep_orig_mas_hash_value` | longinteger Not Null | A hash value that simplifies detection of the members of a specific replication world. If this hash value is different from an expected value for a replication world, Empress knows that T does not belong to that specific replication world. Note that values of attributes that their name start with "rep_orig_mas" show Original Master Access information and Start Timestamp. These values are constant for T, even when Master Table Switch occurs, or if the table accessed by that access information does not exist anymore.
`rep_cur_mas_start_timestamp` | microtimestamp(0) Not Null | Standard time that the Current Master Table T is defined. This value changes as the result of a Master Table Switch.
`rep_world` | bulk(20,0,1024,1) | Contains information about the elements of the replication world that this replication table belongs to.

### C.4.2. Table sys_rep_masters

The database of any Replication Table keeps records of its Replication Master Entries in this system table. Each record of this table contains information about one of the (candidate) replication master tables of a replication table.

Notes:

- In the Explanations, when talking about a specific record of system table sys_rep_masters, we refer to the replication table which claims to have the Replication Master Table, as T, and refer to the Replication Master Table accessed by the information in the record as RMT.
- For each record of sys_rep_masters, a combination of values in `repm_massv, repm_masdb and repm_mastab` forms Master-Side Access Information, which is the required information to access RMT.

System table sys_rep_masters
Attribute | Data Type | Meaning
--- | --- | ---
`repm_tabnum` | integer Not Null | Table number of the replication Table T
`repm_massv` | nlscharacter(32,1) Not Null | Name of the Replication Master Server serving RMT (This attribute is 1st part of the three-part Master-Side Access Information)
`repm_masdb` | nlstext(32,32,32,1) Not Null | Logical or physical name of database containing RMT (This attribute is the 2nd part of the three-part Master-Side Access Information)
`repm_mastab` | nlscharacter(32,1) Not Null | Table name of RMT. (This attribute is the 3rd part of the three-part Master-Side Access Information).
`repm_order` | double precision Not Null | Order number. This is the Replication Master Order for RMT. Smaller order number means RMT will be considered first for becoming the "chosen" Replication Master Table.
`repm_enabled` | character(1,1) Not Null | Shows whether RMT is enabled or not.
`repm_pulled_success_timestamp` | microtimestamp(0) | Standard termination time of the latest successful (synchronization) request from RMT. This value is updated upon requests that are successfully terminated.
`repm_pulled_timestamp` | microtimestamp(0) | Standard starting time of a (synchronization) request made to RMT. The value of this attribute is updated even if the request made to RMT is not successfully terminated.
`repm_pulled_status` | nlstext(32,64,64,1) | The status of the latest (synchronization) request made to RMT. If the value of this attribute shows successful termination of a request, then `repm_pulled_success_timestamp` shows the termination timestamp of this successful request.
`repm_pushed_success_timestamp` | microtimestamp(0) | reserved value
`repm_pushed_timestamp` | microtimestamp(0) | reserved value
`repm_pushed_status` | nlstext (32,64,64,1) | reserved value

### C.4.3. Table sys_rep_replicates

The database of any Replication Table keeps records of its Replication Replicate Entries in this system table. Each record of this table contains information about one of the (candidate) replication replicate tables of a replication table.

Notes:

- In the Explanations, when talking about a specific record of system table sys_rep_replicates, we refer to the replication table which authorizes a Replication Replicate Table, as T, and refer to the Replication Replicate Table accessed by the information in the record as RRT.
- For each record of sys_rep_replicates, a combination of values in `repr_host, repr_repdb and repr_reptab` forms Replicate-Side Access Information, which is the required information to access RRT.

System Table sys_rep_replicates
Attribute | Data type | Meaning
--- | --- | ---
`repr_tabnum` | integer Not Null | Table number of the replication table T.
`repr_rephost` | character(32,1) Not Null | Name of the Host containing RRT. This is the IP address of the host. (This attribute is the 1st part of the three-part Replicate-Side Access Information)
`repr_repdb` | nlstext(32,32,32,1) Not Null | Name of the database containing RRT. (This attribute is the 2nd part of the three-part Replicate-Side Access Information)
`repr_reptab` | nlscharacter(32,1) Not Null | Table name of RRT. (This attribute is the 3rd part of the three-part Replicate-Side Access Information)
`repr_condition` | nlstext(32,0,64,1) | Used for Subset Replication. Contains the Condition (Restricted Subset WHERE Clause) set by T.
`repr_cond_comp` | bulk(20,512,512,1) | Used for Subset Replication.
`repr_repptab` | nlstext(32,32,32,1) | Physical RRT access information. This is the physical, resolved information to access RRT, which might be addressed by its logical path in `repr_rephost, repr_repdb` and `repr_reptab`.
`repr_rephval` | longinteger Not Null | Hash value of `repr_repptab`.
`repr_pulled_success_timestamp` | microtimestamp(0) | Standard termination time of the latest successful request made from RRT. The value of this attribute is updated upon requests that are successfully terminated.
`repr_pulled_timestamp` | microtimestamp(0) | Standard starting time of the request made from RRT. The value of this attribute is updated even if the request made from RRT is not successfully terminated.
`repr_pulled_status` | nlstext(32,64,64,1) | The status of the latest request from RRT. If the value of this attribute shows successful termination of a request, then `repr_pulled_success_timestamp` shows the termination timestamp of this successful request.
`repr_repsv` | nlscharacter(32,1) | reserved value
`repr_repsv_enabled` | character(1,1) | reserved value
`repr_pushed_success_timestamp` | microtimestamp(0) | reserved value
`repr_pushed_timestamp` | microtimestamp(0) | reserved value
`repr_pushed_status` | nlstext(32,64,64,1) | reserved value

## Recovery