Useful queries for IBM Sterling Filegateway SFG

I share in this blog some useful Sterling Filegateway SFG SQL queries.

The FG_ARRIVEDFILE and FG_DELIVERY contain information about SFG transfers. FG_EVENT and FG_EVENTATTR contain the transfer details.

The table SCI_PROFILE contains information about trading partners.

We will query these tables to extract very useful information:

SFG IBM Sterling Filegateway ARRIVED FILES stats per hour: (ORACLE and DB2)

select count(*),TO_CHAR(CREATETS ,'YYYY-MM-DD'),
TO_CHAR(CREATETS ,'HH24') 
from FG_ARRIVEDFILE 
group by TO_CHAR(CREATETS ,'YYYY-MM-DD'),
TO_CHAR(CREATETS ,'HH24')
order by 2,3

SFG IBM Sterling Filegateway Delivery stats per hour:
(ORACLE and DB2)

select count(*),
TO_CHAR(CREATETS ,'YYYY-MM-DD'),
TO_CHAR(CREATETS ,'HH24') 
from FG_DELIVERY 
group by TO_CHAR(CREATETS ,'YYYY-MM-DD'),
TO_CHAR(CREATETS ,'HH24')
order by 2,3

SFG ARRIVED FILES stats per month:
(ORACLE and DB2)

select count(*), 
TO_CHAR(CREATETS,'MMYYYY') 
from FG_ARRIVEDFILE 
group by TO_CHAR(CREATETS,'MMYYYY') 
order by 1 desc

SFG Deliveries stats per month:
(ORACLE and DB2)

select count(*), 
TO_CHAR(CREATETS,'MMYYYY') 
from FG_DELIVERY
group by TO_CHAR(CREATETS,'MMYYYY') 
order by 1 desc

SFG Arrived files per partner:

select count(*),PROD_ORG_KEY 
from FG_ARRIVEDFILE
group by PROD_ORG_KEY 
order by 1 desc

SFG Deliveries by state:

select count(*), STATE 
from FG_DELIVERY
GROUP BY STATE 
ORDER BY 1 DESC

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:

• Database tables you should know! Introduction to the Sterling B2B Integrator DB schema.
• Useful SQL queries for Sterling B2B Integrator.
• Workflow statuses explained with SQL queries: Active, Halted, Halting BPs …
• The “famous” TRANS_DATA table related 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 and SFG!