How to handle ‘Record count of non-partitioned column-store tables’ HANA Alert 17?
The alert called “SCHEMA.TABLENAME has record count 6001215” can be seen in the Alerts tab in HANA.
The alert can be found by following the path Administration Console -> Alerts -> Show: all alerts, in the HANA Studio.
For Solution Manager, you would find the alert using transaction DBACOCKPIT -> choose HANA system -> expand Current Status -> Alerts.
To check the exact time, please go to Administration Console -> Alerts -> double click on the alerts.
Solution:
2 factors have been identified as contributing to this alert:
In the "Configure Check Settings", the threshold value of "Check record count of not partitioned column table” has not be correctly configured.
Note: The default values are as follows:
- High: 300,000,000
- Medium: 200,000,000
- Low: 100,000,000
In this testing, the low level threshold has been manually changed to 5,000,000:
The given prerequisite must be met for a column table:
- The column table has not been partitioned.
- The records number in the column table is more than the threshold of the alert.
To resolve this issue perform the following Steps:
Whenever an alert is triggered due to incorrect threshold value setting, resetting it to the default values or those closest to the threshold values can resolve the issue.
Splitting the column table into partitions can also be an option.
The partitioning feature that the SAP HANA Database has, enables splitting of column-store tables horizontally into disjunctive sub-tables or partitions.
Using this procedure, huge tables can be split down into smaller subdivisions that are also more manageable. Partitioning is a process typical to distributed systems, however, it can also be conveniently used for single-host systems.
You could check the table partitioning by right click the “schema”, and click “Show Table Distribution”.
Note: The following single-level partition specifications are supported by the SAP HANA database:
HASH
ALTER TABLE MY_TABLE PARTITION BY HASH (PK1,PK2) PARTITIONS 3
RANGE
ALTER TABLE MY_TABLE PARTITION BY RANGE (PK) (PARTITION value1 <= VALUES < value2,PARTITION value2 <= VALUES < value3, PARTITION OTHERS)
Round Robin
ALTER TABLE MY_TABLE PARTITION BY ROUNDROBIN PARTITIONS 3
Given below are the guidelines to be followed to partition column table LINEITEM:
Before Partitioning:
When the Column table LINEITEM has not been partitioned and shows 6,001,215 records, which clearly is more than the low level threshold for alert “Check record count of not partitioned column table”.
Execute Partitioning:
The SQL Editor needs to be opened and sql command “ALTER TABLE LINEITEM PARTITION BY HASH (L_ORDERKEY,L_LINENUMBER) PARTITIONS 3” to HASH partition table LINEITEM be executed.
After Partitioning:
When the table LINEITEM is subdivided into 3 parts, the records for individual parts never exceed the threshold.
