[Guide] SAP HANA -SQL STATEMENTS REFERENCE TUTORIAL

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;

ALTER SYSTEM ALTER SESSION UNSET

ALTER SYSTEM ALTER SESSION UNSET:

  • This statement is used to unset 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.

Syntax: ALTER SYSTEM ALTER SESSION <session_id> UNSET <key>

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.

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 unset the session variable ‘SAP_STUDENT’ to NULL.

Ans: ALTER SYSTEM ALTER SESSION 205926 UNSET ‘SAP_STUDENT’;

ALTER SYSTEM APPLICATION ENCRYPTION

ALTER SYSTEM APPLICATION ENCRYPTION:

  • This statement is used to manage encryption keys for applications using the internal data encryption service.
  • This statement forces the creation of a new random encryption key for every application. Applications, in this sense, are all consumers of the internal data encryption service. For example these can be both internal components, like the secure internal credential store, or XS applications.
  • The new keys will be stored encrypted with the current root key of the internal data encryption service. The new keys will begin to be used after the transaction is committed. No changes will be made to data that has already been written to disk.
  • We should use this option if the security policies of company dictate that we should change the encryption keys being used to store our data, or if instructed to do so by SAP Support.

Syntax: ALTER SYSTEM APLLICATION ENCRYPTION CREATE NEW KEY

Example:

  1. Run the below statement to create new encryption key in SAP HANA.

Ans: ALTER SYSTEM APLLICATION ENCRYPTION CREATE NEW KEY

ALTER SYSTEM ALTER TABLE PLACEMENT

ALTER SYSTEM ALTER TABLE PLACEMENT:

  • This statement is used to change table placement properties such as MIN_ROWS_FOR_PARTITIONING, INITIAL_PARTITIONS, REPARTITIONING_THRESHOLD, and LOCATION.

Syntax: ALTER SYSTEM ALTER TABLE PLACEMENT <table_group_key> => <value> (SET/UNSET) <placement_property_key> => <value>

Table group:

  • The table group key can be any of the following.
    • SCHEMA_NAME
    • TABLE_NAME
    • GROUP_NAME
    • GROUP_TYPE
    • SUB_TYPE.

Placement property:

  • The placement property key can be any of the following.
    • MIN_ROWS_FOR_PARTITION
    • INITIAL_PARTITIONS
    • REPARTITIONING_THRESHOLD
    • LOCATION.
Current table placement settings can be found in view M_EFFECTIVE_TABLE_PLACEMENT under SYS schema.

Examples:

  1. Run the below statement to keep all the tables in slave server.

Ans: ALTER SYSTEM ALTER TABLE PLACEMENT SET (location => ‘slave’)

  1. Run the below statement to store all the table from SAP_STUDENT schema in master server.

Ans: ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => ‘SAP_STUDENT’)SET (LOCATION => ‘master’)

  1. Run the below statement to define all the properties for schema SAP_STUDENT.

Ans: ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => ‘SAP_STUDENT’)SET (LOCATION => ‘slave’, MIN_ROWS_FOR_PARTITION => 40000000, REPARTITIONING_THRESHOLD => 40000000, INITIAL_PARTITIONS => 3)

  1. Run the below statement to remove initial partitions setting.

Ans: ALTER SYSTEM ALTER TABLE PLACEMENT (SCHEMA_NAME => ‘SAP_STUDENT’) UNSET (INITIAL_PARTITIONS)

ALTER SYSTEM CANCEL WORK IN SESSION

ALTER SYSTEM CANCEL [WORK IN] SESSION:

  • This statement is used to cancel the currently executing statement of a session.
  • The transaction of the cancelled session will be rolled back.
  • The statement that was executing will return error code 139 (current operation cancelled by request and transaction rolled back).

Syntax: ALTER SYSTEM CANCEL [WORK IN] SESSION <session_id>

Examples:

  1. Run the below query to get the list of current database connection id’s and the statements that are running by connection id’s.

Ans:
SELECT C.CONNECTION_ID, PS.STATEMENT_STRING
FROM M_CONNECTIONS C
JOIN M_PREPARED_STATEMENTS PS ON C.CONNECTION_ID = PS.CONNECTION_ID
AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID
WHERE C.CONNECTION_STATUS = ‘RUNNING’ AND C.CONNECTION_TYPE = ‘Remote’

  1. Execute the below statement to cancel any connection based on the id’s obtained from above query.

Ans:
ALTER SYSTEM CANCEL SESSION ‘<connection_id>’

ALTER SYSTEM CLEAR AUDIT LOG

ALTER SYSTEM CLEAR AUDIT LOG:

  • This statement is used to delete old audit data from the SAP HANA database audit table.
The details about audit log in SAP HANA can be found in view AUDIT_LOG under SYS schema.

Syntax: ALTER SYSTEM CLEAR AUDIT LOG UNTIL <timestamp>

Example:

  1. Clear the audit log in SAP HANA until 2015 October 31st.

Ans: ALTER SYSTEM CLEAR AUDIT LOG UNTIL ‘2015-10-31 23:59:59’

ALTER SYSTEM CLEAR SQL PLAN CACHE

ALTER SYSTEM CLEAR SQL PLAN CACHE:

  • The ALTER SYSTEM CLEAR SQL PLAN CACHE statement removes all of the SQL plans, which are not currently being executed, from the SAP HANA database plan cache.
  • It also removes all plans having reference count of ‘0’ from the plan cache and resets all the statistics of the remaining plans.
  • The command also reset the contents of M_SQL_PLAN_CACHE_OVERVIEW monitoring view.

What is SQL PLAN CACHE?

  • The SQL PLAN CACHE stores plans generated by previous SQL statement executions. The plan cache is used by the SAP HANA database to speed up query execution if the same SQL statement is executed multiple times. The plan cache also collects some statistics regarding plan preparation and execution.

Syntax: ALTER SYSTEM CLEAR SQL PLAN CACHE

Details about SQL PLAN CACHE can be found in below view under SYS schema.M_SQL_PLAN_CACHE: Execution plan statistics.
M_SQL_PLAN_CACHE_OVERVIEW: Overall SQL plan cache statistics.

Example:

  1. Run the below statement to clear SQL PLAN CACHE in SAP HANA.

Ans: ALTER SYSTEM CLEAR SQL PLAN CACHE

ALTER SYSTEM CLEAR TRACES

ALTER SYSTEM CLEAR TRACES:

  • The ALTER SYSTEM CLEAR TRACES statement is used to clear trace contents from trace files.
  • When we use this command all trace files that were opened by the SAP HANA database will be removed or cleared. On distributed systems, this command will clear all trace files on all hosts. If WITH BACKUP is specified, trace files will be compressed and saved instead of removed.
  • We use this command to reduce disk space used by large trace files for example, when trace components are set to INFO or DEBUG.

Syntax: ALTER SYSTEM CLEAR TRACES <trace_type> [WITH BACKUP]

Trace Type:

  • We can clear specific traces from SAP HANA be specific type.
  • Below are the types that can be used while clearing traces.

1

All the information about traces in SAP HANA can be found in below views under SYS schema.M_TRACEFILES : All trace files.
M_TRACEFILE_CONTENTS : Trace file content.

Examples:

  1. Run the below statement to clear ALERT and CLIENT traces from SAP HANA.

Ans: ALTER SYSTEM CLEAR TRACES(‘ALERT’, ‘CLIENT’)

  1. Run the below statement to clear ALERT and CLIENT traces with backup in SAP HANA.

Ans: ALTER SYSTEM CLEAR TRACES(‘ALERT’, ‘CLIENT’) WITH BACKUP

ALTER SYSTEM DISABLE ALL ASYNCHRONOUS TABLE REPLICAS

ALTER SYSTEM DISABLE ALL ASYNCHRONOUS TABLE REPLICAS:

  • This statement deactivates the overall replication operation of all replication tables.

Syntax: ALTER SYSTEM DISABLE ALL ASYNCHRONOUS TABLE REPLICAS

  • This is a straight forward statement and we don’t have options while executing it.
  • Both syntax and example is same.

Example:

  1. Run the below statement to deactivate the table replications.

Ans: ALTER SYSTEM DISABLE ALL ASYNCHRONOUS TABLE REPLICAS

ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM DISCONNECT SESSION:

  • The ALTER SYSTEM DISCONNECT SESSION statement is used to disconnect a specified session from the database.
  • Before disconnection any currently running operations associated with the session will be terminated.

Syntax: ALTER SYSTEM DISCONNECT SESSION <session_id>

Examples:

  1. Run the below statement to get the session id’s of idle sessions.

Ans:
SELECT CONNECTION_ID, IDLE_TIME FROM M_CONNECTIONS
WHERE CONNECTION_STATUS = ‘IDLE’ AND CONNECTION_TYPE = ‘Remote’
ORDER BY IDLE_TIME DESC

  1. Execute below statement to disconnect those sessions.

Ans: ALTER SYSTEM DISCONNECT SESSION ‘<connection_id>’

ALTER SYSTEM ENABLE ALL ASYNCHRONOUS TABLE REPLICAS

ALTER SYSTEM ENABLE ALL ASYNCHRONOUS TABLE REPLICAS:

  • This statement activates the overall replication operation of all replication tables.

Syntax: ALTER SYSTEM ENABLE ALL ASYNCHRONOUS TABLE REPLICAS

  • This is a straight forward statement and we don’t have options while executing it.
  • Both syntax and example is same.

Example:

  1. Run the below statement to activate the table replications.

Ans: ALTER SYSTEM ENABLE ALL ASYNCHRONOUS TABLE REPLICAS

ALTER SYSTEM LOAD PERFTRACE

ALTER SYSTEM LOAD PERFTRACE:

  • This command is used to convert a .tpt file into tables.
  • Tables beginning with <table_prefix>_PERFTRACE_… are created and filled with the content from the .tpt file.
  • The .tpt file will be loaded from the trace directory of our SAP HANA database instance. If we do not specify a file name then ‘perftrace.tpt’ will be used.

Syntax: ALTER SYSTEM LOAD PERFTRACE FILE <file_name> INTO TABLES <schema_name>.<table_prefix> WITH REPLACE

WITH REPLACE:

  • When specified previously existing tables are removed.
  • If the REPLACE option is not specified, an error will be thrown if tables with the specified prefix already exist.

Examples:

  1. Run the below statement to move mytrace.tpt file into SAP_STUDENT.TRACEFILES_* tables.

Ans: ALTER SYSTEM LOAD PERFTRACE FILE ‘mytrace.tpt’ INTO TABLES SAP_STUDENT.TRACEFILES WITH REPLACE

System created below three tables.

SAP_STUDENT.TRACEFILES_PERFTRACE_INFO
SAP_STUDENT.TRACEFILES_PERFTRACE_SERVICES
SAP_STUDENT.TRACEFILES_PERFTRACE_CALLS

ALTER SYSTEM LOGGING

ALTER SYSTEM LOGGING:

  • This statement is used to Enable or disable logging.
  • While logging is disabled no log entries will be persisted, only the data area will be written when a savepoint is reached. This can cause loss of committed transactions, when the indexserver terminates in the middle of a LOAD operation. In case of a termination, we will have to truncate and insert all data again.
  • After enabling logging we have to perform a savepoint to be sure that all data will be persisted. we will also have to perform a data backup, otherwise we will be unable to recover this data.
  • ALTER SYSTEM LOGGING waits for the end (commit/rollback) of existing active write transactions. During the execution of this command write transactions are blocked. In the event that there is a long-running write transaction, this command can be failed with a lock wait timeout error. Starting a new write transaction also can be blocked and failed with the same error.
  • Due to the potential generation of the errors mentioned above we should only use this command whilst performing a database initial load. If used at other times data loss can occur if the indexserver is terminated whilst logging is disabled. Consequently, in a running system, the logging mode should be always ON and should not be modified.
  • We can also adjust the logging settings for single column tables with ALTER TABLE <table_name> {ENABLE | DISABLE} DELTA LOG, see <delta_log_option>.

Syntax: ALTER SYSTEM LOGGING [ON/OFF]

Examples:

  1. Run the below statement to disable logging.

Ans: ALTER SYSTEM LOGGING OFF

  1. Run the below statement to enable logging.

Ans: ALTER SYSTEM LOGGING ON 

ALTER SYSTEM PERSISTENCE ENCRYPTION

ALTER SYSTEM PERSISTENCE ENCRYPTION:

  • This statement controls if persistent data should be stored on disk in an encrypted or non-encrypted format.
  • We can also use this command to recreate the random encryption key or apply a new key to existing disk data.
  • Currently only the finally written disk data will be encrypted. The redo log is not affected by this command.

Syntax: ALTER SYSTEM PERSISTENCE ENCRYPTION <encrypt_option>

  • We have below options for encrypt.
    • ON: Specifies that encryption should be enabled. When we switch on encryption a random encryption key is prepared and an asynchronous background task is started that encrypts all disk data with this key.
    • OFF: Specifies that encryption should be disabled. When we switch off encryption, an asynchronous background task is started which decrypts all encrypted disk data.

CREATE NEW KEY:

  • Forces the creation of a new random encryption key.
  • The new key will begin to be used after the next savepoint has occurred.
  • The time in which this takes to occur depends upon the current workload of the SAP HANA database. No changes will be made to data that has already been written to disk.

APPLY CURRENT KEY:

  • Force all data using old encryption keys to be decrypted and then re-encrypted using the current encryption key.
  • We may wish to do this if an old key has been compromised or if we want to consolidate a set of old keys.

Example:

  1. Run the below statement begin the persistent encryption in SAP HANA.

Ans: ALTER SYSTEM PERSISTENCE ENCRYPTION ON

ALTER SYSTEM RECLAIM DATA SPACE

ALTER SYSTEM RECLAIM DATA SPACE:

  • This statement will execute row store memory reorganization at runtime.
  • It first inspects the system in order to analyze the current status of row store memory utilization. After that, it calculates how much memory can be reduced.
  • When this inspection is complete, a table list is generated. It then locks the member tables in the table list and executes actual row store memory reorganization.
  • Please note that the table list is determined at runtime and it can affect a number of row tables.

Syntax: ALTER SYSTEM RECLAIM [ROW] DATA SPACE [IMMEDIATE] [<host_name>:<port_number>]

IMMEDIATE:

  • Execute row store reorganization right away. The same for omitted case.

Example:

  1. Execute the below statement to reclaim data space.

Ans: ALTER SYSTEM RECLAIM DATA SPACE

ALTER SYSTEM RECLAIM DATAVOLUME

ALTER SYSTEM RECLAIM DATAVOLUME:

  • This statement is used when unused space inside a SAP HANA database disk persistence should be freed. It reduces data volume size to a percentage of payload size.
  • This command works in a similar way to defragmenting a hard drive. Pages that are scattered around a data volume will be moved to the front of the volume, and the free space at the end of the data volume will be truncated.

Syntax: ALTER SYSTEM RECLAIM DATAVOLUME [SPACE] [<host_name>: <port_number>] <percentage_of_payload_size> <shrink_mode>

Percentage of overload size:

  • The desired percentage of the payload to which the data volume should be reduced. Value must not be smaller than 105. Recommended avalues are between 105 and 120.

Shrink mode:

  • The strategy to be used to reduce the persistency size.
  • We have two options,
    • DEFRAGMENT
    • SPARSIFY
    • DEFRAGEMENT is the default.
SPARSIFY is not supported yet and is reserved for future use.

Example:

  1. Run the below statement to persistence all servers data volume with defragmented and reduced to 120% of the payload size.

Ans: ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT

ALTER SYSTEM RECLAIM LOG

ALTER SYSTEM RECLAIM LOG:

  • This statement is used when the SAP HANA database has accumulated a lot of log segments and we want to reclaim disk space of currently unused log segments.
  • Log segment accumulation can be caused in several ways. For example, when automatic log backup is not operational for a long period or a log savepoint is blocked for an extended time. When such issues occur we can use the ALTER SYSTEM RECLAIM LOG command, but only after the root cause of the log accumulation has been fixed.

Syntax: ALTER SYSTEM RECLAIM LOG

We can see the information about currently allocated log segments in the below view under SYS schema.M_LOG_SEGMENTS : Currently allocated log segments.

Example:

  1. Run the below statement to reclaim the currently unused log segments in SAP HANA.

Ans: ALTER SYSTEM RECLAIM LOG

ALTER SYSTEM RECLAIM VERSION SPACE

ALTER SYSTEM RECLAIM VERSION SPACE:

  • This statement is used to trigger the row-store garbage collector to free up memory space and enhance system responsiveness.
  • We do not need to use this command if only short transactions are being executed on the system. This is because the garbage collector is automatically triggered when a transaction ends. If there are long-running transactions on the system, this command helps overall system performance if the m_mvcc_tables monitoring view shows a large number of row-store versions(e.g. over 1M).

Syntax: ALTER SYSTEM RECLAIM VERSION SPACE

The below is under SYS schema is useful to know the row store versions number in SAP HANA.M_MVCC_TABLES : The number of row-store versions in the system.

Examples:

  1. Run the below statement to know the row store versions in SAP HANA.

Ans: SELECT * FROM SYS.M_MVCC_TABLES

  1. Execute the below statement to reclaim version space in SAP HANA.

Ans: ALTER SYSTEM RECLAIM VERSION SPACE

ALTER SYSTEM RECONFIGURE SERVICE

ALTER SYSTEM RECONFIGURE SERVICE:

  • The statement ALTER SYSTEM RECONFIGURE SERVICE is used to reconfigure a specified service by applying the current configuration parameters.
  • This statement is used after changing multiple configuration parameters with the ALTER CONFIGURATION statement without the RECONFIGURE option set.

 

  1. To reconfigure a specific service specify <hostname> and <port_number> and leave <service_name> empty.
  2. To reconfigure all services of a type, specify <service_name> and leave <hostname> and <port_number> empty.
  3. To reconfigure all services, leave all parameters empty.
  4. To reconfigure all services, leave all parameters empty.

Syntax: ALTER SYSTEM RECONFIGURE SERVICE (<service_name>,<hostname>,<port_number>)

The view M_SERVICE_TYPES under SYS schema gives us a list of available service types available in SAP HANA.

Examples:

  1. Run the below statement to reconfigure all the services on host hana.sapstudent.com with port number 30303

Ans: ALTER SYSTEM RECONFIGURE SERVICE (”,’hana.sapstudent.com’,30303)

  1. Run the below statement to reconfigure all services of service type index server.

Ans: ALTER SYSTEM RECONFIGURE SERVICE (‘indexserver’,”,0)

ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY

ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY:

  • The ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY statement invalidate the designated plan cache entry. When invalidated the entry will be recompiled during the next execution time.
  • We use this command when huge changes have occurred to the data cardinalities acted upon by the query plan.
  • We need the OPTIMIZER ADMIN privilege to run this command.

Syntax: ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY <plan_id>

We get the plan id from M_SQL_PLAN_CACHE view under SYS schema in SAP HANA.

Example:

  1. Run the below statement to recompile SQL PLAN CACHE for plan id = 256.

Ans: ALTER SYSTEM RECOMPILE SQL PLAN CACHE ENTRY 256

ALTER SYSTEM REMOVE TRACES

ALTER SYSTEM REMOVE TRACES:

  • We use this command to delete the trace files on a specified host, thereby reducing disk space used by large trace files.
  • When a service has a trace file open it cannot be deleted. In this case we can clear the trace file using the ALTER SYSTEM CLEAR TRACES command.

Syntax: ALTER SYSTEM REMOVE TRACES (<hostname>, <trace_file_list>)

The list of host names and file name combinations can be retrieved from M_TRACEFILES view under SYS schema in SAP HANA.

Example:

  1. Run the below statement to clear extrace.py and extrace.py.old files from hana.sapstudent host.

Ans: ALTER SYSTEM REMOVE TRACES (‘hana.sapstudent’, ‘extrace.py’, ‘extrace.py.old’)

 

ALTER SYSTEM RESET MONITORING VIEW

ALTER SYSTEM RESET MONITORING VIEW:

  • We use this command to reset statistics data for the specified monitoring view.
  • We use this command to define a starting point for our measurements. First, we reset the monitoring view, then execute an action. After the action is completed, query “_RESET” version of the monitor view to get the statistical information gathered since the last reset.

Syntax: ALTER SYSTEM RESET MONITORING VIEW <view_name>

Not all monitoring views can be reset using this command. We can identify a view as being able to be reset as its view name will have the suffix “_RESET”.

Example:

  1. Run the below statement to reset M_HEAP_MEMORY_RESET monitoring view in SYS schema.

Ans: ALTER SYSTEM RESET MONITORING VIEW “SYS”.”M_HEAP_MEMORY_RESET”

ALTER SYSTEM SAVE PERFTRACE

ALTER SYSTEM SAVE PERFTRACE:

  • We use this command to collect raw performance trace data from .prf files and save the information into a single .tpt file. The .tpt file will be saved in the trace directory of our SAP HANA database instance. If we do not specify a file name then ‘perftrace.tpt’ will be used.
  • The performance trace data (.tpt) file can be downloaded from SAP HANA studio. To obtain the trace files in the SAP HANA Systems view, right click on the system we wish to diagnose. Select Administration from the context menu and in the dialog that appears select the Diagnosis Files tab. Right click on a Trace file and select Download. The (.tpt) file can be loaded into tables with ALTER SYSTEM LOAD PERFTRACE

Syntax: ALTER SYSTEM SAVE PERFTRACE INTO FILE <file_name>

The below views under SYS schema in SAP HANA gives information about saved performance trace file and the jobs currently running.M_PERFTRACE : Status of performance tracing.
M_JOB_PROGRESS : Monitor the saving of a trace file. The save job will be shown as ‘Save PerfTrace’ in the system view.
Saving a perftrace can take some time. We can cancel the job shown in M_JOB_PROGRESS with ALTER SYSTEM CANCEL [WORK IN] SESSION

Example:

  1. Run the below statement to save the performance trace into mytrace.tpt file.

Ans: ALTER SYSTEM SAVE PERFTRACE INTO FILE ‘mytrace.tpt’

ALTER SYSTEM SAVEPOINT

ALTER SYSTEM SAVEPOINT:

  • This statement executes a savepoint on the persistence manager. A savepoint is a point in time when a complete consistent image of the database is persisted to disk. The consistent image can be used to restart the database.
  • Normally a savepoint is executed periodically as configured by the configuration parameter savepoint_interval_s in section [persistence]. For special (normally test) purposes, the savepoint can be disabled. In this case, we can use this command to manually execute a savepoint.

Syntax: ALTER SYSTEM SAVEPOINT

The below view under SYS schema gives statistics about SAVE POINT in SAP HANA.M_SAVEPOINT_STATISTICS : Savepoint statistics.

Example:

  1. Execute the below statement to run SAVE POINT in SAP HANA.

Ans: ALTER SYSTEM SAVEPOINT

ALTER SYSTEM START PERFTRACE

ALTER SYSTEM START PERFTRACE:

  • The ALTER SYSTEM START PERFTRACE command starts performance tracing.
  • Only one perftrace can be active at a time.

Syntax: ALTER SYSTEM START PERFTRACE [<user_name>] [<application_user_name>] [<application_name>] [PASSPORT_TRACELEVEL <passport_level>] [PLAN_EXECUTION] [FUNCTION_PROFILER] [DURATION <duration_seconds>]

The below view in SYS schema gives information about currently active performance trace.M_PERFTRACE : Status of performance tracing.

Example:

  1. Run the below statement to start performance trace in SAP HANA.

Ans: ALTER SYSTEM START PERFTRACE USER SAP_STUDENT APPLICATIONUSER BO_USER PLAN_EXECUTION FUNCTION_PROFILER

ALTER SYSTEM STOP PERFTRACE

ALTER SYSTEM STOP PERFTRACE:

  • Stops an active performance trace. After stopping the trace, we collect and save the performance trace data with ALTER SYSTEM SAVE PERFTRACE.

Syntax: ALTER SYSTEM STOP PERFTRACE

Example:

  1. Run the below statement to stop active performance trace in SAP HANA.

Ans: ALTER SYSTEM STOP PERFTRACE

ALTER SYSTEM STOP SERVICE

ALTER SYSTEM STOP SERVICE:

  • This statement stops single or multiple services on the designated host.
Typically a service stopped will be automatically restarted by the SAP HANA database system.
We should use this command after changing a configuration parameter that cannot be changed whilst the SAP HANA database is running.

Syntax: ALTER SYSTEM STOP SERVICE  <host_port> [IMMEDIATE [WITH COREFILE]]

IMMEDIATE:

  • Immediately stop the service without waiting for regular shutdown.

WITH COREFILE:

  • Write a core dump file.

Example:

  1. Run the below statement to stop the services running on host hana.sapstudent with port 30303.

Ans: ALTER SYSTEM STOP SERVICE ‘hana.sapstudent:30303’

CREATE CERTIFICATE

CREATE CERTIFICATE:

  • This statement adds this certificate to the list of certificates, which can be assigned to a PSE store.
Only users having the system privilege CERTIFICATE ADMIN are allowed to create certificates.

Syntax: CREATE CERTIFICATE FROM <certificate> [ COMMENT <comment> ]

Below view in SYS schema store information about certificates.CERTIFICATES : Certificates usable for assignment to PSE stores.

Example:

  1. Run the below statement to create a certificate with subject and issuer SAP AG and a validity between 2011 and 2285. The certificate is shortened and the missing 21 lines mentioned as ‘…’

Ans:
CREATE CERTIFICATE FROM ‘—–BEGIN CERTIFICATE—–
MIIEVjCCAz6gAwIBAgIJAKZmSWxYxVmGMA0GCSqGSIb3DQEBBQUAMHkxCzAJBgNV

zn2Q+T5Og6ozD1WgUYsegJl3W2gNznEj66Ku1SDDzR0POjCnfK5xLt1WE5KBAIav
1SSbSTsw6rCRdg==
—–END CERTIFICATE—–‘ COMMENT ‘Subject SAP AG Valid until 2285’

DROP CERTIFICATE

DROP CERTIFICATE:

  • The DROP CERTIFICATE statement drops this certificate from the list of certificates, which can be assigned to a PSE store.
  • The statement will succeed only, if the certificate is not assigned to a PSE store.
Only users having the system privilege CERTIFICATE ADMIN are allowed to drop certificates.

Syntax: DROP CERTIFICATE <certificate_id>

Below view in SYS schema store information about certificates. We can also get the certificate id that we would like to delete from below view.CERTIFICATES : Certificates usable for assignment to PSE stores.

Example:

  1. Run the below statement to delete certificate 12345678 from SAP HANA.

Ans: DROP CERTIFICATE 12345678

 

 

Leave a Reply