Use Case 4: Drop a Region

An organization deploys three on-premise KVStores, one each at Frankfurt, London, and Dublin. As per their requirement, they created a few MR Tables in all three regions. As part of business down-sizing, they decided to exclude the Dublin region resulting in a two-region NoSQL Database. In the next few topics, you learn how to drop an existing region from the NoSQL environment that you had set up in the previous sections.

If you have not set up a Multi-Region NoSQL Database with three regions already, execute the steps outlined in:

• Use Case 1: Set up Multi-Region Environment
• Use Case 2: Expand a Multi-Region Table

Prerequisites

Learn about the conditions to be satisfied before dropping a region from a Multi-Region NoSQL Database.

Before removing a region from a Multi-Region NoSQL Database, it is recommended to:

• Stop writing to all the MR Tables linked to that region.
• Ensure that all writes to the MR Tables in that region have replicated to the other regions. This helps in maintaining consistent data across the different regions.

Note:

As of the current release, there is no provision in Oracle NoSQL Database to make a table read-only. Hence, you must stop writes to the identified MR Tables at the application level.

Isolate the Region

Learn how to isolate a region from a Multi-Region NoSQL Database.

When you decide to drop a region, it is a good practice to isolate that region from all the other participating regions. Isolating a region disconnects it from all the MR Tables in the Multi-Region NoSQL Database.

Isolating a region ensures that:

• The isolated region cannot see writes from the other regions.
• The other regions cannot see writes from the isolated region.

Note:

Even though it is not mandatory to isolate the region before dropping it from a Multi-Region NoSQL Database, this is considered a cleaner approach and hence suggested.

Steps:

Isolating a region from the Multi-Region NoSQL Database environment involves two tasks. They are:

1. Drop the target region from all the MR Tables in the other regions using the DDL command shown below.
2. Drop all the other regions from all the MR Tables in the region to be isolated.

See Alter MR Table to Drop Regions.

Example:

1. Drop the Dublin region from the users MR table in the other two regions, Frankfurt and London.

   # Connect to the KVStore 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
   
   –- drop the 'dub' region from the 'users' MR table
   sql-> ALTER TABLE users DROP REGIONS dub;
   Statement completed successfully

   # Connect to the KVStore 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
   
   –- drop the 'dub' region from the 'users' MR table
   sql-> ALTER TABLE users DROP REGIONS dub;
   Statement completed successfully

2. Drop the other regions (Frankfurt and London) from the users MR table in the Dublin region.

   # Connect to the KVStore 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
   
   –- drop 'fra' and 'lnd' regions from the 'users' MR table
   sql-> ALTER TABLE users DROP REGIONS fra,lnd;
   Statement completed successfully

Drop MR Tables in the Isolated Region

After you ensure that the region to be dropped is isolated, you can drop all the MR Tables created in that region safely. Dropping an MR Table is exactly similar to dropping a local table.

Example:

Drop users MR table from the isolated region, Dublin.

# Connect to the KVStore 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

–- drop the 'users' MR table
sql-> DROP TABLE users;
Statement completed successfully

Drop the Isolated Region

Finally, you can drop the isolated region from all the other regions.

Note:

Dropping an isolated region is not mandatory. You can retain the isolated region without dropping from other regions, for any future use.

Steps:

To drop the isolated region from other regions:

1. Connect to the sql prompt, and connect to the local KVStore.
2. Execute the following DDL command from the SQL prompt.

   DROP REGION <region name>;

3. Optionally, you can execute the following command to verify that the isolated region is dropped successfully.

   SHOW REGIONS;

Example:

Drop the Dublin region from the other two regions, Frankfurt and London.

# Connect to the KVStore 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

–- drop the 'dub' region
sql-> DROP REGION dub;
Statement completed successfully

– List the regions 
sql-> SHOW REGIONS;
regions

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

# Connect to the KVStore 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

–- drop the 'dub' region
sql-> DROP REGION dub;
Statement completed successfully

– List the regions 
sql-> SHOW REGIONS;
regions

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