Let’s start with some key facts about the Sterling B2B Integrator Database schema:
- Contains 600+ tables.
- More than 1200 indices.
- OLTP (transactional processing) write centric database.
- High commit/second rate.
- Transaction log (redo logs) are crucial.
- Tuning can maximize performance.
The Document life cycle:
A document or a message can come to the application in different ways such as mailboxing, webservices, SFTP, FTP, HTTP, REST … etc.
The picture below, summarizes the document/BP lifecycle and the tables involved:
The DOCUMENT table is one of the most important tables in the schema. It has references to other tables like TRANS_DATA which either stores the physical document as a DB blob (binary object) if the storage is database or will store a pointer to the filesystem.
Other Database tables are also impacted when a document arrive, like MBX_MESSAGE, ACT_SESSION, DATA_FLOW, CORRELATION_SET etc (shown in the blues boxes above). When the document is in the system it will either trigger or bootstrap a Business Process (BP) as soon it arrives. Alternatively, a BP schedule (like the Evaluate Mailbox) will start the needed inbound and outbound operations.
The fourth box lists some of the important transactional tables affected when a BP runs. For example: WORKFLOW_CONTEXT will contain the BP execution steps.
Finally when the BP completes, it will be indexed or moved to the next step so it can be either archived or purged. There are also other DB cleanup processes, listed above in the fifth box in red. The housekeeping processes need to be watched carefully to prevent the Database from growing. I explained how the B2B Integrator Housekeeping works in another post.
Important tables to know:
I will describe some important database tables:
WORKFLOW_CONTEXT: One of the largest tables that contains the execution steps of business processes, node information, service name, link to status report and primary document (stored in trans_data table as binary object blobs or in filesystem)… Example SQL queries.
TRANS_DATA: contains the physical payload files, mailbox messages, documents, status reports… stored as blobs (or points to the physical file on filesystem if storage is FS). Check TRANS_DATA SQL queries.
DOCUMENT: This table is one of the important tables in the schema. Each time a document (message, request, payload) arrives in the application, its meta data (type, name, size, encryption, link to its body, encoding, … ) is stored in this table. It has references to other tables like TRANS_DATA where the physical file is stored as blob or pointed to if stored in the Filesystem.
DATA_TABLE: contains the user and system certificates and keys, XML BP definitions, BP schedules timings, services definition and parameters, maps, XML schema, and other configuration stored as blobs. If this table is deleted accidentally, the application will NOT start. Always take a backup of this important configuration table!.
ARCHIVE_INFO: contains housekeeping information like the removal method and archive_date ( when the workflow or data flow records will be purged from about 40 other tables). This table drives the housekeeping jobs like: Index, Archive and Purge processes.
CORRELATION_SET: one of the largest tables that contains all sort of tracking and correlation data (key-value pairs) like transfer details, EDI tracking, Swift tracking, Connect Direct details etc… . It points to the DOCUMENT table.
WORKFLOW_LINKAGE: contains parent-child BP information. Linkage Purge cleans this table every night. On busy system Linkage Purge should run more frequently with a higher max_business_processes setting. Read this blog if your WORKFLOW_LINKAGE is too big.
MBX_MESSAGE : Contains the mailbox messages information like : message name, size, mailbox name, creation date etc …. Mailbox messages are kept, by default, 10 years in the system unless deleted by a mailbox delete job.
MBX_MAILBOX: contains the mailboxes metadata.
Filegateway specific tables:
FG_EVENT and FG_EVENTATTR: Contains the Filegateway events. Records in these tables are kept 14 days by default in the system. this is defined in the dmivisibility lifespans properties. The FG_EVENTATTR can grow rapidly.
FG_ARRIVEDFILE: Contains entries of every file that arrived in the system after the Mailbox Evaluate process routes the messages.
FG_DELIVERY: Contains information about delivered (sent ) files.
These four SFG tables can be queried to extract very useful information about file transfer statistics. Check these Filegateway SQL queries (useful SFG SQL queries)
Communication sessions tables:
ACT_SESSION: holds the information about every inbound or outbound communication session in the system like session_id, start time, end time, protocol, IP addresses (endpoints), Workflow ID and status.
ACT_XFER: Contains the information about different transfers like: FTP, SFTP, HTTP… in a communication session (links to ACT_SESSION).
DATA_FLOW: a data flow is a number of steps that a message transits into. an example of a flow is a file transfer that navigates through different business processes like FG RouteArrivedFile, Custom protocols BPs, FG SendMessage, Tracking, Error Handling etc… . All these steps are stored in the DATA_FLOW table. Data in this table is kept for 14 days by default.
These tables hold important information about the transfers duration, adapter usage, protocols used, slow partners…. Check useful Communication sessions here SQL query examples)
Audit tables:
ADMIN_AUDIT: Contains user activity like system parameters modification to schedules, services, routes … or Enabling/disabling services, schedules and adapters. It contains also startup and shutdown information among many other operation events.
Check more useful SQL queries and housekeeping related posts:
- 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.
- B2B Integrator Housekeeping explained : Index, Purge, AssociateBPtoDocs, Linkage Purge …
- Why is my database too big? DBStats report: what to look for?
- Tired of SQL queries.? Try SIDBMonitor.
Updated : December 2020.
srini
All, Recently deleted a correlation entry from the correlation set table from the database. But it still showing the entry from the Sterling integrator dashboard.
do you know why it’s showing through it’s deleted from the database table?
Editor
Hi Srini,
Correlation_set table is used by several protocols to store correlations. Some other metadata can be stored on other tables which are read first in dashboard screens. These other tables could be Swift, Ebics, CD or the Data_flow for some other adapters and protocols. So the data shown in your dashboard could come from another table depending on the implementation.
donovan Greaves
Hello, how do you write a query to find the directory location that a BP wrote a datafile to for example a file system BP output to some directory name /share/home/pickup/editext.csv. When I search using BP ID I get all information I need to know where in the database table the information is stored.
Shashi ranjan
Need SQL query to find which certifictae used in what AS2 profile.