Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this page is to provide a brief understanding, common issues and provide relevant documentation for table distribution and partitioning for SAP HANA, 

 

Overview

SAP HANA provides two ways of distributing data

  1. Database      Partitioning - different tables being distributed across several hosts (SAP Note 2044468 - FAQ:      SAP HANA Partitioning)
  1. Table      Partitioning - splitting a column store table and distributing across several hosts

(SAP Note 2081591 - FAQ: SAP HANA Table Distribution ) 

 

The optimizing algorithm takes factors into account such as:

a) Balance tables equally in a way that they are spread over all the slave nodes and make optimal use of the available physical memory on each node

b) Ensure that tables/table partitions which are belonging to the same table groups (see tables _SYS_RT.TABLE_PLACEMENT, SYS.TABLE_GROUPS) are located on the same nodes, as they are logically frequently queried from application side via joins and if they are located on the same nodes, the joins will be executed faster (SAP Note 1862584 - BW on HANA: support of grouping objects)

c) Split tables into (n) partitions dependent on number of contained rows (m) and distribute them round robin on the available nodes while still considering the first two rules (SAP Note 1650394 - SAP HANA DB: Partitioning and Distribution of Large Tables )

 

In order for the table placement rules to come into effect, the tables must have a partitioning specification associated with the table. The table does not necessarily need to be partitioned as it is possible for "user-specified" number of partitions" to 1 so that the table placement rules apply to the table. Partition specifications are lost if the tables are merged manually. Information about partition specification for a table can be found within the CREATE statement or in TABLES under PARTITION_SPEC.

 

Troubleshooting Steps

Often, customers create tickets for suboptimal table redistribution which causes performance issues resulting from not meeting pre-requisites for table redistribution. Some potential areas to investigate are:

  1. Has Table Distribution been set-up correctly?

For BW, refer to SAP Notes

SAP Note 1908075 - BW on SAP HANA: Table placement and landscape redistribution
SAP Note 2143736 - FAQ: SAP HANA Table Distribution for BW
SAP Note 1958216 - HANA landscape redistribution configuration
SAP Note 1950099 - HANA landscape redistribution default configuration

For BPC, refer to SAP Note 2003863 - Enable BPC HANA table distribution

 

For Business Suite, refer to SAP Note 1899817 - SAP Business Suite on distributed SAP HANA database (scaleout): Landscape redistribution

 

  1. Check if TABLE_PLACEMENT has been properly maintained as guided in the notes
  2. SELECT granted to the SAP<SID> user for _SYS_REPO.SCHEMAVERSION AND _SYS_RT.TABLE_PLACEMENT
  3. Specific parameter requirements relevant to each environment

 

  1. Run RSDU_TABLE_CONSISTENCY to resolve any possible inconsistencies.

Related notes:


Make sure the latest RSDU_TABLE_CONSISTENCY report is implemented on the ABAP system.
SAP Note 2025271  RSHDB: RSDU_TABLE_CONSISTENCY NW7.30 SP13
SAP Note 2150645 - RSDU_TABLE_CONSISTENCY: Round Robin partitioning for infocubes has wrong number of servers (Pilot note)
SAP Note 1937062  Usage of RSDU_TABLE_CONSISTENCY
SAP Note 2043632 - Table classification missing or wrong, when create, copy or modify a DSO

 

Emphasis on the following two tables

  1. CL_SCEN_PARTITION_SPEC - Check the Partition Specification of Tables
    1.                                        i.      Partition Specification is stored in TABLES under PARTITION_SPEC
    2. CL_SCEN_TAB_CLASSIFICATION - Check tables are classified correctly
      1.                                        i.      Group types, subtypes and group names are stored in TABLE_GROUPS

 

  1. Table      reorganization failed
         SAP Note 2093572 -      SAP HANA Migration from Multi-Node to Single-Node
  1. REORG_OVERVIEW - Provides information about latest algorithms run such as redistributing, optimizing, status, and what day they had performed it.
    1.                                        i.            ALGORITHM_ID definitions:

1           Redistribute tables after adding host(s)

6           Optimize table distribution           

7           Optimize table partitioning   

 

  1. REORG_PLAN - proposed plan for landscape distribution with details such as new and old partition specification, new and old host and ports for each table. Useful in investigating what changes were recently made to the distribution.
    1.                                        i.      Note that this table is temporary. To receive a copy of the plan from the customer
    2. REORG_STEPS - steps that are planned to be executed or have been executed for table redistribution. Along with the entries in REORG_PLAN, it also has details about status, error messages and start and end times.
          
      1. Create a New Reorg plan: "CALL        REORG_GENERATE(<ALGORITHM_ID>, <PARAMETER>)";
      2.   
      3. Select * from REORG_PLAN
          
      1. Right Click -> Export Result -> Save
  1. Dynamic      Tiering
        SAP Note: 1767880      - Non-active data concept for BW on SAP HANA DB
        SAP Note 2140959 -      SAP HANA dynamic tiering - additional information

 

  1. Increasing      trace levels for the indexserver can provide insight on why were changed      made by reorganization algorithm
  1. indexsever.ini

[trace]

Landscapereorg=debug

 

  1. Related tables and SQL Scripts to find table locations

SELECT * FROM M_TABLE_LOCATIONS WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

SELECT * FROM M_TABLE_PERSISTENCE_LOCATIONS WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

SELECT * FROM M_CS_TABLES WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

SELECT * FROM M_CS_COLUMNS WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

SELECT * FROM M_TABLE_PERSISTENCE_STATISTICS WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

SELECT * FROM M_TABLE_VIRTUAL_FILES WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';
SELECT * FROM M_TABLE_LOB_FILES WHERE SCHEMA_NAME = '<SCHEMA>' AND TABLE_NAME = '<TABLE>';

 

SQL Scripts from SAP Note 1969700 - SQL statement collection for SAP HANA

SQL: "HANA_Tables_PartitionedTables" to list partitioned tables

SQL: "HANA_Tables_Partitions" to list individual partitions of one or multiple tables

SQL: "HANA_Tables_ColumnStore_TableHostMapping" to show the partition distribution of tables across nodes in a scale-out scenario

 

Common Issues/ Questions

Question:  How can you evaluate the distribution might be optimized? Is there any good indicator to evaluate the distribution optimization?

Answer: There isn't a definitive indicator available to suggest whether or not redistribution is necessary. The redistribution algorithm takes into account the factors covered in the overview section. Redistributions should be considered in the following scenarios:

- Re-run a redistribution plan if it was not 100% successful to ensure that the above goals are reached, especially b) and c) from the overview section

- Consider re-running table redistribution if the data volumes' in your system has heavily changed (data grow, data archiving)

- Re-run distribution when adding/removing nodes

 

Problem: Trace files indicate that BW has exceeded 2 billion record limit and states the error:

[36256]{600087}[39/20053484] 2015-02-11 06:22:18.701613 eUdivMethods UdivMethods.cpp(00341) : Maximum number of rows per

partition reached for SAPB2P:/BI0/E0UCSA_C01. Please try merge + optimize compression before adding new rows. Delta-Part=1

Solution: The error is correct in its statement as the  collective sum of records in  main and delta exceed 2 billion. The following SQL statement can be used to confirm the exceeded record limit:

select HOST, PORT, SCHEMA_NAME, TABLE_NAME, PART_ID, RAW_RECORD_COUNT_IN_MAIN, RAW_RECORD_COUNT_IN_DELTA, RAW_RECORD_COUNT_IN_MAIN + RAW_RECORD_COUNT_IN_DELTA as TOTAL_RECORD from M_CS_TABLES where SCHEMA_NAME = '<SCHEMA_NAME>' and TABLE_NAME like '<TABLE_NAME>'

Please merge and optimize compression as indicated in the error

 

Problem: Partition Specification does not match between F and E fact tables causing errors such as:

"column store error:  [2575] flatten scenario failed;invalid table distribution (see SAP Note 1695778): number of source F parts does not match numberof source E partgroups."

Solution: Refer to SAP Note 2151907 - Infocube conversion fails with error invalid table distribution number of source F parts does not match number of source E part

 

Problem: More than one F-fact cube is located on one host which causes the error:

"For details on termination of stored procedure execution, see long text   Message no. RSDRI_HDB051   column store error:  [2575] flatten scenario failed;   multiple source F tables assigned to the same host, should not happen   Errors occurred during conversion "

Solution:  Refer to SAP Note 2103469 - Converting standard cube to HANA optimized fails

 

Problem: After repartitioning, tables appear to have lost the compression they had prior to landscape redistribution

Solution: If tables without activated auto merge or auto compression (e.g. BW tables) are repartitioned, the compression is lost and the tables are significantly larger than before. SQL: "HANA_Tables_ColumnStore_TablesWithoutCompressionOptimization" (SAP Note 1969700) can be used to identify those tables. Then you can trigger a compression optimization using the following command:

UPDATE "<table_name>" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'YES') (SAP Note 2112604 - FAQ: SAP HANA Compression)

 

Related Documents

SAP HANA Administration Guide

Sections:

Table Partitioning

Table Distribution in SAP HANA

Monitor Table Distribution

Redistribution of Tables in a Distributed System

 

SAP HANA Landscape Redistribution with SP6:

http://scn.sap.com/community/hana-in-memory/blog/2013/09/03/sap-hana-landscape-redistribution-with-sp6

 

SAP HANA-optimized BW Objects - Infocubes: 

http://wiki.scn.sap.com/wiki/display/BI/SAP+HANA-optimized+BW+Objects+-+Infocubes

 

How to Configure SAP HANA Table-Distribution for BW-on-HANA Scale-Out Systems

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40d5d44e-2605-3210-2da1-9a0745450116?QuickLink=index&overridelayout=true&59511066999521

 

Other Related SAP Notes/KBAs

General

 SAP Note: 1908082 - SAP HANA DB: Priorities of TABLE_PLACEMENT entries

 

BW Specific Related Notes

SAP Note: 1908073 - BW on SAP HANA: Table distribution and table partitioning

SAP Note: 1702409 - HANA DB: Optimal number of scale out nodes for BW on HANA
SAP Note: 2019973 - BW on HANA - Handling BIG data tables
SAP Note: 2069235 - How to change the initial number of first-level partitions for BW objects

SAP Note: 1924115 - DSO SAP HANA: partitioning of change log tables
SAP Note: 1600929 - SAP BW powered by SAP HANA DB: Information
SAP Note: 2040346 - Changes required when deleting nodes from a scale-out SAP HANA database

 

Other

SAP Note: 1781986 - Business Suite on SAP HANA Scale Out

SAP Note: 2033572 - No table distribution supported for virtual tables

SAP Note: 1979867 - Program for repairing inconsistent partitioning of PSA tables

 

  • No labels