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

;

Different Type of groups in IBM BPM

In IBM BPM, All user groups are stored in LSW_USR_GRP_XREF table. there are different types of group in IBM BPM and they are differentiated by a column 'GROUP_TYPE' in 'LSW_USR_GRP_XREF' table

There are 3 type of user groups in IBM BPM.

1. Groups that exist and managed in external directory services like LDAP. Type 0 Groups
2. Groups that are created in the environment through Admin Console. Type 3 groups
3. Groups that are dynamically created by virtue of task allocation. type 1 and 2 Groups

Groups that exist and managed in external directory services like LDAP

Such groups are created and managed in external directory services like LDAP. Such groups are imported into the BPM database when an user logs in for the first time. Such groups have a GROUP_TYPE as "0" in the table LSW_USR_GRP_XREF. Such groups cannot be edited or deleted through the default Process Admin Console. They needs to be changed from external directory like LDAP.

Groups that are created in the environment through Admin Console.

These groups are created and managed through the Process Admin Console. Since these groups are not represented in the centralized directory services like LDAP, these are purposed to be used inside BPM applications only. These are analogous to TeamWorks logical roles in TeamWorks 6.2. These groups have a GROUP_TYPE as "3" in the table LSW_USR_GRP_XREF.

Groups that are dynamically created by virtue of task allocation.

Groups are dynamically created when a new task is routed to a lane participant (Participant Group) or assigned to a list of users (Ad-hoc group). Dynamic groups corresponding to TeamWorks logical roles are also created during import from a 6.2 environment. These groups are not visible on the Process Admin console, hence cannot be managed. Dynamic groups that are created from Participant Groups or Logical Roles have GROUP_TYPE as "1" in the table LSW_USR_GRP_XREF, where as the dynamic groups crated from list of users have GROUP_TYPE as "2" in the table LSW_USR_GRP_XREF.