Migrating Data Stored in Event Collector

If you are upgrading to this version of ActiveMatrix BPM from:
  • 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:
    • any process instance that was started on the pre-upgrade version 4.x system prior to the upgrade to this version.
    • any sub-process instance that is started on this version, but which has a parent process instance that was started on the pre-upgrade version 4.x system.
    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

The following migration scripts are supplied in TIBCO_HOME\bpm\n.n\scripts\migration\database, where n.n is the version of ActiveMatrix BPM and database is the type of database you are using (db2, mssql or oracle):

Script Description
install-migration-tools.sqlCreates the ec_migration table and a number of stored procedures.
execute-migration.sqlExecutes the amxbpm_migrate_events stored procedure to perform event migration.
execute-set-lifecycle-ids.sqlExecutes 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.sqlExecutes 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.sqlRemoves 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).
Important: Depending on the number of events migrated and commit batch size used, each execution of amxbpm_migrate_events() could result in large transactions and take a long time to execute. Initially, you should execute amxbpm_migrate_events() with the default values. Depending on your database resources and load, you can then gauge how many events can be processed safely in a single call to amxbpm_migrate_events(), without exceeding the database resources.

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:
    • 0 - Data for orphaned sub-process instances will not be purged (default option).
    • 1 - Data for orphaned sub-process instances will be 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:

  • batch_size - is the number of rows to update before performing a commit.
  • max_updates - is the maximum number of updates to perform before exiting.
Before you begin
  • 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:
    • TEMP space for SQL Server
    • Rollback space for Oracle
    • Transaction Log Files for DB2
    Procedure
  1. Copy the migration scripts from TIBCO_HOME\bpm\n.n\scripts\migration\database to a temporary location on the server.
  2. 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).
  3. Run the install-migration-tools.sql script.
  4. If you are upgrading from a pre-4.0 version:
    1. Execute the amxbpm_migrate_events stored procedure to migrate the audit data.
      Note: Execute the procedure as many times as you need to migrate all of your audit data.
    2. When migration is complete, execute the amxbpm_set_lifecycle_ids stored procedure.
  5. If you are upgrading from version 4.0, 4.0.0_HF_x, 4.1 or 4.1.0_HF-001:
    1. Execute the amxbpm_set_lifecycle_root_ids stored procedure, using appropriate parameters.
      Note: You may need to run amxbpm_set_lifecycle_root_ids repeatedly, until all process instances that were started on thepre-upgrade version 4.x system have completed or been cancelled.

      You may then need to run amxbpm_set_lifecycle_root_ids again periodically, to catch any newly completed or cancelled process instances. Repeat this cycle until you are certain that all process instances started on the pre-upgrade version 4.x system have completed or been cancelled.

  6. Run the remove-migration-tools.sql script.
  7. Delete the scripts from the temporary location.