Using cleanupUnusedStaffQueryInstances.py results in delayed deletion of unused shared work items

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-03-13 12:15

When you use the cleanupUnusedStaffQueryInstances.py administrative script to remove unused shared work items, you might experience delayed deletion or deletion failures for large volumes of unused work items in the SWI_T table.

The following messages are written to the SystemOut.log file during manual execution of cleanupUnusedStaffQueryInstances.py script or while using the automatic cleanup daemon:

WICleanup I ### earlyBreak due to 80% of transaction timeout is reached: txStartTime = 1742005907584 = Fri Mar 14 22:31:47 EDT 2025, now = 1742006012839 = Fri Mar 14 22:33:32 EDT 2025, txTimeout = 120000 => 96000 ###

Even after running the cleanupUnusedStaffQueryInstances.py script for several hours, only a small number of records in the SWI_T table might be removed. This indicates that the database is taking a long time to complete the updates and that the script keeps interrupting the transaction and rolls back the changes.

If the deletion of unused shared work items is slow, you can improve the performance by adding a new database index on the table SWI_T for the columns PARENT_WIID and WSID. The following example shows the Db2® command to create INDEX:

   CREATE INDEX "<schema>"."IDX2503221931410" ON "<schema>"."SWI_T"

   ("PARENT_WIID" ASC, "WSID" DESC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;