Full Database Issues and Resolution

Edit online

For various reasons, you might find that your Sterling B2B Integrator database becomes full. This might cause performance issues that appear to be related to memory or other resource usage.

If your database is set to auto extend, the database may attain 99 percent capacity before extending automatically. In this situation, the 99 percent capacity does not indicate a performance issue.

After you run Sterling B2B Integrator for some time and you take out from the database approximately the same amount of data that you put into the database, the database size becomes consistent.

If other applications are using the same database that Sterling B2B Integrator uses, verify that the other applications are also experiencing slow database returns before making any database adjustments.

If the other applications are not experiencing database issues, the performance issue may be related to a cause other than the database.

Symptoms and Causes of a Full Database

The symptoms of a full database may include:

Database does not accept new data.
Database Usage Report shows that your database usage is increasing to capacity.
Sterling B2B Integrator interface is slow.
Business process execution times are slow.

Determining the cause of a full or filling database requires you to review your business processes and your archival and purging practices. Causes of a full or filling database may include:

The database is not sized properly. Determine if the current database size exceeds the expected size determined with your IBM® Sales Representative at the time of sales. (Customers must have this information in the form of a sizing sheet.)
Persistence levels set to high globally in your business processes or for each activity in a business process, causing too much data to be persisted to the database.
Indexing business process is not working properly, causing no data to be flagged for archival or purging and no data being removed from the database. The indexing business process runs every 10 minutes.
Backup business process is not working properly, causing no data to be removed from the database and archived to another location. The archive business process checks for the archive flag and then the date on which the archive is to be completed. When the archive date is reached, the archive business process archives the record.
Purging business process is not working properly, causing no data to be purged from the database. The purge business process checks for the purge flag and then the date on which the purge is to be completed. When the purge date is reached, the purge business process purges the record from the database.
Large or old files that can be archived elsewhere are being stored in the database, causing the database to fill up.
Size of the database is not optimized for your processing requirements, causing the database to fill up prematurely.
Data retention period is set too high, causing data to be kept in the database longer than necessary.
Schedule_BPRecovery business process is either not running or failing, causing business processes to remain in the database in an interrupted state.
IndexBusinessProcess business process is either not running or failing, causing no business processes to be flagged for archival or purging.
AssociateDocsToBP business process is either not running or failing, causing expired documents to not be associated for being purged from the system.

To determine the cause of a full database:

Monitor the Database Usage Report in Sterling B2B Integrator to determine the levels of database usage. If the usage level increases to the high end of its capacity, you may need to take corrective action, unless you have your database set to auto extend (increase in capacity when a specified level is reached). For more information about auto-extending your database, refer to the documentation pertaining to your database.
Review the archive.log and database-specific log by navigating to Operations > System > Logs for information about archiving and purging activities and errors.

Resolve Full Database Issues

Try the following approaches to resolve full database issues.

Approach	Details
Change persistence levels	For information about how to change persistence levels, refer to the topic Changing Persistence Levels.
Complete indexing activities	If your index business process is scheduled, but not running, no data is flagged for archival or purging, and your database may fill up. Review the index business process and verify that the schedule is defined and turned on in the business process, and that business processes are being indexed. To verify that the index business process is running, review the archive.log file for data that was purged or archived. This log file will state whether each data purge or data archive was successful. If the archive.log file shows no data being archived or purged, your index business process may not be running, causing your database to fill up. If the index business process is running and failing, contact IBM Professional Services for assistance.
Complete archiving activities	If your archival business process is scheduled, but is not running, your database can fill up because data is not being archived and moved off the database tables to another storage location. Review the archival business processes and verify that the schedule is defined and turned on in the business process, and that business processes are archived after a reasonable amount of time. To verify that the archival business process is running, review the archive.log file for data that was purged or archived. This log file will state whether each data purge or data archive was successful. If the archive.log file shows no data being archived or purged, your archival business process may not be running, causing your database to fill up. If the archival business process is running and failing, contact IBM Professional Services for assistance.
Complete purging activities	If your purging business process is scheduled, but is not running, your database can fill up because data is not being removed from the database tables. Review the purging business processes and verify that the schedule is defined and turned on in the business process, and that business processes are purged after a reasonable amount of time. To verify that the purging business process is running, review the archive.log file for data that was purged or archived. This log file will state whether each data purge or data archive was successful. If the archive.log file shows no data being archived or purged, your purging business process may not be running, causing your database to fill up. If the PurgeService business process is running and failing, contact IBM Professional Services for assistance.
Complete Associate Docs to BP activities	If an associate document to BP business process is scheduled but not running, your database can fill up because expired document data will not be associated to be removed. Review the business processes and verify that the schedule is defined and turned on in the business processes, and that business processes are purged after a reasonable amount of time.
Reduce the amount of large or old data stored in the database	If old data and large files are using database resources and causing your database to fill up, archive those files to another file system instead of to database tables.
Optimizing the Size of Your Database	As your business and processing needs change, you may find that your initial database size requirements are different from your requirements. If your database is filling and your volume is increasing: Increase the size of your database to reflect your processing needs. Compress your database, if your database has this feature, in order to save more data to the same size database. For more information about compressing your database, refer to your database documentation.
Optimizing the Data Retention Period	You may be retaining information in the database for longer periods of time than necessary. Set the data retention period in the database to lower levels as appropriate. For more information about setting data retention periods in your database, refer to your database documentation.
BPRecovery Business Process is Not Running or is Failing	If your BPRecovery business process is either not running or is failing: Contact IBM Professional Services for assistance with the BPRecovery business process Run the corrected BPRecovery. Restart, resume, or terminate interrupted business processes. You can perform a mass termination of business processes if many are in an interrupted state.

Document Persistence

Sterling B2B Integrator offers two options for persisting documents: a database option and a file system option. When dealing with database sizing, it is important to understand these differences and ensure that you have chosen the correct option for your environment.

Database option – The payload of each document in the system is stored in the DATA_TABLE or TRANS_DATA tables. When documents (payloads) become large, it leads to inefficient use of database disk space and results in greatly increased network traffic.
File system persistence option – This option was created to store the payload of documents out on disk. For documents with file system persistence, the DATA_TABLE/TRANS_DATA tables are still utilized. The difference is that the content of the BLOB data is not the payload, but a serialized Java™ HashMap that contains the file name of the payload. This is used to tie the file with the document.

Use of the database or the system for persisting the document payload is specified in the jdbc.properties file with the defaultDocumentStorageType property. This setting is the system default and can be inherited or overridden at the WFD (workflow definition) level. The location of document payloads is also configurable in the jdbc.properties file with the document_dir property.