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.
   
2. on SQL Server, Oracle or DB2: improves database query performance.

On SQL Server, if deadlocks occur on the 'ec_managed_obj_lifecycle' table, an error similar to the following example is
written to the BPM log file:

   28 Jan 2019 15:18:10,042 [PVM:DB Process Worker-25] [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.
   28 Jan 2019 15:18:10,042 [PVM:DB Process Worker-25] [ERROR] com.tibco.n2.ec.core.triggers.TriggerExecutor - =================================================
   28 Jan 2019 15:18:10,042 [PVM:DB Process Worker-25] [ERROR] com.tibco.n2.ec.core.triggers.TriggerExecutor - Error
   processing trigger com.tibco.n2.ec.core.triggers.impl.ManagedObjectLifecycleTrigger@617c0e70!
   org.hibernate.exception.LockAcquisitionException: could not execute native bulk manipulation query
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
       .
       .
       at com.tibco.n2.ec.core.triggers.impl.ManagedObjectLifecycleTrigger.handleEvent(ManagedObjectLifecycleTrigger.
       java:70)
       .
       .
   Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 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.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.
       java:390)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.
       java:340)
       at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
       at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:308)
       at org.tranql.connector.jdbc.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:165)
       at org.tranql.connector.jdbc.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:106)
       at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
       ... 52 more

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.

All supported operating systems and databases

To make these modifications:

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:

   NOTE: As Event Collector tables can be very large, these commands can take a significant amount of time to complete.

   * 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:
Following the guidance on indexes in the TIBCO ActiveMatrix BPM Installation And Configuration guide, this index should be removed before performing an upgrade and re-applied post upgrade.

Modifying the Indexes Used on the 'ec_managed_obj_lifecycle' Table Avoids Deadlocks on SQL Server and Improves Performance