Useful communication and adapter SQL queries for B2B Integrator

The ACT_SESSION table contains the details about communication sessions created in IBM Sterling B2B Integrator for different protocols. For more details about specific transfers you can consult the ACT_XFER and ACT_NON_XFER tables.

I list here some useful SQL queries:

Communication sessions stats by protocol:

select count(*), PROTOCOL from ACT_SESSION 
group by PROTOCOL ORDER BY 1 DESC

Communication Adapter usage stats:

SELECT COUNT(*), ADAPTER_NAME FROM ACT_SESSION 
GROUP BY ADAPTER_NAME ORDER BY 1 DESC

Stats by Protocol and endpoints:

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

Communication sessions stats per hour and protocol for a given day:
(ORACLE and DB2) (

select count(*),
TO_CHAR(CON_START_TIME,'YYYY-MM-DD'),
TO_CHAR(CON_START_TIME,'HH24'),PROTOCOL 
from ACT_SESSION 
where TO_CHAR(CON_START_TIME, 'YYMMDD') = '191014'
group by TO_CHAR(CON_START_TIME,'YYYY-MM-DD'),
TO_CHAR(CON_START_TIME,'HH24'),
PROTOCOL 
order by 2,3

Query to find communication sessions with long connection time (max longer than 10 second)

SELECT  ENDPOINT1,ENDPOINT2, PROTOCOL, count(*),
round(max((CON_END_TIME - CON_START_TIME) *86400)) as max_sec,
round(min((CON_END_TIME - CON_START_TIME) *86400)) as min_sec,
round(avg((CON_END_TIME - CON_START_TIME) *86400)) as avg_sec
from ACT_SESSION
group by  ENDPOINT1,ENDPOINT2, PROTOCOL
having round(MAX((CON_END_TIME - CON_START_TIME) *86400)) > 10
order by 5 desc

Query to find communication sessions with long connection time on a specific day (longer than 1 minute) (ORACLE and DB2)

SELECT  ENDPOINT1,ENDPOINT2, PROTOCOL, count(*),
max((CON_END_TIME - CON_START_TIME) *86400) as max_sec,
min((CON_END_TIME - CON_START_TIME) *86400) as min_sec,
round(avg((CON_END_TIME - CON_START_TIME) *86400)) as avg_sec
from ACT_SESSION
where TO_CHAR(CON_START_TIME, 'YYMMDD') = '190314'
group by  ENDPOINT1,ENDPOINT2, PROTOCOL
having round(MAX((CON_END_TIME - CON_START_TIME) *86400)) > 1
order by 5 desc

You can drill down to the minute level for  a specific protocol or specific endpoint. Here is an example:

Per minute HTTP per endpoint stats from act_session for a given day: (ORACLE and DB2)

SELECT TO_CHAR(CON_START_TIME, 'DD-MM-YYYY HH24:MI'),
ENDPOINT1,ENDPOINT2, count(*),
round(max((CON_END_TIME - CON_START_TIME) *86400)) as max_sec,
round (min((CON_END_TIME - CON_START_TIME) *86400)) as min_sec,
round(avg((CON_END_TIME - CON_START_TIME) *86400)) as avg_sec
from ACT_SESSION
where TO_CHAR(CON_START_TIME, 'YYMMDD') = '190514' 
and PROTOCOL = 'HTTP'
group by TO_CHAR(CON_START_TIME, 'DD-MM-YYYY HH24:MI'),ENDPOINT1,
ENDPOINT2
order by 1 desc

Then with SQL you can build more useful SQL queries, for example : Display the ‘Busiest Minutes’, in term of communication sessions, having more than 100 connections in the last few days (Oracle and DB2):

SELECT count(*), adapter_name,
TO_CHAR(CON_START_TIME,'YYYY-MM-DD-
HH24:MI') 
from ACT_SESSION 
where CON_START_TIME > sysdate-10
group by  adapter_name,
TO_CHAR(CON_START_TIME,'YYYY-MM-DD-
HH24:MI') having count(*) > 100
order by 1 desc

Check more useful SQL queries:

• Useful SQL queries for Sterling B2B Integrator.
• TRANS_DATA related SQL queries.
• Workflow statuses explained with SQL queries: Active,Halted, Halting BPs …
• Filegateway related SQL queries.
• Slow services and steps SQL.