Resolution:
Description:
============
Steps to purge all open and timed out workitems in an enterprise.

Environment:
==========
TIBCO Product name and version: TIBCO Collaborative Information Manager 8.0.1
Operating System(s): All Operating Systems

Symptoms:
========
N/A

Cause:
=====

N/A


Resolution:
========

The following are the steps required to purge the workitems. Since these steps involve database table update statements, make a backup or dump of the database before carrying out them out. Once the steps are completed, stop the application server, purge all CIM related EMS queues and restart the application server. We recommend that this be done only on development environments.

1). The first step is to identify the enterprise id.

    SELECT * FROM ENTERPRISE WHERE NAME = &ltENTERPRISE NAME>

2). Identify the member ids of all the users belonging to the enterprise.

    SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>

3). Get the workitem IDs of all open and timedout workitems in the enterprise.

    SELECT ID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT')

4). Get the PROCESSLOGIDs of events which created the workitems.

    SELECT DISTINCT PROCESSLOGID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT')

5). Get the EVENTIDs of events which created the workitems.

    SELECT EVENTID FROM PROCESSLOG WHERE ID IN (SELECT DISTINCT PROCESSLOGID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT'))

6). Set the status of the PROCESSLOG entries to SUCCESS.

    UPDATE PROCESSLOG SET STATUS = 'SUCCESS' WHERE ID IN (SELECT DISTINCT PROCESSLOGID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT'))

7). Set the status of PRODUCTLOG entries to SUCCESS.

    UPDATE PRODUCTLOG SET STATUS = 'SUCCESS' WHERE EVENTID IN (SELECT EVENTID FROM PROCESSLOG WHERE ID IN (SELECT DISTINCT PROCESSLOGID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT')))

7). Set the status of the EVENT entries to SUCCESS.

    UPDATE EVENT SET STATUS = 'SUCCESS' WHERE ID IN (SELECT EVENTID FROM PROCESSLOG WHERE ID IN (SELECT DISTINCT PROCESSLOGID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT')))

8). Set the status of open workitems to closed.

    UPDATE WORKITEM SET STATUS = 'CLOSED' WHERE ID IN (SELECT ID FROM WORKITEM WHERE OWNERID IN (SELECT ID FROM MEMBER WHERE ENTERPRISEID = &ltENTERPRISE ID FROM 1>) AND STATUS IN ('OPEN', 'OPEN_TIMEDOUT'))


Attachments:
==========
N/A

References:
=========
N/A


Keywords/Tags:
=============
workitem clean up

Steps to purge all open and timed out workitems in an enterprise.