IBM B2B Integrator TRANS_DATA related SQL queries

TRANS_DATA is the Sterling B2B Integrator Database table where all transactional data is stored as blobs. This data include process data, payload, XML reports . and pointers to the file system or lob segment.

The following SQL queries will help to understand this data.

Trans_data distribution per month (5263 +) Oracle and DB2 only

NEW! In version 5263 a new column CREATION_DATE was added to trans_data.

SELECT 
to_char(CREATION_DATE, 'MM-YYYY'), 
count(*)
FROM trans_data
GROUP BY to_char(CREATION_DATE, 'MM-YYYY') 
ORDER BY 1 desc

Size of all the blobs (Oracle)

SELECT 
round(sum(dbms_lob.getlength(data_object))/1024/1024) sizeMB 
FROM trans_data

Long query to get the trans_data size by type:(Oracle)

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

Long query to get the trans_data size by type for wfid <=0 (Oracle)

SELECT 
reference_table,
data_type,
wf_id,count(*),
round(SUM (dbms_lob.getlength(DATA_OBJECT)) /1024/1024) total_MB
FROM trans_data 
WHERE wf_id <= 0 
GROUP BY reference_table,data_type,wf_id 
ORDER BY 1,2,3

Trans_data distribution per month based on WC excluding <0 (!!for OLDER  versions prior to 5263!!)

SELECT /*old versions */
to_char(b.starttime,'YYYYMM') permonth,
count(*)
FROM trans_data a ,
(SELECT workflow_id,
min(start_time) starttime
FROM Workflow_context
GROUP BY workflow_id) b
WHERE a.wf_id=b.workflow_id
GROUP BY to_char(b.starttime,'YYYYMM')
ORDER BY permonth

Trans_data Orphans count

SELECT 
COUNT(*) 
FROM TRANS_DATA
WHERE (WF_ID=0 or WF_ID=-1) AND REFERENCE_TABLE ='DOCUMENT'
AND NOT EXISTS (SELECT BODY 
FROM DOCUMENT
WHERE BODY=TRANS_DATA.DATA_ID
OR ENC_IV=TRANS_DATA.DATA_ID OR ENC_KEY=TRANS_DATA.DATA_ID)

Trans_data Orphans size (Oracle)

SELECT wf_id,COUNT(*),
round(SUM (dbms_lob.getlength(DATA_OBJECT)) /1024/1024) total_MB 
FROM TRANS_DATA WHERE (WF_ID=0 or WF_ID=-1) 
AND REFERENCE_TABLE ='DOCUMENT' 
AND NOT EXISTS 
(SELECT BODY FROM DOCUMENT WHERE BODY=TRANS_DATA.DATA_ID 
OR ENC_IV=TRANS_DATA.DATA_ID OR ENC_KEY=TRANS_DATA.DATA_ID) 
group by WF_ID

Find large blob’s used by BPs (Oracle Only)

select WF_ID , 
SUM (dbms_lob.getlength("DATA_OBJECT")) 
from TRANS_DATA  GROUP BY WF_ID 
ORDER BY 2 DESC;

If you have some very old data in your trans_data table on Oracle please check this post on how to delete old records from TRANS_DATA.

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.
• 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!