Previous Next JavaScript must be enabled to correctly display this content

## Use Case 2: Expand a Multi-Region Table

An organization deploys two on-premise data stores, 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

Steps:

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

1. Set up a multi-region NoSQL Database with two regions Frankfurt (`fra`) and London (`lnd`). See Use Case 1: Set up Multi-Region Environment.
2. Deploy a local data store with store name as `dubstore` in the new region. For more details, see steps to configure a secure or non-secure data store.
3. Set the new region's local region name to `dub`. See Set Local Region Name.
4. Configure and start the XRegion Service in the `dub` region. See Configure XRegion Service and Start XRegion Service.
5. 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.
6. Create two remote regions, `fra` and `lnd` in the new region `dub`. See Create Remote Regions.

Example:

1. Set the local region name for the new region, Dublin.

       # Connect to the data store deployed at host7, host8, and host9 from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host7:5000,host8:5000,host9:5000 \
       -store dubstore

       -- Set the local region name to 'dub'
       sql-> SET LOCAL REGION dub;
       Statement completed successfully

       -- List the regions
       sql-> SHOW REGIONS;
       regions
           dub (local, active)

2. Create a `json.config` file for the new region, Dublin.

       # Contents of the configuration file in the 'dub' Region
       {
         "path": "<entire path to the home directory for the XRegion Service>",
         "agentGroupSize": 1,
         "agentId": 0,
         "region": "dub",
         "store": "<storename at the dub region>",
         "security": "<path to the security file>",
         "helpers": [
           "host7:5000",
           "host8:5000",
           "host9:5000"
         ],
         "regions": [
           {
             "name": "fra",
             "store": "<store name at the fra region>",
             "security": "<path to the security file>",
             "helpers": [
               "host1:5000",
               "host2:5000",
               "host3:5000"
             ]
           },
           {
             "name": "lnd",
             "store": "<store name at the lnd region>",
             "security": "<path to the security file>",
             "helpers": [
               "host4:5000",
               "host5:5000",
               "host6:5000"
             ]
           }
         ]
       }

3. Start the XRegion Service in the new region, Dublin.

       # Start the XRegion Service in the 'dub' Region
       [oracle@host7 xrshome]$ java -Xms256m -Xmx2048m -jar $KVHOME/lib/kvstore.jar xrstart \
       -config <path to the json config file> -bg

       [1] 24123

       # View the status of the xrstart command in the 'dub' Region
       [oracle@host7 xrshome]$ cat status.<numbe of agents>.<agentId>.txt
       Cross-region agent (region=fra,store=mrtstore, helpers=[host7:5000, host8:5000, host9:5000])
       starts up from config file=/home/oracle/xrshome/ json.config at 2024-04-05 08:57:34 UTC

4. Modify the `json.config` files in the existing regions (Frankfurt and London) to include Dublin as a remote region.

       # Contents of the configuration file in the 'fra' Region
       {
         "path": "<path to the json config file>",
         "agentGroupSize": 1,
         "agentId": 0,
         "region": "fra",
         "store": "<storename at the fra region>",
         "security": "<path to the security file>",
         "helpers": [
           "host1:5000",
           "host2:5000",
           "host3:5000"
         ],
         "regions": [
           {
             "name": "lnd",
             "store": "<storename at the lnd region>",
             "security": "<path to the security file>",
             "helpers": [
               "host4:5000",
               "host5:5000",
               "host6:5000"
             ]
           },
           {
             "name": "dub",
             "store": "<storename at the dub region>",
             "security": "<path to the security file>",
             "helpers": [
               "host7:5000",
               "host8:5000",
               "host9:5000"
             ]
           }
         ]
       }

       # Contents of the configuration file in the 'lnd' Region
       {
         "path": "<path to the json config file>",
         "agentGroupSize": 1,
         "agentId": 0,
         "region": "lnd",
         "store": "<storename at the lnd region>",
         "security": "<path to the security file>",
         "helpers": [
           "host4:5000",
           "host5:5000",
           "host6:5000"
         ],
         "regions": [
           {
             "name": "fra",
             "store": "<storename at the fra region>",
             "security": "<path to the security file>",
             "helpers": [
               "host1:5000",
               "host2:5000",
               "host3:5000"
             ]
           },
           {
             "name": "dub",
             "store": "<storename at the dub region>",
             "security": "<path to the security file>",
             "helpers": [
               "host7:5000",
               "host8:5000",
               "host9:5000"
             ]
           }
         ]
       }

5. Create two remote regions, `fra` and `lnd` in the new region, Dublin.

       # Connect to the data store deployed in the 'dub' region from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host7:5000,host8:5000,host9:5000 \
       -store dubstore

       –- Create remote regions 'fra' and 'lnd'
       sql-> CREATE REGION fra;
       Statement completed successfully
       sql-> CREATE REGION lnd;
       Statement completed successfully

       – List the regions
       sql-> SHOW REGIONS;
       regions

           dub (local, active)
           fra (remote, active)
           lnd (remote, active)

### Create MR Table in New Region

Steps:

As a first step in expanding an MR Table to a new region, you must create the MR Table in the new region using the `CREATE TABLE` statement. See Create Multi-Region Tables.

Note:

Creating the MR Table in the new region alone does not ensure replicating the data from the existing regions. This is because you have not yet linked the new region to this MR Table from the existing regions.

Example:

Create the `users` MR Table in the new region, Dublin.

    # Connect to the data store deployed in the 'dub' region from the SQL shell
    [~]$java -jar $KVHOME/lib/sql.jar \
    -helper-hosts host7:5000,host8:5000,host9:5000 \
    -store dubstore

    -- Create the users MR Table
    sql-> CREATE TABLE users(
       -> id INTEGER,
       -> name STRING,
       -> team STRING,
       -> PRIMARY KEY (id))
       -> IN REGIONS dub,fra,lnd;
    Statement completed successfully

    # Connect to the data store deployed in the 'dub' region from the kv prompt
    [~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
    -helper-hosts host7:5000,host8:5000,host9:5000 \
    -store dubstore

    # Verify the regions associated with the users MR table
    kv-> SHOW TABLE -NAME users
    {
      "json_version": 1,
      "type": "table",
      "name": "users",
      "regions": {
        "1": "dub",
        "2": "fra"
        "3": "lnd"
      },
      "fields": [
        {
          "name": "id",
          "type": "INTEGER",
          "nullable": false
        },
        {
          "name": "name",
          "type": "STRING",
          "nullable": true
        },
        {
          "name": "team",
          "type": "STRING",
          "nullable": true
        }
      ],
      "primaryKey": [
        "id"
      ],
      "shardKey": [
        "id"
      ]
    }

### Add New Region to Existing Regions

As a next step, you must create the new region as a remote region in the existing regions. Then, you must associate the new region with the MR Table in the existing regions.

Steps:

Execute the following steps from each existing region:

1. Add the new region as a remote region. See Create Remote Regions.
2. Associate the new region with the existing MR Table using the DDL command shown below.

       ALTER TABLE <table name> ADD REGIONS <region name>;

Example:

1. Add the new region, Dublin as a remote region from the existing regions, Frankfurt and London.

       # Connect to the data store deployed in the 'fra' region from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host1:5000,host2:5000,host3:5000 \
       -store mrtstore

       –- Create a remote region 'dub'
       sql-> CREATE REGION dub;
       Statement completed successfully

       – List the regions
       sql-> SHOW REGIONS;
       regions

           fra (local, active)
           lnd (remote, active)
           dub (remote, active)

       # Connect to the data store deployed in the 'lnd' region from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host4:5000,host5:5000,host6:5000 \
       -store mrtstore

       –- Create a remote region 'dub'
       sql-> CREATE REGION dub;
       Statement completed successfully

       – List the regions
       sql-> SHOW REGIONS;
       regions

           lnd (local, active)
           fra (remote, active)
           dub (remote, active)

2. In the existing regions, alter the `users` MR Table to add the new region, Dublin.

       # Connect to the data store deployed in the 'fra' region from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host1:5000,host2:5000,host3:5000 \
       -store mrtstore

       –- Add the 'dub' region to the users MR Table
       sql-> ALTER TABLE users ADD REGIONS dub;
       Statement completed successfully

       # Connect to the data store deployed in the 'fra' region from the kv prompt
       [~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
       -helper-hosts host1:5000,host2:5000,host3:5000 \
       -store mrtstore

       # Verify the regions associated with the users MR table
       kv-> SHOW TABLE -NAME users
       {
         "json_version": 1,
         "type": "table",
         "name": "users",
         "regions": {
           "1": "fra",
           "2": "lnd"
           "3": "dub"
         },
         "fields": [
           {
             "name": "id",
             "type": "INTEGER",
             "nullable": false
           },
           {
             "name": "name",
             "type": "STRING",
             "nullable": true
           },
           {
             "name": "team",
             "type": "STRING",
             "nullable": true
           }
         ],
         "primaryKey": [
           "id"
         ],
         "shardKey": [
           "id"
         ]
       }

       # Connect to the data store deployed in the 'lnd' region from the SQL shell
       [~]$java -jar $KVHOME/lib/sql.jar \
       -helper-hosts host4:5000,host5:5000,host6:5000 \
       -store mrtstore

       –- Add the 'dub' region to the users MR Table
       sql-> ALTER TABLE users ADD REGIONS dub;
       Statement completed successfully

       # Connect to the data store deployed in the 'lnd' region from the kv prompt
       [~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
       -helper-hosts host4:5000,host5:5000,host6:5000 \
       -store mrtstore

       # Verify the regions associated with the users MR table
       kv-> SHOW TABLE -NAME users
       {
         "json_version": 1,
         "type": "table",
         "name": "users",
         "regions": {
           "1": "lnd",
           "2": "fra"
           "3": "dub"
         },
         "fields": [
           {
             "name": "id",
             "type": "INTEGER",
             "nullable": false
           },
           {
             "name": "name",
             "type": "STRING",
             "nullable": true
           },
           {
             "name": "team",
             "type": "STRING",
             "nullable": true
           }
         ],
         "primaryKey": [
           "id"
         ],
         "shardKey": [
           "id"
         ]
       }

### 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 MR 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 MR Table has a huge volume of data in the existing regions, 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 MR Table from the existing regions without any disruption. Adding a new region is transparent to the customers accessing the MR Table from the existing regions. However, the MR 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.