IBM Support

How do I manage the size of the IBM FileNet Business Process Manager Process Analyzer database?

Question & Answer


Question

What can I do to reduce the size of the VMAEDM database?

Cause

Normal usage will cause the database to grow. To improve performance and conserve disk resources, it may be necessary to decrease the size of the tables.

Answer

Use the built-in Process Analyzer prune function.

Process Analyzer Process Task Manager -> Action menu -> Prune Events (Start) command.

The prune function deletes all event information in the VMAEDM database associated with terminated workflows. These events have already been processed and are not necessary to generate reports. As the information as already been moved into the OLAP database, the reports will continue to display data from terminated workflows whose events are deleted by the event pruning job

Pruning deletes records from the F_Events, D_DMWorkItem, D_DMWorkflow, and X_TerminatedWorkflows tables.


Manually Trim the additional Process Analyzer Fact Tables



There are four historical fact tables that contain data from terminated workflows necessary to generate reports. The size of these tables can be reduced manually, but you should only remove aged data that is no longer required for reports. This is done by choosing a date and then removing all records in the table from before that date. In the steps below, this referred to as the "threshold date".

The threshold date value is the date from which all previous historical Process Analyzer data can be deleted. All table entries with a date older than the threshold date will be removed.

Determine your threshold date value and then use the delete statements below in the specified order. Make sure to execute them one at a time during non-production hours with Process Analyzer stopped. They can be executed over the span of several days, but must be done in the this order.

Use your threshold date value in place of '2010-09-01 12:00:00.000' and run all of the commands directly on the VMAEDM database.

    1. Make a full backup of the VMAEDM Process Analyzer database.
    2. Delete F_DMQueueLoad data:
      DELETE FROM F_DMQueueLoad WHERE TimeInterval <= '2010-09-01 12:00:00.000'
    3. Delete F_DMProductivity data:
      DELETE FROM F_DMProductivity WHERE TimeInterval <= '2010-09-01 12:00:00.000'
    4. Delete F_DMWorkload data:
      DELETE FROM F_DMWorkload WHERE TimeInterval <= '2010-09-01 12:00:00.000'
    5. Delete F_DMRouting data:
      DELETE FROM F_DMRouting WHERE TimeInterval <= '2010-09-01 12:00:00.000'

[{"Product":{"code":"SSTHRT","label":"IBM Case Foundation"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Process Analyzer","Platform":[{"code":"PF033","label":"Windows"}],"Version":"5.0;4.5.1;4.5.0;4.0.3;4.0.2;4.0.1;4.0","Edition":"All Editions","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
17 June 2018

UID

swg21566580