SAP HANA 2.0 SPS 02 What’s New: System Administration – by the SAP HANA Academy
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
- SAP HANA 2.0 SPS 01 What’s New: Administration
- SAP HANA 2.0 SPS 00 What’s New: Administration
- SAP HANA 1.0 SPS 12 What’s New: Administration
- SAP HANA 1.0 SPS 11 What’s New: Administration
- SAP HANA 1.0 SPS 10 What’s New: Administration
For the full SAP HANA 2.0 SPS 02 blog list, see
Tutorial Video
URL: 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:
- Manage Blacklisted Parameters
- Manage Restricted Features
- Configure Workload Allocation
- Set Restart Mode
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
- Lock Parameters Against Editing for a Tenant Database – SAP HANA Tenant Databases Operations Guide
- Restrict Features Available to a Tenant Database – SAP HANA Tenant Databases Operations Guide
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
- Prevent the Start of a Tenant Database at System Startup – SAP HANA Tenant Database Operation Guide
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
- Managing Peak Load (Admission Control) – SAP HANA Administration Guide
- Managing Workload with Workload Classes – SAP HANA Administration Guide
- Workload Management Statements – SAP HANA SQL and System Views Reference
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
- License Keys for the SAP HANA Database – SAP HANA Tenant Databases Operations Guide
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
- Configure Host-Independent Tenant Addresses – SAP HANA Tenant Database Operation Guide
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
- Port Assignment in Tenant Databases – SAP HANA Administration Guide
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
- Start a Tenant Database – SAP HANA Tenant Database Operation Guide
- Stop a Tenant Database – SAP HANA Tenant Database Operation Guide
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
- Row to Column Table Replication – SAP HANA Administration Guide
- Table Replication – SAP HANA Administration Guide
- 2340450 – FAQ: SAP HANA Table Replication
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
- Static and Dynamic Partition Pruning – SAP HANA Administration Guide
- CREATE STATISTICS Statement (Data Definition) – SAP HANA SQL and System Views Reference
- Table Partitioning – SAP HANA Administration Guide
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
- Monitoring Health in Multi-Host Systems – SAP HANA Tenant Databases Operations Guide
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
- Export Resources – SAP HANA Administration Guide
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
- SAP HANA HDBSQL Options – SAP HANA Administration Guide
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
- Table and Catalog Consistency Checks – SAP HANA Administration Guide
- 1977584 – Technical Consistency Checks for SAP HANA Databases
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
- View Diagnostic Files in the SAP HANA Database Explorer – SAP HANA Administration Guide
- Using the Cockpit to Troubleshoot an Unresponsive Resource – SAP HANA Administration Guide
- Getting Support – SAP HANA Administration Guide
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 HANA Cockpit (New and Changed) – Administration and Monitoring – What’s New in the SAP HANA Platform 2.0 (Release Notes)
- Lock Parameters Against Editing for a Tenant Database – SAP HANA Tenant Databases Operations Guide
- Restrict Features Available to a Tenant Database – SAP HANA Tenant Databases Operations Guide
- Export Resources – SAP HANA Administration Guide
- View Diagnostic Files in the SAP HANA Database Explorer – SAP HANA Administration Guide
- Using the Cockpit to Troubleshoot an Unresponsive Resource – SAP HANA Administration Guide
- Getting Support – SAP HANA Administration Guide
- License Keys for the SAP HANA Database – SAP HANA Tenant Databases Operations Guide
- Managing Peak Load (Admission Control) – SAP HANA Administration Guide
- Managing Workload with Workload Classes – SAP HANA Administration Guide
- Workload Management Statements – SAP HANA SQL and System Views Reference
- Row to Column Table Replication – SAP HANA Administration Guide
- Static and Dynamic Partition Pruning – SAP HANA Administration Guide
- CREATE STATISTICS Statement (Data Definition) – SAP HANA SQL and System Views Reference
- Table Partitioning – SAP HANA Administration Guide
- Port Assignment in Tenant Databases – SAP HANA Administration Guide
- Configure Host-Independent Tenant Addresses – SAP HANA Tenant Database Operation Guide
- Prevent the Start of a Tenant Database at System Startup – SAP HANA Tenant Database Operation Guide
- Start a Tenant Database – SAP HANA Tenant Database Operation Guide
- Stop a Tenant Database – SAP HANA Tenant Database Operation Guide
- Table and Catalog Consistency Checks – SAP HANA Administration Guide
- SAP HANA HDBSQL Options – SAP HANA Administration Guide
- SAP HANA System Administration (New and Changed) – What’s New in the SAP HANA Platform 2.0 (Release Notes)
SAP Notes
- 2460914 – SAP HANA Platform 2.0 SPS 02 Release Note
- 2462387 – SAP HANA cockpit 2.0 SP 03
- 2340450 – FAQ: SAP HANA Table Replication
- 1977584 – Technical Consistency Checks for SAP HANA Databases
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
- Subscribe to our YouTube channel for updates
- Join us on LinkedIn: linkedin.com/in/saphanaacademy
- Follow us on Twitter: @saphanaacademy
- Google+: plus.google.com/+saphanaacademy
- Facebook: facebook.com/saphanaacademy
Question to everyone: Regarding the feature "Restricted SQL Access" I'm questioning where exactly the benefit lays, when we open another SQL port, and also need to secure this port to the external network. Since there is a re-routing set from port 3 - instance number - 13 to 3 - instance number - 17, why is it more secure? Does anyone questioned this before and came to an explanation? Or does anybody here already set up this feature in productive environment and is able to share the experiences?
Thanks in advance!
Hi Elwin,
It is probably best to post your question to https://answers.sap.com/questions/ask.html.
The comment section under a blog is more intended for comments related to that blog.
Denys / SAP HANA Academy