Use Case 2: Expand a Multi-Region Table

An organization deploys two on-premise KVStores, one each at Frankfurt and London. As per their requirement, they create a few MR Tables in both the regions. The Users table is one of the many MR Tables created and maintained by this organization. Now, they decide to expand their organization by adding another NoSQL Database in Dublin. After creating Dublin as the new region, they want to expand the existing MR Tables to the new region. In the next few topics, you learn how to add the Dublin region to the Users table that you already created in the previous use case.

If you have not created the Users MR Table earlier, execute the steps outlined in Use Case 1: Set up Multi-Region Environment.

Prerequisites

Before expanding the Users table to the new region, you must have set up the new region by executing the following tasks:

Set up a multi-region NoSQL Database with two regions Frankfurt (FRA) and London (LND). See Use Case 1: Set up Multi-Region Environment.
Deploy a local KVStore with store name as dubstore in the new region. See Configuration Overview.
Set the new region's local region name to DUB by executing the following DDL command from the kv prompt. See Set Local Region Name.
```
kv-> execute 'SET LOCAL REGION DUB'
```

Configure and start the XRegion Service in the DUB region. See Configure XRegion Service and Start XRegion Service.

Sample json.config file for the DUB region:

{
 "path": "/home/oracle/xrshome",
 "agentGroupSize": 1,
 "agentId": 0,
 "region": "DUB",
 "store": "dubstore",
 "helpers": [
 "host7:5000",
 "host8:5000",
 "host9:5000"
 ],
 "regions": [
 {
 "name": "FRA",
 "store": "earnback",
 "helpers": [
 "host1:5000",
 "host2:5000",
 "host5:5000"
 ]
 },
 {
 "name": "LND",
 "store": "mrtstore",
 "helpers": [
 "host3:5000",
 "host4:5000",
 "host6:5000"
 ]
 }
 ]
}

Update the json.config file in the existing regions, that is, Frankfurt (FRA) and London (LND) to include DUB (Dublin) as a remote region.

Note:

You must restart the agent at existing regions to pick up the new region (DUB) from the json.config file.

Sample json.config file for the LND region:

{
  "path": "/home/oracle/xrshome",
  "agentGroupSize": 1,
  "agentId": 0,
  "region": "LND",
  "store": "mrtstore",
  "helpers": [
    "host3:5000",
    "host4:5000",
    "host6:5000"
  ],
  "regions": [
    {
      "name": "FRA",
      "store": "earnback",
      "helpers": [
        "host1:5000",
        "host2:5000",
        "host5:5000"
      ]
    },
    {
      "name": "DUB",
      "store": "dubstore",
      "helpers": [
        "host7:5000",
        "host8:5000",
        "host9:5000"
      ]
    }
  ]
}

Sample json.config file for the FRA region:

{
  "path": "/home/oracle/xrshome",
  "agentGroupSize": 1,
  "agentId": 0,
  "region": "FRA",
  "store": "earnback",
  "helpers": [
    "host1:5000",
    "host2:5000",
    "host5:5000"
  ],
  "regions": [
    {
      "name": "LND",
      "store": "mrtstore",
      "helpers": [
        "host3:5000",
        "host4:5000",
        "host6:5000"
      ]
    },
    {
      "name": "DUB",
      "store": "dubstore",
      "helpers": [
        "host7:5000",
        "host8:5000",
        "host9:5000"
      ]
    }
  ]
}

Create LND (London) and FRA (Frankfurt) as two remote regions to the new region by executing the following DDL command from the kv prompt. See Create Remote Regions.
```
kv-> execute 'CREATE REGIONS LND, FRA'
```

Create MR Table in New Region

As a first step in expanding the Users MR Table to the new region, you must create the Users Table in the new region by executing the following DDL command. See Create Multi-Region Tables.

kv-> execute 'create table Users (id integer, name string, team string, primary key(id)) in regions DUB, LND, FRA;

Note:

Executing the above DDL command creates the Users table in the DUB region as an MR Table with three regions. However, querying this table does not return any data because the new region (DUB) is not yet linked to the Users MR Table in the existing regions, that is, LND and FRA.

Add New Region to Existing Regions

In addition to creating the Users table at the new region (DUB), you also need to include the new region (DUB) in the Users MR Table at the existing regions, that is, LND and FRA.

To add a new region to the Users table in the existing regions:

Connect to the kv prompt from the local region, and connect to the local KVStore.
Execute the following command to add the new region (DUB) as its remote region.
```
kv-> execute ‘CREATE REGION <new remote region>’
```
Optionally, you can execute the following command to verify if the remote region is created successfully.
```
kv-> execute ‘show regions’
```
Execute the following to add the new region to the Users MR Table.
```
kv-> execute 'alter table Users add regions DUB'
```
Optionally, you can verify the MR Table creation by executing the following command.
```
kv-> show table -name Users
```

Creating DUB as a remote region from the LND region:

kv-> EXECUTE 'CREATE REGION DUB'
Statement completed successfully

Validating that the DUB region is added as a remote region in London.

kv-> execute 'show regions'
regions

  LND (local, active)
  FRA (remote, active)
  DUB (remote, active)

Adding DUB region to the Users MR Table from the LND region:

kv-> execute 'alter table Users add regions DUB'
Statement completed successfully

Verifying that the DUB region is added to the Users table in London.

kv-> show table -name Users
{
  "json_version" : 1,
  "type" : "table",
  "name" : "Users",
  "shardKey" : [ "id" ],
  "primaryKey" : [ "id" ],
  "fields" : [ {
    "name" : "id",
    "type" : "INTEGER",
    "nullable" : false,
    "default" : null
  }, {
    "name" : "name",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  }, {
    "name" : "team",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  } ],
  "regions" : {
    "2" : "FRA",
    "3" : "DUB",
    "1" : "LND"
  }
}

Similarly, add DUB as a remote region from the Frankfurt (FRA) region. Then, alter the Users MR Table in the Frankfurt (FRA) region to include the new region (DUB).

Access MR Table in New and Existing Regions

After performing the tasks discussed in the previous sections, you can perform read/write operations on the Users table from the new region without any disruption. However, the table may not return the complete data from the existing regions until the initialization completes in the background. Especially if the Users table in the existing regions has a huge volume of data, it may take a while for the new table to see the data from the remote regions.

Similarly, you can continue performing read/write operations on the Users table from the existing regions without any disruption. Adding a new region is transparent to the customers accessing the Users table from the existing regions. However, the Users table at the existing regions may also need initialization to see the writes from the new region. If the table at the new region is empty or small, the existing regions will quickly sync up with it. To learn how to access the MR Tables, see Access and Manipulate Multi-Region Tables.