Useful SQL queries for IBM Sterling B2B Integrator Database

Updated SQL queries  to understand the IBM Sterling B2B Integrator  Database.

SQL queries :

Index count

SELECT count(*) 
FROM ARCHIVE_INFO 
WHERE ARCHIVE_FLAG in (-1,-2,-5)

Purge count

SELECT count(*) 
FROM ARCHIVE_INFO 
WHERE ARCHIVE_FLAG in (1,2)

Eligible Purge records : Purge current backlog (Oracle)

SELECT count(*) 
FROM ARCHIVE_INFO 
WHERE ARCHIVE_FLAG in (1,2) and 
ARCHIVE_DATE < sysdate

DB2: replace sysdate by CURRENT DATE.

MS SQL SERVER : replace sysdate with GETDATE()

Purge records per month : DB2 and Oracle only:

SELECT count(*), 
to_char(archive_date,'mmyyyy') 
FROM archive_info 
WHERE archive_flag in (1,2) 
GROUP BY to_char(archive_date,'mmyyyy')

Oracle and DB2  archive_info stats by day :

SELECT count(*),
to_char(archive_date,'YYYYMMDD') 
FROM archive_info 
WHERE archive_flag=0 
GROUP BY to_char(archive_date,'YYYYMMDD') 
ORDER BY to_char(archive_date,'YYYYMMDD')

Oracle and DB2  archive_info stats  by year :

SELECT count(*),to_char(archive_date,'YYYY') 
FROM archive_info 
WHERE archive_flag in (0,1,2) 
GROUP BY to_char(archive_date,'YYYY') 
ORDER BY to_char(archive_date,'YYYY')

BP stats by number of runs and number of steps :

SELECT T2.NAME, 
COUNT(DISTINCT WORKFLOW_ID) AS NO_OF_RUNS, 
COUNT(*) AS NO_OF_STEPS 
FROM WORKFLOW_CONTEXT T1, WFD T2
WHERE T1.WFD_ID = T2.WFD_ID AND 
T1.WFD_VERSION = T2.WFD_VERSION 
GROUP BY T2.NAME 
ORDER BY 2 DESC

BP execution times stats (Oracle):

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
-- Uncomment to specify a time Interval
--and wfc.start_time between 
--to_timestamp('201903010030','YYYYMMDDHH24MI') and
--to_timestamp('201903011700','YYYYMMDDHH24MI')
 GROUP BY WFD.NAME,WFC.WFD_ID, WFC.WORKFLOW_ID,WFC.NODEEXECUTED
 ORDER BY 7 DESC

Bps with large number of steps (>3000 steps)

BPs with large number of steps can cause lock escalations during Purge on a non tuned MS SQL server :

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

Number of executions per cluster node statistics:

SELECT count(distinct WORKFLOW_ID),
NODEEXECUTED 
FROM WORKFLOW_CONTEXT 
GROUP BY NODEEXECUTED

Communication sessions Stats by Protocol :

SELECT COUNT(*),
PROTOCOL, 
ENDPOINT1, 
ENDPOINT2,
CON_IS_SUCCESS, 
MAX(CON_START_TIME),
MIN(CON_START_TIME)
FROM  ACT_SESSION 
GROUP BY PROTOCOL, ENDPOINT1, ENDPOINT2 ,CON_IS_SUCCESS 
ORDER BY 1 DESC

Total document_size per month : Oracle and DB2

SELECT count(*), 
sum(document_size)/1024/1024 MB,
to_char(create_time,'mmyyyy') 
FROM DOCUMENT 
GROUP BY to_char(create_time,'mmyyyy') 
ORDER BY 2 desc;

MS SQL server : query to return the total size of documents per month:

SELECT 
convert(varchar(6),CREATE_TIME,112) TIME_YYYYMM,
count(*) COUNT,
cast(ROUND(sum(DOCUMENT_SIZE)/1024/1024,2,1) as decimal(10,2)) SIZE_MB
FROM DOCUMENT 
GROUP BY convert(varchar(6),CREATE_TIME,112) 
ORDER BY 1

Oracle and DB2  workflow_context BP records per month:

SELECT count(*), 
to_char(start_time,'mmyyyy') 
FROM WORKFLOW_CONTEXT 
GROUP BY to_char(start_time,'mmyyyy') 

Oracle and DB2   Workflow_context BP stats by Hour:

SELECT to_char(START_TIME, 'HH24'), 
count(*) 
FROM workflow_context 
GROUP BY to_char(START_TIME, 'HH24') 
ORDER BY 1 desc

Oracle Tablespace statistics  :

SELECT Total.name "Tablespace Name", 
nvl(Free_space, 0) Free_space, nvl(total_space-Free_space, 0) Used_space,
total_space FROM (select tablespace_name,
 sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name ) Free, 
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name ) Total 
WHERE Free.Tablespace_name(+) = Total.name ORDER BY Total.name; 

Oracle trans_data size distribution by reference_table: (very slow query on large DBs)

SELECT reference_table,
data_type,
count(*),
round(SUM (dbms_lob.getlength(DATA_OBJECT)) /1024/1024) total_MB 
FROM TRANS_DATA 
GROUP BY reference_table,data_type 
ORDER BY 1,2

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:

• Database tables you should know! Introduction to the Sterling B2B Integrator DB schema.
• TRANS_DATA related 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.
• SQL query to display your Bps’ configuration (life_span, persistence_level, queue and execution_node)
• Why is my database too big? DBStats report: what to look for?
• Tired of SQL queries? try the SIDBMonitor tool.

Best practices must reads:

Oracle Database best practices for Sterling B2B Integrator.

MS SQL Server best practices for Sterling B2B Integrator.

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!