Chapter 3
Empress Replication Scenarios

3.1 Introduction

This chapter introduces Empress Replication by examples. The examples are given through different scenarios that an imaginary company XYZ encounters when distributing information to its divisions and branches. The purpose of each scenario is to show different situations in which Empress Replication can be used.

3.1.1 The Company XYZ

The company XYZ has its headquarters in Toronto, with its divisions in New York and Tokyo. New York division has two branches in Brooklyn and Manhattan. The hierarchical structure of the company XYZ is given in [Figure 3.1.].

Figure 3.1. XYZ company's Hierarchical Structure

The product list data is updated in headquarters in Toronto. This company used to send weekly CDs containing its complete list of products and their prices to its divisions and all the branches.

In this chapter we will see how Company XYZ can benefit from Empress Replication to distribute the product list more efficiently.

3.1.2 Format of each Scenario

Each replication scenario starts with a graphical representation. The operational assumption is that the tables of each side live in separate databases on separate hosts. A description of the scenario and its operations are given. Each separate Operation is assigned a unique number. When applicable, a hyperlink to a detail explanation of the steps of each operation is provided.

General format of the operations are:

framework_name: OpN. Do a certain Operation ( param1 [, param2, ...] )

Where:

framework_name:
is the name of the framework in which the operation should be performed. This framework can be a Database name in the case of SQL Commands, or a Host name in the case of Operating System Commands and utilities.
OpN.
is the unique number given to each operation.
Do a certain Operation
is a short description (name) of the command. This operation is hyperlinked to its detail explanation, in [Empress Replication Details] .
[(param1 [, param2, ...])]
is a parameter list for each operation. The parameter list contains the table names and/or server names related to the operation.: is the name of the framework in which the operation should be performed. This framework can be a Database name in the case of SQL Commands, or a Host name in the case of Operating System Commands and utilities.
OpN.
is the unique number given to each operation.
Do a certain Operation
is a short description (name) of the command. This operation is hyperlinked to its detail explanation, in [Empress Replication Details] .
[(param1 [, param2, ...])]
is a parameter list for each operation. The parameter list contains the table names and/or server names related to the operation.: is the unique number given to each operation.
Do a certain Operation
is a short description (name) of the command. This operation is hyperlinked to its detail explanation, in [Empress Replication Details] .
[(param1 [, param2, ...])]
is a parameter list for each operation. The parameter list contains the table names and/or server names related to the operation.: is a short description (name) of the command. This operation is hyperlinked to its detail explanation, in [Empress Replication Details] .
[(param1 [, param2, ...])] is a parameter list for each operation. The parameter list contains the table names and/or server names related to the operation.: is a parameter list for each operation. The parameter list contains the table names and/or server names related to the operation.

The practical SQL commands and utility usage that can be performed for each operation are given after each command. For each scenario, a link to the script is given in [Scenario Scripts]. A brief explanation of the terms used in this chapter and throughout the rest of the manual is given in [Glossary].

The assumptions for the examples of this chapter are as follows:

In any Replication Relation, RMT side is shown with M and RRT side with R.
Product List in Location "xxx" is in a table called xxxPList, in a database called xxxDB in a host named xxxHost.
$xxxHost are variables set to the physical location (Host Address, or IP address) of their corresponding hosts.
$xxxDB are variables set to the physical database name (including actual Path to the database) of their corresponding databases.

TorontoPList, the table containing the original source data is defined as follows:

***  Table: TorontoPList  ***

  Attributes:
    prod_id                           integer  Not Null
    prod_name                         character(50,1)  Not Null
    prod_price                        float(5)
    prod_lang                         character(20,1)

  Indices:     PRIMARY KEY BTREE ON (prod_id)
  Lock Level:  RECORD

Lines that start with "xxx%" can be executed as operating system commands. For example
```
  TorontoHost% OS_Command
```
means that the Operating System Command can be executed from host in Toronto.
Lines that start with "xxxDB>" can be executed as SQL commands in xxxDB database. For example
```
  TorontoDB> SQL_command;
```
means that the SQL command can be executed in TorontoDB database.
$xxx_RM_info is a variable that contains information to access a Replication Master Table xxxPList. This RMT Side access information is a combination of :
```
  'replication master server':'database name with full path':'RMT'
```
For example :
```
  $Toronto_RM_info='TorontoRepServer':'/usr/empress/TorontoDB':'TorontoPList'
```
$xxx_RR_info is a variable that contains information to access a Replication Replicate Table xxxPList. This RRT Side access information is a combination of :
```
  'host':'database name with full path':'RRT'
```
For example :
```
  $Brooklyn_RR_info='BrooklynHost':'/usr/empress/BrooklynDB':'BrooklynDB'
```

3.2 Scenario A: Basic Replication

**Figure 3.2.** *Basic Replication Scenario*

3.2.1 Description

Company XYZ has decided to distribute its Product List to the branches in New York and Tokyo. In this section we describe the steps for creating a scenario to replicate the Product List in Toronto, to the branch in New York. Steps for replicating the Product List to Tokyo are the same.

3.2.2 Operations for Creating the Scenario

The script of commands to set up this scenario and to later undo its effects are given in [Scripts: Scenario A].

This is the simplest form of a Replication. This configuration deals with a master table TorontoPList and a replicate table NewYorkPList.

Operations OpA1, OpA2, and OpA3 can be performed to define a Master Table and start a Replication Master Server to serve this Master Table.

TorontoDB: OpA1. Define master table (TorontoPList)
```
 
   TorontoDB> ALTER TABLE TorontoPList CHANGE TABLE TYPE TO MASTER; 
   
```
Explanation of OpA1: Execute an SQL command in TorontoDB to alter TorontoPList to a master table.
TorontoHost: OpA2. Configure Network Server on RMT Side (TorontoRepServer)
Explanation of OpA2: Configure a Replication Master Server TorontoRepServer in TorontoHost. This is configuring the environment variables and configuration files to be used by the Replication Master Server.
- Copy the Network Server Configuration File (netserver.cfg) from $EMPRESSPATH/config directory to your $HOME (or $HOME/.empress) directory.
- In a Network Configuration Block for Replication Master Server (the block having TYPE=replication_master), set name of the Replication Master Server (to "TorontoRepServer" in this case).
```
MSNETSERVER
    ...
    NAME=TorontoRepServer
    TYPE=replication_master
    HOST=localhost
    PORT_ADMIN=12340
    PORT_SERVICE=12345
    ADMINISTRATOR=${USER}
    ...
MSNETSERVEREND
```
TorontoHost: OpA3. Start Replication Master Server (TorontoRepServer)
```
  TorontoHost% empsvadm TorontoRepServer start
   
```
Explanation of OpA3: Start a Replication Master Server in TorontoHost. Use the start function of the Empress Server Administration Utility.

Operations OpA4, OpA5 and OpA6 are used to authorize table NewYorkPList as candidate RRT of TorontoPList, and to create replicate table NewYorkPList as an RRT of TorontoPList.

TorontoDB: OpA4. Create Replication Replicate (NewYorkPList, TorontoPList)
```
  TorontoDB> CREATE REPLICATION REPLICATE 
     $NewYork_RR_info
     ON TorontoPList;
   
```
Explanation of OpA4: Execute an SQL command in TorontoDB to authorize table NewYorkPList to be a candidate RRT of TorontoPList. This command creates a replication replicate entry $NewYork_RR_info for TorontoPList on TorontoDB.
NewYorkHost: OpA5. Configure Network Server on RRT Side (NewYorkPList)
Explanation of OpA5: Configure Network Server on the RRT (client) side NewYorkPList. This configuration makes connection to Replication Master Server in TorontoHost possible. This connection is required so that RMT TorontoPList and RRT NewYorkPList would be able to exchange data.
- Copy the Network Server Configuration File (netserver.cfg) $EMPRESSPATH/config to your $HOME (or $HOME/.empress) directory.
- In a Network Configuration Block for Replication Master Server (the block having TYPE=replication_master), set name of the Replication Master Server (to "TorontoRepServer" in this case). In the same configuration Block, set host address of the Replication Master Server (in this example, set it to the internet address of the host containing TorontoRepServer, which would be something like TorontoHost.XYZ.com):
```
MSNETSERVER
    ...
    NAME=TorontoRepServer
    TYPE=replication_master  
    HOST=TorontoHost.XYZ.com
    PORT_ADMIN=12340
    PORT_SERVICE=12345
    ADMINISTRATOR=${USER}
    ...
MSNETSERVEREND
```
NewYorkDB: OpA6. Create Replicate Table (NewYorkPList, TorontoPList)
```
 NewYorkDB> CREATE REPLICATE TABLE 
    NewYorkPList 
    FROM $Toronto_RM_info
   
```
Explanation of OpA6: Execute an SQL command in NewYorkDB to create NewYorkPList to be a replicate of TorontoPList; this requires a connection from RRT Side NewYorkHost, to the Replication Master Server TorontoRepServer residing at the RMT side. Execution of this command creates a replicate table which has the same structure as TorontoPList. NewYorkPList is a read-only table. When creating NewYorkPList you have the option of copying the data of its RMT TorontoPList. This is similar to the initial synchronization of NewYorkPList with TorontoPList.

Operation OpA7 performs a Replication Synchronization.

NewYorkHost: OpA7. Perform a Synchronization (NewYorkPList)
```
  NewYorkHost% emprepsync NewYorkDB NewYorkPList
   
```
Explanation of OpA7: Whenever synchronization is desired, invoke the synchronization utility in NewYorkHost. This requires access to the replication master server TorontoRepServer. Successful usage of this utility copies the changes made to TorontoPList since its last synchronization to RRT NewYorkPList.

Operation OpA8 can be executed to check the table structure, synchronization status, and contents of RRT.

NewYorkDB: OpA8. View Replication Table and Synchronization Status (NewYorkPList)
```
   NewYorkDB> DISPLAY TABLE NewYorkPList ALL;
   NewYorkDB> SELECT * FROM NewYorkPList;
```
Explanation of OpA8: This first SQL command checks the table structure and the latest synchronization status of a replication table. The second SQL command checks the contents of a replication table.

3.2.3 Other possible Operations in the Scenario

For Stopping Replication Master Server execute OpA9.

TorontoHost: OpA9. Stop Replication Master Server (TorontoRepServer)
```
  TorontoHost% empsvadm TorontoRepServer stop
   
```
Explanation of OpA10: Stop the Replication Master Server TorontoRepServer started for TorontoPList on TorontoHost. This is done by calling the Replication Master Server utility with the STOP command. This operation will disallow the RRT side where TorontoPList resides, to perform synchronization operations with its RMT side where TorontoPList resides.

3.3 Scenario B: Cascaded Replication

The simplified graphical representation of the Cascaded Replication scenario is given in [Fig. 3.3]. The host, database and Replication Master Servers are not drawn. The structure of each RMT side and RRT side in the figure are as the one shown in [Scenario A] .

Figure 3.3. Cascaded Replication Scenario

3.3.1 Description

XYZ's division in New York will distribute the Product list to its branches in Brooklyn and Manhattan. In this case, New York division will be responsible for selecting the branches which should have the copies of the Product List. The branches that are close to New York, don't need to take their Product List information from headquarters in Toronto. The steps for creating a scenario to replicate Product List in New York to one of its branches in Brooklyn are explained. Steps for replicating the Product List to Manhattan are the same.

3.3.2 Operations for Creating the Scenario

The script of commands to set up this scenario and to later undo the effects of it are given in [Scripts: Scenario B]. The scenario shown by this configuration has one master table TorontoPList two replicate tables :

NewYorkPList in Database NewYorkDB,
BrooklynPList in Database BrooklynDB

This scenario deals with the table BrooklynPList as an RRT of NewYorkPList. Creating BrooklynPList as an RRT of NewYorkPList, makes NewYorkPList to be an RMT for BrooklynPList as well as being an RRT for TorontoPList. This scenario is applied to avoid bottleneck of having to synchronize from a single table in TorontoDB.

Note that in this scenario, NewYorkPList being RRT or RMT depends on the Replication Relationship which it participates in. In replication relation with TorontoPList, NewYorkPList is an RRT, and in relationship with BrooklynPList, NewYorkPList is the RMT. So there is no "absolute" RMT or RRT in Empress Replication concepts.

The operations OpB1, OpB2, OpB3, OpB4, OpB5 and OpB6 can be executed to define TorontoPList as Master Table, start a Replication Master Server to serve TorontoPList, and create NewYorkPList as its RRT.

TorontoDB: OpB1. Define master table (TorontoPList)

 
   TorontoDB> ALTER TABLE TorontoPList CHANGE TABLE TYPE TO MASTER;

TorontoHost: OpB2. Configure Network Server on RMT Side (TorontoRepServer)
Create $HOME/.empress/netserver.cfg file as follows:

MSNETSERVER
   NAME=TorontoRepServer
   TYPE=replication_master  
   HOST=localhost
   PORT_ADMIN=12340
   PORT_SERVICE=12345
   ADMINISTRATOR=${USER}
MSNETSERVEREND

TorontoHost: OpB3. Start Replication Master Server TorontoRepServer)
```
  TorontoHost% empsvadm TorontoRepServer start
   
```

TorontoDB: OpB4. Create Replication Replicate (NewYorkPList, TorontoPList)

  TorontoDB> CREATE REPLICATION REPLICATE 
     ${NewYork_RR_info} 
     ON TorontoPList;

NewYorkHost: OpB5. Configure Network Server on RRT Side (NewYorkPList)
Create $HOME/.empress/netserver.cfg file as follows:

MSNETSERVER
   NAME=TorontoRepServer
   TYPE=replication_master  
   HOST=TorontoHost.XYZ.com
   PORT_ADMIN=12340
   PORT_SERVICE=12345
   ADMINISTRATOR=${USER}
MSNETSERVEREND

NewYorkDB: OpB6. Create Replicate Table and Load Data (NewYorkPList, TorontoPList)
```
 NewYorkDB> CREATE AND INSERT INTO REPLICATE TABLE 
   NewYorkPList 
   FROM $Toronto_RM_info 
   
```
Explanation of OpB6: Execute an SQL command in NewYorkDB to create NewYorkPList to be a replicate of TorontoPList; Execution of this command creates a replicate table which has the same structure as TorontoPList. When creating NewYorkPList, using the option "AND INSERT INTO", you have the option of copying the data of its RMT TorontoPList. This is similar to the initial synchronization of NewYorkPList with TorontoPList.

Perform operations OpB7 and OpB8 on NewYorkHost to start a Replication Master Server NewYorkRepServer.

NewYorkHost: OpB7. Configure Network Server on RMT Side (NewYorkRepServer)
Explanation of OpB7: Append the following block to the end of the already existing Network Server Configuration File (netserver.cfg). This is for configuring a new Replication Master Server called NewYorkRepServer. Note that the NAME, PORT_ADMIN and PORT_SERVICE are different from the block belonging to TorontoRepServer.
```
MSNETSERVER
    NAME=NewYorkRepServer
    TYPE=replication_master
    HOST=localhost
    PORT_ADMIN=12350
    PORT_SERVICE=12355
    ADMINISTRATOR=${USER}
MSNETSERVEREND
```
NewYorkHost: OpB8. Start Replication Master Server (NewYorkRepServer)
```
  NewYorkHost% empsvadm NewYorkRepServer start
```

Similar to OpB4, OpB5 and OpB6, operations OpB9, OpB10 and OpB11 are used to authorize table BrooklynPList as candidate RRT of NewYorkPList, and create Replicate Table BrooklynPList as a replicate of NewYorkPList.

NewYorkDB: OpB9. Create Replication Replicate (BrooklynPList, NewYorkPList)

  NewYorkDB> CREATE REPLICATION REPLICATE 
     $Brooklyn_RR_info
     ON NewYorkPList;

BrooklynHost: OpB10. Configure Network Server on RRT Side (BrooklynPList)
Configure the synchronization utility for BrooklynPList in BrooklynHost. This allows BrooklynPList to perform synchronization with its RMT NewYorkPList.

In a Network Configuration Block for Replication Master Server set name of the Replication Master Server (to "NewYorkRepServer" in this case). In the same configuration Block, set host address of the Replication Master Server (in this example, set it to the actual internet address of the host containing "NewYorkRepServer", which would be something like "NewYorkHost.XYZ.com"). Create the file $HOME/.empress/netserver.cfg to look as follows:
```
MSNETSERVER
    NAME=NewYorkRepServer
    TYPE=replication_master  
    HOST=NewYorkHost.XYZ.com
    PORT_ADMIN=12350
    PORT_SERVICE=12355
    ADMINISTRATOR=${USER}
MSNETSERVEREND
```

BrooklynDB: OpB11. Create Replicate Table and Load Data (BrooklynPList, NewYorkPList)

 BrooklynDB> CREATE AND INSERT INTO REPLICATE TABLE BrooklynPList 
   FROM ${NewYork_RM_info};

3.4 Scenario C: RMT Switch (Selecting an Alternative Source of Data)

**Figure 3.4.** *Selecting an Alternative Source of Data Scenario*

3.4.1 Description

The computer in the New York division goes down occasionally, and XYZ company has decided to directly distribute the Product list to the branch in Brooklyn. So the headquarters in Toronto authorizes Brooklyn branch to get Product List from the computer located in the headquarters.

After adding Toronto as an alternative Source of data for the Brooklyn branch, the computer in Brooklyn can choose among the two sources and select the available one with the smallest Replication Master Order. Choosing a different RMT from the previous chosen RMT is called RMT Switch. The selection of the appropriate source of data is done automatically by synchronization utility. However, the administrator of Brooklyn branch can manually alter the list of candidate RMTs, to affect the automatic RMT Selection. The manual alteration of the list of candidate RMTs is described in Scenarios D and E.

3.4.2 Steps for Creating the Scenario

The script of commands to set up this scenario and to later undo the effects of it are given in [Scripts: Scenario C].

This scenario adds TorontoPList as an alternative RMT for BrooklynPList. BrooklynPList is an RRT of NewYorkPList, which is itself an RRT of TorontoPList. This scenario allows BrooklynPList to perform synchronization with TorontoPList whenever NewYorkPList is not accessible, or when the administrator of BrooklynPList wants to do so.

The relation between BrooklynPList and NewYorkPList is shown with "0". This number is indicates the Replication Master Order of NewYorkPList as RMT for BrooklynPList. This relation is created before the relation between BrooklynPList and TorontoPList, which is shown with "1". This means that synchronization of BrooklynPList is first tried to be done with NewYorkPList. If a connection between synchronization utility accessing BrooklynPList and the Replication Master Server accessing NewYorkPList can not be established, then synchronization utility will look for the RMT with a higher Replication Master Order for BrooklynPList, which is TorontoPList.

Execute the operations of Scenario B to create the same configuration. Execute OpC1 to authorize BrooklynPList to be an RRT of TorontoPList.

TorontoDB: OpC1. Create Replication Replicate (BrooklynPList, TorontoPList)

  TorontoDB> CREATE REPLICATION REPLICATE 
     $Brooklyn_RR_info
     ON TorontoPList;

Execute Operation OpC2 and OpC3 to establish a new RMT for BrooklynPList.

BrooklynHost: OpC2. Configure Network Server on RRT Side (BrooklynPList)
Explanation of OpC2: Append the following block to the end of the already existing Network Server Configuration File (netserver.cfg). This is for the Synchronization Utility residing at BrooklynHost to recognize a TorontoRepServer as a Replication Master Server. Note that the NAME, PORT_ADMIN and PORT_SERVICE are different from the block belonging to NewYorkRepServer.
```
MSNETSERVER
    NAME=TorontoRepServer
    TYPE=replication_master
    HOST=TorontoHost.XYZ.com
    PORT_ADMIN=12340
    PORT_SERVICE=12345
    ADMINISTRATOR=${USER}
MSNETSERVEREND
```
BrooklynDB: OpC3. Create Replication Master (TorontoPList, BrooklynPList)
```
 BrooklynDB> CREATE REPLICATION MASTER 
   $Toronto_RM_info 
   ON BrooklynPList;
```
Explanation of OpC3: Execute an SQL command in BrooklynDB to claim table TorontoPList to be a RMT of BrooklynPList.

To see TorontoPList is an RMT of BrooklynPList, execute OpC4.

BrooklynDB: OpC4. View Replication Table and Synchronization Status (BrooklynPList)
```
   BrooklynDB> DISPLAY TABLE BrooklynPList ALL;
```

3.5 Scenario D: Changing Order of the RMT

**Figure 3.5.** *Changing Order of the RMT Scenario*

3.5.1 Description

The computer in the New York division goes down so frequently that company XYZ decides to choose the headquarters in Toronto as the main distributor of the Product List to the active branch in Brooklyn. Only when a connection to Toronto can not be established, shall Brooklyn branch request the Product List from New York. The selection between sources of information will be done automatically after Brooklyn branch changes the Order of its sources for replication.

3.5.2 Operations for Creating the Scenario

The script of commands to set up this scenario and to later undo its effects are given in [Scripts: Scenario D]. This scenario, has the same configuration as Scenario C, only that TorontoPList has a smaller order number than NewYorkPList as an RMT for BrooklynPList. So, the synchronization for BrooklynPList is first tried to connect to be done with TorontoPList. If a connection between RRT side in BrooklnHost and RMT side in TorontoHost can not be established, the synchronization utility will check for validity of NewYorkPList for performing the synchronization.

Execute the same operations as in Scenario C to have the same model.

Execute OpD1 to change the Replication Master Order for BrooklynPList.

BrooklynDB: OpD1. Change the Replication Master Order (BrooklynPList, NewYorkPList, order)
```
 BrooklynDB> ALTER TABLE BrooklynPListCHANGE REPLICATION MASTER ORDER 
                $NewYork_RM_info   4, 
                $Toronto_RM_info   3;
   
```
Explanation of OpD1: Execute an SQL command on BrooklynDB to change the replication master order of tables NewYorkPList and TorontoPList for replicate table BrooklynPList.

3.6 Scenario E: Disabling RMT

**Figure 3.6.** *Disabling RMT*

3.6.1 Description

The computer in Toronto goes down so frequently that the product list in that branch is rarely up-to-date, so Brooklyn branch decides to synchronize data from New York instead. Hence, Brooklyn will disable the automatic selection of Toronto as its source of Product List.

3.6.2 Operations for Creating the Scenario

The script of commands to set up this scenario and to later undo its effects are given in [Scripts: Scenario E]. In this scenario, the TorontoPList is disabled from being a replication master for BrooklynPList. It means that even if the replication relation between TorontoPList and BrooklynPList exist, synchronization utility on BrooklynHost does not consider TorontoPList as an RMT for BrooklynPList when doing synchronization for BrooklynPList, regardless of the Replication Master Order of TorontoPList.

Perform the operation OpE1 to disable TorontoPList from being an RMT of BrooklynPList.

BrooklynDB: OpE1. Disable the Replication Master (BrooklynPList, TorontoPList)
```
 BrooklynDB> ALTER TABLE BrooklynPList 
    DISABLE REPLICATION MASTER $Toronto_RM_info ;
   
```
Explanation of OpE1: Execute an SQL command on BrooklynDB to disable replication master TorontoPList. This table will no longer be considered in the synchronization processes of BrooklynPList, no matter what the Replication Master Order of TorontoPList is.

Note that RMTs are Enabled by default, and are Disabled only by executing the operation OpE1.

3.6.3 Other Possible Operations in the Scenario

Perform OpE2 to enable TorontoPList as an RMT for BrooklynPList.

BrooklynDB: OpE2. Enable the Replication Master (BrooklynPList, TorontoPList)
```
 BrooklynDB> ALTER TABLE BrooklynPList 
    ENABLE REPLICATION MASTER  $Toronto_RM_info ;
   
```
Explanation of OpE2: Execute an SQL command on BrooklynDB to enable RMT TorontoPList. This table will be considered in the synchronization processes of BrooklynPList, depending on its order number, and whether the enabled RMTs with smaller order number are available at the time of synchronization.

3.7 Scenario F: Replication Table Switch

**Figure 3.7.** *Replication Table Switch*

3.7.1 Description

At some point in time, Company XYZ management decides to discontinue its operations at Toronto headquarters and decides to establish new headquarters in New York. The New York division (now headquarters) will be responsible for updating the Product List, and all other branches and divisions will get the updated Product List information from New York. In this scenario, a replicate table must be changed into a new Master Table. Later, a master table can be altered to a a replicate table. The switching process of replication tables to each other is called Replication Table Switch. When a Replicate Table switches to a 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. In a Replication world the latest replication table which switches to a Master Table, is called the Current Master Table.

3.7.2 Operation for Creating the Scenario

The script of commands to set up this scenario and to later undo its effects are given in [Scripts: Scenario F]. In this scenario NewYorkPList from NewYorkDB will be the new Master Table for the replication world which TorontoPList, NewYorkPList and BrooklynPList belong to. When NewYorkPList becomes a Master Table, it is not a read-only table anymore, so the users of NewYorkPList can modify the table. BrooklynPList is a Replicate of NewYorkPList and performs the synchronization with this table.

Perform operations in [Scenario B] to create the same scenario. Then perform operation OpF1 to change the table type of NewYorkPList, from REPLICATE to MASTER. This operation is usually needed when TorontoPList is not physically available, or a connection to TorontoHost can not be established from the RRT side where NewYorkPList resides.

NewYorkDB: OpF1. Define master table (NewYorkPList)

NewYorkDB> ALTER TABLE NewYorkPList CHANGE TABLE TYPE TO MASTER;

After this operation, NewYorkPList will be the current Master Table. Any table which is already an RRT, or will become an RRT of NewYorkPList, will recognize this table as the current Master Table. Note that it is the responsibility of administrator of NewYorkPList to authorize other tables to be candidate RRT of NewYorkPList. It is also the responsibility of the authorized RRT to claim NewYorkPList as their RMT.

3.7.3 Other Possible Operations in the Scenario

Later, company XYZ might decide to reverse the decision and reestablish Toronto as headquarters.

Before changing the type of NewYorkPList to a replicate table, it is a good practice to change the original master table into an RRT of the current Master Table NewYorkPList, then synchronize it with NewYorkPList, so that TorontoPList would be up-to-date before becoming the current master table. Perform the following operations to synchronize TorontoPList with NewYorkPList.

NewYorkDB: OpF2. Create Replication Replicate (TorontoPList, NewYorkPList)

NewYorkDB> CREATE REPLICATION REPLICATE $Toronto_RR_Info ON NewYorkPList;

TorontoDB: OpF3. Create Replication Master (TorontoPList, BrooklynPList)

 TorontoDB> CREATE REPLICATION MASTER $NewYork_RM_info ON TorontoPList;

TorontoDB: OpF4. Change Table type to Replicate (TorontoPList)

TorontoDB> ALTER TABLE TorontoPList CHANGE TABLE TYPE TO REPLICATE;

TorontoHost: OpF5. Perform a Synchronization (TorontoPList)
```
TorontoHost% emprepsync TorontoDB TorontoPList
```

TorontoDB: OpF6. Change Table Type to Master(TorontoPList)

TorontoDB> ALTER TABLE TorontoPList CHANGE TABLE TYPE TO
MASTER;

OpF7 can be executed to change the type of NewYorkPList to a replicate table.

NewYorkDB: OpF7. Change Table type to Replicate (NewYorkPList)
```
NewYorkDB> ALTER TABLE NewYorkPList CHANGE TABLE TYPE TO REPLICATE;
  
```
Explanation of OpF7: Execute an SQL command on NewYorkDB to change table type of NewYorkPList to Replicate. This table will be read-only for the users of NewYorkPList, and no more new updates can be made by regular SQL commands to this table.

After execution of this command, whenever NewYorkPList performs a synchronization with TorontoPList, the updates made to NewYorkPList during the time that NewYorkPList was a master table, will be reflected to TorontoPList and it will be up to date.

3.8 Scenario G: Subset Replication

**Figure 3.8.** *Subset Replication Scenario*

3.8.1 Description

Company XYZ sells movies on video tapes. It sells only limited number of tapes on Japanese market, only those done in Japanese language. That is why there is no need to distribute the whole product list to its division in Tokyo. Headquarters in Toronto will distribute only part of the table to Tokyo (tapes done in Japanese) and a part of the table to New York (tapes done in English).

3.8.2 Operations for Creating the Scenario

The script of commands to set up Subset Replication scenario are given in [Scripts: Scenario G]. In this example, a certain subset of the rows of TorontoPList are replicated to NewYorkPList, and a certain subset of the rows of TorontoPList are replicated to TokyoPList during synchronization. Subset replication, replicates rows from the master table that satisfy a predetermined condition. The subset condition is a restricted SQL Where Clause, called Subset Replication Search Condition (SRSC).

A Master Table whose subset of rows will be replicated, must have a primary key defined for it. The master table will specify the list of its attributes that will participate in selecting the rows of a subset replication. This attribute list is called Subset Attribute List (SAL). Operation OpG1. establishes a Master Table along with specifying the SAL. Perform operations OpG2. and OpG3. to start a Replication Master Server for TorontoPList.

TorontoDB: OpG1. Establish Master Table with Subset attribute List (TorontoPList, SAL)
```
 TorontoDB> ALTER TABLE TorontoPList 
     CHANGE TABLE TYPE TO MASTER FOR SUBSET ON (prod_lang);
    
```
Explanation of OpG1: Execute an SQL command in TorontoDB to alter TorontoPList to be a master table, specifying the attributes that will participate in SRSC, where the rows of the master table will be selected. Only the attributes specified in SAL may participate in SRSC.

TorontoHost: OpG2. Configure Network Server on RMT Side (TorontoRepServer)
Create $HOME/.empress/netserver.cfg file as follows:

MSNETSERVER
   NAME=TorontoRepServer
   TYPE=replication_master
   HOST=localhost
   PORT_ADMIN=12340
   PORT_SERVICE=12345
   ADMINISTRATOR=${USER}
MSNETSERVEREND

TorontoHost: OpG3. Start Replication Master Server (TorontoRepServer)
```
  TorontoHost% empsvadm TorontoRepServer start
   
```

The rows to be selected for subset replication are selected in SRSC when creating replication replicate for a master table. Execution of Operation OpG4. creates a replication replicate NewYorkPList for table TorontoPList and gives out the subset condition. Execution of Operation OpG5. creates a replication replicate TokyoPList for table TorontoPList. After these operations, perform operations OpG6, OpG7 and OpG8 and OpG9 to authorize RRT and create subset replicate tables in NewYorkDB and TokyoDB, and perform operation OpG10 and OpG11 to perform synchronization with TorontoPList.

TorontoDB: OpG4. Create Replication Replicate with Selected Rows (TorontoPList, NewYorkPList, "SRSC_english_products")
```
 TorontoDB> CREATE REPLICATION REPLICATE ${NewYork_RR_info} 
    ON TorontoPList WHERE prod_lang = 'english';
    
```
Explanation of OpG4: Execute an SQL command in TorontoDB to authorize NewYorkPList in NewYorkDB to be a replicate table for selected rows in TorontoPList. These rows are selected using a Replication Subset Where Clause specified by "SRSC" string, and perform a query on SAL specified in OpG1.

TorontoDB: OpG5. Create Replication Replicate with Selected Rows (TorontoPList, TokyoPList, "SRSC_japanese_products")

 TorontoDB> CREATE REPLICATION REPLICATE $Tokyo_RR_info 
   ON TorontoPList WHERE prod_lang = 'japanese';

NewYorkHost: OpG6. Configure Network Server on RRT Side (NewYorkPList)
Create $HOME/.empress/netserver.cfg file as follows:

MSNETSERVER
   NAME=TorontoRepServer
   TYPE=replication_master  
   HOST=TorontoHost.XYZ.com
   PORT_ADMIN=12340
   PORT_SERVICE=12345
MSNETSERVEREND

NewYorkDB: OpG7. Create Replicate Table (NewYorkPList, TorontoPList)

 NewYorkDB> CREATE AND INSERT INTO REPLICATE TABLE NewYorkPList 
   FROM $Toronto_RM_info ;

TokyoHost: OpG8. Configure Network Server on RRT Side (TokyoPList)
Create $HOME/.empress/netserver.cfg file as follows:

MSNETSERVER
   NAME=TorontoRepServer
   TYPE=replication_master  
   HOST=TorontoHost.XYZ.com
   PORT_ADMIN=12340
   PORT_SERVICE=12345
MSNETSERVEREND

TokyoDB: OpG9. Create Replicate Table (TokyoPList, TorontoPList)

 TokyoDB> CREATE AND INSERT INTO REPLICATE TABLE TokyoPList 
   FROM $Toronto_RM_info ;

NewYorkHost: OpG10. Perform a Synchronization (NewYorkPList)

  NewYorkHost% emprepsync NewYorkDB NewYorkPList

TokyoHost: OpG11. Perform a Synchronization (TokyoPList)

  TokyoHost% emprepsync TokyoDB TokyoPList

Chapter 3 Empress Replication Scenarios