Using the Database Maintenance Utility

By default, Database Maintenance is grouped under the System Management group in the

Navigation pane.

The transactional tables within the MES database grow rapidly and continuously. To keep the size of the MES database within manageable limits, you should archive and/or purge the database at regular time intervals. You can archive all the data of large tables and the associated (dependency) tables and then restore them to another database for reporting. In some selected cases, you might purge the data from the database if the data is no longer important for reporting by your users. This keeps the database from growing too large and slowing the performance of the reports.

You can create and manage archive, purge, and restore jobs using the Database Maintenance

editor. The Database Maintenance editor allows you to:

• Create new jobs
• Run jobs
• View jobs
• Modify jobs
• Delete jobs
• Access job logs
• In an environment where there is significant activity from the production transactions, the APR node should be configured to use a separate Middleware Server from the production Middleware Servers.

Archive and Purge Jobs

You can manually run the archive and purge jobs on the MES database to archive and/or purge the data and run the restore jobs on the non-production database. You can manually select one of the APR jobs currently defined in the database for execution. When you select a job, all configuration information for that specific job is shown. You can review and modify the specifications for the job before executing it.

• If you run an archive job or an archive purge job, the following is created on the DB Maintenance Service (at the configured root path):
• A directory is created with the job name, if it doesn't exists already

Under the job directory, as defined in the Archive root directory path in the General Parameters - Archive section, a time-stamped ZIP file is created that contains a .CSV file for each archived table. The ZIP file also contains a manifest file. A log file with the same name is created alongside the ZIP file.

You can remove an existing job using the Database Maintenance section. Before removing the job, make sure that the job is not running.

If you run a purge job or a restore job, the following is created on the DB Maintenance Service (at the configured root path):

• A directory is created with the job name, if it doesn't already exist
• Under the job directory, a time-stamped log file is created

You can schedule the APR job using the Windows Task Scheduler on the APR server. This allows the APR jobs to run automatically at a pre-determined time or on a regular basis, such as daily, monthly, and so on.

You must configure the scheduled job on the same computer as the APR server using the Windows Task Scheduler. The proxy must be configured to send requests to this service in order for the command line program to send its requests.

Archive Jobs

You can create a new archive job to execute a defined archive operation. This job will retrieve selected records from the MES database and record them in an archive file. You can also configure the archive jobs to execute a purge operation on the archived records following the successful completion of the archive operation.

To create an archive job:

1. Add a new job to the list and select its type.
2. Select the table or tables to be archived. The list of tables available represent the top level tables. All dependent tables related to the top level table are also included in the APR job.
3. Decide on the range of data to be archived and purged. This can be based on a fixed time range or a relative time range. It is also possible to specify a work order by name.

When the archive job is run, it will only archive work orders where all the jobs within the work order have been completed. Active jobs will prohibit a work order from being archived.

Restore Jobs

You can create a restore job to execute a restore operation. This job will insert records from an archive file into the MES Restore DB. The MES database must already exist and will not be created as part of the restore operation.

Purge Jobs

You can create a purge job to execute a purge operation. This job will remove records from an active, production MES database to free-up resources on the database server. The purge operation can be done independently or as a follow-up to an archive job.