Housekeeping of HDI containers in developer workspaces

[Cocquerel]

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.