HANA: hdbsql queries for connections, backups and version
SAP HANA SQL queries to list connections, tenants, backups and get the version.
Useful hdbsql shortcuts and SQL queries to inspect connections, backups, tenants and the version of an SAP HANA system.
hdbsql shortcuts
Inside an hdbsql session, \mu and \al toggle display options (multi-line and auto-line mode); \s shows the session and system status, including the version.
List current connections
SELECT connection_status as "connection",
SUBSTRING(start_time,1,19) as "start",
end_time,
authentication_method as "authentication",
connection_type as "type",
created_by,
current_schema_name as "schema",
client_distribution_mode,
host,
transaction_id,
client_host,
user_name
FROM M_CONNECTIONS
WHERE connection_status != ''
ORDER BY 1,2,3 DESC;
Overview per user and client host:
SELECT CURRENT_UTCDATE, CURRENT_UTCTIME, user_name, client_host, count(*)
FROM M_CONNECTIONS
WHERE connection_status != ''
GROUP BY user_name, client_host;
All client users that have been connected:
SELECT connection_status,
authentication_method as "authentication",
connection_type,
created_by,
current_schema_name,
client_distribution_mode,
host,
transaction_id,
client_host,
user_name,
CURRENT_OPERATOR_NAME
FROM M_CONNECTIONS
WHERE client_host != ''
ORDER BY 1,3 DESC;
List tenant databases
SELECT DATABASE_NAME FROM SYS.M_DATABASES;
List backups
Backups other than log backups, most recent first (from a tenant, or from the system database with the SYSTEMDB.SYS prefix):
SELECT SYSTEM_ID, BACKUP_ID, ENTRY_TYPE_NAME, SYS_START_TIME, STATE_NAME
FROM M_BACKUP_CATALOG
WHERE ENTRY_TYPE_NAME != 'log backup'
ORDER BY SYS_START_TIME DESC;
With the destination files:
SELECT cat.BACKUP_ID, cat.UTC_START_TIME, ENTRY_TYPE_NAME, fil.DESTINATION_PATH, fil.DESTINATION_TYPE_NAME
FROM M_BACKUP_CATALOG_FILES fil
INNER JOIN M_BACKUP_CATALOG cat ON fil.BACKUP_ID = cat.BACKUP_ID
WHERE ENTRY_TYPE_NAME != 'log backup'
ORDER BY cat.UTC_START_TIME DESC;
The oldest successful complete data backup of the last 15 days (useful as a starting point for housekeeping):
SELECT TOP 1 MIN(TO_BIGINT(BACKUP_ID))
FROM ${SAPSYSTEMNAME}.SYS.M_BACKUP_CATALOG
WHERE SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP,-15)
AND ENTRY_TYPE_NAME='complete data backup'
AND STATE_NAME='successful';
Here ${SAPSYSTEMNAME} is the name of the database as a shell variable or placeholder to replace.
Get the HANA version
In hdbsql:
\s
Or with the system overview script shipped with HANA:
cd /usr/sap/<SID>/HDB<nr>/exe/python_support/
python systemOverview.py
Sample output:
| Section | Name | Status | Value |
| ---------- | --------------- | ------- | --------------------------------------------- |
| System | Instance ID | | <SID> |
| System | Instance Number | | 00 |
| System | Distributed | | No |
| System | Version | | 2.00.070.00.1679989823 (fa/hana2sp07) |
| System | Platform | | Red Hat Enterprise Linux 8.6 (Ootpa) |
| Services | All Started | OK | Yes |
| Memory | Memory | OK | Physical 251.64 GB, Swap 5.00 GB, Used 227.81 |
| CPU | CPU | OK | Available 8, Used 1.60 |
| Disk | Data | OK | Size 319.8 GB, Used 206.1 GB, Free 35 % |
| Disk | Log | OK | Size 119.9 GB, Used 14.1 GB, Free 88 % |
| Statistics | Alerts | WARNING | cannot check statistics w/o SQL connection |
Notes
- See SAP HANA under the hood: HDB for the
HDBcommand. M_CONNECTIONSrows with an emptyconnection_statusare not open connections, hence the filter.