If you are checking “Table Replication Examples” in guide SAP HANA Administration Guide for SAP HANA Platform, and want to see more “Examples Using Partitioned Tables”, this blog post will give you 3 examples that can help you to better understand replication of partitioned tables in scale out systems.

Example 1:

Distributed SAP HANA tenantDB, 3 indexservers distributed on 3 hosts:

<host1>:3xx03
<host2>:3xx03
<host3>:3xx03

1. Create a table which has 2 partitions

create schema NSE;

set schema NSE;

CREATE COLUMN TABLE "NSE"."TEST_TABLE" ("COL1" INTEGER CS_INT PAGE LOADABLE , "COL2" INTEGER CS_INT PAGE LOADABLE , "COL3" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE PARTITION BY RANGE("COL1") ( (PARTITION '0' <= VALUES < '10' PAGE LOADABLE , PARTITION OTHERS COLUMN LOADABLE )) PAGE LOADABLE WITH PARAMETERS ('PARTNAMES'=('3','7'));

2. Check the location info, table TEST_TABLE which has 2 partitions on 2 indexservers <host1>:3xx03 and <host2>:3xx03

select * from M_TABLE_LOCATIONS where table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host2>;3xx03;NSE ;TEST_TABLE ;2 ;<host2>:3xx03

3. Following query execute failed because the topology of replica is following the source(source has 2 indexservers), there is no enough indexserver for replica(only 1 free indexserver for replica)

alter table TEST_TABLE add replica at all locations; ---> execute failed

4. Check result: no replica table for TEST_TABLE

select * from M_TABLE_LOCATIONS where table_name like '_SYS_REP_TEST_TABLE%' or table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host2>;3xx03;NSE ;TEST_TABLE ;2 ;<host2>:3xx03

5. Then we add another indexserver to the tenantDB, when there are two free indexservers, creates a replica at all locations successfully

ALTER DATABASE ZD7 ADD 'indexserver' AT LOCATION '<host2>:3xx40' —> execute on system DB

alter table TEST_TABLE add replica at all locations; —> execute successfully

6. Check result: table TEST_TABLE which has 2 partitions on 2 indexservers <host1>:3xx03 and <host2>:3xx03, its replica table SYS_REP_TEST_TABLE#0 also has 2 partitions on 2 indexservers <host2>:3xx40 and <host3>:3xx03

select * from M_TABLE_LOCATIONS where table_name like '_SYS_REP_TEST_TABLE%' or table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host2>;3xx03;NSE ;TEST_TABLE ;2 ;<host2>:3xx03
3;<host2>;3xx40;NSE ;_SYS_REP_TEST_TABLE#0;1 ;<host2>:3xx40
4;<host3>;3xx03;NSE ;_SYS_REP_TEST_TABLE#0;2 ;<host3>:3xx03

Example 2:

Distributed SAP HANA tenantDB, 3 indexservers distributed on 3 hosts:
<host1>:3xx03
<host2>:3xx03
<host3>:3xx03

1. Create a table which has 2 partitions

create schema NSE;

set schema NSE;

CREATE COLUMN TABLE "NSE"."TEST_TABLE" ("COL1" INTEGER CS_INT PAGE LOADABLE , "COL2" INTEGER CS_INT PAGE LOADABLE , "COL3" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE PARTITION BY RANGE("COL1") ( (PARTITION '0' <= VALUES < '10' PAGE LOADABLE , PARTITION OTHERS COLUMN LOADABLE)) PAGE LOADABLE WITH PARAMETERS ('PARTNAMES'=('3','7'));

2. Check the location info, table TEST_TABLE which has 2 partitions on 2 indexservers <host1>:3xx03 and <host2>:3xx03

select * from M_TABLE_LOCATIONS where table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host2>;3xx03;NSE ;TEST_TABLE ;2 ;<host2>:3xx03

3. If we specify the location of the replica, then it creates replica of both partitions in that particular indexserver

alter table TEST_TABLE add replica at '<host3>:3xx03'; —> execute successfully

4. Check result: table TEST_TABLE which has 2 partitions on one indexserver <host3>:3xx03, its replica table SYS_REP_TEST_TABLE#0 which created on one indexserver <host3>:3xx03

select * from M_TABLE_LOCATIONS where table_name like '_SYS_REP_TEST_TABLE%' or table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host2>;3xx03;NSE ;TEST_TABLE ;2 ;<host2>:3xx03
3;<host3>;3xx03;NSE ;_SYS_REP_TEST_TABLE#0;1 ;<host3>:3xx03
4;<host3>;3xx03;NSE ;_SYS_REP_TEST_TABLE#0;2 ;<host3>:3xx03

Example 3:

SAP HANA tenantDB has one indexserver, the indexserver distributed on host1:
<host1>:3xx03

1. Create a table which has 2 partitions

create schema NSE;

set schema NSE;

CREATE COLUMN TABLE "NSE"."TEST_TABLE" ("COL1" INTEGER CS_INT PAGE LOADABLE , "COL2" INTEGER CS_INT PAGE LOADABLE , "COL3" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE PARTITION BY RANGE("COL1") ( (PARTITION '0' <= VALUES < '10' PAGE LOADABLE , PARTITION OTHERS COLUMN LOADABLE )) PAGE LOADABLE WITH PARAMETERS ('PARTNAMES'=('3','7'));

2. Check the location info, table TEST_TABLE which has 2 partitions on 2 indexservers <host1>:3xx03 and <host2>:3xx03

select * from M_TABLE_LOCATIONS where table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host1>;3xx03;NSE ;TEST_TABLE ;2 ;<host1>:3xx03

3. Add 2 indexservers at host2 and host3

ALTER DATABASE ZR1 ADD 'indexserver' AT LOCATION '<host2>:3xx40' ; —> execute on system DB

ALTER DATABASE ZR1 ADD 'indexserver' AT LOCATION '<host3>:3xx43' ; —> execute on system DB

4. Creates replica at all locations

alter table TEST_TABLE add replica at all locations;

5. Check result: table TEST_TABLE which has 2 partitions on one indexserver <host3>:3xx03, its replica tables SYS_REP_TEST_TABLE#0 & SYS_REP_TEST_TABLE#1 which created at indexservers <host2>:3xx40 & <host3>:3xx43

select * from M_TABLE_LOCATIONS where table_name like '_SYS_REP_TEST_TABLE%' or table_name = 'TEST_TABLE';

;HOST ;PORT ;SCHEMA_NAME;TABLE_NAME ;PART_ID;LOCATION
1;<host1>;3xx03;NSE ;TEST_TABLE ;1 ;<host1>:3xx03
2;<host1>;3xx03;NSE ;TEST_TABLE ;2 ;<host1>:3xx03
3;<host2>;3xx40;NSE ;_SYS_REP_TEST_TABLE#0;1 ;<host2>:3xx40
4;<host2>;3xx40;NSE ;_SYS_REP_TEST_TABLE#0;2 ;<host2>:3xx40
5;<host3>;3xx43;NSE ;_SYS_REP_TEST_TABLE#1;1 ;<host3>:3xx43
6;<host3>;3xx43;NSE ;_SYS_REP_TEST_TABLE#1;2 ;<host3>:3xx43

Conclusion:

The topology of table replica is following the source table