[Guide] Most frequently Used Commands For SAP HANA Administrators

To Check Timezone

============================

select * from m_host_information where key=’timezone_name’
CHECKING FULL DB BACKUP
==============================
SELECT ENTRY_TYPE_NAME,STATE_NAME,SYS_END_TIME,SYS_START_TIME from “SYS”.”M_BACKUP_CATALOG” where ENTRY_TYPE_NAME=’complete data backup’ and STATE_NAME=’successful’ order by ‘SYS_END_TIME’ desc

cHECK CURRENT STATUS OF DB BACKUP
=================================
select state_name from “SYS”.”M_BACKUP_CATALOG” where backup_id = &

CHECK LOGBACKUP
===================================
SELECT ENTRY_TYPE_NAME,STATE_NAME,SYS_END_TIME from “SYS”.”M_BACKUP_CATALOG” where ENTRY_TYPE_NAME=’log backup’ order by ‘SYS_END_TIME’ desc
SELECT ENTRY_TYPE_NAME,STATE_NAME,SYS_START_TIME from “SYS”.”M_BACKUP_CATALOG” where ENTRY_TYPE_NAME=’log backup’ and STATE_NAME=’running’

CANCEL RUNNING BACKUP
======================
SELECT BACKUP_ID FROM “M_BACKUP_CATALOG” WHERE ENTRY_TYPE_NAME = ‘complete data backup’ AND STATE_NAME = ‘running’ ORDER BY SYS_START_TIME DESC
BACKUP CANCEL 1438604920493
CALCULATE BACKUP SIZE
======================
select round(sum(allocated_page_size/1024/1024/1024),2) as “backup size in GB” from M_CONVERTER_STATISTICS
select round(sum(v.total_size /1024/1024/1024),2) as “allocated size in GB” from m_volume_files v, m_services s where v.file_type = ‘DATA’ and s.host = v.host and s.port = v.port
select round(sum(allocated_page_size/1024/1024/1024),2) as “backup size in GB” from M_CONVERTER_STATISTICS

SERVICE starting
===============================
sapcontrol -prot NI_HTTP -nr 04 -function StartService AZ2
CHECK LOG MODE
================================
select distinct log_mode from m_log_buffers

License check
========================
select SYSTEM_ID,HARDWARE_KEY,EXPIRATION_DATE,PERMANENT,VALID from m_license
TO CHECK blocked transactions:
===============================
select * from M_BLOCKED_TRANSACTIONS

CONNECTIONS
=============================================================

Find running connections

SELECT “HOST”, “PORT”, “CONNECTION_ID”, “TRANSACTION_ID”, “START_TIME”, “IDLE_TIME”, “CONNECTION_STATUS”, “CLIENT_HOST”, “CLIENT_IP”, “CLIENT_PID”, “USER_NAME”, “CONNECTION_TYPE”, “OWN”, “IS_HISTORY_SAVED”, “MEMORY_SIZE_PER_CONNECTION”, “AUTO_COMMIT”, “LAST_ACTION”, “CURRENT_STATEMENT_ID”, “CURRENT_OPERATOR_NAME”, “FETCHED_RECORD_COUNT”, “AFFECTED_RECORD_COUNT”, “SENT_MESSAGE_SIZE”, “SENT_MESSAGE_COUNT”, “RECEIVED_MESSAGE_SIZE”, “RECEIVED_MESSAGE_COUNT”, “CREATOR_THREAD_ID”, “CREATED_BY”, “IS_ENCRYPTED”, “END_TIME”, “PARENT_CONNECTION_ID”, “CLIENT_DISTRIBUTION_MODE”, “LOGICAL_CONNECTION_ID”, “CURRENT_SCHEMA_NAME”, “CURRENT_THREAD_ID”
FROM “PUBLIC”.”M_CONNECTIONS”
WHERE CONNECTION_STATUS = ‘RUNNING’
ORDER BY “START_TIME” DESC

Resetting Connections
=============================================================

Find the connection

SELECT CONNECTION_ID, IDLE_TIME

FROM M_CONNECTIONS

WHERE CONNECTION_STATUS = ‘IDLE’ AND CONNECTION_TYPE = ‘Remote’

ORDER BY IDLE_TIME DESC
Disconnect Session
=============================================================

ALTER SYSTEM DISCONNECT SESSION ‘203927’;

ALTER SYSTEM CANCEL SESSION ‘237048’;
Find owners of objects

SELECT * FROM “PUBLIC”.”OWNERSHIP” WHERE SCHEMA=’SCHEMA’

Find Granted Privileges for Users
=============================================================

SELECT * FROM PUBLIC.GRANTED_PRIVILEGES

WHERE GRANTEE_TYPE = ‘USER’ AND GRANTOR = ‘NAIRV’
PASSWORD Policy

Disable password policy on a user, this is used when you don’t want the policy to be applied on a user. This will set to lifetime.
=============================================================

ALTER USER USER DISABLE PASSWORD LIFETIME

SYSTEM REPLICATION
==================================
1. from OS end
hdbcons -e hdbindexserver “replication info” ##Mainly check „ReplicationStatus_Active“
hdbnsutil -sr_state

2. from DB
select REPLICATION_STATUS from M_SERVICE_REPLICATION

TO ENABLE\DISABLE FULL SYNC OPTION
========================================
hdbnsutil -sr_fullsync –enable|–disable
It changes the setting of the global.ini file accordingly:
global.ini/[system_replication]/enable_full_sync
Change replication mode
=================================

The replication mode can be changed without having to go through a full data shipping from the primary to the secondary afterwards.
Procedure on Secondary:
„X Stop secondary
„X Run: hdbnsutil -sr_changemode –mode=sync|syncmem|async
„X Start secondary
Searching a file in OS level
=============================
find / -name “hdbrename” -print
To check xs engine working
====================================
https://hostabcd.rot.pec.sap.biz:43<instance no>
To Re-Start SAP HOST AGENT
=======================================
1)Restart saphostagent as root user(Should not be used if two systems running on a same host )
======================================================================================================

/etc/init.d/sapinit stop

/etc/init.d/sapinit start
2)Restart sapstartsrv as sidadm
Get sapstartsrv command, look for sapstartsrv process and copy it.

ps -ef | grep <sidadm>

Stop sapstartsrv

sapcontrol -nr <InstanceNumber> -function StopService

Execute sapstartsrv command

/usr/sap/H61/HDB06/exe/sapstartsrv pf=/usr/sap/H31/SYS/profile/H31_HDB06_vfsh3ha1db01 -D -u h61adm
•Make sure you can stop start database as sidadm.

START AND STOP Stand-Alone HANA DB
=======================================

HDB stop

HDB start

START AND STOP Multi-node
===========================================

sapcontrol -nr <InstanceNumber> -function StopSystem ALL HDB

sapcontrol -nr <InstanceNumber> -function StartSystem ALL HDB

Query the current status of all hosts
=========================================
/usr/sap/hostctrl/exe/sapcontrol -nr &-function GetSystemInstanceList

Install Permanent License Using SQL
======================================
Execute the following SQL command:

SET SYSTEM LICENSE ‘&’

You can delete all installed license keys by executing the following SQL command

UNSET SYSTEM LICENSE ALL

To Install the license key

SET SYSTEM LICENSE LicenseFile

Total Memory Used
=========================================================================

SELECT round(sum(TOTAL_MEMORY_USED_SIZE/1024/1024)) AS “Total Used MB”
FROM SYS.M_SERVICE_MEMORY;
Code and Stack Size
============================================================

SELECT round(sum(CODE_SIZESTACK_SIZE)/1024/1024) AS “Codestack MB”
FROM SYS.M_SERVICE_MEMORY;

Total Memory Consumption of All Columnar Tables
=====================================================================
SELECT round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS “Column Tables MB”
FROM M_CS_TABLES;

Total Memory Consumption of All Row Tables
=============================================================

SELECT round(sum(USED_FIXED_PART_SIZE +
USED_VARIABLE_PART_SIZE)/1024/1024) AS “Row Tables MB”
FROM M_RS_TABLES;

Total Memory Consumption of All Columnar Tables by Schema
=============================================================

SELECT SCHEMA_NAME AS “Schema”,
round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS “MB”
FROM
M_CS_TABLES
GROUP BY SCHEMA_NAME
ORDER BY “MB” DESC;

List All Columnar Tables of Schema ‘SYSTEM’
=============================================================
SELECT TABLE_NAME AS “Table”,
round(MEMORY_SIZE_IN_TOTAL/1024/1024, 2) as “MB”
FROM M_CS_TABLES
WHERE SCHEMA_NAME = ‘SYSTEM’
ORDER BY “MB” DESC;

Available Physical Memory
=============================================================

select round((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY) /1024/1024/1024, 2) as “Physical Memory GB”
from PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Execute the Linux command cat /proc/meminfo | grep MemTotal

Free Physical Memory
=============================================================

Execute the SQL query:

select round(FREE_PHYSICAL_MEMORY/1024/1024/1024, 2) as “Free Physical GB”
from PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Execute the Linux command:

awk ‘BEGIN {sum = 0};

/^(MemFree|Buffers|Cached):/ {sum = sum + $2};

END {print sum}’ /proc/meminfo
Display memory usage by components
=============================================================
SELECT host, component,
sum(used_memory_size) used_mem_size
FROM PUBLIC.M_SERVICE_COMPONENT_MEMORY
group by host, component
ORDER BY sum(used_memory_size) desc;

Database resident
=============================================================
Resident memory is the physical memory actually in operational use by a process.

SELECT SUM(PHYSICAL_MEMORY_SIZE/1024/1024/1024) “Database Resident”
FROM M_SERVICE_MEMORY;

Find the total resident on each node and physical memory size
=============================================================
SELECT HOST,
ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024, 2) AS “Resident GB”,
ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, 2) AS “Physical Memory GB”
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Find total Resident
=============================================================

SELECT T1.HOST,
(T1.USED_PHYSICAL_MEMORY + T2.SHARED_MEMORY_ALLOCATED_SIZE)/1024/1024/1024 “Total Resident”
FROM M_HOST_RESOURCE_UTILIZATION AS T1
JOIN
(SELECT M_SERVICE_MEMORY.HOST,
SUM(M_SERVICE_MEMORY.SHARED_MEMORY_ALLOCATED_SIZE)
AS SHARED_MEMORY_ALLOCATED_SIZE
FROM SYS.M_SERVICE_MEMORY
GROUP BY M_SERVICE_MEMORY.HOST) AS T2
ON T2.HOST = T1.HOST;

Maximum peak used memory
=============================================================

SAP HANA database tracks the highest-ever value of Used Memory reached since the database was started. In fact, this is probably the single most significant memory indicator that you should monitor as an overall indicator of the total amount of memory required to operate the SAP HANA database over a long period of time.

SELECT
ROUND(SUM(“M”)/1024/1024/1024, 2) as “Max Peak Used Memory GB”
FROM
(SELECT
SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS “M”
FROM SYS.M_SERVICE_MEMORY
UNION
SELECT
SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS “M”
FROM M_HEAP_MEMORY
WHERE DEPTH = 0);
Peak used memory
=============================================================

SAP HANA maintains a special Used Memory indicator, called the Peak Used Memory.
This is useful to keep track of the peak value (the maximum, or “high water mark”) of Used Memory over time. Here is how to read the Peak Used Memory:

SELECT ROUND(SUM(“M”)/1024/1024/1024, 2) as “Peak Used Memory GB”
FROM
(SELECT SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS “M”
FROM SYS.M_SERVICE_MEMORY
UNION
SELECT SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS “M”
FROM M_HEAP_MEMORY_RESET
WHERE DEPTH = 0);

Memory usage in server
=============================================================

free –g | awk ‘/Mem:/ {print “Physical Memory: ” $2 ” GB.”} /cache:/ {print “Resident: ” $3 ” GB.”}’
To see what tables are loaded to memory at any given time;
=============================================================

If a report is running slow see if the table is loaded to memory though the tables goes on lazy loading it is a best practice to have the table loaded to memory.

SELECTLOADED,
TABLE_NAME,
RECORD_COUNT,
RAW_RECORD_COUNT_IN_DELTA ,
MEMORY_SIZE_IN_TOTAL,
MEMORY_SIZE_IN_MAIN,
MEMORY_SIZE_IN_DELTA
from M_CS_TABLES
where schema_name = ‘SCHEMA’
order by RAW_RECORD_COUNT_IN_DELTA Desc

To drill down further and see what columns is not loaded /loaded please use below
=============================================================

Select top 100 LOADED,
HOST,
TABLE_NAME,
COLUMN_NAME,
MEMORY_SIZE_IN_TOTAL
from PUBLIC.M_CS_COLUMNS
WHERE SCHEMA_NAME = ‘SCHEMA’
AND LOADED <> ‘TRUE’

MERGE DELTA
=============================================================
See if there is delta to be merged. RAW_RECORD_COUNT_IN_DELTA will provide the delta count.

SELECT LOADED,
TABLE_NAME,
RECORD_COUNT,
RAW_RECORD_COUNT_IN_DELTA ,
MEMORY_SIZE_IN_TOTAL,
MEMORY_SIZE_IN_MAIN,
MEMORY_SIZE_IN_DELTA
from M_CS_TABLES
where schema_name = ‘SCHEMA’
order by RAW_RECORD_COUNT_IN_DELTA Desc

Forcing delta Merge
=============================================================

UPDATE SCHEMA.COLUMN_STATISTICS MERGE DELTA INDEX;

Smart merge
=============================================================

UPDATE <table_name> MERGE DELTA INDEX WITH PARAMETERS (‘SMART_MERGE’=’ON’)
Find Auto Merge On
=============================================================

select TABLE_NAME, AUTO_MERGE_ON from SYS.TABLES

Find Compression
=============================================================

When used:

To see the uncompressed size and the compression ratio in HANA for the loaded tables.

SELECT top 100 “SCHEMA_NAME”,
sum(“DISTINCT_COUNT”) RECORD_COUNT,
sum(“MEMORY_SIZE_IN_TOTAL”) COMPRESSED_SIZE,
sum(“UNCOMPRESSED_SIZE”) UNCOMPRESSED_SIZE,
(sum(“UNCOMPRESSED_SIZE”)/sum(“MEMORY_SIZE_IN_TOTAL”)) as COMPRESSION_RATIO,
100*(sum(“UNCOMPRESSED_SIZE”)/sum(“MEMORY_SIZE_IN_TOTAL”)) as COMPRESSION_PERCENTAGE
FROM “SYS”.”M_CS_ALL_COLUMNS”
GROUP BY “SCHEMA_NAME”
having sum(“UNCOMPRESSED_SIZE”) >0
ORDER BY UNCOMPRESSED_SIZE DESC ;

To go on a detail level and identify what type of compression is applied on each column and the ratio please use below

select
COLUMN_NAME,
LOADED,
COMPRESSION_TYPE,
MEMORY_SIZE_IN_TOTAL,
UNCOMPRESSED_SIZE,
COMPRESSION_RATIO_IN_PERCENTAGE as COMPRESSION_FACTOR
from M_CS_COLUMNS
where schema_name = ‘SCHEMA’

Forcing compression on a table

update SCHEMA.COLUMN_STATISTICS with parameters (‘OPTIMIZE_COMPRESSION’ = ‘TRUE’);
Find which node is active

SELECT
HOST,
PORT,
CONNECTION_ID
FROM M_CONNECTIONS
WHERE OWN = ‘TRUE’;

Expensive Statements

=============================================================

Ensure the expensive statement trace is ON

When used:

To troubleshoot a report failure or a sql failure and understand why it failed.

Also to monitor the expensive sqls executed in HANA. Identify the ways for performance optimization.
Find expensive statements for errors

SELECT
“HOST”,
“PORT”,
“CONNECTION_ID”,
“TRANSACTION_ID”,
“STATEMENT_ID”,
“DB_USER”,
“APP_USER”,
“START_TIME”,
“DURATION_MICROSEC”,
“OBJECT_NAME”,
“OPERATION”,
“RECORDS”,
“STATEMENT_STRING”,
“PARAMETERS”,
“ERROR_CODE”,
“ERROR_TEXT”,
“LOCK_WAIT_COUNT”,
“LOCK_WAIT_DURATION”,
“ALLOC_MEM_SIZE_ROWSTORE”,
“ALLOC_MEM_SIZE_COLSTORE”,
“MEMORY_SIZE”,
“REUSED_MEMORY_SIZE”,
“CPU_TIME”
FROM “PUBLIC”.”M_EXPENSIVE_STATEMENTS”
WHERE ERROR_CODE > 0
ORDER BY START_TIME DESC;

Finding expensive statements executed by User

SELECT
“HOST”,

“PORT”,

“CONNECTION_ID”,

“TRANSACTION_ID”,

“STATEMENT_ID”,

“DB_USER”,

“APP_USER”,

“START_TIME”,

“DURATION_MICROSEC”,

“OBJECT_NAME”,

“OPERATION”,

“RECORDS”,

“STATEMENT_STRING”,

“PARAMETERS”,

“ERROR_CODE”,

“ERROR_TEXT”,

“LOCK_WAIT_COUNT”,

“LOCK_WAIT_DURATION”,

“ALLOC_MEM_SIZE_ROWSTORE”,

“ALLOC_MEM_SIZE_COLSTORE”,

“MEMORY_SIZE”,

“REUSED_MEMORY_SIZE”,

“CPU_TIME”

FROM “PUBLIC”.”M_EXPENSIVE_STATEMENTS”

WHERE STATEMENT_STRING LIKE ‘%NAIRV%’

Enable global auditing
=============================================================

alter system alter configuration (‘global.ini’,

‘SYSTEM’)

set (‘auditingconfiguration’,

‘global_auditing_state’ ) = ‘true’ with reconfigure;

Set the auditing file type

alter system alter configuration (‘global.ini’,’SYSTEM’)

set (‘auditingconfiguration’

,’default_audit_trail_type’ ) = ‘CSVTEXTFILE’

with reconfigure;

aduit target path

alter system alter configuration (‘global.ini’,’SYSTEM’)

set (‘auditingconfiguration’

,’default_audit_trail_path’ ) = ‘path’

with reconfigure;
Find the policy implemented

Select * from public.audit_policies;

To enable/ disable global auditing

— change the configuration for setting the audit

alter system alter configuration (‘global.ini’,

‘SYSTEM’)

set (‘auditingconfiguration’,

‘global_auditing_state’ ) = ‘true’ with reconfigure;

Add audit policy

CREATE AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 AUDITING SUCCESSFUL DROP TABLE LEVEL CRITICAL;
Policy enable/disable

ALTER AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 ENABLE;

Location of Configuration Files
======================================================
The configuration files (.ini files) are located by default in the following directories

$DIR_INSTANCE/../SYS/global/hdb

custom/config global.ini

indexserver.ini

nameserver.ini

For host-specific configuration settings

$SAP_RETRIEVAL_PATH

sapprofile.ini

daemon.ini

Trace Files Location
==================================================
trace files can be found here:

/usr/sap/hdb<Instance Number>/trace

Leave a Reply