Why is my WORKFLOW_LINKAGE table growing?

The WORKFLOW_LINKAGE table in Sterling B2B Integrator contains the Workflow parent-child information. It has records containing BPs identified by their workflow IDs (WF_ID) and parents BPs workflow IDS (ROOT_WF_ID).

The old records in this table are cleaned by the Linkage Purge Service. Check other B2B Integrator housekeeping processes here.

The Linkage Purge (Exact name: Schedule_BPLinkagePurgeService ) runs only once a day and deletes only 200 records each run by default. 

On busy systems, you need to run this service more frequently and with increasing the max_business_processes in the source of the BP.

Best practices:

• Change the schedule to run more frequently. The name of the schedule is  Schedule_BPLinkagePurgeService.

• Increase max_business_processes in the linkage_purge BP from 200 to higher value (50000 in the example below).

 
 
 
50000 
500 
 
 
 
 
 
 

• Monitor the Schedule execution time.
• Get the Execution plan of the SQL query used by the Purge Linkage service. And add indexes if needed.
• The SQL query used can be found in the properties : selectWorkflowLinkageCandidates . Search for linkage : jdbc.properties:query.selectWorkflowLinkageCanidates.oracle=SELECT DISTINCT A.ROOT_WF_ID, SUM( CASE WHEN B.WF_ID IS NOT NULL THEN 1 ELSE 0 END ) FROM WORKFLOW_LINKAGE A LEFT OUTER JOIN ARCHIVE_INFO B ON (( A.ROOT_WF_ID = B.WF_ID OR A.C_WF_ID = B.WF_ID) AND GROUP_ID = 1) GROUP BY A.ROOT_WF_ID HAVING SUM( CASE WHEN B.WF_ID IS NOT NULL THEN 1 ELSE 0 END ) = 0

• Analyse the query execution plan and tune.
• You can run the Linkage Purge BP more frequently until you clear the backlog.

Oracle procedure to delete old records from WORKFLOW_LINKAGE table:

You can also delete the old records from this table using an external SQL procedure to speed up the cleanup if needed.

I share here an Oracle PL/SQL stored procedure that helps with cleaning old records from the WORKFLOW_LINKAGE table (older than a certain number of days: 30 in this example) in small batches (10000 in this example):

-- STORED PROCEDURE TO DELETE OLD RECORDS FROM WORKFLOW_LINKAGE TABLE
-- DDL TO CREATE DEBUGGING TABLE -----> PLEASE RUN FOR THE FIRST TIME: ------->
-- create TABLE CLEANUP_DEBUG(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESCRIPTION VARCHAR2(60)); 
-- to clean the debug table add 
-- truncate table CLEANUP_DEBUG; 

DECLARE
nCount  NUMBER:=0;
iCount  NUMBER:=0;
wfLimit1 NUMBER:=0;
batchSize NUMBER:=10000;

sql1 VARCHAR2(2000);
starttime timestamp;


BEGIN

EXECUTE IMMEDIATE 'select max(WORKFLOW_ID) from YOURUSER.WORKFLOW_CONTEXT where END_TIME < sysdate - 30' into wfLimit1;

sql1 := 'delete from YOURUSER.WORKFLOW_LINKAGE where ROOT_WF_ID < ' || wfLimit1 || '  and rownum <= ' || batchSize;

LOOP
starttime:=sysdate;
EXECUTE IMMEDIATE sql1;

   nCount := sql%rowcount;
    iCount:=iCount+nCount;

insert into CLEANUP_DEBUG values (batchSize,iCount,starttime, sysdate,'DELETE old linkage records '); 
    commit;

   EXIT WHEN nCount = 0;

END LOOP;

end;

Other housekeeping related posts:

B2B Integrator Housekeeping jobs explained.

TRANS_DATA PL/SQL procedure to remove old records.