SAP HANA – SQL System Management Statements

Hello Everyone,

In this article, we are going to look at different SQL System Management Statements (SMMS) available in SAP HANA along with their syntax and examples.

 

Different statements available in SAP HANA are,

ALTER SYSTEM ALTER CONFIGURATION
ALTER SYSTEM ALTER SESSION SET
ALTER SYSTEM ALTER SESSION UNSET
ALTER SYSTEM APPLICATION ENCRYPTION
ALTER SYSTEM ALTER TABLE PLACEMENT
ALTER SYSTEM CANCEL [WORK IN] SESSION
ALTER SYSTEM CLEAR AUDIT LOG
ALTER SYSTEM CLEAR SQL PLAN CACHE
ALTER SYSTEM CLEAR TRACES
ALTER SYSTEM DISABLE ALL ASYNCHRONOUS TABLE REPLICAS
ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM ENABLE ALL ASYNCHRONOUS TABLE REPLICAS
ALTER SYSTEM LOAD PERFTRACE
ALTER SYSTEM LOGGING
ALTER SYSTEM PERSISTENCE ENCRYPTION
ALTER SYSTEM RECLAIM DATA SPACE
ALTER SYSTEM RECLAIM DATAVOLUME
ALTER SYSTEM RECLAIM LOG
ALTER SYSTEM RECLAIM VERSION SPACE
ALTER SYSTEM RECONFIGURE SERVICE
ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY
ALTER SYSTEM REMOVE TRACES
ALTER SYSTEM RESET MONITORING VIEW
ALTER SYSTEM SAVE PERFTRACE
ALTER SYSTEM SAVEPOINT
ALTER SYSTEM START PERFTRACE
ALTER SYSTEM STOP PERFTRACE
ALTER SYSTEM STOP SERVICE
CREATE CERTIFICATE
DROP CERTIFICATE
SET SYSTEM LICENSE
UNSET SYSTEM LICENSE ALL
ALTER PSE
CREATE PSE
DROP PSE
SET PSE
UNSET PSE

Let’s look in detail about each statement below.

ALTER SYSTEM ALTER CONFIGURATION

ALTER SYSTEM ALTER CONFIGURATION:

  • Sets or removes configuration parameters in an ini file. Ini file configuration is used for the layered configuration of DEFAULT, SYSTEM, HOST layers.
The DEFAULT layer configuration parameters cannot be changed or removed using this command.
  • The following is an example of ini file locations:
    • DEFAULT: /usr/sap/<SYSTEMNAME>/HDB<INSTANCENUMBER>/exe/config/indexserver.ini
    • SYSTEM: /usr/sap/<SYSTEMNAME>/SYS/global/hdb/custom/config/indexserver.ini
    • HOST: /usr/sap/<SYSTEMNAME>/HDB<INSTANCENUMBER>/<HOSTNAME>/indexserver.ini
  • The priority of the configuration layers is as follows: DEFAULT < SYSTEM < HOST.
  • Database-specific configuration files are located in the directory /hana/shared/$SID/global/hdb/custom/config/DB_<dbname>.
  • This means that the layer that has the highest priority is the HOST layer, followed by the SYSTEM layer and finally the DEFAULT layer. The configuration with the highest priority will be applied to the running environment. If the highest priority level configuration is removed, then the configuration with the next highest priority will be applied.
The below views in SYS schema give information about ini files in SAP HANA system.

M_INIFILES : Currently available ini files.
M_INIFILE_CONTENTS : Current ini file values that the SAP HANA database is using.

Syntax: ALTER SYSTEM ALTER CONFIGURATION (<filename>, <layer>[, <layer_name>])
   {SET | UNSET} <parameter_key_value_list> [WITH RECONFIGURE]

Below are the options we have while using ALTER SYSTEM statement in SAP HANA.

File_Name:

  • The filename of the configuration file to be modified. If the file does not exist on the required layer, the file will be created when a SET command is used.

Layer:

  • Sets the target layer for the configuration change.
  • This parameter can be either ‘SYSTEM’, ‘HOST’ or ‘DATABASE’.
  • The SYSTEM layer is the recommended layer for customer settings.
  • The HOST layer should generally only be used for minor configuration, for example parameters contained in daemon.ini.
  • In multiple-container systems, system configuration files have an additional layer DATABASE to facilitate the configuration of properties for individual databases.

Layer_Name:

  • If the layer parameter above is set to ‘HOST’, <layer_name> is used to target either a tenant name or a target host name. For example, ‘selxeon12’ would target the ‘selxeon12’ host.
The ‘HOST’ value must be provided in lowercase only.

SET:

  • Updates the value of a key if the key already exists, or inserts a new key if required.

UNSET:

  • Removes a key and its associated value.

parameter_key_value_list:

  • A list of configuration file entries to be modified or removed.
  • <parameter_key_value_entry> ::= (<section_name>,<parameter_name>) [ = <parameter_value>]

section_name:

  • The section name of the parameter to be modified.

parameter_name:

  • The name of the parameter to be modified.

parameter_value:

  • The value of the parameter.

WITH RECONFIGURE:

  • Specifies that the configuration changes will be directly applied to the running SAP HANA database instance.
  • When WITH RECONFIGURE is not specified the configuration changes will be written to the required ini file, however the modified values will not applied to the current running system.
  • The changes will only be applied during a restart of the SAP HANA database or a subsequent configuration change with WITH RECONFIGURE. In this case there can be inconsistencies between the ini file contents and the actual configuration value that the SAP HANA database is currently using.

Examples:

  1. Run the below statement to set a parameter new_test_value in the alt_sys_test section of the global.ini file.

Ans: ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘alt_sys_test’, ‘new_test_value’) = ‘test’;

  1. Run the below statement to unset the new_test_value parameter set in the previous step.

Ans: ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) UNSET (‘alt_sys_test’, ‘new_test_value’);

ALTER SYSTEM ALTER SESSION SET

ALTER SYSTEM ALTER SESSION SET:

  • This statement is used to set session variable for database sessions.
  • We can check the list of predefined variables in view M_SESSION_CONTEXT under SYS schema.
  • Session variables can be retrieved using SQL function ‘SESSION_CONTEXT’ command or from M_SESSION_CONTEXT view in SYS schema and can be unset using the ALTER SYSTEM ALTER SESSION UNSET statement.

Syntax: ALTER SYSTEM ALTER SESSION <session_id> SET <key> = <value>

session_id:

  • The session ID of the session where the variable should be set.

 key:

  • The key of a session variable. The maximum length of key is 32 characters.

 value:

  • The desired value of a session variable. The maximum length of value is 512 characters.

Examples:

  1. Run the below statement to get our own session id from M_CONNECTIONS view.

Ans: SELECT CONNECTION_ID FROM SYS.M_CONNECTIONS WHERE OWN = ‘TRUE’;

  1. Set the variable ‘SAP_STUDENT’ value to ‘WEBSITE’ for the above session id.

Ans: ALTER SYSTEM ALTER SESSION 205926 SET ‘SAP_STUDENT’= ‘WEBSITE’;

  1. Run the below statement to check the variable ‘SAP_STUDENT’ value using SESSION_CONTEXT function.

Ans: SELECT SESSION_CONTEXT(‘SAP_STUDENT’) FROM DUMMY;

Check Also

Capture

SAP HANA – SQL Session Management Statements

Hello Everyone, In this article, we are going to look at different SQL Session Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

6 − five =