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;
◉ 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
No comments:
Post a Comment