As Content Manager OnDemand servers age, metadata stored in segment tables in the database can become fragmented. There’s a variety of reasons why this problem can develop:
- Historical data was loaded at the same time current data was being loaded
- Source systems provided documents for a variety of dates at load time
- CMOD’s Enhanced Retention Management is holding selected documents
- Bad table segment size configuration for Application Groups
Just like de-fragmenting a hard drive back in the 1990s and early 2000’s, performing a Table Consolidation can improve performance of your OnDemand archive by re-ordering the location of specific records in the database, or consolidating multiple tables into a single, re-organized, indexed and optimized one.
Our Table Consolidation Tool has the following features:
- A cautious and methodical process for combining tables
- Runs online, with near-zero impact to end users
- Uses standard DB2 methods for performing the movement of data
- Maintains table indexing, annotations, and storage set information
- Does not delete document metadata in tables until it has been moved & verified
- Creates backup files for rolling back changes
- Can be stopped and restarted, and will resume consolidations from where it stopped
- Results in one or more optimized database tables
Here are some situations where we’ve performed OnDemand Table Consolidations:
A top-ten Canadian Bank was having recurring performance issues during the busiest parts of the day. Adding single-field and composite indexes were helping, but a high volume of documents and a default segment size were hindering performance. By consolidating over 20 tables with 10 million rows each, a single 200-million-row table was created that contained data for the prior 18 months of activity. After the consolidation was complete, the table was altered to enable compression, indexes added, the table re-organized, and statistics collected. Afterwards, ALL queries were restored to sub-second performance.
A New-Jersey based finance company had requested assistance with their Content Manager OnDemand server, because they were having problems loading new data. It was discovered that an administrator who wasn’t experienced with CMOD had altered the Segment Table size in the OnDemand Application Group configuration to be 1000, when the acceptable range is anywhere from 10 million to 250 million. This caused CM OnDemand to create over 30,000 database tables for the one Application Group — preventing new loads due to a DB2 limitation on the number of database tables per instance. On the first evening, we ran the automatic Table Consolidation tool, and combined nearly 500 tables overnight. A decision was made the next morning to leave the tool running, and over the course of two weeks, performed the CMOD Repair with no downtime and no impact to end users.