Introduction
In the upcoming weeks, we will be posting new videos to the
SAP HANA Academy to show new features and functionality introduced with
SAP HANA 2.0 Support Package Stack (SPS) 02.
The topic of this blog is
system administration.
For the previous versions of this blog, see
For the full SAP HANA 2.0 SPS 02 blog list, see
Tutorial Video
URL:
https://youtu.be/gKPGn8DsLNM
https://youtu.be/gKPGn8DsLNM
What's New?
Managing Tenant Databases (Changed)
New functionality has been added to the
Manage Database page in SAP HANA cockpit.
For a selected tenant, you can now:
Blacklisted parameters and restricted features for tenant databases have been available since the initial release of multi-tenant database systems. For the configuration, you have set the relevant system parameters. As of cockpit 2.0 SP 03, we can now very easily do this using the tool.
Workload Management is also not new but here also we now have the cockpit interface to do the configuration. For the changes to workload management, see the section below.
New menu items:

Blacklisted Parameters for Tenants
Restricted Parameters for Tenants
Memory and CPU Allocation for Tenant
For more information, see
Prevent the Start of a Tenant Database at System Startup (New)
At system startup, all tenant databases that were running before the SAP HANA system was stopped are restarted. Previously, the only way to prevent a particular database from starting is to stop that database before you stop the system. Inconvenient? Yes, sometimes.
You can now configure the restart mode of a tenant database in HANA cockpit.
For more information, see
Workload Management (Changed)
Workload management, introduced in SPS 10, allows you to manage workloads, that is, making sure that simple OLTP, complex OLAP statements, or long-running data loading jobs all get a fair share of the computing resources (CPU and memory).
To manage peak load situations,
Admission Control was added to Workload Management in SAP HANA 2.0. This allows you to configure how HANA should handle rush hour and peak times.
Works as advertised, so, as of SPS 02, admission control is now enabled by default. Some of the default values for the configuration parameters have been revised as well.
Two additional properties have been added for workload classes:
- TOTAL STATEMENT MEMORY LIMIT
- TOTAL STATEMENT THREAD LIMIT
CREATE WORKLOAD CLASS "Sample"
SET 'PRIORITY' = '5',
'TOTAL STATEMENT MEMORY LIMIT' = '30',
'TOTAL STATEMENT THREAD LIMIT' = '50';
For more information, see
Tenant-Specific Licenses (New)
You can install a license specifically for a tenant database.
There is no UI for this feature, yet. You implement this with the SET SYSTEM LICENSE command (SQL).
For more information, see
Configure Host-Independent Tenant Addresses (New)
By default, the SQL port to a tenant database is fixed and includes the instance number, typically 3
<instance_no>13. If you move a tenant database to another system the instance number could very well change. Now what?
As of SPS 02, you can specify additional port numbers and map them to the tenant. Any available port number is fine, except 0.
There is no UI for this feature, yet. You implement this like you would set any system parameter with an ALTER SYSTEM ALTER CONFIGURATION.
For more information, see
Restricted SQL Access (New)
To prevent the exposure of the system database SQL port (port 3
<instance>13) to the external network, you can now open additional SQL ports. This opens port 17 for SQL requests to the system database and restricts access through port 3<instance>13 for database mapping. The connection through port 3<instance>13 is re-routed to 3<instance>17 if a connection to the system database is required.
You still need to make sure that port 3<instance>17 is not exposed to the external network, of course.
To enable this feature, set systemdb_separated_sql_port= true in global.ini, section [multidb].
For more information, see
Authorization for Database Stop/Start (New)
Two new system privileges were introduced to authorize users to start and stop a database: DATABASE START and DATABASE STOP. Both system privileges are part of DATABASE ADMIN.
For more information, see
Table Replication: Row Store to Column Store Tables (New)
Table replication can help to reduce network traffic when, for example, slowly-changing master data often has to be joined with (partitions of) tables, located on other hosts. It is typically used in distributed (scale out) systems.
New in SPS 02 is that you can now replicate data
asynchronously from a row store source table to a column store target table.
Row store tables typically suit OLTP-type workloads. Column store tables, in general, offer the best performance for analytics workload. Row to column table replication may, therefore, be an optimal replication configuration for
mixed workload types to get the best performance from both types of table.
There is no UI for this feature, yet. You implement row-to-column-store-table-replication with SQL.
For more information, see
Partitioning: Dynamic Partition Pruning (New)
Partitioning can help you to manage very large (column-store) tables. It is typically used in distributed (scale out) systems.
To optimize query processing on partitioned tables, a background process called pruning is active. It involves the query optimizer analyzing the WHERE clause of queries to determine whether or not the filters match the given partitioning specification of a table. It is based on the partition definition, which does not change, so it is now labeled static pruning.
SPS 02 introduces
dynamic partition pruning. This is content-based and takes place at run time based on the existence of statistics; it helps to avoid the need to access and load into memory partitions which are not required.
Statistics for dynamic partition pruning must be explicitly created with the VALID FOR DATA DEPENDENCY clause:
CREATE STATISTICS test_dynPruning_tab1_col1
ON tab1 (col1)
TYPE SIMPLE
VALID FOR DATA DEPENDENCY;
For more information, see
System Health (Changed)
For distributed (scale-out) systems, cockpit provides status information on the health of system components on their respective servers, and on resource utilization of hardware components, including CPU, memory, network, and storage on the respective servers on the
System Health page. In cockpit 2.0 SP 03, this page has been enhanced with additional functionality.
For more information, see
Resource Registration (Changed)
You can now import and export resources from the Cockpit Manager as JSON file. Contact information and technical user login name are optional.
For more information, see
SAP HANA HDBSQL (New and Changed)
attemptencrypt (New)
The new -attemptencrypt option specifies that encrypted data transmission is used. It is an attempt and if the attempt does not succeed an error message is returned.
separatorownline (Deprecated)
HDBSQL now recognizes BEGIN ... END blocks in a file or interactive input so that you no longer need to modify the script and use the -separatorownline option. The -separatorownline flag has not changed but is now marked as deprecated and probably, eventually, will be removed.
For more information, see
Table Consistency Check (Changed)
To perform table consistency checks, it is no longer necessary to enable the corresponding collector. These checks are now performed automatically.
Previously, you had to first run an update statement on the STATISTICS_SCHEDULE table:
-- enable the collector (SPS 01 and earlier)
UPDATE _sys_statistics.statistics_schedule
SET status = 'Idle' WHERE id = 5047;
CALL CHECK_TABLE_CONSISTENCY ('CHECK', 'USER_1', 'TABLE_A')
For more information, see
Administering Offline Resources (Changed)
The
SAP HANA cockpit for offline administration is no longer required for the administration of offline resources.
Initially, SAP HANA cockpit was part of a SAP HANA system. System down? No cockpit. For offline administration, the SAP host agent installed on every HANA system was used with the
SAP HANA cockpit for offline administration as its web UI
.
Now, with SAP HANA cockpit 2.0, this separate tool is no longer required and as of SP 03, all capabilities that could formerly be performed only through the offline cockpit are now included.
SAP HANA cockpit for offline administration is still available but, who knows? Some day, it might be gone. The documentation recommends using the SAP HANA cockpit for both on- and offline activities.
Manage Services, including stop system:
Offline functions available for a stopped system.
Viewing diagnostic files in
Database Explorer:
Troubleshoot unresponsive system:
Saying goodbye to the SHC4OA.
For more information, see
Playlist
On the SAP HANA Academy, there is a
full playlist covering all aspects of administration:
Documentation
For more information, see:
SAP Help Portal
SAP Notes
Thank you for watching
The
SAP HANA Academy provides free online video tutorials for the developers, consultants, partners, and customers of SAP HANA.
Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.
For the full library, see
SAP HANA Academy Library - by the SAP HANA Academy
For the full list of blogs, see
Blog Posts – by the SAP HANA Academy