Sap Hana

sap hana:

1):
free hana tutorials 

SAP HANA Administration Guide 

SAP HANA Administration Guide  DOC-60368 

DOC-28294 SAP HANA Developer Edition in the Cloud 

SAP HANA on AWS 

andy silvey 

Suse Linux commands

cat /etc/SuSE-release

# My Environment
env|egrep -i 'HOME|PATH|HANA|SAP|LIB|H'|sort

whoami: what login name currently in
w: how many people are login right now

#Current shortcut or alias
alias|sort

#Current User Process Count
ps -ef|cut -d' ' -f1|sort |uniq -c|sort -k1n |awk '{printf("%s\t%s\n",$2,$1)}END{print}'

top
top -u myuser #only list process belong to myuser
top -u <sid>adm

netstat -nat

#Current mount points and file system info
df -h
mount

# File Structure
/usr/sap/<sid>
/hana/shared/<sid>  for binary,trace and configuration files
/hana/data/<sid>
/hana/log/<sid>

Turn off auto Commit In Hana Studio

Windows --> Show View --> Properties --> Session --> Auto Commit --> Off

hana command line tools

HDB: HDB info; HDB proc; HDB start; HDB stop
hdbsql: sql statement
hdbuserstore: user and password
hdblcm hdblcmgui hdbinst hdbupd Unified Installer: installation and update ( lcm:LifeCycle Management)

Currently Installed HANA Version

HDB version
-- major.minor.patch.build : eg: 1.00.103.9999 means SPS 10 Revision 3, build 999

select version from m_database;
select version from "SYS"."M_DATABASE"; 

select version,
substr_before (version, '.') as major,
substr_before (substr_after (version, '.'), '.') as minor,
substr_before (substr_after (substr_after (version, '.'), '.') , '.') as patch
from m_database;select version from m_database;
select version from "SYS"."M_DATABASE"; 

select version,
substr_before (version, '.') as major,
substr_before (substr_after (version, '.'), '.') as minor,
substr_before (substr_after (substr_after (version, '.'), '.') , '.') as patch
from m_database;

Configuration Setting ini Parameter File

# hana studio -- system monitor -- performance -- Configuration 
# hana studio --> Select System --> Configuration --> xsengine.ini

global.ini
indexserver.ini
nameserver.ini
scriptserver.ini

select * from m_system_limits;

-- alter configuration .ini value: Enable/Disable the trace
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('backup', 'trace') = 'debug' with reconfigure;

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') UNSET ('backup', 'trace') with reconfigure;

Start And Stop HANA Instance

login as sidadm:
# su -l hdbadm
/usr/sap/HDB/HDB00> HDB info 
/usr/sap/HDB/HDB00> HDB proc 

# ./HDB --help
usage: ./HDB { start|stop|reconf|restart|version|info|proc|admin|kill|kill-|term}

# ./HDB stop
# ./HDB start
/usr/sap/<SAPSID>/<Instance_Name>/HDB start
/usr/sap/<SAPSID>/<Instance_Name>/HDB stop

login as root:
/usr/sap/hostctrl/exe/sapcontrol -nr 00 -function Start
/usr/sap/hostctrl/exe/sapcontrol -nr 00 -function Stop
/usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList

# find / -name "saphostctrl" -print 2>/dev/null
/usr/sap/hostctrl/exe/saphostctrl -function ListInstances

Database Information

-- running status
select * from m_database;
select * from m_databases;

-- disk usage
select * from "SYS"."M_DISK_USAGE";
select * from "SYS"."M_DISKS";
-- logical: DATA DATA_BACKUP LOG LOG_BACKUP TRACE

-- net size on disk:
SELECT round(SUM(disk_size)/1024/1024/1024,2) PERSISTENCE_SIZE_IN_GB 
FROM m_table_persistence_statistics;

-- indexServer, nameServer,scriptServer
select v.host, v.port, v.service_name, s.volume_id, v.livecache_store,
s.path, s.filesystem_type, s.type
from m_volumes v, m_volume_io_total_statistics s
where v.host=s.host and v.port=s.port and v.volume_id = s.volume_id;

-- log segment information
select * from m_log_segments where state = 'Writing';

select * from m_log_segments where state = 'Free'

select 
round(sum(used_size)/(1024*1024),0) usedSizeMb, 
round(sum(total_size)/(1024*1024),0) totalSizeMb, 
count(*) cnt, state 
from m_log_segments 
group by state 
order by state;

-- -- remove all free log segments
-- alter system reclaim log;

-- one log partition contains many lot segments
select * from m_log_partitions;

-- log switch information in m_log_buffers
select * from m_log_buffers;

sap hana command line tools by andy silvey 

http://wiki.scn.sap.com/wiki/display/inmemory/SAP+HANA+Installation

see SAP Note - 1925267 - HANA Forgot SYSTEM password
How to Reset Hana system password 

hdbuserstore

1): list
hdbuserstore list

2): use userstore key
# hdbsql Using the key listed in "hdbuserstore list" as username, eg: DEFAULT and backup 
hdbsql -U DEFAULT  
hdbsql -U backup

3): create
hdbuserstore -i SET backup hanahost:30015 backupuser backuppassword 
hdbuserstore -i SET mykey myhostname:port myuser mypassword

hdbsql

hbdsql -h
\h or \?: help
\q      : quit

; \g --> run a command
\mu --> multiline mode
\a: autocommit on or off

\pa: toggle page by page scroll output on or off

\du  -- list of db users
\ds: list schemas


hdbsql -c \; -U DEFAULT

hdbsql -c \; -m -U DEFAULT --> -m: multiline mode

hdbsql -c \; -m -a -U DEFAULT --> -a: supress column header 

hdbsql -d SytemDB

select current_connection from dummy;

select * from m_databases;

SYS_DATABASES.*


hdbsql -U DEFAULT

SAP HANA Studio

developer prospective

SAP HANA Cockpit: 
-- (Right Click the selected system) -- Configuration and Monitoring -- Open Dashboard 
-- (Right Click the selected system) -- Configuration and Monitoring -- Open Memory Overview 
-- (Right Click the selected system) -- Configuration and Monitoring -- Resource Utilization

Configuration and Monitoring -- Open Dashboard  -- Open Administration

-- administrator prospective
hanastudio -- Window -- Open Perspective -- SAP HANA Administration Console

-- developer prospective
hanastudio -- Window -- Open Perspective -- SAP HANA Development

-- modeler prospective
Windows --> Open Perspective --> Modeler

hanastudio -- Window -- Open Perspective -- SAP HANA Backup Console

hanastudio -- Window -- Show View -- Systems -- (select a system)

-- (Right Click the selected system) -- Backup and Recovery -- Open Backup Console
-- (Expand the selected system) -- Double Click "Backup"  -- Open Backup Console
-- 1): overview tab
-- 2): Configuration tab ( Backup Schedule )
-- 3): Backup Catalog

-- (Right Click the selected system) -- Security --- Open Security Console 
-- (Expand the selected system) -- Security -- Security

-- (Right Click the selected system) -- Properties -- 

Remote sources: DB_LINK in HANA
-- (Expand the selected system) -- Provisioning -- Remote sources -- ORCL|MSSQLDB1|SYBASEIQ 
-- (Expand the selected system) -- catalog -- Traditional Table
-- (Expand the selected system) -- content -- Analytical View, Catalog View

SAP HANA Studio:
1): hanastudio Check Version
switch to HANA Database Development perspective
Using the Systems window connect to the target SAP HANA database.
Right click on the database to display context menu 
choose option:Configuration and Monitoring 
choose Open Administration

2): hanastudio Configuration ini setting
Go to SAP HANA Studio -> Select System -> Configuration -> xsengine.ini

3): hanastudio table operation
right click selected table to display context menu 
1. Load into Memory
2. UnLoad from Memory
3. Export
4. Import
5. Generate (script )
6. Open Data In Preview
7. Perform Delta Merge

4): hanastudio Create User
hana Studio -- Security -- Users -- New User
Authentication: Kerboses, SAML, SAP Logon Ticket, X509, SAP Assertion Ticket

5): hanastudio Re-Activate/De-Activate User
hana Studio -- Security -- Users -- Double Click A Selected User 

6): hanastudio Create Role
hana Studio -- Security -- Roles -- New Role

7): hanastudio Turn Off AutoCommit
Windows -- Show View -- Properties -- Session -- Auto Commit -- Off

8): hanastudio export import
-- or you can use hana studio,by right clicking on the schema and selecting export, and later import
-- Quick Lauch-- Content-- Export or Import
-- File Menu
-- right Click Context Menu

basic table, index operation

set schema xxx;

create column table xxx ...;
alter table xxx column;
alter table xxx row;

alter table pseudodso add primary key ("RECORD");
alter table pseudodso add primary key ("REQUEST", "RECORD");
alter table pseudodso drop primary key;

create index PDSO_I1 on PSEUDODSO_S (REQUEST, DATAPAKID);
drop index PDSO_I1;

Space requirements for single and multicolumn primary keys are huge
Multicolumn primary keys need way more memory

-- ways to create concatenated columns:
ALTER TABLE ... WITH PARAMETERS ('CONCAT_ATTRIBUTE'= ('$...$' , 'COL1', ...  ))

ALTER TABLE <table_name> WITH PARAMETERS ('DELETE_CONCAT_ATTRIBUTE' = '<concat attribute name>');
ALTER TABLE PSEUDODSO_S WITH PARAMETERS ('DELETE_CONCAT_ATTRIBUTE' = '$DATAPAKID$RECORD$RECORDMODE$REQUEST$' );
ALTER TABLE PSEUDODSO_S WITH PARAMETERS ('DELETE_CONCAT_ATTRIBUTE' = '$REQUEST$DATAPAKID$' );
note: The concat attribute will now be recreated the next time the multi column join is executed.

-- foreign key constraints

select * from "SYS"."REFERENTIAL_CONSTRAINTS"
WHERE SCHEMA_NAME='MYSCHEMA';

Compression

create column table mytable_xxx

UPDATE mytable_xxx 
       WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE');

select column_name
       , memory_size_in_total as mem_total
       , memory_size_in_delta as mem_delta
       , count
       , distinct_count
       , compression_type 
       , compression_ratio_in_percentage as compr_pct 
from m_cs_columns 
where table_name ='MYTABLE_XXX';

Sequence

create sequence xxx start with 10;
SELECT MYSCHEMA.MYSEQUENCE.currval FROM DUMMY;
SELECT MYSCHEMA.MYSEQUENCE.nextval FROM DUMMY;

-- CURRVAL: Provide Current value of Sequence
-- NEXTVAL: Provide Next value of sequence

extract object or table DDL

call get_object_definition ('MY_SCHEMA','MY_TABLE');

delta merge

merge delta of mytable;

1):
merge delta of mytable parameters ( 'FORCED_MERGE' = 'ON' );

hana studio --> object navigator --> right click table_name --> Perform Delta Merge

2):
select * from M_DELTA_MERGE_STATISTICS;

select table_name, record_count, raw_record_count_in_main,raw_record_count_in_delta
from m_cs_ables where table_name = 'mytable';

hana studio --> Administration Console --> System Information --> Merge Statistics --> select and double click to show details

3):
alter table my_table disable automerge;

export and import

export <schema name>."*" AS BINARY INTO '<path>' with replace

export myschema."*" AS BINARY INTO '/tmp/MyBackup' with replace

export ${SchemaName}."*" AS BINARY INTO '${TARGETDIR}' WITH NO DEPENDENCIES THREADS 10

import <schema name>."*" as binary from '<path>' with replace

import myschema."*" as binary from '/tmp/MyBackup' with replace

-- or to a different schema
import "myschema1"."*" as binary from '/home/SBODEMOGB_901' with replace threads 10 
rename schema "myschema1" to "myschema2";

-- or you can use hana studio,by right clicking on the schema and selecting export, and later import
-- Quick Lauch-->Content-->Export or Import
-- File Menu
-- right Click Context Menu

hana studio: -- right on schema ( table ) -- on context menu -- select export or import

-- export in csv or text format
export MY_SCHEMA.tab1 AS CSV INTO '/tmp' WITH REPLACE SCRAMBLE THREADS 10

export a."*", b."*" AS CSV INTO '/tmp' WITH REPLACE

-- Calculation view Export & Import
Export: 
Open the Hana Studio
-->File
-->Export-->
SAP Hana Content-->
Developer Mode--> 
Select the System--> 
Browse the particular Calculation View on the left side--> 
Then Click on ADD --> 
Select the Target folder--> 
Click on Finish.

Import: 
Open the Hana Studio
-->File
-->Import
-->SAP Hana Content
-->Developer Mode
--> Select the System
--> Browse the particular Calculation View present in the target Folder--> 
Then Click on ADD --> 
Click on Finish.

Else you can create a delivery unit with the particular Calculation view 
and then import it in the Hana system.

backup and restore

backup/recovery in hana studio 

hdbuserstore list

hdbuserstore -i SET backup hana:30115 backup_operator  -- put password here
 
needs to use unique name prefixes for each data backup,

-- hana studio: perform a backup
backup using hana studio 

practical advice for hana backup 

#!/bin/bash  
# define backup prefix  
TIMESTAMP="$(date +\%F\_%H\%M)"  
BACKUP_PREFIX="SCHEDULED"  
BACKUP_PREFIX="$BACKUP_PREFIX"_"$TIMESTAMP"  
# source HANA environment  
. /usr/sap/shared/DB1/HDB01/hdbenv.sh  
# execute command with user key  
# asynchronous runs job in background and returns prompt  
#-- hdbsql -U backup "backup data using file ('$BACKUP_PREFIX') ASYNCHRONOUS"  
#-- hdbsql> backup data using file ('MONDAY') 

hdbbackupcheck -v <Backup file>

BACKUP LIST DATA 
-- $DIR_INSTANCE/work/strategyOutput.xml

system privileges related to backups:
CATALOG READ:    Read information from backup catalog
BACKUP OPERATOR: execute a backup
BACKUP ADMIN:    cancel a running backup

select ENTRY_TYPE_NAME, SYS_START_TIME, SYS_END_TIME, STATE_NAME 
from 
PUBLIC.M_BACKUP_CATALOG 
WHERE 
ENTRY_TYPE_NAME = 'complete data backup' and 
SYS_START_TIME > CURRENT_DATE;

-- How to cancel a backup 
1): Get the backup_id of your running backup:
select BACKUP_ID from "SYS"."M_BACKUP_CATALOG" 
where entry_type_name = 'complete data backup' 
and state_name = 'running'  
order by sys_start_time desc;

2): Cancel the backup
backup cancel <backup_id>

3): Confirm the backup has cancelled
select state_name from "SYS"."M_BACKUP_CATALOG" 
where backup_id = <backup_id>

4): Check to  make sure there are no running backups still

select * from  "SYS"."M_BACKUP_CATALOG" 
where STATE_NAME = 'running';

5): If the backup still exists, you can try to cancel the thread through the commands:

ALTER SYSTEM CANCEL SESSION ;
ALTER SYSTEM DISCONNECT SESSION ;

recover database:
"initialize log area" option: used when recovering the database with an unusable log volume

backup consol -- backup catalog -- (specific) backup

RECOVER DATABASE
1):

-- RECOVER DATABASE UNTIL TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
-- Eg: RECOVER DATABASE UNTIL TIMESTAMP '2013-03-22 15:00:00'

-- RECOVER DATABASE FOR Tenant_1 
-- UNTIL TIMESTAMP '2017-09-21 15:00:00' 
-- CHECK ACCESS ALL;
2):
RECOVER DATABASE FOR Tenant_1 
UNTIL TIMESTAMP '2017-09-21 15:00:00' 
USING DATA PATH ('/backup/THURSDAY/') 
USING LOG PATH ('/backup/logs1/','/backup/logs2/');

3):
RECOVER DATABASE 
UNTIL TIMESTAMP '2027-09-21 15:00:00' 
USING SNAPSHOT;

RECOVER DATABASE FOR TENANT_1 
UNTIL TIMESTAMP '2027-09-21 15:00:00' 
USING SNAPSHOT;

4):
RECOVER DATABASE FOR Tenant_1 UNTIL 
TIMESTAMP '2017-09-19' 
USING CATALOG PATH ('/hana/PR1/backup/catalog') 
USING LOG PATH ('/hana/HHB/backup/log') 
CHECK ACCESS ALL;

1):
RECOVER DATA
RECOVER DATA USING FILE ('/backup/THURSDAY') CLEAR LOG;

2):
RECOVER DATA FOR Tenant_1 
USING SOURCE 'Tenant_0@PR1' 
USING BACKINT('2017-09-21') 
CLEAR LOG;

recoverSys.py tool
HDBSettings.sh recoverSys.py without any parameters:
recoverSys.py performs a recovery to the most recent point in time

The Python script recoverSys.py supplies the restore command to the services and starts the database.

# -- to restore the services and starts the database.
# python /usr/sap//SYS/exe/hdb/python_support/recoverSys.py \
#    --password= --wait --command="RECOVER DATABASE \
#    UNTIL TIMESTAMP '2013-03-29 23:59:59'"

user,role,permission,privileges

-- User Permissions
1. Granted Roles
2. System Privileges
3. Object Privileges
4. Analytic Privileges
5. Package Privileges
6. Application Privileges
7. Privileges on Users

Builtin user: SYSTEM SYS _SYS_REPO

CREATE USER
CREATE RESTRICTED USER

ALTER USER myuser PASSWORD mypassword;
ALTER USER myuser FORCE PASSWORD CHANGE;
ALTER USER myuser DISABLE PASSWORD LIFETIME; -- password no-expire
ALTER USER myuser RESET CONNECT ATEMPTS;     -- reactivate user locked by too many failed logons
ALTER USER myuser DROP CONNECT ATEMPTS;      -- drop information about failed connect attempts

ALTER USER MY_USER ACTIVATE USER NOW   ;
ALTER USER MY_USER DEACTIVATE USER NOW  ;

-- ALTER USER SYSTEM DEACTIVATE USER NOW;
-- ALTER USER SYSTEM ACTIVATE USER NOW.

CREATE USER USER1 WITH REMOTE IDENTITY USER2 AT DATABASE DB2;

ALTER USER <user_name> ENABLE CLIENT CONNECT;

create restricted user myRestrictedUser password yyyy;
Restricted User cannot create database objects
Restricted User cannot view data in database
Restricted User connects to database through HTTP Only
Restricted User: all permissions through explicit SQLs

grant xxxx_erp_auth_role to NY_USER;
alter user xxxx_spms_all_role to MY_USER;
alter user myusername enable saml;
alter user myusername add identity 'myusername' for SAML Provider SIDSAML;

ALTER USER xxxx DEACTIVATE;
drop user xxxx;
drop user xxxx CASCADE;

select   
  USER_NAME , ADMIN_GIVEN_PASSWORD, PASSWORD_CHANGE_TIME, PASSWORD_CHANGE_NEEDED, 
  USER_DEACTIVATED, DEACTIVATION_TIME,
  IS_PASSWORD_ENABLED, IS_SAML_ENABLED
from USERS 
ORDER BY 1;

select   
  USER_NAME , ADMIN_GIVEN_PASSWORD, PASSWORD_CHANGE_TIME, PASSWORD_CHANGE_NEEDED, 
  USER_DEACTIVATED, DEACTIVATION_TIME,
  IS_PASSWORD_ENABLED, IS_SAML_ENABLED
from USERS 
WHERE USER_NAME IN ('SYSTEM','MY_USERNAME_XXX')
ORDER BY 1;

system user:
SYSTEM
SYS
_SYS_STATISTICS
_SYS_REPO
_SYS_DATAPROV
_SYS_AFL

select * from USERS;       
select * from SYS.USERS;

select * from REMOTE_USERS; 
select * from SYS.REMOTE_USERS;

_SYS_SECURITY._SYS_PASSWORD_BLACKLIST

create role my_etl_role;
select * from ROLES;

EFFECTIVE_PRIVILEGES
STRUCTURED_PRIVILEGES

ACCESSIBLE_VIEWS

SPS10 P86-87
system privilege ROLE ADMIN
System privilege CATALOG READ:read dictionary information
System privilege USER ADMIN: alter/create users
System privilege INIFILE ADMIN
System privilege TRACE ADMIN
System privilege AUDIT ADMIN
System privilege RESOURCE ADMIN
System privilege AUDIT OPERATOR
System privilege SERVICE ADMIN
System privilege SESSION ADMIN   Kill sessions
System privilege VERSION ADMIN
System privilege LICENSE ADMIN
System privilege SAVEPOINT ADMIN Force execution of savepoint
System privilege LOG ADMIN       Allows freeing up space on log volume
system privilege BACKUP ADMIN;
system privilege BACKUP OPERATOR
System privilege CREATE SCHEMA
System privilege EXPORT
System privilege IMPORT: import from client table  in schema
system privilege CREATE R SCRIPT;
system privilege CREATE SCENARIO;
system privilege DATABASE ADMIN;

system privilege DATA ADMIN:create/drop database schema,create/alter/drop table 

system privilege CREATE STRUCTURED PRIVILEGE
system privilege STRUCTUREDPRIVILEGE ADMIN


System privilege CREATE STRUCTURED PRIVILEGE
System privilege STRUCTUREDPRIVILEGE ADMIN

Object Privileges:
P90-91
ALL Privileges,

Analytic privilege _SYS_BI_CP_ALL  unrestricted access to the content of all activated data models

ROLE:
MODELING
MONITORING
PUBLIC
CONTENT_ADMIN
SAP_INTERNAL_HANA_SUPPORT


Role repo_manager
Role EXECUTE_TRANSPORT
Role xs_app_dev_public
Role data_modeling
Role build_analytic_privilege_generic
Role reporting_generic
Role basis_support_read
Role basis_support_trace
Role app_support_read
Role app_support_trace
Role app_support_read_data

schema "_SYS_BI": SELECT;

SELECT on schema _SYS_BIC
EXECUTE on schema _SYS_BIC

object "SYS"."REPOSITORY_REST": EXECUTE

SELECT on schema _SYS_STATISTICS;

SELECT, INSERT, UPDATE, DELETE on table _SYS_PASSWORD_BLACKLIST in schema _SYS_SECURITY;

GRANT  SAP_INTERNAL_HANA_SUPPORT TO   MY_GRANTEE;
REVOKE SAP_INTERNAL_HANA_SUPPORT FROM MY_GRANTEE:

--
GRANT_ACTIVATED_ROLE procedure

_SYS_REPO.GRANT_APPLICATION_PRIVILEGE
_SYS_REPO.REVOKE_APPLICATION_PRIVILEGE

REPO.EDIT_NATIVE_OBJECTS
REPO.ACTIVATE_NATIVE_OBJECTS

GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE 
REVOKE_ACTIVATED_ANALYTICAL_PRIVILEGE

SYS.REPOSITORY_REST

-- --
variables:
SESSION_USER

SELECT * found FROM "ROLES";

SELECT * FROM "USERS" WHERE "USER_NAME" = :grantee;


-- ROLE prilileges
grant modeling      to my_etl_role;
grant content_admin to my_etl_role; -- system administrator needs content_admin role

-- system privileges
grant data admin                to my_etl_role;
grant create scenario           to my_etl_role;
grant catalog read              to my_etl_role;
grant structuredprivilege admin to my_etl_role;

-- object privileges
grant execute on repository_rest to my_etl_role;

-- schema privileges
call "_SYS_REPO"."GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"
('SELECT,EXECUTE,CREATE ANY,ALTER,DROP,INSERT,UPDATE,DELETE,INDEX,TRIGGER,DEBUG','_SYS_BIC', 'MY_DSSTG_ETL_ROLE');

-- Analytic privileges
call "_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGES"('"_SYS_BI_CP_ALL"', 'MY_DSSTG_ETL_ROLE');

-- PACKEGE Privileges
grant repo.read,repo.edut_native_objects,repo.activate_native_objects,repo.maintain_native_packages,
repo.maitain_imported_packages,repor.activate_imported_objects,repo.edit_imported_objects 
on "Peoplesoft" to MY_DSSTG_ETL_ROLE;

-- ----------------------------
CALL grant_ACTIVATED_ROLE ( 'MYSID1_PKG.MYSID1_Peoject.ROLES::MY_SELECTONLY_APPL_SCHEMAS_ROLE', 'XXXX');
CALL grant_ACTIVATED_ROLE ( 'MYSID1_PKG.MYSID1_Peoject.ROLES::MY_SELECTONLY_APPL_CONTENT_ROLE', 'XXXX');
CALL grant_ACTIVATED_ROLE ( 'sap.bi.common::BI_DATA_ANALYST', 'XXXX');
-- ----------------------------

privilege: 
DATABASE ADMIN ( multi-tenant database ) alter system start database 
BACKUP ADMIN
BACKUP OPERA
CATALOG READ
USER ADMIN
ROLE ADMIN

select schema_name
     , '' as "OBJECT_NAME",
     , 'SCHEMA' as "OBJECT_TYPE"
     , SCHEMA_OWNER as "OWNER_NAME"
from public.schemas
where schema_owner = 'XXX'
union all
select schema_name
     , object_name,
     , object_type,
     , owner_name 
from public.ownership
where schema_name in ( select schema_name from public.schemas where schema_owner = 'XXX') 
      or OWNER_NAME = 'XXXX'
)
ORDER BY SCHEMA_NAME, OBJECT_NAME

data dictionary

select * from M_CS_TABLES;

select table_name,loaded, memory_size_in_total, memory_size_in_main, memory_size_in_delta
from m_cs_tables
where schema_name ='INDEXPERF' and table_name in ('PSEUDODSO', 'PSEUDODSO_S')
order by table_name;

select table_name, loaded, sum(memory_size_in_total) col_total, 
sum(memory_size_in_main) col_main, sum(memory_size_in_delta) col_delta
from m_cs_columns
where schema_name ='MYSCHEMA' and table_name in ('MYTABLE1', 'PSEUDODSO', 'PSEUDODSO_S')
group by table_name, loaded
order by table_name;

m_cs_all_columns

select table_name, column_name, loaded, memory_size_in_total,
COMPRESSION_RATIO_IN_PERCENTAGE "COMPR_%"
,internal_attribute_type
from m_cs_all_columns
where schema_name ='INDEXPERF' and table_name in ('PSEUDODSO', 'PSEUDODSO_S')
order by table_name, memory_size_in_total desc;

select table_name, index_name from indexes
where schema_name ='INDEXPERF' and table_name ='PSEUDODSO_S';

performance tuning

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

SELECT CONNECTION_ID, IDLE_TIME
FROM M_CONNECTIONS
WHERE CONNECTION_STATUS = 'IDLE' AND CONNECTION_TYPE = 'Remote'
ORDER BY IDLE_TIME DESC;

select connection_id, user_name, own, idle_time, connection_status
from m_connections
where own = 'TRUE';

select  connection_id, user_name, own, dle_time, connection_status
from m_connections 
where  
user_name = 'xxx'
and own = 'FALSE'
and idle_time <> 0
and connection_status='RUNNING';

# hana studio -- system monitor -- performance -- Sessions -- right click a selected session -- cancel session 

# Cancel a running sql, session id is connection id
ALTER SYSTEM CANCEL SESSION '237048';

# Kill or Disconnect a Session, session id is connection id
ALTER SYSTEM DISCONNECT SESSION '203927';

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;

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%';

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;

-- Find owners of objects
SELECT * FROM "PUBLIC"."OWNERSHIP" WHERE SCHEMA='SCHEMA';
 
SELECT * FROM PUBLIC.GRANTED_PRIVILEGES
WHERE GRANTEE_TYPE = 'USER' AND GRANTOR = 'NAIRV';

-- PASSWORD Policy
ALTER USER USER DISABLE PASSWORD LIFETIME;

Advanced SQL

SQL Statement Comments:
Single Line Comment - Double Hyphens eg: "-- This is one line comment"

Multiple Line Comment: /* */ eg: "/* This can be multi-line comment */ "

1):
select grouping_id (some_flag, a_date ) as grp_id
      , some_flag, a_date 
      , count (*) 
from 
    compression_test
group by 
        grouping sets (some_flag, a_date )
order by grouping_id ( some_flag, a_date );

connection

-- current session or connectgion
select current_connection from dummy;

select * from m_session_context where connection_id=current_connection;

select host,port,connection_id, key,value,section 
from m_session_context where connection_id=current_connection;

-- other connections
select * from m_connections;

ALTER SYSTEM CANCEL [WORK IN] SESSION '<CONNECTION_ID>'

SELECT   
    'alter system cancel session ''' || C.CONNECTION_ID || ''';' command,   
    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';

transaction

# hana studio -- system monitor -- performance -- blocked transaction -- right click a selected transaction 

select * from m_transactions;

select connection_id, transaction_id, update_transaction_id 
from m_transactions where connection_id = 343432;

Find Dictionary View

catalog --> sys (sys schema ) --> Views --> 

select * from objects;

select * from objects where object_name like '%INDEX%';

select schema_name, object_name, object_type from objects where object_name like '%INDEX%';

slelect * from procedures;

select * from m_context_variables;

select * from user_parameters;

select * from indexes;
select * from index_columns;

select * from m_active_statements;

select * from m_active_procedures;

select * from m_database;
select * from m_databases;


select * from "SYS"."M_DISKS";

select * from "SYS"."M_DISK_USAGE";

select  * from m_object_locks;

select  * from m_record_locks;

select * from m_mvcc_tables;

Memory Usage

select 
vm.host,
s.service_name,
allocated_memory_size as allocated_mem,
used_memory_size as used_mem,
reclaimed_version_size as reclaimed_vers,
free_memory_size as free_mem
from 
m_version_memory vm
join m_services s
on (vm.host,vm.port) = (s.host,s.port)
order by vm.host, s.service_name;

-- M_TABLE_PERSISTENCE_STATISTICS. 

SELECT
 SCHEMA_NAME,TABLE_NAME,round(DISK_SIZE/(1024*1024),0) DISK_SIZE
FROM
 PUBLIC.M_TABLE_PERSISTENCE_STATISTICS 
 where schema_name = 'MY_SCHEMA' and table_name = 'MY_OBJECT' 
ORDER BY
 DISK_SIZE DESC;

SELECT
 SCHEMA_NAME,TABLE_NAME,DISK_SIZE
FROM
 PUBLIC.M_TABLE_PERSISTENCE_STATISTICS
ORDER BY
 DISK_SIZE DESC;

SELECT
 SCHEMA_NAME,TABLE_NAME,DISK_SIZE
FROM
 PUBLIC.M_TABLE_PERSISTENCE_STATISTICS 
 where schema_name = 'MY_SCHEMA' and table_name = 'MY_OBJECT' 
ORDER BY
 DISK_SIZE DESC;

sapcar

SAPCAR -xvf IMDB_SERVER100_56-10009569.SAR