HDI, get objects details

[Cocquerel]

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:

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.