Database Maintenance Check

Edit online

Sterling B2B Integrator runs a scheduled maintenance check on the database every Monday at 4:00 AM. This process is the default setting and is configurable.

This maintenance check is performed by the DB Monitor service and its associated Schedule_DBMonitorService business process. The system verifies if any of the following conditions have occurred:
  • Database is more than 80 percent full.
  • Database has grown more than 30 percent since the last maintenance check.
  • Database has to be indexed.

If any of these conditions are found, the system sends an event notification. By default, it sends an e-mail to the system administrator.

If you are notified that the database has to be indexed, contact your database administrator or use the db_optimization_tool script to rebuild the indexes and optimize the database.

Important: The reports and tools described in this section are not a replacement for standard database tools and maintenance scripts, and should be used only if your DBA is not involved with regular maintenance on the Sterling B2B Integrator database.

View the Database Statistics Report

To view information about business processes in the database:

  1. From the Administration menu, select Operations > Reports.

  2. In the Reports page, under Search, enter DBStats in the Name box and click Go!.

    Note: The DBStats report can also be found under List. However, it cannot be found using the Type "list" under Search.
  3. Click source manager.
  4. Select the appropriate report format from the drop-down list and click execute.

Database Statistics Report

The Database Statistics Report provides statistical information about business processes in the database. You should review the Database Statistics Report if you experience full database or failed database connection problems, or if you receive automated notification that the database's health needs to be checked.

The Database Statistics Report is divided into the following sections:
Report Section Description and Fields
Business Process Runs in Active System
Lists all the business process definitions that have had at least one instance executed. Fields are:
  • Business Process Name – Name of the business process instance.
  • Number of Runs – Number of times the instance has run.
  • Number of Persisted Steps – Number of steps persisted per business process definition.
  • Average Number of Persisted Steps – Average number of steps persisted.
Business Process Runs in Active System - Last 24 Hours Lists all the business process definitions that have had at least one instance executed in the last 24 hours.
Business Process Runs in Active System - Interval of Last 48 to 24 Hours Lists all the business process definitions that have had at least one instance executed during the previous day (24-48 hours before the current time).
Number of BPs by Removal Method
Shows the number of business processes flagged to be archived, purged, or indexed. It contains the following fields:
  • Archive Flag – Shows the number of business processes in each category:
    • To Be Archived – Business processes flagged to be archived
    • To Be Purged – Business processes flagged to be purged
    • Archived, to be Purged – Business processes that have been archived and are flagged to be purged
    • To Be Indexed – Business processes flagged to be indexed
  • Count - Total number of business processes with the stated Archive flag
Number of Eligible BPs by Removal Method

Number of eligible business processes that are flagged to be archived, purged, or indexed.

The Number of BPs by Removal Method To Be Indexed count minus the Number of Eligible BPs by Removal Method To Be Indexed count provides the number of business processes that, for whatever reason, are not eligible for indexing. These business processes will not be purged until they become eligible for indexing.
Archive Dates by Removal Method
Shows the date range of business processes that are flagged to be archived, purged, or indexed. Fields are:
  • Archive Flag – Shows one of the following conditions:
    • To Be Archived – Business processes flagged to be archived
    • To Be Purged – Business processes flagged to be purged
    • Archived, to be Purged – Business processes that have been archived, and are flagged to be purged
  • Min – Date and time of the first expired business process
  • Max – Date and time of the last expired business process
Table Row Counts
Shows the number of rows for each table in the active system. Fields are:
  • Table Name – Name of the table
  • Count – Number of rows in the table
Table Row Counts - Last 24 Hours Shows the number of rows for each table in the active system during the last 24 hours.
Table Row Counts - Interval of Last 48 to 24 Hours Shows the number of rows for each table in the active system during the previous day (24 to 48 hours prior to the current time).
Table Row Counts - Index Shows the number of rows that have not been indexed for each table in the active system.
Table Row Counts - Archive The Table Row Counts - Archive section shows the number of rows eligible for archiving for each table in the active system.
Table Row Counts - Purge The Table Row Counts - Purge section shows the number of rows eligible for purging for each table in the active system.
Number of Rows with No Matching Records in Archive Info
Shows the number of rows in a table that are orphaned (have no matching records in the archive data). It contains the following fields:
  • Table Name – Name of the table
  • Orphaned Record Count – Number of orphaned rows
No Matching Workflow ID (Null)
The section shows the number of records in TRANS_DATA with NULL workflow IDs or -1 workflow IDs. It contains the following fields:
  • Number of BPs with No Matching Records in Data Table, and Count – Total number of workflows with -1 Workflow IDs in the data table
  • No Workflow ID Assigned, and Count – Number of business processes that have a null workflow ID.

Optimize the Database with the db_optimization_tool Script

Sterling B2B Integrator provides a database optimization script that allows you to optimize your database by performing tasks such as analyzing tables and rebuilding indexes.

Note: The db_optimization_tool script does not work for DB2®.
To run the database optimization script, run the following from the command line:
  • For UNIX, run install_dir/bin/db_optimization_tool.sh options
  • For Windows, run install_dir\bin\db_optimization_tool.cmd options
Option Description
-l

View a list of all index rebuild or analyze database SQL statements. Must be used with -i or -a, or both.

Uses the current date and time as the cutoff for expired data, unless the -t option is used. Either -l or -r is required.
-r

Run all index rebuild or analyze database SQL statements. Must be used with -i or -a. Uses the current date and time as the cutoff for expired data, unless the -t option is used. Either -l or -r is required.

Note: The database optimization scripts can do a lot of database updates to rebuild the indexes and analyze the database tables while the database is up and running. However, if this is done while Sterling B2B Integrator is running, it might lead to database deadlocks and have an impact to the application. Therefore, it is not recommended to run the scripts while the application is running. But, if you must run these scripts while the application is running, then you must run them only during non-peak times.
-i

Use a list of the table indexes that need to be rebuilt (optimized). Must be used with -l or -r. Either -i or -a is required.
-a Use a list of the tables to analyze. Must be used with -l or -r. Either -i or -a is required.
-t

Date and time cutoff to look for expired data. Format: yyyyMMdd-HH:mm:ss.SSS. Default is current date. This date and time will be used as the starting point to check for expired tables. If the table has expired since the last time it was rebuilt, that table is added to a list for rebuild or index analyzing.

Must be used with -l or -r. Optional.

-o Specifies a file name to save output messages to. Provide a full path to the file. If not used, output will be displayed only on the screen. Must be used with -l or -r. Optional.
-d Check all tables. If not used, the check will only be done on tables that were recorded on the database after the last rebuilds. Must be used with -l or -r. Optional.
-p

Print the stack trace if there is an exception.

If used with the -o option, the stack trace will print to a file. Otherwise, it is displayed on the screen. Must be used with -l or -r. Optional.
-h or -? View the help screen.

Command Examples

To view a list of table indexes that have to be rebuilt, and to save the output to a file named myList, go to the install_dir/bin directory and run the following command:

db_optimization_tool.sh -l -i -o myList

To rebuild the indexes for all the tables, run the following command:

db_optimization_tool.sh -r -i -d