Introduction
When ever a developer is doing local build of a db module in WebIDE for HANA, it creates an HANA service instance that name looks like <USER><GUID><MODULE_NAME>.
You can see those HANA service instances via XSA Cockpit
For each HANA service instance, there is a specific schema with suffix _1 for the first developer, _2 for the second and so on…
Over the time, the number of such local developer HDI containers will continuously grow and depending on how many developers and projects you have on your development system, it may increase a lot.
For the time being, SAP does not provide any standard program to make housekeeping of those HDI containers and so, I have developed a custom one that I wanted to share the coding in this blog.
Code description
The program consists of one main procedure relying on 2 sub-procedures that will launch some xs command via SDI File Adapter.
The first sub-procedure allows to delete a service instance. It is based on the xs delete-service command
PROCEDURE "hdi::SP_DELETE-SERVICE"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "SERVICE" VARCHAR(1024))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
DECLARE "OUTPUT" TABLE (RESULT NVARCHAR(1073741823));
CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs delete-service ''' || :SERVICE || ''' -f', OUTPUT);
END
The second sub-procedure allows to get the schema name of an hana service instance. The information is stored in service keys of the service and can be retrieved by parsing the json result of the command xs service-key (alias sk)
PROCEDURE "hdi::SP_GET_SCHEMA"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "SERVICE" VARCHAR(1024), OUT SCHEMA_NAME NVARCHAR(256))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
DECLARE "OUTPUT" TABLE (RESULT NVARCHAR(1073741823));
CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs sk ''' || :SERVICE || ''' SharedDevKey | sed ''1,3d'' | head -n -3', OUTPUT);
SELECT JSON_VALUE("RESULT", '$.schema') AS "SCHEMA_NAME" INTO SCHEMA_NAME FROM :OUTPUT;
END
Having the schema name is useful to be able to call the LIST_DEPLOYED HDI API via dynamique SQL
PROCEDURE "hdi::SP_LIST_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"
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS BEGIN
EXEC 'CALL ' || :HDI_SCHEMA_NAME || '#DI.LIST_DEPLOYED( :PATHS, :PARAMETERS, :RETURN_CODE, :REQUEST_ID, :MESSAGES, :RESULT)' INTO RETURN_CODE, REQUEST_ID, MESSAGES, RESULT USING :PATHS, :PARAMETERS;
END;
The main procedure has the following input parameters:
◉ Organization & Space to be cleaned
◉ retention_days that is the number of days above which a developer hdi container get deleted if no objects get modified.
◉ testmode and persist_result are just for debug purpose
◉ dev_workspaces output table provide the list of developer hdi containers and a flag “to_be_deleted” that means it is eligible to be deleted.
Here is now the coding of main procedure that can be divided into 3 steps:
step 1
It creates a cursor based on several tables from schema SYS_XS_RUNTIME. Note that this system schema is not documented by SAP meaning that I had to make some hypotheses that may not be valid depending of HANA version ( In my case, I was on HANA 2.0 SPS05)
One of these hypotheses is that developer HANA services instances can be identified in table SYS_XS_RUNTIME.STOREDSERVICEINSTANCE thanks to the following WHERE clause :
"PARAMETERS" LIKE '%"makeUniqueName":true%'
step 2
For each of the service instances, I’m getting the list of objects in order to identify the last time there was a modification. This allows to identify if the service instance is eligible to be deleted.
step 3
In this last step, I’m deleting all eligible service instances identified previously.
PROCEDURE "hdi::SP_DEV_WORKSPACES_CLEAN" (IN "ORGANIZATION" VARCHAR(1024),
IN "SPACE" VARCHAR(1024),
IN RETENTION_DAYS INT,
IN TESTMODE BOOLEAN,
IN PERSIST_RESULT BOOLEAN,
OUT DEV_WORKSPACES TABLE(
"NAME" NVARCHAR(255),
"SCHEMA_NAME" NVARCHAR(256),
"CREATE_TIMESTAMP_UTC" LONGDATE,
"MODIFICATION_TIMESTAMP_UTC" LONGDATE,
"TO_BE_DELETED" BOOLEAN
))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
BEGIN
-- step 1
DECLARE CURSOR C_STOREDSERVICEINSTANCES FOR
SELECT A."GUID", A."NAME", ADD_SECONDS('1970-01-01',A."CREATEDAT"/1000) AS "CREATE_TIMESTAMP_UTC"
FROM "SYS_XS_RUNTIME.STOREDSERVICEINSTANCE"() A
INNER JOIN "SYS_XS_RUNTIME.STOREDSPACE"() B
ON A.SPACEGUID = B.GUID
INNER JOIN "SYS_XS_RUNTIME.STOREDORGANIZATION"() C
ON B.ORGANIZATIONGUID = C.GUID
WHERE DAYS_BETWEEN(ADD_SECONDS('1970-01-01',A."CREATEDAT"/1000),CURRENT_DATE) > :RETENTION_DAYS
AND A."PARAMETERS" LIKE '%"makeUniqueName":true%'
AND C.NAME=:ORGANIZATION
AND B.NAME=:SPACE;
DECLARE SCHEMA_NAME NVARCHAR(256);
DECLARE MESSAGES "hdi::TT_MESSAGES";
DECLARE RETURN_CODE INT;
DECLARE REQUEST_ID BIGINT;
DECLARE RESULT "hdi::TT_FILESFOLDERS_METADATA";
DECLARE MODIFICATION_TIMESTAMP_UTC LONGDATE;
CREATE LOCAL TEMPORARY COLUMN TABLE #PARAMETERS LIKE "hdi::TT_PARAMETERS";
INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ('recursive', 'true');
VAR_T_NO_FILESFOLDERS = select * from "hdi::T_NO_FILESFOLDERS";
VAR_PARAMETERS = select * from #PARAMETERS;
-- step 2
FOR SERVICEINSTANCE AS C_STOREDSERVICEINSTANCES
DO
CALL "hdi::SP_GET_SCHEMA"(
ORGANIZATION => :ORGANIZATION,
SPACE => :SPACE,
SERVICE => :SERVICEINSTANCE."NAME",
SCHEMA_NAME => SCHEMA_NAME );
IF :SCHEMA_NAME IS NOT NULL THEN
CALL "hdi::SP_LIST_DEPLOYED"(
HDI_SCHEMA_NAME => :SCHEMA_NAME,
PATHS => :VAR_T_NO_FILESFOLDERS,
PARAMETERS => :VAR_PARAMETERS,
RETURN_CODE => RETURN_CODE,
REQUEST_ID => REQUEST_ID,
MESSAGES => MESSAGES,
RESULT => RESULT );
SELECT MAX("MODIFICATION_TIMESTAMP_UTC") INTO MODIFICATION_TIMESTAMP_UTC FROM :RESULT;
ELSE
MODIFICATION_TIMESTAMP_UTC = NULL;
END IF;
DEV_WORKSPACES = SELECT * FROM :DEV_WORKSPACES UNION ALL
SELECT :SERVICEINSTANCE.NAME,
:SCHEMA_NAME,
:SERVICEINSTANCE.CREATE_TIMESTAMP_UTC,
:MODIFICATION_TIMESTAMP_UTC,
CASE
WHEN DAYS_BETWEEN(IFNULL(:MODIFICATION_TIMESTAMP_UTC,:SERVICEINSTANCE.CREATE_TIMESTAMP_UTC),CURRENT_DATE) > :RETENTION_DAYS
THEN True
ELSE False
END
FROM "SYS.DUMMY";
END FOR;
IF :PERSIST_RESULT = true THEN
DELETE FROM "hdi::T_DEV_WORKSPACES";
INSERT INTO "hdi::T_DEV_WORKSPACES" SELECT * FROM :DEV_WORKSPACES;
END IF;
-- step 3
IF :TESTMODE = false THEN
BEGIN
DECLARE CURSOR DEV_WORKSPACES_TO_BE_DELETED FOR
SELECT *
FROM :DEV_WORKSPACES
WHERE TO_BE_DELETED = true;
FOR DEV_WORKSPACE AS DEV_WORKSPACES_TO_BE_DELETED
DO
CALL "hdi::SP_DELETE-SERVICE"(
ORGANIZATION => :ORGANIZATION,
SPACE => :SPACE,
SERVICE => :DEV_WORKSPACE."NAME" );
END FOR;
END;
END IF;
DROP TABLE #PARAMETERS;
END;
test and decide on retention time
Before scheduling the procedure, you can launch it a first time in test mode and store the result in a table having the following structure:
COLUMN TABLE "hdi::T_DEV_WORKSPACES" ("NAME" NVARCHAR(255),
"SCHEMA_NAME" NVARCHAR(256),
"CREATE_TIMESTAMP_UTC" LONGDATE CS_LONGDATE,
"MODIFICATION_TIMESTAMP_UTC" LONGDATE CS_LONGDATE,
"TO_BE_DELETED" BOOLEAN
) UNLOAD PRIORITY 0 AUTO MERGE
Then, the following query gives the number of services that will be deleted depending on the retention time.
SELECT NB_J AS RETENTION_DAYS, MAX(ROWNUM) FROM
(SELECT NB_J, ROW_NUMBER() OVER(ORDER BY NB_J DESC) AS ROWNUM FROM
(SELECT
DAYS_BETWEEN(IFNULL("MODIFICATION_TIMESTAMP_UTC","CREATE_TIMESTAMP_UTC"),CURRENT_DATE) AS NB_J
FROM "Z1A00_PLATFORM_UTILITIES"."hdi::T_DEV_WORKSPACES"))
GROUP BY NB_J
ORDER BY NB_J DESC
Here is what it was look like graphically in my case.
Based on this result, I decided to go for a retention time of 84 days (12 weeks). At the first run, it deleted half the containers (around 250)
No comments:
Post a Comment