Skip to Content
Author's profile photo Denys van Kempen
Denys van Kempen
August 17, 2017 10 minute read

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

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:

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

 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Elwin Foo
      Elwin Foo

      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!

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      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