SAP HANA 2.0 SPS 03 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) 03.

The topic of this blog is system administration.

For the previous versions of this blog, see

• SAP HANA 2.0 SPS 02 What’s New: System Administration
• 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

For the full SAP HANA 2.0 SPS 03 blog list, see

• What’s New with SAP HANA 2.0 SPS 03

What’s New?

SAP HANA Administration Playlist

On the SAP HANA Academy, there is a full playlist on the topic of SAP HANA Administration.

Managing Tenant Databases

Fallback Snapshot

A fallback snapshot allows you to quickly and easily return back to a previous database state, for example, to reset a tenant database used for training.

They cannot be used for the system database and you can only create one at a time.

For a demonstration, see the video tutorial:

To create one interactively, you can use Manage Databases in SAP HANA cockpit and use the same view to reset the database or delete the snapshot.

SAP HANA cockpit > Manage Databases: Create Fallback Snapshot

SAP HANA cockpit > Manage Databases: Reset to Fallback Snapshot

For automation, use SQL:

-- create
ALTER DATABASE <database name> CREATE FALLBACK SNAPSHOT;
-- reset to snapshot
ALTER SYSTEM START DATABASE <database name> FROM FALLBACK SNAPSHOT;
-- delete
ALTER DATABASE <database name> DROP FALLBACK SNAPSHOT;

The system view M_SNAPSHOTS lists the snapshots (but you can only create one at a time, per tenant).

For the documentation, see

• Create a Fallback Snapshot – SAP HANA Administration Guide
• ALTER DATABASE Statement  – SAP HANA SQL and System Views Reference

Command Line Tool: hdbmdcutil

In the unlikely event, that the persistence for your system database is damaged ánd you do not have a backup of the system database (both conditions should apply), you can now reset the system database using the new hdbmdcutil tool.

A reset of the system database is not without consequences, as any changes made to the system database are lost (license, imported delivery units or MTARs, etc.), which in particular affects the XS Advanced environment. However, resetting the system database is very quick and avoids long downtime as compared to a full new installation with a move of all associated tenant databases.

For a demonstration, see the video tutorial:

For the documentation, see

• 2588284 – Re-initialize a not recoverable system database

Tenant Service Port Change

You can now change the TCP port (or host) of the different services (indexserver, scriptserver, xsengine, dpserver).

ALTER DATABASE <database_name> ALTER 'scriptserver' 
AT '<host>:30303' TO '<host>:30340'

For the documentation, see

• ALTER DATABASE Statement (Tenant Database Management)

System Administration

Parameter Tracking

As of SPS 03, changes to configuration values are now logged by default with the option to add the reason for the change as a comment. This feature maintains a history of changes to configuration values including details of the time the change was made, the username, the current and previous values in the view M_INIFILE_CONTENT_HISTORY.

For the documentation, see

• Configuring SAP HANA System Properties (INI Files) – SAP HANA Administration Guide

Persistent Memory

I already have mentioned it on my blog about SAP HANA 2.0 SPS 03 What’s New: Installation and Update: as of SPS 03, persistent memory (non-volatile RAM) is supported a storage type.

Persistent storage works the same way as regular memory and would be used for main data fragments of the column store where approximately 90% of the data of a table is held. The delta area of the column store table remains is DRAM memory.

The advantage of using persistent memory is found in the accelerated start-up times because much less data needs to be loaded into memory and this will minimize system downtime.

Persistent memory can be enabled at the column-, partition-, table-, or database-level (default), which allows for a great deal of flexibility. Below some examples from the documentation. At the time of writing, the persistent memory technology is not yet available for SAP HANA so we could not run any tests or make any tutorial video yet on this topic. When it comes available we will certainly revisit this topic.

CREATE COLUMN TABLE PMTABLE (C1 INT, C2 VARCHAR (10)) 
PERSISTENT MEMORY ON;

CREATE COLUMN TABLE PMTABLE (C1 INT) 
  PARTITION BY RANGE (C1) (
    PARTITION ‘0’ <= VALUES < ’10’ PERSISTENT MEMORY ON, 
    PARTITION OTHERS PERSISTENT MEMORY OFF);

For the documentation, see

• Persistent Memory
• 2618154 – SAP HANA Persistent Memory – Release Information

Data Volumes Partitions

As of SPS 03, you can now partition the data volumes of the database engine (indexserver). As an in-memory database, most operations will not benefit much from striped volumes. However, for example, startup times – when all column tables marked for load need to be read from disk – can be improved using data volume partitions.

Below an example of the SQL to manage partitions from the documentation.

ALTER SYSTEM ALTER DATAVOLUME ADD PARTITION;

Additionally, two new views have been added to keep score:

• M_DATA_VOLUME_STATISTICS
• M_DATA_VOLUME_PARTITION_STATISTICS

For the documentation, see

• Partitioning Data Volumes

Monitoring

Changes made to Alert Thresholds

A history of changes made to alert thresholds is now maintained and can be retrieved from this system view: STATISTICS_ALERT_THRESHOLDS_HISTORY

For the documentation, see

• STATISTICS_ALERT_THRESHOLDS_HISTORY System View (Embedded Statistics Service)

New Alerts

Nine new alerts have been introduced (105-110, 117, 127, 128).

For the documentation, see

• Reference: Alerts – SAP HANA Troubleshooting and Performance Analysis Guide

Managing Tables

Column Table as Default Table Type

As of SPS 03, if not specified, new tables will be created as column tables. Previously, a row table would have been created.

Heterogeneous Partitioning

There are different ways how you can partition a large table. Partitioning is typically used on a distributed system and mostly only concern very large tables. Partitioning types are range, hash, or round-robin, and this allows you, for example, to implement load balancing, maybe overcome the size limitation of column-store tables (a mere 2 billion rows), parallelize operations, improve delta merges, and for other reasons, as documented in the Administration Guide.

To overcome the limitations of hash or range partitioning, you can implement multi-level partitioning, which also makes it possible to partition by a column that is not part of the primary key.

As of SPS 03, we can now also implement heterogeneous partitioning which allows for a more flexible partitioning strategy and even includes the use of extended storage (Dynamic Tiering). Below a few examples from the documentation.

The keyword SUBPARTITION BY indicates that we are implementing heterogeneous partitioning.

Using AT LOCATION, to specify the host where the partition should reside, here a Dynamic Tiering host using extended storage.

The USING EXTENDED STORAGE clause assigns the storage type to a partition.

For the documentation, see

• Heterogeneous Partitioning

System-Versioned Tables

In the very early days of SAP HANA, one of the advantages explained for the insert-only approach for columnar tables is that it would allow for time travel. In the documentation, this is referred to as history tables.

SPS 03 now introduces system-versioned tables and this will eventually replace the existing history tables feature. With system-versioned tables, you can track changes on column store tables by capturing the validity period of each record.

System-versioned tables are part of the SQL standard and always consist of two physical tables: the main table of records that are currently valid, and a corresponding history table of archived records.

Details of system-versioned tables and their corresponding history table are available in the view SYS.TEMPORAL_TABLES.

For the documentation, see

• History Tables
• System-Versioned Tables
• TEMPORAL_TABLES System View

Table Redistribution

As of SPS 03, you can now perform table redistribution from the command line with new configuration options and a series of weighting parameters to influence the priorities of the redistribution. One of these options is to apply the configuration only to your current session, which allows you to test redistribution options by repeatedly generating, reviewing, and optimizing the plan before it is finally executed.

The syntax for these commands is:

• CALL REORG_GENERATE(<algorithm integer>, <optional parameter string>);
• CALL REORG_EXECUTE(<plan_id>)

Examples of algorithm integer are:

• 1 = Add server
  • SCHEMA_NAME
  • TABLE_NAME
  • GROUP_NAME
  • GROUP_TYPE
  • GROUP_SUBTYPE
  • RECALC
  • NO_PLAN
• 6 = Balance landscape
  • SCHEMA_NAME
  • TABLE_NAME
  • GROUP_NAME
  • GROUP_TYPE
  • GROUP_SUBTYPE
  • RECALC
  • NO_PLAN
  • NO_SPLIT
  • SCOPE
• 7 = Check number of partitions
• 14 = Check table placement
  • LEAVE_UNCHANGED_UNTOUCHED
  • KEEP_VALID
  • NO_SPLIT
• 15 = Rerun plan
• 16 = Housekeeping
  • OPTIMIZE_COMPRESSION
  • DEFRAG
  • LOAD_TABLE
  • MERGE_DELTA
  • ALL

CALL REORG_GENERATE(1, 'SCHEMA_NAME => SAPBWP')
CALL REORG_GENERATE(6, 'SCOPE=>FILLED,NOLOB')

For information about the table distribution operations, you can query the system views

• REORG_OVERVIEW
• REORG_STEPS – details of the individual steps of each plan.
• REORG_PLAN – last table redistribution plan generated with this database connection
• REORG_PLAN_INFOS – details of the last generated plan

For the documentation, see

• Table Redistribution Commands – SAP HANA Administration Guide

Table Consistency Check

Table consistency check has been around for some time (the first SAP Note on this topic goes back to 2012) but SPS 03 has added a couple of new configuration parameters for runtime management of the job defined in the indexserver.ini system parameter file.

For the documentation, see

• Configuration Parameters for the Table Consistency Check – SAP HANA Administration Guide

SAP HANA Troubleshooting and Performance Analysis

Workload Management

You can now set a timeout values for queued jobs (admission control) and a statement timeout value for workload classes (in a workload class or as a configuration parameter).

SQL Plan Stability

SQL Plan Stability allows you to protect the performance of queries by capturing query plans in a source system and reusing them in a target system to regenerate the original query plan. You can use this feature, for example, when upgrading from SAP HANA 1.0 to SAP HANA 2.0.

ALTER SYSTEM START CAPTURE ABSTRACT SQL PLAN WITH SQL PLAN CACHE;
ALTER SYSTEM ENABLE ABSTRACT SQL PLAN FOR ALL;

For the documentation, see

• SQL Plan Stability – SAP HANA Troubleshooting and Performance Analysis Guide

ESX and HEX

Not new to SPS 03 but mentioned in the SPS 03 What’s New guide, so repeated here for completeness sake. As documented,

Starting from HANA 2 SPS 02, two new processing engines to execute SQL queries are being phased into SAP HANA: the Extended SQL Executor (ESX), and the HANA Execution Engine (HEX). The functionality of the product remains the same but these engines offer better performance.

For the documentation, see

• Statement Performance Analysis – SAP HANA Troubleshooting and Performance Analysis Guide

Security Administration – Managing Data Encryption

Client-side Data Encryption

With client-side data encryption, you can encrypt columns using an encryption key accessible only by the client, which means that column data is encrypted and decrypted only on the client.

New privileges and SQL statements have been added to support client-side encryption.

For the documentation, see

• Getting Started With Client-Side Encryption – SAP HANA Security Guide
• Client-Side Data Encryption –  SAP HANA Administration Guide
• Configuring the Client for Client-Side Encryption and LDAP – SAP HANA Client Interface Programming Reference

SAP HANA HDBSQL

Options

Three new options have been added to the SAP HANA Database interactive terminal (hdbsql)

• -strictSeparatorLine
• -printoutput
• \read

For the documentation, see

• SAP HANA HDBSQL Options – SAP HANA Administration Guide

References

SAP Help Portal

• SAP HANA System Administration (New and Changed) – What’s New in the SAP HANA Platform 2.0

Thank you for watching

The SAP HANA Academy provides technical enablement, implementation and adoption support for customers and partners with 1000’s of free tutorial videos.

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