MS SQL Server T-SQL helper procedures for Sterling B2B Integrator DB

TRANS_DATA and WORKFLOW_CONTEXT are one of the most important tables in the IBM Sterling B2B Integrator Database. In the case of an infinite looping Business Process (BP) with thousands of steps or even millions of steps, the Purge process will not be able to delete the related BP data without locking the entire TRANS_DATA and WORKFLOW_CONTEXT tables or even the entire Database (When the memory allocated to the Database is limited). Check my article about MS SQL Server best practices explaining lock escalations.

In this article, I will share some useful MS SQL Server procedures used in the case of looping (very large) BPs to help with deleting the associated records.

First, to find looping/large BPs, you can use this SQL query that will return all the BPs that have a number of steps larger than a certain value: 10000 as an example:
SELECT DISTINCT WF_ID , count(WF_ID) as steps
FROM TRANS_DATA
GROUP BY WF_ID HAVING COUNT(WF_ID) > 10000
ORDER BY COUNT(WF_ID) DESC

SELECT DISTINCT WORKFLOW_ID , count(WORKFLOW_ID) as steps
FROM WORKFLOW_CONTEXT
GROUP BY WORKFLOW_ID HAVING COUNT(WORKFLOW_ID) > 10000
ORDER BY COUNT(WORKFLOW_ID) DESC

Remember, MS SQL Server can escalate Database locks from a fine-grained locks like row and page locks into table locks when a transaction acquires more than 5000 locks on a single table.  When housekeeping processes like Purge, AssociateBPToDocs try to delete or update large number of records, table locks may occur and will affect the application performance. This will result in queueing, hanging and slowness in the application.

The following useful T-SQL procedures will delete the records related to a large BP in small batches to avoid lock escalations and large transaction logs.

The first procedure deals with TRANS_DATA and will delete the record associated to a given workflow (change 00000000 to your large WF_ID):

--Batch deleting for large BPs for MS SQL server
-- TRANS_DATA
-- IBM B2B Sterling Integrator

DECLARE @Counter INT
DECLARE @BatchSize INT
DECLARE @LatestWorkflowID INT
SET @Counter = 0
-- Variables to customize
SET @BatchSize=1000
SET @LargeWorkflowID=00000000
WHILE EXISTS (SELECT * FROM TRANS_DATA
WHERE WF_ID = @LargeWorkflowID )
BEGIN
BEGIN TRANSACTION
DELETE TOP(@BatchSize) FROM TRANS_DATA
WHERE WF_ID = @LargeWorkflowID
SET @Counter = @Counter + @BatchSize
COMMIT
PRINT 'ROWS PROCESSED '+ CONVERT(VarChar(10),@Counter)
END
GO

The second procedure will delete the records of a large given BP from the WORKFLOW_CONTEXT table. Only use this procedure when you have large BPs with more than 20k+ steps. Think also about rewriting these large BPs to reduce the number of steps or reduce their persistence level.

--Batch deleting for large BPs for MS SQL server
-- WORKFLOW_CONTEXT
-- IBM B2B Sterling Integrator
DECLARE @Counter INT
DECLARE @BatchSize INT
DECLARE @StepTrimSize INT
DECLARE @LatestWorkflowID INT
SET @Counter = 0
-- Variables to customize
SET @BatchSize=1000
SET @LargeWorkflowID=00000000
WHILE EXISTS (SELECT * FROM WORKFLOW_CONTEXT
WHERE WORKFLOW_ID = @LargeWorkflowID)

BEGIN
BEGIN TRANSACTION
DELETE TOP(@BatchSize) FROM WORKFLOW_CONTEXT
WHERE WORKFLOW_ID = @LargeWorkflowID
SET @Counter = @Counter + @BatchSize
COMMIT
PRINT 'ROWS PROCESSED '+ CONVERT(VarChar(10),@Counter)
END
GO

Remember to run SQL queries returning large result-sets OUTSIDE of the B2B Integrator application SQL manager. Use external tools like MS SQL SERVER MANAGEMENT STUDIO..

Check more useful SQL queries:

• Useful SQL queries for Sterling B2B Integrator.
• TRANS_DATA related SQL queries.
• Workflow statuses explained with SQL queries: Active, Halted, Halting BPs …
• Filegateway related SQL queries.
• Communication and adapter stats SQL.
• Slow services and steps SQL.

If you are struggling to find some information in the B2B Integrator Database, please post a comment below. You can also share your useful SQL for Integrator!