UCA disappearing from Event Monitor or Corrupted UCA problem in IBM BPM - Solved

Problem Description - 

==============

Facing issues with Event Monitor related to UCA scheduling , its behaving weird the entries are randomly disappearing from the scheduler .

Even after re-enabling(checking unchecking from the application side) , the UCA appear in event Monitor for short time and disappear after one run or after some time.

Reason for Problem -

=============

When a time-based UCA is scheduled, an event manager task in the

LSW_EM_TASK table and two keywords in the LSW_EM_TASK_KEYWORDS 

table are created. These keywords are 'UCA4.<uca-id>' and 

'scheduledExec'. On rare occasions, you might see event manager 

tasks for time-based UCAs that have only the 'scheduledExec' 

keyword. When a UCA is rescheduled, the keywords are used to 

delete the previous event manager task.  However, having only 

the 'scheduledExec' keyword for a particular UCA could cause all 

other time-based UCAs to be deleted during the rescheduling 

process.

In our case we believed reason for this problem was due to our DB file system was running at 100% at couple of times.

Solution -

======

The proposed solution is to manually delete the corrupted task from the database. This must be done carefully in order to not corrupt the whole database. The following queries should be executed at times     

where they do not interfere with the scheduling time of the UCA, i.e. not at full hours, or 5,15,25,35,45 or 55 minutes after full hours or you UCA is about to get executed.

There are can be two type of problem as one of the keyword for perticular UCA is not present. I will explain both scenarios.

Scenario 1 -

------------

1.) Retrieve TASK_IDs for scheduled UCAs that have only the 'scheduledExec' keyword, but not the uca id keyword:

select task_id from lsw_em_task_keywords where keyword = 'scheduledExec' and task_id not in ( select task_id  from lsw_em_task_keywords where keyword like 'uca4%')

2.) Display description and ID of ucas returned by query2:  

select description,task_arguments_str from lsw_em_task where task_id in(select task_id from lsw_em_task_keywords where keyword = 'scheduledExec' and task_id not in ( select task_id  from lsw_em_task_keywords where keyword like 'uca4%'))

3.) If the result of query 2 shows only tasks with description "Execute UCA <UCA_Name> <Schedule>, on set schedule", then you can delete the corrupt UCA and keyword with the following queries:  

3.1) Delete corrupted lsw_em_task: 

delete from lsw_em_task where task_id in (select task_id from lsw_em_task_keywords where keyword = 'scheduledExec' and task_id not in (select task_id  from lsw_em_task_keywords where keyword like 'uca4%'))

3.2) Delete corrupted lsw_em_task_keyword:

delete from lsw_em_task_keywords where task_id in (select task_id from lsw_em_task_keywords where keyword = 'scheduledExec' and task_id not in (select task_id  from lsw_em_task_keywords where keyword like 'uca4%'))

Scenario 2 -

------------

1.) Retrieve TASK_IDs for scheduled UCAs that have only the  uca id keyword, but not the 'scheduledExec' keyword:

select task_id from lsw_em_task_keywords where keyword = 'uca4%' and task_id not in ( select task_id  from lsw_em_task_keywords where keyword like 'scheduledExec')

2.) Display description and ID of ucas returned by query2:

select description,task_arguments_str from lsw_em_task where task_id in (select task_id from lsw_em_task_keywords where keyword = 'uca4%' and task_id not in ( select task_id  from lsw_em_task_keywords where keyword like 'scheduledExec'))

3.) If the result of query 2 shows only tasks with description " Execute UCA <UCA Name>, on set schedule",then you can delete the corrupt UCA and keyword with the following queries:  

3.1)  Delete corrupted lsw_em_task:

delete from lsw_em_task where task_id in (select task_id from lsw_em_task_keywords where keyword = 'uca4%' and task_id not in (select task_id  from lsw_em_task_keywords where keyword like 'scheduledExec'))                                                       

                                                                        

3.2) Delete corrupted lsw_em_task_keyword:

delete from lsw_em_task_keywords where task_id in (select task_id from lsw_em_task_keywords where keyword = 'uca4%' and task_id not in (select task_id  from lsw_em_task_keywords where keyword like 'scheduledExec'))       

You could also only delete the corrupt task keyword with Query4. APAR JR47574 will then take care, that the associated UCA task is not executed and not rescheduled