Housekeeping of HDI containers in developer workspaces


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 (see my previous blog on the subject ).
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).

Conclusion

This program can help you to save some space in your development system. Even if you run it in test mode only, it provides you with interesting figures about developer HDI containers.