BizStore problems and stale reporting data – a rare, but showstopper issue

Have your end-users raised tickets that their Dashboards are not updating or that they are getting stale data in some interfaces? If yes, this blog provides a possible root cause and a work around to resolve such issues.

The event processing engine of Savvion BPM is very robust and populates reporting information using the data from events generated from the execution of processes deployed on the server. Some examples of BPM Events are as follows.

• Process Template Events: Install, Uninstall, Refresh, Reinstall etc.
• Process Instance Events: Creation, Activation, Completion etc.
• Workstep Events: Activation, Completion, Assignment etc.
• Dataslot Events: Dataslot value updation

These events get stored into the BIZEVENT table before processing. The BizStore Event Processing engine is responsible for picking up the events from the BIZEVENT table and processing them sequentially.

Under certain circumstances, the BizStore server stops processing events and that in turn results in the reporting tables (populated by BizStore) getting out-of-sync with the BizLogic process engine. Prior to the SBM 7.X versions, this would happen due to missing events in the BIZEVENT table. This problem has been resolved in the SBM 7.X or later versions of the product. The BizStore server now does a few retries to wait for the missing events and then assumes that those events will not be received, logs them in the missing event database table and proceeds with the next available event in the BizEvent table. This ensures that the BizStore server does not get stuck and continues processing events.

However, it has been observed that the BizStore server may still get stuck under certain circumstances – for example, sometimes the process instance tables get corrupted during process installation/uninstallation. This is more likely to happen if your process is using a monitoring workstep.

If your reports, dashboards or task interfaces are using BizStore reporting tables, users are bound to get stale data and raise issues. Below are the steps to confirm if your BizStore server is stuck as well as the steps (workaround) to resolve the problem.

How to identify if the BizStore server is stuck?

Step 1: When end users report a problem similar to one described above, please check the BizStore and BizLogic log files to see if it is giving an error while processing any event.

Step 2: Then execute the below three queries to confirm that the server is not processing any further events:

SELECT MAX(EVENT_ID) FROM BIZEVENT;

SELECT LAST_EVENT_ID FROM BIZSTOREEVENTCOUNTER;

• If the result for the above queries is the same, then there is no issue and the server is not stuck.
• If the result is not the same, then you should observe if the LAST_EVENT_ID from the BIZSTOREEVENTCOUNTER table is increasing periodically.
• If the LAST_EVENT_ID from the BIZSTOREEVENTCOUNTER table is not increasing for a long time (Ex. >5 minutes) while there are business transactions on the Savvion system, it is an indication that the BizStore event processing is stuck and you have hit a major problem.

Workaround for resolving the “Missing Event” issue:

Alternative 1:

• Examine the details of the event on which the BizStore server is stuck. If that event is related to a process template that you just reinstalled, then you can clean up all the data related to that process template from the database(you don’t need that data anyway since you are reinstalling the template) as follows.
• Shutdown the SBM Servers (all cluster nodes) and take the database backup.
• If your process template id is say 100 then delete all the rows that correspond to that template id from these tables: BIZLOGIC_PROCESSTEMPLATE, BIZLOGIC_PROCESSINSTANCE, BIZLOGIC_WORKSTEPINSTANCE, BIZLOGIC_WORKITEM, BIZLOGIC_CWORKITEM, BIZLOGIC_CSTEP, BIZLOGIC_PROCESS_NOTES, BIZLOGIC_WAITWORKSTEP, PROCESSXML, BIZLOGIC_TIMERACTION, BIZLOGIC_PT_REPLACE_INFO, BIZLOGIC_DOCDS_FOLDER, PROCESSRESOURCEINFO, BIZEVENT, SBM_MESSAGES, STAT_APP_AVG_DURATION, STAT_APP_PRIORITY, STAT_APP_DUE_INFO, STAT_APP_STATUS_HISTORY, STAT_WI_DUE_INFO, PI_MIGRATION_FILTER, PROCESSTEMPLATE, PROCESSWORKSTEPINFO, PROCESSINSTANCE, SUBPROCESSINFO, WORKSTEP, WORKITEM, PROCESSDATAINFO, WORKSTEPVOTEINFO, CWORKITEM, CSTEP, CSTEP_DS, PROCESS_NOTES, WI_RESOURCE_USAGE & PROCESS_STATUS

• Drop the table BIZLOGIC_DS_100, BS_100 and <application_table>
• Start the SBM Servers.
• Observe if the LAST_EVENT_ID is increasing in the BIZSTOREEVENTCOUNTER table.
• Keep checking until the max (EVENT_ID) from the BIZEVENT table is the same as the LAST_EVENT_ID from the BIZSTOREEVENTCOUNTER table.

Alternative 2:

• If the stuck event is not related to a process template that you just reinstalled then you can do the following
• Shutdown the SBM Servers (all cluster nodes) and take the database backup
• Update the BIZSTOREEVENTCOUNTER with the next available event in the BIZEVENT table. Make sure you review the event details to confirm that there would be no significant data loss if the processing for that event would be skipped. You may have to update that data manually in the reporting tables.
• Start the SBM Servers
• Observe if the LAST_EVENT_ID is increasing in the BIZSTOREEVENTCOUNTER table
• Keep checking until the max (EVENT_ID) from the BIZEVENT table is the same as the LAST_EVENT_ID from the BIZSTOREEVENTCOUNTER table.