Database Maintenance Recommendations

Archiving Tables in the Database

The following table lists and describes the tables that can be archived in the database.

Table	Table Name	Description	Archive Strategy
Measures	os_measure	Operational status information (usually timed) about processes that take place in the system (deploying a bundle, starting a service, merging, and so on).	As needed.
Work Orders	os_work_orders	A list of Work Order tasks generated by the system for execution.	As needed.
Work Order Items	os_work_order_item	A list of Work Order Items that represent the individual operations that take place during the execution of a Work Order.	As needed.
Change Data Capture (If enabled)	os_cdc_change	Captures changes that take place to subjects within the system.	As needed.
Change Data Capture Subscription	os_cdc_subscription	A list of subscribers to change data capture events.
Change Data Capture Trace	os_cdc_trace	Detailed information on a change data capture event.
Ramp Control	os_ramp_control	A list of subject operations that load data into the system from the relational on ramp.

Archiving Error and Information Logs in

All logs and system-generated information is stored in the omnigen/OmniGenData directory. The following list describes the location, purpose, and archiving recommendations of the system logs and deployment logs.

System Logs
- Location:
```
omnigen/OmniGenData/logs/{command, controller, server, OmniDesignerRepository, …etc}
```
  System logs are separated into subdirectories by application name.
- Purpose: Provides detailed records of a particular application component of the system.
- Archiving: In a high volume system, the controller and server log directories can grow to be quite large. These should be monitored and archived according to the requirements of the specific system (frequency and size threshold) the software is running on.
Deployment Logs
- Location:
```
omnigen/OmniGenData/deployment
```
- Purpose: Provides a detailed record of the steps that occurred during a bundle deployment.
- Archiving: The deployment logs are smaller in size (less than 20kB) and do not need to be archived.

General Maintenance Recommendations for Common Database Systems

The following list describes the general maintenance recommendations for common database systems.

Oracle
Tools: Oracle Database Resource Manager

Maintenance: Many of the generic maintenance tasks can be automated to run automatically during specific maintenance intervals.
1. Automatic Optimizer statistics collection: Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.
2. Automatic segment advisor: Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.
3. Automatic SQL tuning advisor: Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. You can configure this advisor to automatically implement SQL profile recommendations.
PostgreSQL
Tools:

Cron scripts, Windows Task Scheduler, and check_postgres are available for monitoring database health and reporting unusual conditions.

Maintenance:
1. Periodic vacuuming: Either manual or through a daemon.
  The PostgreSQL VACUUM command has to process each table on a regular basis for the following reasons:
  1. To recover or reuse disk space occupied by updated or deleted rows.
  2. To update data statistics used by the PostgreSQL query planner.
  3. To update the visibility map, which speeds up index-only scans.
  4. To protect against the loss of very old data due to transaction ID wraparound or multixact ID wraparound.
2. Update planner statistics: Analyze
3. Prevention transaction ID wraparound failures: Must be vacuumed at least once every two billion transactions.
4. Routine reindexing: Reclaim space and improve performance.
5. Maintaining log files: Log rotation, archiving, and deletion.
SQL Server
Tools: SQL Server Management Studio (SSMS)

Maintenance:

The Maintenance Plan Wizard can be started from SSMS and can be found in the Management section of the SSMS tree. It creates scheduled jobs, which are run by the SQL Server Agent and can perform the following tasks:
1. Reorganize index pages: The Reorganize Index task runs the ALTER INDEX statement with the REORGANIZE option on the indexes in the selected databases. This task helps to remove index fragmentation, but does not update index and column statistics. If you use this option to remove index fragmentation, then you will also need to run the Update Statistics task as part of the same Maintenance Plan.
2. Rebuild indexes: The Rebuild Index task runs the ALTER INDEX statement with the REBUILD option on indexes in the selected databases, by physically rebuilding indexes from scratch. This removes index fragmentation and updates statistics simultaneously.
3. Update statistics on the indexes: The Update Statistics task runs the sp_updatestats system stored procedure against the tables of the selected databases, updating index, and column statistics. It is normally run after the Reorganize Index task is run. Do not run it after running the Rebuild Index task, as the Rebuild Index task performs this same task automatically.
4. Backup database and transaction logs: The Back Up Database (Transaction Log) task allows you to specify the databases, destination files, and overwrite options for a transaction log backup.
5. Perform internal consistency checks: The Internal Consistency Checks task checks data and data pages within the database to make sure that a system or software problem has not damaged data.
6. Delete Backup and Restore History: The History Cleanup task deletes historical data from the SQL Server database, including historical data regarding backup and restore, SQL Server Agent, and Maintenance Plans. If you do not perform this task periodically, then over time, then the SQL Server database can grow very large.
7. Cleanup tasks: The Cleanup task allows you to define the databases for which you want to delete database task history.
DB2
1. REORGCHK / REORG: After many changes to table data that are caused by the insertion, deletion, and updating of variable length columns activity, logically sequential data can be located in on non-sequential physical data pages. Because of that, the database manager performs extra read operations to access data. Reorganize DB2 tables to eliminate fragmentation and reclaim space by using the REORG utility.
2. AUTO_RUNSTATS (automatic statistics collection): RUNSTATS (manual statistics collection).
3. DB2 Optimizer: Uses information and statistics in the DB2 catalog to determine the best access to the database, which is based on the query that is provided.
4. DB2 Health Monitor: Calculates health indicators based on data retrieval from database system monitor elements, the operating system, and the DB2 database.

Purging Old Data

systems maintain some runtime data, which diminishes over time. This data resides in a relational database as follows:

Work Orders (os_work_order)
Work Order Items (os_work_order_item)
Measures (os_measure)

The following server-based runtime settings are available to control purge events:

Purge Time. A cron-based expression that defines when to run the purge job. The default value is 0 0 3 * * ?, which indicates every day at 3:00am. For more information on cron expressions, see the following website:
```
https://docs.oracle.com/cd/E12058_01/doc/doc.1014/e12030/cron_expressions.htm
```
Purge Age. Defines the age of data (in days) that should be purged. The default value is 30 days.

Purging Inactive Data

The following server-based runtime settings are available to control the purging of inactive user data:

Purge Inactive Time. A cron-based expression that defines when to run a purge job for inactive data. The default value is 0 0 1 * * ?, which indicates every day at 1:00am. When this cron-based expression evaluates as true, a SETUP_DELETE_STALE work order is created, and only becomes active if there are no other work orders currently in progress.
When a SETUP_DELETE_STALE work order becomes active, a corresponding DELETE_STALE work order is issued for each subject in the model that is populated with data. In a DELETE_STALE work order, all inactive records of a given subject that were last updated before the value specified by the Purge Inactive Age setting are deleted. This includes source, instance, master, history, and master-master reference records.
Purge Inactive Age. Specifies the number of days after which inactive data (records) are purged. The default value is 10 days.

You can access these runtime settings from the Runtime tab, located under Configuration on the left pane of the Omni Console, as shown in the following image.