Deadlock detector graph in HANA

Please collect a deadlock detector graph .

This can be done via hdbcons -

hdbcons "deadlockdetector waitgraph -o {filename}"
hdbcons "deadlockdetector waitgraph -w -o {filename}"

You need to specify the full path for like /tmp/waitgraph.dot or /tmp/waitgraph_with_w.dot.

Binary export of a table in HANA

Here , I Explained about how to get the Binary export of a table in HANA Database using export utility.

Case Study : Binary export of the table from System SID !!!!

      Binary export for the Single table .

      export "Schema_Name"."Table_Name" as binary into 'PATH_DETAIL' ;

      Example:

      export "HANADBXY"."/1XT/D140/SID" as binary into xyz/HDB/BACKUP/;

      Binary export for  More then One tables at a time 

      export hanadbxy."SRIT_SEQ" "MSG", hanadbxy."SRIT_SEQ" "SEQ", hanadbxy."SRIT_MAP" as binary into xyz/HDB/BACKUP/ ;

      Compress Binary Export 

      Compress directory into a single file, e.g.

      tar czvf export.tgz export_dir_on_server

How to get the metadata of a table in HANA

Here , I Explained about how to get the metadata of a table in HANA Database using export utility.

we can export the table as CSV and the metadata is created in one of the files.

export HANADBXY."AA05B" AS CSV INTO '/hana/shared' WITH CATALOG ONLY

export HANADBXY."AA05B" AS CSV INTO 'xyz/hana/shared'

how to check alerts in HANA database

Here, I Explained about how To check status of alerts on HANA database using the following sql, the status should be IDLE , if the status is DISABLED the alerts won't run the next scheduled time

select * from _sys_statistics.STATISTICS_SCHEDULE;

if status DISABLED then To enabled the status of alerts back to Idle from DISABLED use as below 

update _sys_statistics.statistics_schedule set status='Idle' where status='Disabled';

how To check the count of alerts generated and accumulated for email processing check :

select count(*) from "_SYS_STATISTICS"."STATISTICS_EMAIL_PROCESSING";

Case Study - Suppose , we had 500000 emails accumulated which crashed the index server and thus had to truncate the email processing table as :

truncate table "_SYS_STATISTICS"."STATISTICS_EMAIL_PROCESSING";

how To check the email recepients on HANA database for alerts 

select * from "_SYS_STATISTICS"."STATISTICS_EMAILRECIPIENTS"

how To check the threshold values for each alerts on HANA database

Select * from  "_SYS_STATISTICS"."STATISTICS_ALERT_THRESHOLDS"

how To check the SMTP configuration for HANA database alerts

select * from   _SYS_STATISTICS.STATISTICS_PROPERTIES

how to check Memory Usage in HANA

Here, I explained about how to check Memory Usage detail in hana database using sql queries.

1- How to find out the top 100 memory usage in HANA database between a specified date and time .

select top 100 round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as
"Used Memory GB", HOST, SERVER_TIMESTAMP  from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVER_TIMESTAMP between '14.04.2016 08:00:00' and '16.04.2016 16:00:00' order by TOTAL_MEMORY_USED_SIZE desc

2- How to check the peak memory usage .

select top 1 HOST, SERVER_TIMESTAMP, round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Used Memory GB" from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVICE_NAME = 'indexserver'
order by TOTAL_MEMORY_USED_SIZE desc

3- How to find the schema name, current memory usage, max memory usage (if all tables and all their columns were loaded into memory simultaneously) and record count in each schema .

select schema_name, round(sum(memory_size_in_total/1024/1024/1024),1) as "Current MEM GB",
round(sum(estimated_max_memory_size_in_total/1024/1024/1024),1) as "MAX MEM GB",
sum(record_count)  from "SYS"."M_CS_TABLES"  group by schema_name  order by "Current MEM GB" desc

4- How to get the total table count in a schema.

SELECT  count(*) "TABLE_NAME" FROM "SYS"."M_TABLES" where "SCHEMA_NAME" = 'SAPHP'

5- How to get the total memory usage of a schema .

For column tables -

SELECT TABLE_NAME AS "Table",sum(round(MEMORY_SIZE_IN_TOTAL/1024/1024, 2)) as "MB" FROM  M_CS_TABLES WHERE SCHEMA_NAME = 'HANADBXY'

For Row tables -

select round(sum(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) as "Row Tables MB Used" from M_RS_TABLES where "SCHEMA_NAME" = 'HANADBXY'

How to Check HANA Database Size

Here , I explained how to check hana database size by using hdbsql & hdbcons utility.

First Login to HANA Database using hdbsql then run the below queries.

1- The number of used blocks in the data volumes

hdbsql SID=> select sum(allocated_page_size) from m_converter_statistics

SUM(ALLOCATED_PAGE_SIZE)
3497635840

2- The license relevant HANA memory usage

select product_usage from m_license

Note : The first metric is useful to compare HANA with other RDBMS, e.g. what is the compression ratio compared to ordinary row-store databases. The second metric is important e.g. for sizing the hardware.

Checking Database Size Using hdbcons Utility -

xxx200132:HDB:sidadm xyz/usr/sap/SID/HDB02/vadbabc> hdbcons 'dvol info'

SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'ABC', instance '02'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 9104)
[OK]
--
DataVolume #0 (xyz/hdb/SID/data/mnt00004/hdb00002/)
  size= 16760078336
  used= 3464384512

NOTE: We recommend shrinking this DataVolume. Use 'dvol shrink -i 0 -o -v'.
      Please use a percentage higher than 110% to avoid long runtime and performance decrease!

[OK]
--
[EXIT]
--
[BYE]

How to query from HANA Database

Here , I explained about how to query from HANA database by using hdbsql.

Executed the statement via hdbsql -

SID:xyz/usr/sap/SID/HDB02>   hdbsql -n L14 -i 02 -u HANADBXY -p pwd -a

Welcome to the SAP HANA Database interactive terminal.

hdbsql=> \o test.out      – specfying the output file, make sure the output file is touched
hdbsql=>  \i test.sql      – specfying the input file, the sql file created before

1 row selected (overall time 15.39 msec; server time 740 usec)

1 row selected (overall time 14.15 msec; server time 4.203 msec)


hdbsql SID=> \o       – closing the output file
hdbsql SID=> \q       – quitting from hdbsql

SID:xyz/usr/sap/SID/HDB02>  more test.out

"/ABC/ABC14",11909
"/ABC/ABC15",6999691
"/ABC/ABC12A",29544151

SID:xyz/usr/sap/SID/HDB02>