Skip to Content

Hello,

quite often it’s necessary to fix issues related to HANA model execution – e.g. defective views,  troubles with authorizations etc. There are some SQL statements that are quite handy for that purpose and I like to share those:

 

–Check invalid custom DB views
select * from “SYS”.”VIEWS”
where schema_name not like ‘SAP%’ and is_valid = ‘FALSE’;

–Check inactive custom DB objects
select * from “_SYS_REPO”.”INACTIVE_OBJECT”
where “PACKAGE_ID” not like ‘sap%’;

–Show custom settings within 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’);

–Check which SAP language settings are being used by current user
select session_context(‘LOCALE_SAP’), session_context(‘LOCALE’) from dummy;

–Search executed SQL statements, e.g. to find out who deleted a table
select * from “SYS”.”M_EXECUTED_STATEMENTS” where “STATEMENT_STRING” LIKE ‘DROP TABLE%’;

–Show details of users that have been logged-in
select * from “SYS”.”USERS”
where “LAST_SUCCESSFUL_CONNECT” is not null
order by 9 desc;

–Show assigned user roles
select * from “SYS”.”GRANTED_ROLES”
where “GRANTEE_TYPE” = ‘USER’;

–Show assigned repository privileges
select * from “SYS”.”GRANTED_PRIVILEGES”
where object_type = ‘REPO’;

–Show 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;

–Analyze expensive statement trace
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”) “DISTINCT”,
“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;

 

Please feel free to modify to your requirements. I am looking forward to your comments, especially if you have similar SQL statements to share.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Marcel Scherbinek

    Thanks for sharing! I can recommend transforming the CDATA SQL in readable VARCHAR (e.g. “Analyze expensive statement trace”)

    CAST(BINTOSTR(CAST(“STATEMENT_STRING” AS binary)) AS varchar) AS “STATEMENT_STRING”,

     

    And one other helpful SQL for those using Calculation Views is searching for a specific Field, Node or Formula, etc.

    — Search Calculation View Content
    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
    ;

    (0) 
  2. Bernhard Sauerteig

    Thank you Marcel, for sharing the CalcView XML search statement. That can be very useful for sure.

    However, I don’t really get why you need a type conversion in order to read the result? Displaying the cell content e.g. of “STATEMENT_STRING” should work fine in HANA Studio SQL Console out of the box. Just make sure that you go to Preferences/SAP HANA/Runtime/Result where you can activate “Enable zoom of LOB columns” and also set “Limit for LOB Columns” and “Limit for Zoom” to a reasonable size. But even if you need the type conversion anyway; why cast to binary and then string first – couldn’t you simply directly use to_varchar(“STATEMENT_STRING”)?

    Anyway, I frequently also use the table ACTIVE_OBJECTCROSSREF. It is very useful to create custom where-used queries for tables, views, procedures, functions, roles, privileges…

    Here is a simple example:

    –Show CalcViews that use TableFunctions
    select * from “_SYS_REPO”.”ACTIVE_OBJECTCROSSREF”
    where “FROM_OBJECT_SUFFIX” = ‘calculationview’
    and “TO_OBJECT_SUFFIX” = ‘hdbtablefunction’

    Best regards,
    Bernhard

    (0) 

Leave a Reply