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
;
