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:

SCHEMA_NAME

NVARCHAR(256)

The object’s database schema name

OBJECT_NAME

NVARCHAR(256)

The name of the container object

OBJECT_TYPE

VARCHAR(32)

The type of the container object

IS_VALID

NVARCHAR(5)

The object’s validity (“TRUE” or “FALSE”)

PATH

NVARCHAR(511)

A single path is either a fully qualified path to the deployed file (for example, /path/to/a/file.txt‘)

CREATE_TIMESTAMP_UTC

TIMESTAMP

Time stamp indicating when the deployed file was created

MODIFICATION_TIMESTAMP_UTC

TIMESTAMP

Time stamp indicating when the deployed file or folder was last modified

SIZE

BIGINT

Size of the listed deployed file in bytes

CONTENT

BLOB

Content of the deployed file

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.