Sterling B2B Integrator workflow statuses explained with SQL queries

In IBM Sterling B2B Integrator, the WORKFLOW_CONTEXT table stores the Business Process ( BP) or Workflow execution steps. It has a BASIC_STATUS column that defines the Workflow status whether it is : Active, Halted, Halting, Terminated, Waiting on IO etc …

In this blog, I explain the different BP statuses and give some useful SQL queries to monitor and troubleshoot B2B Integrator and Sterling Filegateway SFG.

Basic Status column meaning:

• 0 = Success
• 1 = Error
• 2 = Warning
• 3 = Waiting
• 10 = Waiting on IO
• 100 = WFE Halted
• 200 = WFE System Error
• 300 = Configuration Error
• 400 = Interrupted  (For BPs that have been auto-resumed)
• 450 = Interrupted (Manual Intervention Required)
• 900 = Terminated

Integrator WORKFLOW or BP states:

ACTIVE/Running : Last step in branch does not have NOOP or DONE in next activity.

COMPLETED: Last step in branch has NOOP or DONE in next activity and WFE status=0

HALTING: Last step in branch does not have NOOP or DONE in next activity, and an entry exists for the WF instance in the WF_INACTIVE table.

HALTED: Last step in branch has basic_status= 100, 200 or 300 or basic_status=1 or 2 (error or warning).

WAITING: Last step in branch has basic_status=3

Interrupted (RESUMABLE): Last step in branch has basic_status= 400

Interrupted Manually : Last step in branch has basic_status= 450

TERMINATED : Last step in branch has basic_status= 900

SQL queries:

The following SQL queries can be used for monitoring the application automatically with external tools like: ELK, Splunk, SiteScope, New Relic, AppDynamics or any other monitoring solution that supports calling SQL queries.

Equally, the queries can be used for troubleshooting purposes.

List HALTING BPs:

SELECT DISTINCT NAME 
FROM WORKFLOW_CONTEXT T1, 
(SELECT MAX(STEP_ID) STEP_ID , WORKFLOW_ID 
FROM WORKFLOW_CONTEXT GROUP BY WORKFLOW_ID) T2,
WFD T3,
WF_INACTIVE T4
WHERE T1.WORKFLOW_ID = T2.WORKFLOW_ID 
AND T1.WORKFLOW_ID = T4.WF_ID 
AND T2.STEP_ID = T2.STEP_ID
AND T1.NEXT_AI_ID != -1 AND 
T1.WFD_ID = T3.WFD_ID
AND T1.WFD_VERSION = T3.WFD_VERSION AND
BASIC_STATUS = 0

Count of Halted BPs

SELECT 
count(distinct WC.WORKFLOW_ID) as "Halted"
FROM WORKFLOW_CONTEXT WC, ARCHIVE_INFO AI
WHERE WC.WFD_ID > 0 
and (WC.BASIC_STATUS in (1, 2, 100, 200,300)) 
AND AI.WF_ID = WC.WORKFLOW_ID 
AND AI.GROUP_ID = 1 
AND AI.ARCHIVE_FLAG = -1 

Names and WF_ID of Halted BPs:

SELECT 
distinct WC.WORKFLOW_ID, NAME as "Halted" 
FROM WORKFLOW_CONTEXT WC, ARCHIVE_INFO AI,
WFD 
where WC.WFD_ID=WFD.WFD_ID and
WC.WFD_ID > 0 and 
(WC.BASIC_STATUS in (1, 2, 100, 200,300)) AND
AI.WF_ID = WC.WORKFLOW_ID AND 
AI.GROUP_ID = 1 AND
AI.ARCHIVE_FLAG = -1 

List Interrupted manually BPs:

SELECT 
WC.WORKFLOW_ID interrupted_man
FROM WORKFLOW_CONTEXT WC, ARCHIVE_INFO AI
WHERE
WC.BASIC_STATUS = 450
AND AI.WF_ID = WC.WORKFLOW_ID AND AI.GROUP_ID = 1 AND AI.ARCHIVE_FLAG = -1
AND WC.WORKFLOW_ID NOT IN (
SELECT WC.WORKFLOW_ID
FROM WORKFLOW_CONTEXT WC, ARCHIVE_INFO AI
WHERE 
WC.BASIC_STATUS = 900
AND AI.WF_ID = WC.WORKFLOW_ID AND AI.GROUP_ID = 1 AND AI.ARCHIVE_FLAG = -1)

List Active BPs (Oracle and DB2)

SELECT 
DISTINCT NAME , 
WC2.WORKFLOW_ID, 
sysdate - START_T AS TIMEDIFF
From WORKFLOW_CONTEXT WC,
(
SELECT MAX(STEP_ID) STEP_ID , MIN(START_TIME) START_T, WORKFLOW_ID
FROM WORKFLOW_CONTEXT
GROUP BY WORKFLOW_ID
) WC2,
WFD
WHERE WC.WORKFLOW_ID = WC2.WORKFLOW_ID
AND WC.STEP_ID = WC2.STEP_ID
AND WC.NEXT_AI_ID != -1
AND WC.WFD_ID = WFD.WFD_ID
AND WC.WFD_VERSION = WFD.WFD_VERSION
AND BASIC_STATUS =0 

List Active BPs (MS SQL server):

select DISTINCT NAME ,
 T2.WORKFLOW_ID,
datediff(mi,ST,CURRENT_TIMESTAMP)
From WORKFLOW_CONTEXT T1, 
(SELECT MAX(STEP_ID) STEP_ID ,MIN(START_TIME) ST, WORKFLOW_ID 
FROM WORKFLOW_CONTEXT GROUP BY WORKFLOW_ID)T2, WFD T3 
WHERE T1.WORKFLOW_ID = T2.WORKFLOW_ID AND T1.STEP_ID = T2.STEP_ID 
AND T1.NEXT_AI_ID != -1 AND 
T1.WFD_ID = T3.WFD_ID 
AND T1.WFD_VERSION = T3.WFD_VERSION AND 
BASIC_STATUS = 0 
and datediff(mi,ST,CURRENT_TIMESTAMP)>9

List Waiting BPs (Oracle and DB2)

SELECT DISTINCT NAME , 
WC2.WORKFLOW_ID, 
sysdate - START_T AS TIMEDIFF
FROM WORKFLOW_CONTEXT WC,
(
SELECT MAX(STEP_ID) STEP_ID , MIN(START_TIME) START_T, WORKFLOW_ID
FROM WORKFLOW_CONTEXT
GROUP BY WORKFLOW_ID
) WC2,
WFD
WHERE WC.WORKFLOW_ID = WC2.WORKFLOW_ID
AND WC.STEP_ID = WC2.STEP_ID
AND WC.NEXT_AI_ID != -1
AND WC.WFD_ID = WFD.WFD_ID
AND WC.WFD_VERSION = WFD.WFD_VERSION
AND BASIC_STATUS = 3 

Remember to run SQL queries returning large result-sets OUTSIDE of the B2B Integrator application SQL manager. Use tools like Oracle SQL developer.

Check more useful SQL queries:

• Useful SQL queries for Sterling B2B Integrator.
• TRANS_DATA related queries.
• 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!