Overview of Using Partitions for Process Audit Log Data
In the default stored procedure method of archiving, the stored procedures search for the records to archive (or delete) row by row, based on the input criteria. This is generally not a problem for smaller databases, but the process can be time-consuming for a large database with many audit records to be archived.
As an alternative to using stored procedures to archive and delete Process Audit data, you can use database partitioning, an option that greatly decreases the time required to archive and delete data. The database partitions themselves are a standard feature of each database vendor, although you may need to purchase a separate partitioning license from your database vendor if you do not already have one. Monitor provides Oracle, Microsoft SQl, and IBM DB2 database scripts to configure and manage your partitions.
Note: Partition archiving support is only provided for Process Audit data. You must continue to use stored procedures for all other audit data.
To archive or delete audit data with partitioning, the first step is to define the needed partitions. Then, when you archive a partition, the script moves it from your active Process Audit database to the archive Process Audit database, and operation that typically takes seconds to complete, compared with archiving by stored procedures, which can take hours. To delete data, you drop the relevant partition.
Each partition stores only those records that fall within the partition’s date range based on the column, ATRESTTIMESTAMP. When creating partitions, adhere to the following rules:
Create as many partitions as you need.
Configure each partition with a non-overlapping date range.
Define every Process Audit database table with identical partitions.
Monitor stores process instances that are still running in a partition named WM_FUTURE (Oracle and DB2) or partition 1 (MS SQL). As audit data is written to the Process Audit tables, Monitor automatically writes audit data to this partition. This partition stores all audit data that is not yet considered complete. When a process instance completes, Monitor updates the ATRESTTIMESTAMP with the final completion date and moves all associated audit entries to the appropriate partition. This guarantees that all related audit data for a process instance exists in the same partition.