This page documents commonly used SQL queries for gathering communication and processing statistics from IBM Sterling databases (Oracle, DB2, and MS SQL Server).

List Largest Tables and Update Statistics

Checking the largest tables and updating table statistics.

For Oracle Only:

select table_name, round((num_rows*avg_row_len)/1048576) tbsize, num_rows, last_analyzed
from all_tables
where owner='XXXXXXX' and num_rows>0
order by num_rows desc

Communication Sessions Stats by Protocol

Generic for Oracle/DB2:

SELECT 
   COUNT(*) AS SESSION_COUNT,
   PROTOCOL, 
   ENDPOINT1, 
   ENDPOINT2, 
   CON_IS_SUCCESS, 
   MAX(CON_START_TIME) AS LAST_SESSION, 
   MIN(CON_START_TIME) AS FIRST_SESSION
FROM ACT_SESSION
GROUP BY PROTOCOL, ENDPOINT1, ENDPOINT2, CON_IS_SUCCESS
ORDER BY SESSION_COUNT DESC;

This query returns the number of communication sessions grouped by protocol, endpoints, and success status.

Total Document Size per Month

Returns total number and size of documents grouped by month.

Generic for Oracle/DB2:

SELECT 
   COUNT(*) AS DOC_COUNT,
   SUM(DOCUMENT_SIZE)/1024/1024 AS MB,
   TO_CHAR(CREATE_TIME, 'MMYYYY') AS MONTH_YEAR
FROM DOCUMENT
GROUP BY TO_CHAR(CREATE_TIME, 'MMYYYY')
ORDER BY MB DESC;

For MS SQL Server

SELECT 
   CONVERT(VARCHAR(6), CREATE_TIME, 112) AS TIME_YYYYMM, 
   COUNT(*) AS COUNT, 
   CAST(ROUND(SUM(DOCUMENT_SIZE)/1024.0/1024.0, 2) AS DECIMAL(10,2)) AS SIZE_MB
FROM DOCUMENT
GROUP BY CONVERT(VARCHAR(6), CREATE_TIME, 112)
ORDER BY TIME_YYYYMM;

Workflow Context (Business Process) Records per Month

Shows the number of business process records created per month.

Generic for Oracle/DB2:

SELECT 
   COUNT(*) AS BP_COUNT, 
   TO_CHAR(START_TIME, 'MMYYYY') AS MONTH_YEAR
FROM WORKFLOW_CONTEXT
GROUP BY TO_CHAR(START_TIME, 'MMYYYY');

Workflow Context Stats by Hour

Displays hourly distribution of business process executions.

Generic for Oracle/DB2:

SELECT 
   TO_CHAR(START_TIME, 'HH24') AS HOUR_OF_DAY, 
   COUNT(*) AS BP_COUNT
FROM WORKFLOW_CONTEXT
GROUP BY TO_CHAR(START_TIME, 'HH24')
ORDER BY HOUR_OF_DAY DESC;

Ver também