SI Database Maintenance
Sterling Commerce documentation is used to create this article!!
Database Maintenance Check
Sterling Integrator performs a scheduled maintenance check on the database every Monday at 4:00 a.m. This 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.
db_optimization_tool
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.
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.
|
|
-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.
|
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
View the Database Statistics Report
To view information about business processes in the database:
- 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.
- Click source manager.
- Select the appropriate report format from the drop-down list and click execute.
Data Sweeper Service
The Data Sweeper service is an optionally scheduled system service that cleans up data that is not in use and not cleaned by other system clean up processes due to lack of any continued associations to the data.
Data Sweeper service is a system service that runs a business process. You can run
DataSweeper.sh or DataSweeper.cmd from the command line. ARCHIVE_INFO table
We can check how many records there are in a database, that are not indexed or waiting for archiving.
|
ARCHIVE_FLAG
|
Meaning
|
|
-1
|
Not Indexed
|
|
0
|
Indexed, awaiting Archiving
|
|
1
|
Lifespan Exceeded, Ready for Purge
|
|
2
|
Archive Complete, Ready for Purge
|
Try to execute the following command and see how many un-indexed records there are in a database:
For example:
select count(*) from ARCHIVE_INFO where ARCHIVE_FLAG = -1
BP States (in troubleshooter)
We have to check if there are processes in the system that are in Halted or Interupted state, as such processes will never be archived and purged.
Only Completed and Terminated states are properly indexed, archived and purged.
From the troubleshooter, click on Business Process Usage. You'll see a listing of processes in various states.
Click on the number of processes listed for both Halted and Interrupted_Manual processes. That'll pop up another box that will allow you to select all the processes, choose Terminate from the dropdown, and click Go to terminate all the processes.
Use the ControllerWorkflow script in the bin directory to mass terminate Halted/Interrupted BP's. You should be able to run it once from the command line to get the usage:
Usage: ControllerWorkFlow -t [H/W/I/N [name]/D [Id]]/-s [N [name]/D [Id]] /-r [N [name]/D [Id]]/-s [N [name]/D [Id]] /-e [N [name]/ A/ D/ T [start_time] [end_time]]
t - Terminate all WorkFlows
H - Terminate halted WorkFlows only
W - Terminate waiting WorkFlows only
I - Terminate interrupted WorkFlows only
N [name] - Terminate WorkFlow specified by name
D [Id] - Terminate WorkFlow specified by the given ID
f - Force Terminate all WorkFlows
H - Force Terminate halted WorkFlows only
W - Force Terminate waiting WorkFlows only
I - Force Terminate interrupted WorkFlows only
N [name] - Force Terminate WorkFlow specified by name
D [Id] - Force Terminate WorkFlow specified by the given ID
s - Stop all active WorkFlows
N [name] - Stop WorkFlow specified by name
D [Id] - Stop WorkFlow specified by the given ID
m - Resume all halted WorkFlows
N [name] - Resume a halted WorkFlow specified by name
D [Id] - Resume a halted WorkFlow specified by the given ID
r - Restart all halted/interrupted WorkFlows
N [name] - Restart a halted/interrupted WorkFlow specified by name
D [Id] - Restart a halted/interrupted WorkFlow specified by the given ID
e - Restart all completed with errors WorkFlows
N [name] - Restart completed with errors WorkFlows specified by name
[A/D] A - Restart completed with errors WorkFlows in ascending order
D - Restart completed with errors WorkFlows in descending order
T [start date] [end date] - Restart completed with errors WorkFlows in the time interval between the dates given
- Dates have to be in the format ["mm/dd/yyyy HH:MM:SS [AM/PM]" ]: Do not forget the quotes
- THERE IS A SPACE BETWEEN PARAMETERS
Archive and Purge
- Archive Manager settings
Go to Administration Menu …. Operations >> Archive Manager >> Configure Archive Settings
You can check how often backup is done, as well as you can go to
Administration menu … Business Processes >> Monitor >> Central Search and search for ‘backup’. You can see if your backup process is executed regularly, that is once a day, at 2am by default:
Please check in Monitor >> Central Search if the following processes are run regularly:
|
Business Process
|
Schedule period
|
|
Schedule_IndexBusinessProcessService
|
Every 10 minutes (default setting)
|
|
Schedule_BackupService
|
Once a day, 2 AM (default setting)
|
|
Schedule_PurgeService
|
Every 10 minutes (default setting)
|
- Backup Service settings
Also go to Administration Menu … Deployment >> Configuration >> Search for BackupService >> edit it and go to Configuration
File System cleaning
The following options are more for cleaning the file system of the documents that are placed into the File System, but also connected with a DB.
- archivethread.properties
Check archivethread.properties file
PURGE_DOCS_ON_DISK
Specifies whether documents that do not go through the archive process are immediately removed from disk during purge. If PURGE_DOCS_ON_DISK is enabled, these documents will not appear in the purge_dod_list.txt file but will be removed from disk. Documents that go through the archive process will still be placed into the purge_dod_list.txt file and remain on disk until removed either manually or by some scheduled process. Valid values:
- true - enable PURGE_DOCS_ON_DISK
- false - disable PURGE_DOCS_ON_DISK
- Schedule_AssociateBPsToDocs
Possible orphan data in the Gentran Integration Suite database could be left from the AssociateBPsToDocs process.
Description: When the Schedule_AssociateDocsToBP process runs to claim unassociated documents in the system, it may leave Document Extension tracking records behind, causing the main data storage table in Gentran Integration Suite to grow.
Should be solved with support’s help!
