What's New in the SAP HANA Platform 2.02.0 SPS 07 related to System Administration #SAP HANA

SAP HANA introduces new and changed administration features.

Partitioning (Changed)

Privileges for Partitioning

• The PARTITION ADMIN system privilege is now available so that users with this privilege can perform some partitioning operations with the ALTER TABLE statement. More specific ALTER object privileges for the table are still required to modify partitions.

Table Partitioning

Range Partitioning

• For heterogeneous range partitioning the numeric data types now support negative and positive numbers for the partitioning column.

Range Partitioning

Dynamic Range Partitioning

• The NVARCHAR data type is now supported for INTERVAL property values in dynamic range partitioning.
• The numeric data types now support negative and positive numbers for dynamic range partitioning. Details of how this is applied are given in the documentation.

Dynamic Range Partitioning

Persistent Memory Property

• The Persistent memory (NVM) property is now supported for both balanced and heterogeneous partitioning types. Persistent memory is consumed only when the partitioning property is set to TRUE. Consumption is shown as PERSISTENT_MEMORY_SIZE_IN_TOTAL in the monitoring view M_TABLE_PARTITIONS. Properties can be modified for a partition using the ALTER PARTITION key words or using PARTITION BY.

Partition Properties

Partition Locations

• When you partition or repartition a table you can now specify at which location the partition should be created using the AT LOCATION clause. This option can be used to move a partition to a different location and can be used with all partitioning types: hash, roundrobin and range.

Partitioning Operations

Monitoring Views for Partitioning

• You can now enable the collection of additional detailed statistical information in the monitoring view M_TABLE_PRUNING_STATISTICS. This will help you to analyze the effectiveness of partition pruning.

System Views for Monitoring Partitions

Global Cache (Changed)

• You can now define limits to apply a global cache size. You can define upper and lower limits using a set of configuration parameters in the memoryobjects section of the global.ini file. You can set values either as an absolute number of bytes or as a percentage of available memory.

Caching

Consistency Checks - Alerts (New and Changed)

• An additional statistics server alert has been added (Alert #146 Table Consistency Status) for the table consistency check to report the number of errors in tables with inconsistencies.

Alerts Reference

• A new statistics server alert checker has been introduced (Alert #145 Nameserver Topology Inconsistencies) to check the system topology for any inconsistencies. This runs on the nameserver and issues a warning if any inconsistency is found.

Alerts Reference

Workload Management (Changed)

Workload Classes

• The workload mapping property 'Application Source Name' is now available, this is applied at the statement level and corresponds to the session variable APPLICATION_SOURCE.

Properties for Workload Classes and Mappings

• The use of wildcard characters for matching workload classes with the client has been extended. The wildcard character can now be used on its own without any prefix character so that any value for the property will match successfully. See the examples in Properties for Workload Classes and Mappings.

Properties for Workload Classes and Mappings

• The workload class properties to manage thread limits (Statement Thread Limit and Total Statement Thread Limit) can now also be defined in hierarchical parent-child relationships so that a total restriction defined in a parent class can be inherited by child classes.

Hierarchies of Workload Classes

• Enhancements have been introduced to preserve the validity of workload class mappings in cases where the mapping has dependencies to other procedures. Mappings may be invalidated if a dependent procedure is deleted, but validity is restored if the link to the procedure is restored. You can see the status of a mapping from the WORKLOAD_MAPPINGS.IS_VALID column.

Managing Workload with Workload Classes

System Replication (Changed and Deprecated)

• The configuration parameter datashipping_parallel_processing has been deprecated in SAP HANA 2.0 SPS 07. This is no longer required because multistreamed datashipping with parallel processing is now always active.

SAP HANA System Replication Configuration Parameters

• The configuration parameter enable_takeover_with_uninitialized_volumes has been introduced to check before a takeover takes place that all databases are correctly initialized. This can be used either to prevent a takeover or to simply issue a warning.

SAP HANA System Replication Configuration Parameters

• The option to use table preloading with the delta data shipping mode is no longer supported (see configuration parameter preload_column_tables).

SAP HANA System Replication Configuration Parameters

• SSL security is now enabled by default for all system replication connections. The default value of the configuration parameter enable_ssl has been changed to 'on'.

Configure Secure Communication (TLS/SSL) Between Primary and Secondary Sites

• A new option has been added to the hook method srServiceStateChanged. The parameter --details returns additional information about services which may have crashed and are no longer available.

Hook Methods

SAP HANA Dynamic Tiering

• SAP HANA dynamic tiering 2.0 will not receive any new features going forward. The current released version of SAP HANA dynamic tiering is SAP HANA dynamic tiering 2.0 SP 04. Customers using SAP HANA dynamic tiering with SAP HANA 2.0 SPS 06 or beyond continue to use the 2.0 SP 04 version of SAP HANA dynamic tiering.

SAP HANA dynamic tiering is an optional add-on to the SAP HANA database for managing less frequently accessed warm data. It has been superseded by SAP HANA native storage extension (NSE) in SAP HANA 2.0 SPS 04. We recommend that you use NSE for your data tiering requirements.

SAP Note 2636634 

Backup Retention

It is now possible to flag complete data backups to be retained. If backups are flagged as retained, they cannot be deleted by the BACKUP CATALOG DELETE command or by the scheduled housekeeping tasks.

BACKUP DATA Statement (Backup and Recovery)

BACKUP CATALOG DELETE Statement (Backup and Recovery)

BACKUP CATALOG RETAINED Statement (Backup and Recovery)

hdbbackupcheck

The hdbbackupcheck tool now supports encrypted backups.

  

SAP HANA Database Security new capabilities and enhancements, introduced with SAP HANA Platform 2.0 SPS 07 #HANA Database

 SAP HANA Platform 2.0 SPS 07 introduces new and changed features for security.

Auditing (New and Changed)

• The audit log table is now configured to use SAP HANA Native Storage Extensions (NSE) by default. You can also choose to load it completely into memory as before.

  ALTER SYSTEM ALTER AUDIT LOG (System Management)

  Audit Trails

• You can now specify schema names for some DDL based audit actions. The Audit Actions system view has been extended to include this information.

• The audit actions IMPORT, IMPORT SCAN, and EXPORT statements can now be tracked by the audit policy.

  Audit Policies

  CREATE AUDIT POLICY Statement (Access Control)

  AUDIT_ACTIONS System View

• The audit actions ALTER CREDENTIAL, CREATE CREDENTIAL, and DROP CREDENTIAL statements can now be tracked by the audit policy.

  CREATE AUDIT POLICY Statement (Access Control)

• Audit policies now supports the specification of usergroups in addition to users for explicit inclusion or exclusion in the logging of audit events. The AUDIT_POLICIES System View has been modified to reflect this change.

  AUDIT_POLICIES System View

• The audit log now displays the name of the authentication method used to authenticate the connection.

Authentication (New and Changed)

• It is now possible to configure SAML and JWT identity providers for automatic user creation in conjunction with LDAP group authorization.

  This means that if you use an LDAP-compliant directory server to manage users and their access to resources, you can now leverage this infrastructure to provision the database users required for SAML- and JWT-authenticated users.

  A database user can be automatically created for a SAML-/JWT-authenticated user if the user exists in your LDAP server and this user is a member of at least one LDAP group mapped to at least one SAP HANA role.

  Single Sign-On Using SAML 2.0

  Single Sign-On Using JSON Web Tokens

  Configure SAML or JWT Authentication with LDAP-Based User Provisioning

• The default value of the parameter issuer (section jwt_identity_provider) does not include the hostname anymore, because that raises an alert in system replication scenarios.

  <service>.ini

Authorization (New and Changed)

• It is no longer necessary to have object privileges on tables to perform some operations. To execute ALTER TABLE statements that do not change the structure of the table and do not allow access to table data either explicitly or implicitly, the TABLE ADMIN system privilege is sufficient.

  ALTER TABLE Statement (Data Definition)

• The procedure GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS now displays SAP passport information to improve authorization error analysis.

  GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS

• The COMMENT ON statement now allows you to add a comment on certificates and public keys.

  COMMENT ON Statement (Data Definition)

• You can now store X.509 duplicate certificates with different names if a certificate name has been provided.

Communication Security (New and Changed)

• An alert occurs if TLS/SSL is not configured. To confirm TLS/SSL configuration see column PURPOSE of the PSES System View.

  PSES System View

• The SQL statement ALTER SYSTEM RELOAD FILE PSE enforces changes to PSE stores in the file system without requiring a database restart.

  ALTER SYSTEM RELOAD FILE PSE Statement (System Management)

• TLS1.3 is expected to be rolled out during the lifetime of SAP HANA Platform 2.0 SPS 07. To allow for this rollout, the default for sslMaxProtocolVersion has been changed to TLS12.

  Communication Configuration Properties for LDAP

  Server-Side TLS/SSL Configuration Properties for External Communication (JDBC/ODBC)

Encryption Configuration and Encryption Key Management (New and Changed)

• A round off of SQL statements and hdbnsutil commands relevant for securing and recovering SAP HANA root keys has been done.

Certificate Management (New and Changed)

• You can now trigger a reread of file system-based PSEs without restarting your SAP HANA database.

SAP HANA Server Installation and Update new capabilities and enhancements, introduced with SAP HANA Platform 2.0 SPS 07 #HANA Database

 SAP HANA Platform 2.0 SPS 07 introduces new and changed features. in this blog, I tried to record these changes with the help of SAP Documents. 

SAP HANA Server Installation and Update (New and Changed)

Data and Log Encryption (Changed)

The default value of the parameter volume_encryption has been changed to --volume_encryption (on). This changed setting is applied to new installations, but existing systems are not modified when upgrading.

Backup Encryption (New)

Enable or disable backup encryption. The default for this parameter is --backup_encryption (on). This parameter is available in interactive mode.

Systemd Integration (New)

SAP HANA and SAP Start Service can be integrated as services in the systemdb management. 

For details see SAP Note 3189534 - Linux: systemdb integration for sapstartsrv and SAP HANA.

Local Secure Store (LSS) (Changed)

As of SAP HANA 2.0 Support Package Stack (SPS) 07, SAP HANA Local Secure Store will be installed and activated by default during an SAP HANA system installation if it has been detected as a component by hdblcm. To opt out of Local Secure Store activation during installation, run hdblcm with the parameter secure_store=ssfs or do not select it as a component.

LSS must be activated before it can be used. For more information, see Activate the Local Secure Store (LSS).

SAP HANA Database Security (New and Changed) ---- Next blog

SAP HANA alert :Total Open Transactions Check / Alert ID 48: "Long-running uncommitted write transaction either False or Duration of the Uncommitted Transaction is Inaccurate #HANA Database

When you receive an alert about "Total Open Transactions" or "Long-running uncommitted Transaction' write tra in SAP HANA, it typically indicates too many open transactions, impacting performance and stability. Here’s a step-by-step guide to troubleshoot and resolve this issue:

1. Identify Open Transactions

First, identify which transactions are open:

1. Check System View:
• As the System is up and running fine, we can use HANA Administration tools to find active transactions by using the SQL command given below:

SELECT * FROM "SYS"."M_TRANSACTIONS" WHERE TRANSACTION_STATUS ='ACTIVE';

     2. Transaction Manager:

•  The next step to identify open transactions is to use SAP HANA Studio or HANA Cockpit and navigate to the Transaction Manager to view and analyze open transactions.

2. Analyse the Transactions

Identify the details of the open transactions to understand their impact:

1. Transaction Duration:
• Check how long the transactions have been open. Long-running transactions can be problematic.

SELECT TRANSACTION_ID, CONNECTION_ID, START_TIME, DURATION, USER_NAME, APPLICATION FROM M_TRANSACTIONS WHERE STATE='ACTIVE';

     2.Transaction Type:

• Determine if the transactions are read or write operations. Write operations can cause locks and affect performance.

3. Investigate the Root Cause

Understanding why the transactions are not being closed is crucial:

1. Application Issues:
• Check if there are any application issues causing transactions to remain open. Review the application logic for proper transaction management.
2. Long-Running Queries:
• Identify any long-running queries or processes that might be holding transactions open. Optimize these queries or processes if necessary.
3. Lock Contention:
• Investigate if there is any lock contention that might be causing transactions to stay open. Use the following SQL to check lock waits:

SELECT * FROM M_BLOCKED_TRANSACTIONS.

4. Take Corrective Actions

Based on your findings, take appropriate actions:

1. Terminate Unnecessary Transactions:
• If there are unnecessary transactions, terminate them. This can be done via HANA Studio or SQL:

sql

Copy code

ALTER SYSTEM CANCEL TRANSACTION <TRANSACTION_ID>;

2. Application Fixes:
• Ensure that applications correctly commit or rollback transactions. Review and fix any application code if needed.
3. Query Optimization:
• Optimize any long-running queries or processes. Ensure proper indexing and efficient query design.
4. Configuration Tuning:
• Tune HANA system parameters if necessary. For example, adjust the max_open_transactions parameter if the current limit is too low for your workload.

What are triggers in SAP HANA environments ?

 Triggers in SAP HANA environments are database objects that automatically execute a specified set of SQL statements or logic in response to certain events on a particular table or view. They are used to enforce business rules, maintain data integrity, and automate system processes. Here’s a detailed overview of triggers in SAP HANA:

Example:

CREATE TRIGGER
  "_SYS_STATISTICS"."STATISTICS_SWITCH_PROFILE"
AFTER UPDATE ON
  "_SYS_STATISTICS"."STATISTICS_PROPERTIES"
REFERENCING OLD ROW MYOLDROW, NEW ROW MYNEWROW FOR EACH ROW
begin  
  if :mynewrow.key = 'internal.sizing.profile' then
    if :mynewrow.value != :myoldrow.value then
      call _SYS_STATISTICS.shared_copy_profiles (:myoldrow.value, :mynewrow.value);
    end if;
  end if;
end

 

Types of Triggers in SAP HANA

1. Row-Level Triggers: These triggers execute once for each row affected by the triggering event. They are useful for enforcing row-specific constraints or logging changes.
2. Statement-Level Triggers: These triggers execute once for the entire SQL statement, regardless of how many rows are affected. They are used for actions that apply to the whole statement, such as auditing.

Trigger Events

Triggers can be defined to execute in response to the following events:

1. INSERT: Activates when a new row is inserted into the table.
2. UPDATE: Activates when an existing row is updated.
3. DELETE: Activates when a row is deleted from the table.

Timing of Triggers

Triggers can be specified to execute at different times relative to the triggering event:

1. BEFORE: Executes before the triggering event. Used for validation or modification of data before it is committed.
2. AFTER: Executes after the triggering event. Used for logging, enforcing business rules, or cascading changes to other tables.
3. INSTEAD OF: Executes in place of the triggering event. Commonly used for views to provide custom behavior for insert, update, or delete operations.

This trigger is executed in context of the SAP HANA statistics server (SAP Note 2147247) after updates on table STATISTICS_PROPERTIES in order to make sure that under certain key / value conditions the procedure SHARED_COPY_PROFILES is called in order to perform follow-up actions.

2800020 - FAQ: SAP HANA Triggers

Thanks !

 

How can I interpret the thread state? or What are different state of thread in SAP HANA database ?

 In SAP HANA, threads can be in various states, each indicating a different stage of their lifecycle or activity type. Understanding these thread states is crucial for diagnosing performance issues and optimizing system performance. 

Here are the different thread states in SAP HANA:

Thread state	Area	Details
Barrier Wait	Lock	Barrier wait
ConditionalVariable Wait	Lock	Mainly record and table lock waits, but also some internal locks
ConditionalVar Wait
ExclusiveLock Enter	Lock	Exclusive read / write lock waits
Futex Wait	Lock	Futex lock wait
Inactive	Idle	Idle thread
IntentLock Enter	Lock	Intentional read / write lock waits
IO Wait	I/O	Disk I/O wait (e.g. during column load or hybrid LOB access)
Job Exec Waiting	Idle	Waiting for a JobWorker thread executing the actual work
Joining	Idle	Joining of execution context of another thread, i.e. waiting for the termination of the other thread
Mutex Wait	Lock	Mutex lock waits
Network Connect	Network	Network I/O between SAP HANA nodes or services, timeouts can be configured with client properties like connectTimeout and nodeConnectTimeout (SAP Note 2186744)
Network Poll	Network	Network I/O between SAP HANA nodes or services
Network Read	Network	Network I/O read between SAP HANA nodes or services
Network Write	Network	Network I/O write between SAP HANA nodes or services
Resource Load Wait	I/O	Disk I/O wait (e.g. during undo file load, DataAccess::UndoFileAnchor::allocateFile)
Running	SQL	Request execution, typically CPU consumption
Semaphore Wait	Lock	Semaphore lock waits
SharedLock Enter	Lock	Shared read / write lock waits
Sleeping	Lock	Waiting for a lock
Speculative Lock Retry backoff	Lock	Transactional memory lock (back-off waiting on transaction retry)
Speculative Lock Wait for fallback	Lock	Transactional memory lock state (waiting on fallback lock)
TimestampLatch Wait	Lock	Waiting for a timestamp latch
3rd party Thread	Unknown	State of Native threads created outside of the standard SAP HANA context can't be determined and so they are displayed as '3rd party Thread' (SAP HANA >= 2.00.059.06, >= 2.00.065).
		THREAD_DETAIL may contain related call stack details

                                                                                                                       

Thanks !