A Sterling B2B Integrator environment that’s been running for a few years almost always has the same quiet problem: the database keeps growing, and nobody notices until a maintenance window runs long, a report times out, or someone finally asks why backups take three times as long as they used to. Workflow history, transaction data, and document records accumulate in the background for as long as the platform runs – and most of that growth is invisible until it isn’t.
IBM Sterling B2B Integrator ships with a built-in diagnostic for exactly this problem: the DBStats report. It’s not something most teams run on a regular cadence, which is a shame, because it answers the two questions that actually matter for database health: what’s taking up space, and is it supposed to still be there.
Start with the Top Execution Business Processes section
This section lists every business process (workflow) that has executed at least once, along with its total executions, total persisted steps, and the oldest and newest state dates on record. The first thing worth doing is sorting by Minimum State Date – any workflow sitting there with a date that’s years old is worth a second look.
When you find one, pull it up in Advanced BP Search and check its Expires link. That tells you exactly when the system is scheduled to purge it, and for how long it’s configured to stick around. It’s not unusual to find a workflow instance with a ten-year lifespan, which is almost always one of three things:
- A Mailbox Add step whose associated message still exists in a mailbox. As long as that mailbox message is present, the workflow instance that created it won’t be purged.
- An EDIEncoder or EDIEnvelope step with its
MODEparameter set todeferred. This is a known combination that results in a ten-year lifespan rather than the shorter one you’d expect. - The business process actually was configured for a ten-year lifespan. Sometimes this is intentional (long-term audit retention), and sometimes it’s a leftover default nobody revisited.
If none of those explain it, that’s the point to open a support case rather than guess.
Workflows sitting at a 30-day lifespan are usually a different, more mundane case: they’re waiting on an EDI acknowledgment that never arrived. That lifespan is governed by the document.lifespan parameter in enveloping.properties – lowering it only affects new workflows going forward, not ones already in flight. If the OverdueAckCheck business process is running on its schedule, overdue acknowledgments get manually acknowledged and the workflow re-indexes against its normal configured lifespan instead of sitting in limbo.
High-persisting workflows deserve their own look
Total Executions and Total Persisted Steps tell you something beyond “this ran a lot” – divide one by the other and you get the average number of persisted steps per run. A business process with thousands of persisted steps per execution is a much bigger contributor to database size than one that runs just as often but persists almost nothing.
The fix isn’t always “redesign the workflow.” Often it’s as simple as lowering the persistence level from Full down to something like Start/Stop or Step Status. In one real example, a workflow persisting at Full generated over 15,000 records in the WORKFLOW_CONTEXT table for a single execution; dropping it to a minimal persistence level brought that down to two. You can also give a specific, high-volume process its own shorter lifespan instead of inheriting the system default from the Archive Manager, which is worth doing for anything that runs on a tight schedule and doesn’t need long retention.
The Archive Status Report is your early warning for a stuck cleanup job
This section breaks down Business Process, Document Tracking, and Visibility records by status – Un-Indexed, To Be Archived, To Be Purged – with counts and date ranges for each. Two patterns are worth watching for:
- A high count sitting in “To Be Archived” or “To Be Purged” for a long time usually means your scheduled cleanup processes have fallen behind, or stopped running entirely. That data isn’t doing anything useful; it’s just occupying space until the purge catches up.
- An unusually high Visibility count can point to more server adapter / protocol activity than normal, which is sometimes the first visible symptom of a partner integration misbehaving.
GIS Table Statistics: find your biggest tables before they find you
This section (not available on DB2 configurations) lists row counts, data size, and index size per table. In most environments, TRANS_DATA, WORKFLOW_CONTEXT, and CORRELATION_SET are consistently the largest tables, so they’re the ones worth benchmarking first.
One simple heuristic: if a table’s index size is larger than its data size, that’s often a sign the table needs maintenance (rebuilding indexes, in particular). On its own this report section won’t tell you what “normal” looks like for your environment – that’s something you have to establish by watching it over time – but once you have a baseline, checking it periodically turns database bloat from a surprise into an early warning.
Don’t skip the Unassociated Document Report
This section is short, but it catches something the other sections won’t: documents in the DATA_TABLE or DOCUMENT tables that aren’t tied to a current workflow or reference. In most healthy systems, the DATA_TABLE entry count roughly matches your document table’s total row count. When it doesn’t, that mismatch usually points to old workflow data that was never fully cleaned up. The DOCUMENT entry specifically reflects documents uploaded through protocol server adapters outside of normal workflow processing – there’s no universal “this number is too high” threshold here; it’s a case-by-case read.
The real takeaway
None of this requires exotic tooling – it’s a report that already ships with the platform. The habit that actually matters is treating it like any other monitoring signal: run it periodically, know what your baseline table sizes and persistence patterns look like, and investigate anything that drifts from that baseline instead of waiting for a maintenance window to surface the problem for you. A ten-minute DBStats review every month is a lot cheaper than an emergency purge during a holiday freight season.
This is the kind of tuning work we do as part of EDI & MFT Tuning & Performance engagements and ongoing EDI Managed Services – if your Sterling B2B Integrator database has been growing quietly for a while and nobody’s looked under the hood recently, it’s worth a proper assessment before it becomes an incident.