SAP HANA: maintenance and troubleshooting queries on system views
SQL queries on the SYS and _SYS_REPO views: custom objects, settings, users, privileges, expensive statements and calculation views.
Queries against the SAP HANA system views (SYS schema and _SYS_REPO repository) for maintenance and troubleshooting. Run them in hdbsql or SAP HANA Studio, as a user with the monitoring privileges.
The SYS views have to be read with a suitable user. The queries use the default SAP naming, where SAP-delivered objects start with SAP and custom objects are in packages such as CUSTOM%.
Custom objects
Invalid custom views:
SELECT * FROM "SYS"."VIEWS"
WHERE schema_name NOT LIKE 'SAP%' AND is_valid = 'FALSE';
Inactive custom repository objects:
SELECT * FROM "_SYS_REPO"."INACTIVE_OBJECT"
WHERE "PACKAGE_ID" NOT LIKE 'sap%';
Calculation views that use table functions:
SELECT * FROM "_SYS_REPO"."ACTIVE_OBJECTCROSSREF"
WHERE "FROM_OBJECT_SUFFIX" = 'calculationview'
AND "TO_OBJECT_SUFFIX" = 'hdbtablefunction';
Search the content of calculation views for a word, within a package:
SELECT
"PACKAGE_ID" AS "PACKAGE",
"OBJECT_NAME" AS "OBJECT",
CAST(BINTOSTR(CAST("CDATA" AS binary)) AS varchar) AS "DATA"
FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE "OBJECT_SUFFIX" = 'calculationview'
AND "PACKAGE_ID" LIKE 'CUSTOM%' -- package to search in
AND "CDATA" LIKE '%MATNR%' -- word to search for
;
Configuration
Custom settings in global.ini and indexserver.ini:
SELECT * FROM "SYS"."M_INIFILE_CONTENTS"
WHERE ("LAYER_NAME" = 'SYSTEM' OR "HOST" <> '')
AND ("FILE_NAME" = 'global.ini' OR "FILE_NAME" = 'indexserver.ini');
The source note had a truncated literal in the HOST condition. It was restored as an empty string: check that this matches the intended filter.
SAP language settings used by the current session:
SELECT session_context('LOCALE_SAP'), session_context('LOCALE') FROM dummy;
Users and privileges
Users who logged in successfully, most recent first:
SELECT * FROM "SYS"."USERS"
WHERE "LAST_SUCCESSFUL_CONNECT" IS NOT NULL
ORDER BY 9 DESC;
Roles granted to users:
SELECT * FROM "SYS"."GRANTED_ROLES"
WHERE "GRANTEE_TYPE" = 'USER';
Repository privileges:
SELECT * FROM "SYS"."GRANTED_PRIVILEGES"
WHERE object_type = 'REPO';
Objects owned by non-system users:
SELECT * FROM "SYS"."OWNERSHIP"
WHERE owner_name NOT LIKE 'SAP%' AND owner_name NOT LIKE '%SYS%'
ORDER BY 1, 2;
Statements
Executed statements containing a given text, for example to find who dropped a table:
SELECT * FROM "SYS"."M_EXECUTED_STATEMENTS"
WHERE "STATEMENT_STRING" LIKE 'DROP TABLE%';
Expensive statements of the day, during business hours (8 h to 17 h), most expensive first:
SELECT
to_varchar("STATEMENT_START_TIME", 'DD.MM.YYYY') "EXEC_DATE",
to_varchar("STATEMENT_START_TIME", 'HH24:MI:SS') "EXEC_TIME",
to_int("DURATION_MICROSEC"/1000000) "DURATION_S",
to_decimal("MEMORY_SIZE"/1073741824, 10, 1) "MEM_GB",
"RECORDS",
"DB_USER",
"APP_USER",
"APPLICATION_NAME",
"STATEMENT_STRING",
length("STATEMENT_STRING") "SQL_LENGTH",
OCCURRENCES_REGEXPR('JOIN' FLAG 'i' IN "STATEMENT_STRING") "JOIN",
OCCURRENCES_REGEXPR('CASE' FLAG 'i' IN "STATEMENT_STRING") "CASE",
"ERROR_TEXT",
"PARAMETERS"
FROM "SYS"."M_EXPENSIVE_STATEMENTS"
WHERE "OPERATION" IN ('INSERT', 'SELECT', 'AGGREGATED_EXECUTION') -- exclude background activity
AND "RECORDS" > 0
AND to_varchar("STATEMENT_START_TIME", 'YYYYMMDD') = current_date
AND to_int(to_varchar("STATEMENT_START_TIME", 'HH24'))
BETWEEN 8 AND 17 -- business hours
ORDER BY 3 DESC;
Notes
- The source note had a typographic quote in
'HH24'that broke the query: it was corrected. The column alias that countedCASEoccurrences was namedDISTINCT: renamedCASE. - The comparison
to_varchar(...,'YYYYMMDD') = current_datecompares a string with a date: adapt it to your HANA version (for example, compare withto_varchar(current_date, 'YYYYMMDD')) if the filter returns no rows. - The queries were not run against a HANA system while this article was written.