Sap Hana

I am in the process of putting my notes into web pages, please keep checking in later for more contents
home  sybase ase  sybase iq  Andy Silvey  hanatec  hana course, Lauch Course  sample book  sample book appendex 
start and stop  hdbsql  basic ddl  sequence  extract object ddl  delta_merge  export_and_import  backup_and_restore  user permissions  data dictionary  performance_tuning  compression  advanced_sql 
sap hana:

1):
http://www.freehanatutorials.com/2012/10/hana-start-stop-commands.html

http://scn.sap.com/servlet/JiveServlet/download/60368-2-371619/SAP_HANA_Administration_Guide_en.pdf

http://scn.sap.com/docs/DOC-60368

http://scn.sap.com/docs/DOC-28294

http://scn.sap.com/docs/DOC-59815

http://scn.sap.com/community/hana-in-memory/blog/2013/05/27/andy-silvey--sap-hana-command-line-tools-and-sql-reference-examples-for-netweaver-basis-administrators

top   prev   next  

Turn off auto Commit In Hana Studio

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

top   prev   next  

start and stop hana instance

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

login as sidad:
# su -l hdbadm
# ./HDB stop
# ./HDB start
# ./HDB --help
usage: ./HDB { start|stop|reconf|restart|version|info|proc|admin|kill|kill-|term}

/usr/sap/<SAPSID>/<Instance_Name>/HDB start
/usr/sap/<SAPSID>/<Instance_Name>/HDB stop
/usr/sap/HDB/HDB00> HDB info; netstat -nat
http://scn.sap.com/community/hana-in-memory/blog/2013/05/27/andy-silvey--sap-hana-command-line-tools-and-sql-reference-examples-for-netweaver-basis-administrators

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

see SAP Note - 1925267 - HANA Forgot SYSTEM password

top   prev   next  

hdbsql

hdbuserstore list
hdbsql -U DEFAULT 
hdbsql -U backup
hdbuserstore -i SET backup hana:30115 backup_operator  -- put password here

hbdsql -h
\h or \?
\q

\du  -- list of db users

\mu --> multiline mode
; \g --> run a command

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 * from m_databases;

SYS_DATABASES.*


hdbsql -U DEFAULT

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

top   prev   next  

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  WITH PARAMETERS ('DELETE_CONCAT_ATTRIBUTE' = '');
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.

top   prev   next  

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';
top
top   prev   next  

Sequence

create sequence xxx start with 10;

top   prev   next  

extract object or table DDL

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

top   prev   next  

delta merge

merge delta of mytable;
hana studio --> object navigator --> right click table_name --> Perform Delta Merge

merge delta of pseudidso with parameters ( 'FORCED_MERGE' = 'ON' );

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

top   prev   next  

export and import

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

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

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

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

-- 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

-- 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


top   prev   next  

backup and restore

hdbuserstore list

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

http://passionategeek.me/2013/04/22/practical-advice-for-hana-backups/

#!/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"  

top   prev   next  

user,role,permission,privileges

CREATE USER
CREATE RESTRICTED USER

CREATE USER USER1 WITH REMOTE IDENTITY USER2 AT DATABASE DB2;

ALTER USER  ENABLE CLIENT CONNECT;

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 restricted user xxxx password yyyy;
grant xxxx_erp_auth_role to ksears;
alter user xxxx_spms_all_role to ksears;
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;         SYS.USERS
select * from REMOTE_USERS;  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
System privilege USER ADMIN
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
system privilege CREATE R SCRIPT;
system privilege CREATE SCENARIO;
system privilege DATABASE ADMIN;
system privilege DATA ADMIN;

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 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', 'NCLH_DSSTG_ETL_ROLE');

-- Analytic privileges
call "_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGES"('"_SYS_BI_CP_ALL"', 'NCLH_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 NCL_DSSTG_ETL_ROLE;
-- ----------------------------
CALL grant_ACTIVATED_ROLE ( 'MYSID1_PKG.MYSID1_Peoject.ROLES::NCL_SELECTONLY_APPL_SCHEMAS_ROLE', 'XXXX');
CALL grant_ACTIVATED_ROLE ( 'MYSID1_PKG.MYSID1_Peoject.ROLES::NCL_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

top
top   prev   next  

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 ='INDEXPERF' and table_name in ('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';

top   prev   next  

performance tuning

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

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

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'
 
SELECT * FROM PUBLIC.GRANTED_PRIVILEGES
WHERE GRANTEE_TYPE = 'USER' AND GRANTOR = 'NAIRV'

PASSWORD Policy
ALTER USER USER DISABLE PASSWORD LIFETIME



top   prev   next  

Advanced SQL

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 );


top   prev   next  

connection

ALTER SYSTEM CANCEL [WORK IN] SESSION ''

select * from m_connections;

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

top   prev   next  

sapcar

SAPCAR -xvf IMDB_SERVER100_56-10009569.SAR 
top