Migrating Data Stored in Event Collector

The Event Collector schema in version 4.x of ActiveMatrix BPM has changed significantly from the schema in version 2.x and 3.x. If you are upgrading to version 4.x from a 2.x or 3.x version of ActiveMatrix BPM, audit data from your previous ActiveMatrix BPM system is unavailable after the upgrade. For example, events that have not been migrated do not appear in the graphical audit trail. To migrate your data from your previous ActiveMatrix BPM system to your upgraded ActiveMatrix BPM system, you must manually execute a stored procedure after you have performed the upgrade.

Note: You do not need to perform this task if you are upgrading from an earlier 4.x version of ActiveMatrix BPM to a later 4.x version.

The stored procedures are available for different databases, for example, Microsoft SQL Server, Oracle, and DB2. The installer installs scripts containing the stored procedures for the database at 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.

There are three scripts:
  • install-migration-tools.sql - Installs the stored procedures and creates the ec_migration table used to migrate the data.
  • execute-migration.sql - This script executes the amxbpm_migrate_events() stored procedure. This performs the event migration.
  • remove-migration-tools.sql - Removes the stored procedures used to migrate the data.
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 of 20,000 events and batch size of 500. 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.

Prerequisites

  • The 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
  • These stored procedures do not include any provision for backing up or archiving your database. You must backup 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.

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.

Procedure