Skip to Content

For SAP HANA SP6 (Revision 60 and above), the landscape redistribution process has been fundamentally revised: you can now control the distribution of tables to the nodes of a distributed SAP HANA DB and the number of level 1 partitions of tables using the table classification and placement functionality.

Table Classification and Placement

Usually, one table is not the sole storage location for data from a particular application, but is instead closely related to other tables that are also associated with that application.

So that the landscape redistribution process can recognize similar or associated database tables as such, the table classification functionality was created. This allows the landscape redistribution to optimally distribute tables of this kind to different nodes in a distributed SAP HANA database. In this way, you can prevent tables that are regularly joined being stored on separate nodes, which would mean that they first had to be sent across the network when SQL statements are executed. In this case, the tables are placed on the same node and the join can be locally optimized and executed.

For each table, you can specify a group type, subtype, and group name. You can either do this when creating the table or later:

CREATE TABLE … GROUP TYPE <TYPE>  GROUP SUBTYPE <SUBTYPE>  GROUP NAME <NAME>;

ALTER TABLE … SET GROUP TYPE <TYPE>  GROUP SUBTYPE <SUBTYPE>  GROUP NAME <NAME>;

From revision 65 onwards also an “ALTER TABLE … UNSET GROUP” will be available. (For the precise syntax, see the SAP HANA SQL Reference.)

For example, the table classification for the tables of a BW Data Store object with the technical name ZFIGL would be:

Table Name
Group Type
(GROUP_TYPE)
Subtype
(SUBTYPE)
Group Name
(GROUP_NAME)

/BIC/AZFIGL00

sap.bw.dso

ACTIVE

ZFIGL

/BIC/AZFIGL40

sap.bw.dso

QUEUE

ZFIGL

/BIC/B0000197000

sap.bw.dso

CHANGE_LOG

ZFIGL

All of the tables in this example have the group type sap.bw.dso and are therefore identified as tables of BW DataStore objects. The subtype is different, depending on the use of each of the individual tables. The group name is the technical name of the DSO in BW. This allows landscape redistribution to identify that these tables are associated. This means that all three tables are placed on the same node of the distributed SAP HANA database during the landscape redistribution process.

The table classification is stored in the TABLE_GROUPS table (see also SAP HANA System Tables and Monitoring Views documentation). There is an entry in this table for all tables for which a group type, subtype, or group name was specified. Any tables that do not have an entry in this table have not yet been classified.

Creating New Tables

The table classification and the table placement that results from it are, however, not only taken into account by the landscape redistribution. The defined rules are also used when creating new tables. In the example above, this would mean that an additional table with the group name ZFIGL would be created on the same node as the existing tables.

Number of Level 1 Partitions

Another use case for the table classification functionality is to control the number of partitions at level 1 of the partitioning specification.

The DSO ZFIGL from the example above has the following partitioning:

  • Level 1: HASH (DOCNR, LINEITEM)
  • Level 2: RANGE (CALMONTH)

The number of partitions at level 2 (range for the CALMONTH column) is defined and managed by the application. The number of partitions at level 1 and therefore the distribution of a table across multiple nodes, on the other hand, can be determined by the landscape redistribution. For example, small tables should only have one partition here, and should not be distributed. Very large tables, on the other hand, should be stored with as many partitions as possible on a large number of nodes.

Customizing Table Placement

The rules that are used for table placement and for determining the level 1 partitions are stored in the table TABLE_PLACEMENT in the schema _SYS_RT.

The TABLE_PLACEMENT table has the following columns for specifying groups of tables or individual tables:

  • SCHEMA_NAME, TABLE_NAME, GROUP_NAME, GROUP_TYPE, and SUBTYPE

You do not need to fill all of these columns when creating and maintaining rules. A more specific entry takes precedence over a more general entry. For example, if you only fill SCHEMA_NAME, the rules apply for all tables in the schema. If there is also an entry in which SCHEMA_NAME and GROUP_TYPE are filled, this entry overwrites the first, more general entry for the corresponding tables. (For a more detailed overview of the priorities when evaluating the entries in the TABLE_PLACEMENT table, see SAP Note 1908082.)

You can control the number of level 1 partitions with the following columns:

  • MIN_ROWS_FOR_PARTITIONING

This defines the minimum number of records that must exist in a table before level 1 partitioning takes place.

  • INITIAL_PARTITIONS

If the threshold value in the MIN_ROWS_FOR_PARTITIONING column is exceeded, the landscape redistribution performs a partitioning. The initial number of partitions is stored in this column.

  • REPARTITIONING_THRESHOLD

Once a table has been partitioned with the specified initial number of partitions, for performance reasons, the table is only repartitioned by doubling the number of partitions. For example, if the initial number of partitions is three, this would result in six partitions being created during a repartitioning. You maintain the threshold value for the number of records in a partition that triggers a repartioning of this kind in this parameter.

However, as a general principle, the system never creates more partitions than the number of available nodes. For example, if a distributed SAP HANA database only has five nodes, the repartioning described above, from three partitions to six partitions, would not take place. The partitioning rules also apply only to tables for which HASH or ROUNDROBIN partitioning is specified at level 1. Tables without a partitioning specification are also not automatically partitioned when the threshold values are exceeded.

The table placement is controlled using the column LOCATION. Possible values are MASTER, SLAVE, and ALL. You can use these to determine the types of nodes of a distributed SAP HANA database on which the respective tables can be stored.

Example: Customizing SAP BW InfoCubes

SCHEMA_NAME

TABLE_NAME

GROUP_TYPE

SUBTYPE

GROUP_NAME

MIN_ROWS_FOR_
PARTITIONING

INITIAL_
PARTITIONS

REPARTITIONING_
THRESHOLD

LOCATION

2.000.000.000

3

2.000.000.000

slave

SAPBWP

2.000.000.000

3

2.000.000.000

master

SAPBWP

sap.bw.cube

40.000.000

3

40.000.000

slave

The first entry in the TABLE_PLACEMENT table is the default entry. All specification columns are empty for this entry. This ensures that there is always an applicable rule for creating or distributing tables.

The second entry overwrites the location of the default entry (slave) for all tables of the SAP BW schema.

The third entry defines the size of level 1 partitions and their location for InfoCubes in the SAP BW schema.

This Customizing means that all InfoCube tables are stored on the slave nodes of the distributed SAP HANA system. InfoCube tables with more than 40 million records are divided into three partitions. If the individual partitions have more than 40 million entries on average, these are halved again in accordance with the REPARTITIONING_THRESHOLD threshold value. This results in a table with six partitions. (As described above, the system does not create more partitions for a table than there are nodes available. The dividing of the three initial partitions into six therefore only takes place, if the SAP HANA database has at least six slave nodes. If there are five or fewer nodes, the number of partitions in this Customizing example is limited to three. For performance reasons, there is no automatic repartitioning from three to five partitions, for example. If you want to repartition to five partitions, you need to enter a corresponding Customizing rule in the TABLE_PLACEMENT table.)

There is also no automatic repartitioning when threshold values are exceeded. Instead, this is proposed during the next execution of the landscape redistribution process.

Tables in the SAP BW schema that do not belong to the group type sap.bw.cube are stored on the master node, in accordance with the second entry. These include, for example, the tables of the SAP Basis component, and those of the ABAP runtime environment.

Tables that are not in the SAP BW schemas (for example, tables replicated using SLT) are always stored on a slave node, in accordance with the first entry.

  Note: For reasons of clarity, this example only takes account of the InfoCube tables of an SAP BW system. For a complete description of the table placement for SAP BW, see SAP Note 1908073.

Landscape Redistribution Parameters

For a general guide to performing the landscape redistribution, see the SAP HANA Administration Guide. This article therefore only explains a few parameters that influence the behavior of the landscape redistribution in relation to the table classification and placement functionality.

  • Global Config (global.ini):

[table_placement] same_num_partitions

If this parameter is set to true (default), all tables with the same group name have the same number of level 1 partitions. The number of partitions is determined by the largest table within the group.

  • Service Config (typically ‘indexserver.ini’):

[table_redist] all_moves_physical

By default, when the landscape redistribution is performed, tables are only moved to the new node with their working memory part. The persistence part is written to the new node during the next delta merge. If you want to have the persistence part moved immediately during the landscape redistribution, set this parameter to true. Note, however, that this can significantly extend the runtime.

[table_redist] force_partnum_to_splitrule

Setting this parameter to true forces the execution of operations that change the number of level 1 partitions. For example, if a table has two level 1 partitions but should have three according to the Customizing settings, the landscape redistribution process would not, by default, adjust this. Activating this option forces the adjustment.

Additional Important Notes

  • All tables in the ‘SYS%‘ and ‘_SYS%‘ schemas are explicitly excluded from this functionality.
  • The classification of row store tables is currently not taken into account. To ensure that these tables are always created on the master node, or are moved there, you need to set the parameter ‘method‘ in the section [table_placement] of global.ini to “2”.
  • The Customizing in the TABLE_PLACEMENT table replaces the INI parameters used before SAP HANA SP6 for the column store.
  • The landscape redistribution process is performed in two steps:
    • Generation of a plan
    • Execution of the plan (Currently, the plan generated in step 1 is always completely executed. In the future, it will be possible to manually adjust the plan.)
To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Andy Silvey

    Hi Jochen,

    this is an excellent blog, thank you.

    I will add a reference for this and section heading to the Hana Reference for Basis Administrators.

    All the best,

    Andy.

    (0) 
  2. Lars Breddemann

    Hi Jochen,

    I agree with Andy: a very nice post!

    One thing I struggle with is to find the actual syntax documentation for supplying group type, subtype, and group name. Can you please provide the actual link?

    Thanks and cheers,

    Lars

    (0) 
    1. Jochen Becker Post author

      Hi Lars!

      Thank you for the feedback!

      Unfortunately the syntax didn’t make it into the HANA SQL Reference documentation in time. I added the SQL statements to the blog.

      Best regards,

      Jochen

      (0) 
  3. Andy Silvey

    Hi All,

    incase anyone else is an OSS Notes nerd like me, these might be complimentary to this blog:

    OSS 1908133 Landscape Redistribution – force split rules

    OSS 1908075 BW on SAP Hana SP06: Landscape redistribution

    have fun,

    Andy.

    (0) 
  4. Sridhar Bendapudi

    Hello All ,

      Make sure your on BW Version SP10 and above and HANA Rev 63+.. There is an issue with TABLE_PLACEMENT and RSDU_TABLE_CONSISTENCY not updating the group table with correct names… If you are already on SAP BW 7.31 and SP10 you ca run the following reports ..

    SHDB_GROUP_TABLES_LOAD_BASED

    SHDB_DISTRIBUTION_TABLES

    Sridhar

    (0) 

Comments are closed.