Cleaning TEMP GROUPS and LSW_USR_GRP_MEM_XREF

There is a procedure available which cleans the TEMP Groups which deletes all temporary groups that are no longer referenced by a task.

LSW_ERASE_TEMP_GROUPS

I believe, we can use this procedure to clean all the unwanted groups and group member references from DB.

Please Note that, this procedure is already included in ‘LSW_BPD_INSTANCE_DELETE’ and ‘LSW_ERASE_TASK’, so if we are deleting instances or task using  stored procedure it automatically calls this proc.

Content of Procedure is as below(just if anyone is curious) – 

CREATE PROCEDURE LSW_ERASE_TEMP_GROUPS

(

)

LANGUAGE SQL

  -- This procedure deletes all temporary groups that are no longer referenced by a task. --

  BEGIN

 

    DECLARE groupId DECIMAL

(

   12,0

)

;

DECLARE sqlCmd VARCHAR(500)

;

DECLARE at_end DECIMAL(1,0)

;

DECLARE not_found CONDITION for SQLSTATE '02000'

;

DECLARE CONTINUE HANDLER for not_found SET at_end = 1

;

BEGIN

        DECLARE groupsToDelete CURSOR FOR stmt1

;

SET sqlCmd = 'SELECT DISTINCT g.GROUP_ID FROM LSW_USR_GRP_XREF g WHERE NOT EXISTS (SELECT 1 FROM LSW_TASK t WHERE g.GROUP_ID = ABS(t.GROUP_ID)) AND g.GROUP_TYPE = 2'

;

PREPARE stmt1

FROM sqlCmd

;

OPEN groupsToDelete

;

DELETE_GROUP_LOOP:

        LOOP

            SET at_end = 0

;

FETCH groupsToDelete INTO groupId

;

IF (at_end = 1) THEN

                LEAVE DELETE_GROUP_LOOP

;

END IF

;

DELETE

FROM LSW_USR_GRP_MEM_XREF

WHERE GROUP_ID = groupId

;

DELETE

FROM LSW_USR_GRP_XREF

WHERE GROUP_ID = groupId

;

END LOOP

;

CLOSE groupsToDelete

;

END

;

END

;