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:

• the view M_OBJECTS
• the procedure READ_DEPLOYED

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.