HDI, get objects details

Introduction

When working with HANA Repository, you may be used to query the table “_SYS_REPO”.”ACTIVE_OBJECT” to get details about design-time objects.

With HDI, the same information is now split by containers and you have to query 2 objects:

For some use cases, it would be convenient to be able to query all HDI containers at once and get information from M_OBJECTS and READ_DEPLOYED at the same time.

For this purpose, I have developed a custom procedure called “Z1A00_PLATFORM_UTILITIES”.”hdi::SP_OBJECTS”

Parameters

HDI_SCHEMA_NAME_REGEXPR [IN]

The procedure has an input parameter HDI_SCHEMA_NAME_REGEXPR that allows to restrict the HDI containers you want to look for and get better performance.

HDI_SCHEMA_NAME_REGEXPR has to follow regular expression syntax. As examples:

• ‘^ABC’ allows to get containers that starting by ABC string
• ‘SCHEMA1|SCHEMA2|SCHEMA3’ allows to get containers SCHEMA1, SCHEMA2 and SCHEMA3

OBJECTS_LIST [OUT]

The output is a table containing the following information:

Examples of usage

The following example shows how to retrieve details for objects belonging to containers matching the regular expression pattern ^ABC

CALL "Z1A00_PLATFORM_UTILITIES"."hdi::SP_OBJECTS"( HDI_SCHEMA_NAME_REGEXPR => '^Z1A00'/*<NVARCHAR(5000)>*/, OBJECTS_LIST => ? );

The following example shows all invalidated objects in container MYCONT

do begin DECLARE OBJECTS_LIST "Z1A00_PLATFORM_UTILITIES"."hdi::TT_OBJECTS"; CALL "Z1A00_PLATFORM_UTILITIES"."hdi::SP_OBJECTS"( HDI_SCHEMA_NAME_REGEXPR => 'MYCONT'/*<NVARCHAR(5000)>*/, OBJECTS_LIST => OBJECTS_LIST); SELECT * FROM :OBJECTS_LIST WHERE IS_VALID = 'FALSE';
end

The following example shows all objects updated during last week in container MYCONT

do begin DECLARE OBJECTS_LIST "Z1A00_PLATFORM_UTILITIES"."hdi::TT_OBJECTS"; CALL "Z1A00_PLATFORM_UTILITIES"."hdi::SP_OBJECTS"( HDI_SCHEMA_NAME_REGEXPR => 'MYCONT'/*<NVARCHAR(5000)>*/, OBJECTS_LIST => OBJECTS_LIST); SELECT * FROM :OBJECTS_LIST WHERE DAYS_BETWEEN( MODIFICATION_TIMESTAMP_UTC, CURRENT_DATE) < 7;
end

The following example parses the SQLScript objects  as well as Calculation properties to get the list of objects using HINTS in all containers

do begin DECLARE OBJECTS_LIST "Z1A00_PLATFORM_UTILITIES"."hdi::TT_OBJECTS"; CALL "Z1A00_PLATFORM_UTILITIES"."hdi::SP_OBJECTS"( HDI_SCHEMA_NAME_REGEXPR => ''/*<NVARCHAR(5000)>*/, OBJECTS_LIST => OBJECTS_LIST); SELECT * FROM :OBJECTS_LIST WHERE ( OBJECT_TYPE='VIEW' AND BINTOSTR(TO_VARBINARY(CONTENT)) LIKE '%executionHints%') OR ( OBJECT_TYPE IN ('PROCEDURE','FUNCTION') AND BINTOSTR(TO_VARBINARY(CONTENT)) LIKE '%WITH HINT%') ;
end

Coding

As prerequisite, the following procedure allows to the procedure READ_DEPLOYED for an HDI  container schema given as parameter

PROCEDURE "hdi::SP_READ_DEPLOYED"( IN HDI_SCHEMA_NAME NVARCHAR(256), IN PATHS "hdi::TT_FILESFOLDERS", IN PARAMETERS "hdi::TT_PARAMETERS", OUT RETURN_CODE INT, OUT REQUEST_ID BIGINT, OUT MESSAGES "hdi::TT_MESSAGES", OUT RESULT "hdi::TT_FILESFOLDERS_METADATA_CONTENT"
) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER
AS BEGIN EXEC 'CALL ' || :HDI_SCHEMA_NAME || '#DI.READ_DEPLOYED( :PATHS, :PARAMETERS, :RETURN_CODE, :REQUEST_ID, :MESSAGES, :RESULT)' INTO RETURN_CODE, REQUEST_ID, MESSAGES, RESULT USING :PATHS, :PARAMETERS; END;

The procedure “hdi::SP_OBJECTS” is looping over a given list of HDI containers and perform the following steps:

• call READ_DEPLOYED
• Join the result of READ_DEPLOYED with M_OBJECTS
• Union all join results

PROCEDURE "hdi::SP_OBJECTS"( IN HDI_SCHEMA_NAME_REGEXPR NVARCHAR(5000), OUT OBJECTS_LIST "hdi::TT_OBJECTS") LANGUAGE SQLSCRIPT SQL SECURITY DEFINER
AS BEGIN DECLARE OBJECTS "hdi::TT_OBJECTS"; DECLARE OBJECTS_ALL "hdi::TT_OBJECTS"; DECLARE MESSAGES "hdi::TT_MESSAGES"; DECLARE RETURN_CODE INT; DECLARE REQUEST_ID BIGINT; DECLARE RESULT "hdi::TT_FILESFOLDERS_METADATA_CONTENT"; DECLARE CURSOR C_CONTAINERS FOR SELECT "CONTAINER_NAME" FROM "__SYS_DI#SYS_XS_HANA_BROKER.M_CONTAINERS" WHERE "CONTAINER_NAME" LIKE_REGEXPR :HDI_SCHEMA_NAME_REGEXPR FLAG 'i'; VAR_T_NO_FILESFOLDERS = select * from "hdi::T_NO_FILESFOLDERS"; CREATE LOCAL TEMPORARY COLUMN TABLE #PARAMETERS LIKE "hdi::TT_PARAMETERS"; INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ('ignore_folders', 'true'); INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ('recursive', 'true'); VAR_PARAMETERS = select * from #PARAMETERS; FOR CONTAINER AS C_CONTAINERS DO CALL "hdi::SP_READ_DEPLOYED"( HDI_SCHEMA_NAME => :CONTAINER."CONTAINER_NAME", PATHS => :VAR_T_NO_FILESFOLDERS, PARAMETERS => :VAR_PARAMETERS, RETURN_CODE => RETURN_CODE, REQUEST_ID => REQUEST_ID, MESSAGES => MESSAGES, RESULT => RESULT ); EXEC 'SELECT ''' || :CONTAINER."CONTAINER_NAME" || ''' AS "SCHEMA_NAME", ' || 'A."OBJECT_NAME" AS "OBJECT_NAME", ' || 'A."OBJECT_TYPE" AS "OBJECT_TYPE", ' || 'A."IS_VALID" AS "IS_VALID", ' || 'B."PATH" AS "PATH", ' || 'B."CREATE_TIMESTAMP_UTC" AS "CREATE_TIMESTAMP_UTC", ' || 'B."MODIFICATION_TIMESTAMP_UTC" AS "MODIFICATION_TIMESTAMP_UTC", ' || 'B."SIZE" AS "SIZE", ' || 'B."CONTENT" AS "CONTENT" ' || 'FROM "' || :CONTAINER."CONTAINER_NAME" || '#DI"."M_OBJECTS" AS A ' 'RIGHT OUTER JOIN :RESULT AS B ' 'ON A."PATH" = B."PATH" ' INTO OBJECTS USING :RESULT; OBJECTS_LIST = select * from :OBJECTS_LIST union all select * from :OBJECTS; END FOR; DROP TABLE #PARAMETERS; END

Conclusion

Hope you will find this procedure useful to have a better view of your HDI objects. Do not hesitate to share other examples of usage.