SQL queries to detect slow BPs and services for IBM B2B Integrator and SFG

Long BP steps running for more than 10 seconds: 

select SERVICE_NAME,round(MAX(END_TIME - START_TIME)*86400) , 
round(MIN(END_TIME - START_TIME)*86400) , 
count(*) 
from WORKFLOW_CONTEXT 
group by SERVICE_NAME 
Having round(MAX(END_TIME - START_TIME)*86400) > 10 order by 2 desc

Long BPs – running for more than 10 seconds

SELECT WFD.NAME,WFC.WFD_ID, WFC.WORKFLOW_ID,wfc.nodeexecuted, 
MIN(WFC.START_TIME) as "Start",
 MAX(WFC.END_TIME) as "End", 
round((MAX(WFC.END_TIME)-MIN(WFC.START_TIME))*60*60*24) as " Runtime Sec" 
FROM WORKFLOW_CONTEXT WFC,WFD WHERE WFD.WFD_ID=WFC.WFD_ID 
GROUP BY WFD.NAME,WFC.WFD_ID, WFC.WORKFLOW_ID,wfc.nodeexecuted 
HAVING round((MAX(WFC.END_TIME)-MIN(WFC.START_TIME))*60*60*24) >10 ORDER BY 7 DESC

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

Check more useful SQL queries and posts:

• Useful SQL queries for Sterling B2B Integrator.
• TRANS_DATA related queries.
• Filegateway related SQL queries.
• Communication and adapter stats SQL.
• Why is my Sterling B2B Integrator slow?

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!