Migrating Data Stored in Event Collector
- a pre-4.0 version: The Event Collector schema used in ActiveMatrix BPM 4.x versions is significantly different from the schema used in earlier versions. As a result, audit data from your earlier version is not automatically available after the upgrade. For example, events that have not been migrated do not appear in the graphical audit trail.
If you want to migrate existing audit data so that it is still available after the upgrade, you must run the amxbpm_migrate_events and amxbpm_set_lifecycle_ids stored procedures.
- version 4.0, 4.0.0_HF_x, 4.1, 4.1.0_HF-001: Version 4.1.0_HF-002 introduced a change to the way that lifecycle events for process instances are recorded in the BPM database. As a result, the following process instances are not correctly recorded as completed in the ec_managed_obj_lifecycle database table when upgrading from a system using one of these versions: This results in a warning message in the BPM log file, and also means that these instances cannot be purged or archived. To correct this, you may need to run the amxbpm_set_lifecycle_root_ids stored procedure, to ensure that completed or cancelled process instances can be purged or archived correctly.
Migration Scripts
Script | Description |
---|---|
install-migration-tools.sql | Creates the ec_migration table and a number of stored procedures. |
execute-migration.sql | Executes the amxbpm_migrate_events stored procedure to perform event migration. |
execute-set-lifecycle-ids.sql | Executes the amxbpm_set_lifecycle_ids stored procedure. This ensures that when data is purged, sub-process instance data is only purged when its parent process instance has already completed. |
execute-set-lifecycle-root-ids.sql | Executes the amxbpm_set_lifecycle_root_ids stored procedure. This corrects the information held in the ec_managed_obj_lifecycle table for process instances that were started on a pre-upgrade version 4.x system, meaning that they can be subsequently purged or archived. |
remove-migration-tools.sql | Removes the ec_migration table and the stored procedures. |
Duplicate Message ID Values
Instead of all the message-related attributes being persisted in the ec_event_int table for every event, they are now persisted once in the ec_message table. They are then referenced from the ec_message table by the message_id value. This means the message_id value in the ec_message table must be unique. However, it is possible that existing data on your system could have multiple events that use different values for message, priority, severity, component_id and/or message_category, but use the same message_id, therefore creating more than one message type with the same message_id. When upgrading, two different message types with the same message_id cannot be inserted into the ec_message table. Therefore, migration will insert all the message variants for a message_id, but the events will only reference the first instance that is inserted and subsequent, duplicate message_id inserts will add a sequence number to the message_id to make it unique. For example, CUST_MSG, CUST_MSG_1, CUST_MSG_2 and so on. These messages are not referenced from the event. They are simply being persisted so that the information is not lost.
amxbpm_migrate_events Stored Procedure
The amxbpm_migrate_events stored procedure is used to perform event migration. You can execute it either directly, or by running the execute-migration.sql script.
amxbpm_migrate_events takes the following parameters:
amxbpm_migrate_events (num_events, batch_size)
where:
- num_events - is the number of events to be migrated (default: 20000).
- batch_size - is the batch size to commit (default: 500).
amxbpm_set_lifecycle_ids Stored Procedure
The amxbpm_set_lifecycle_ids stored procedure ensures that when data is purged, sub-process instance data is only purged when its parent process instance has already completed. You can execute it either directly, or by running the execute-set-lifecycle-ids.sql script.
amxbpm_set_lifecycle_ids takes the following parameters:
amxbpm_set_lifecycle_ids (batch_size, max_updates, insert_active, set_orphaned_as_root)
where:
- batch_size - is the number of rows to update before performing a commit (default: 20000).
- max_updates - is the maximum number of updates to perform before exiting (default: 1000000).
- insert_active - must be set to '1' when migrating to this version.
- set_orphaned_as_root - defines how any orphaned sub-process data (which may exist if only some audit data was migrated from the old version) is treated when data is purged:
amxbpm_set_lifecycle_root_ids Stored Procedure
The amxbpm_set_lifecycle_root_ids stored procedure corrects the information held in the ec_managed_obj_lifecycle table for affected process instances, meaning that they can be subsequently purged or archived. You can execute it either directly, or by running the execute-set-lifecycle-root-ids.sql script.
amxbpm_set_lifecycle_root_ids (batch_size, max_updates)
where:
Prerequisites
- These stored procedures do not include any provision for backing up or archiving your database. You must back up your database, according to whatever backup strategy your organization has implemented, before initiating the stored procedures. Refer to the documentation supplied with your database server for information on how to do this.
- The amxbpm_migrate_events stored procedure copies data from the ec_event_old table to the ec_event_int table. Therefore, you must make sure that your database is the correct size. Your database must be of sufficient size to store the current ec_event_old table, twice over. Depending on the database you are using, you need to consider the following resource limits:
Procedure
- Copy the migration scripts from TIBCO_HOME\bpm\n.n\scripts\migration\database to a temporary location on the server.
- From an appropriate database query/management tool (for example, SQL Server Management Studio, Oracle SQL*Plus or a DB2 command window), connect to the BPM database using the BPM database user credentials (default: bpmuser).
- Run the install-migration-tools.sql script.
-
If you are upgrading from a pre-4.0 version:
-
If you are upgrading from version 4.0, 4.0.0_HF_x, 4.1 or 4.1.0_HF-001:
- Run the remove-migration-tools.sql script.
- Delete the scripts from the temporary location.