LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon