Modifying the Indexes Used on the 'ec_managed_obj_lifecycle' Table

Modifying the indexes used on the ec_managed_obj_lifecycle table:

  1. On SQL Server: prevents a known issue from occurring where, in certain circumstances, deadlocks can occur on the ec_managed_obj_lifecycle table.
    Note: If deadlocks occur on the ec_managed_obj_lifecycle table, an error similar to the following example is written to the BPM log file: 
    [ERROR] org.hibernate.util.JDBCExceptionReporter - Transaction (Process ID 110) was deadlocked on lock | communication buffer 
resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  2. On SQL Server, Oracle or DB2: improves database query performance.

The required index modifications are:

  1. Remove the existing clustered index - the event_pk primary key - from the ec_managed_obj_lifecycle table. (event_pk can be safely removed as it is not used by any queries.)
  2. Add a new clustered index on the managed_obj_id column of the ec_managed_obj_lifecycle table.

Procedure

  1. Take a backup of the BPM database.
  2. Create and run a SQL script that executes the following SQL commands on the BPM database:
    Database Required SQL Commands
    SQL Server 
    BEGIN
DECLARE @pk NVARCHAR(256);
BEGIN 
SELECT @pk = name
FROM sys.indexes 
WHERE name LIKE 'PK__ec_manag__%' AND object_id = (
SELECT object_id
FROM sys.tables 
WHERE name = 'ec_managed_obj_lifecycle'
);
EXECUTE ('ALTER TABLE amxbpm.ec_managed_obj_lifecycle DROP CONSTRAINT ' + @pk);
END
END
GO
     
CREATE CLUSTERED INDEX ix_ec_managed_obj_lifecycle_idx2 ON amxbpm.ec_managed_obj_lifecycle(managed_obj_id)
GO
    Oracle 
    ALTER TABLE ec_managed_obj_lifecycle DROP CONSTRAINT pk_ec_managed_obj_lifecycle;

CREATE INDEX ix_ec_lifecycle_idx2 ON ec_managed_obj_lifecycle(managed_obj_id);
    DB2 
    ALTER TABLE ec_managed_obj_lifecycle DROP CONSTRAINT pk_ec_managed_obj_lifecycle/

CREATE INDEX ix_ec_managed_obj_lifecycle_idx2 ON ec_managed_obj_lifecycle(managed_obj_id)/
    Note: As Event Collector tables can be very large, these commands can take a significant amount of time to complete.