DBStats & You: A Tale of Database Woes and How to Diagnose Them (Part 1 Archive Status Report)

Body

A very common question I get on IBM Sterling B2B Integrator is "Why is my database so big?" The first thing I look at is the DBStats report. The DBStats report consolidates a lot of information about what Sterling B2B Integrator is storing in the database, how it got there, and when it is going to be deleted. Understanding this report can give you a lot of information on how you can configure your system to use fewer data and to identify bottlenecks in processing and purging old data.

In this post I am going to focus on the Archive Status Report section of the DBStats report.

Use pdf format.

The DBStats report is optimized for the pdf format. You can use the html and xls formats, but the formatting is clunky and some labels do not show up correctly. The Archive Status Report section in particular shows up poorly in html and xls. My examples use the pdf format.

Start by looking at the counts. For each status there are two counts.

Count: The number of objects in that status.

# Eligible: The number of objects that are in that state and ready to be processed to the next step.

Take a look at where the highest count is. That will tell you where most of the objects are and where a potential bottleneck might be.

Some basic scenarios:

High count in Un-Indexed (Reprocessed).

This means that index is likely failing and marking things to be reprocessed. When Index marks something to reprocess, it tries again in 24 hours. Run the report again after 24 hours and see if it resolves itself. If not, something is stuck and causing index to fail.

High count in Un-Indexed.

Index is behind. Make sure index is running and completing. Consider raising the max_business_processes setting in Schedule_IndexBusinessProcess service from the default of 5000. Index runs every 10 minutes. If you run more than 5000 processes every 10 minutes, this setting needs to be higher.

High count in To Be Archived.

Make sure Schedule_BackupService is running and completing. It runs every 24 hours by default.

Go to Deployment > Services and edit BackupService. Raise "Number of Days Per Backup Set" to 180.

Do you use the backup sets? If not, consider changing all of the processes in the system to purge directly.

High count and # Eligible in To Be Purged and Archived, To Be Purged.

If the # Eligible is high, make sure purge is running and completing.

If you are using the internal Schedule_PurgeService, make sure it running every 10 minutes and completing. Consider moving to External Purge. If you do want to move to External Purge, applying the most recent fixpack. As of this writing that is fixpack 5.2.4.2 generic interim fix 3. External Purge is not available in Sterling B2B Integrator 5.1 or earlier.

If you are using External Purge, make sure that it is up and running. Check the external purge logs under the <install_dir>/logs/PURGE directory of the node External Purge is running on. The logs should be updating while External Purge is running.

High count and low # Eligible in To Be Purged and Archived, To Be Purged.

If the Count is high and the # Eligible is low in To Be Purged and Archived and To Be Purged everything is running ok. Things are purging when they expire. If you want to make the database smaller, consider reducing the lifespan of business processes.

Check to make sure you are deleting mailbox messages. If you have a mailbox that isn't being cleaned up, that can pin processes to the system. Make sure that all messages are deleted at some point. Also consider lowering the lifespan on the messages. The faster you can delete them, the smaller your database will be.

Visibility and DMI Tracking # Eligible are high while Business Process and Document Tracking are low.

If using External Purge, apply the most recent fixpack. In earlier fixpacks the Visibility and DMI Tracking information had a very low priority in External Purge and it could let them get very large before deleting.

Next Week: Top Executed Business Processes. Find the business processes that are taking up the space.