Maintaining the enterprise database

The enterprise database used with a HCL Traveler High Availability pool requires periodic maintenance just like the internal database used by a standalone server.

After many changes to table data, the table and its indexes can become fragmented. Logically sequential data might reside on nonsequential pages, forcing the database manager to perform additional read operations to access data. Enterprise database servers provide utilities for maintaining the databases. For example, DB2® provides REORG and RUNSTATS utilities to help maintain optimal performance for the database. These utilities can be configured to run automatically. Microsoft SQL Server and MySQL both provide similar index reorganization utilities. Whether run manually or automatically, a regularly scheduled maintenance plan is essential to maintain peak performance of your system.
Note: Maintenance should be run on all of the tables in the database. We recommend performing online index maintenance once a week during non-peak hours and offline maintenance about once a month. Consult the database server product documentation for more information on database maintenance.
Note: Since server version 9.0.1.8, HCL Traveler includes the ability to run database maintenance. For more information, refer to HCL Traveler database maintenance for standalone servers.

Online database maintenance

The database allows read and write access during maintenance action and there is minimum performance impact to the application.
DB2
REORG INDEXES ALL FOR TABLE <tablename> CLEANUP ONLY
For more information about this command and performing REORG commands for DB2, refer to the information on the REORG INDEXES/TABLE command in this topic.
Microsoft SQL Server
ALTER INDEX ALL ON <tablename> REORGANIZE
For more information about this statement and maintaining indexes, refer to the Reorganize and Rebuild Indexes section of this link.
MySQL
ANALYZETABLE <tablename> ENGINE= <table engine>;
Rebuilding the indexes can be done through analyzing the table. For more information, see this article.

Offline database maintenance

The database only allows read access during maintenance, does not allow any access during maintenance, or may experience excessive locking during maintenance. Because of this, the offline maintenance should be run with Traveler shutdown on all servers or other blocking in place to prevent any of the Traveler servers from trying to connect to the database during maintenance. If the database is highly available or has secondary clusters, the maintenance only needs to be run on the primary database.
DB2
REORG INDEXES ALL FOR TABLE <tablename>
REORG TABLE <tablename>
For more information about performing REORG commands for DB2, refer to the information on the REORG INDEXES/TABLE command in this topic
Microsoft SQL Server
ALTER INDEX ALL ON <tablename> REBUILD
For more information about this statement and performing ALTER INDEX commands for Microsoft SQL Server, refer to the information on the ALTER INDEX section of this link.
ALTER TABLE <tablename> REBUILD
For more information about this statement and performing ALTER TABLE commands for Microsoft SQL Server, refer to the information in the ALTER TABLE section of this link.
DBCC CHECKDB
For more information about performing DBCC CHECKDB commands for Microsoft SQL Server, refer to the information in the DBCC CHECKDB section of this link.
MySQL
ALTER TABLE <tablename> ENGINE= <table engine>;
Rebuilding the table can be done in a stationary manner by setting the table engine to the previous value. The default table engine is INNODB. For more information, see this article.