Performance issues caused by "ECC Queue - mark outputs state" BR unable to update ECC Queue Output records to "processing" state, leading to the same job being run by the MID Server multiple times, multiple Inputs for the sane Output in the ECC Queue


Description

After upgrading to Madrid "ECC Queue - mark outputs state" Business Rule does not update ECC Queue Output record to "processing" state leading to performance issue. The MID Server will only stop re-running the output once an input returns and causes the output to be set as processed.

(more on the ecc_queue flow can be seen in KB0855595 How the ECC Queue works - from output ready, to input processed)

Symptoms:

This only happens on instances where the ecc_queue table does not use Table Rotation, when the Business rule is looking for the specific rotation shard, which won't exist. Most instances have a Rotation of 7 x 1 day, but some have been customised to remove the rotation.

JDBC Data Sources, LDAP Imports or other Orchestration/Integration Hub database related jobs are most likely to see this problem. That is because:

Steps to Reproduce

  1. On Madrid instance, remove the table rotation for the ecc_queue table.
  2. Install SQL Server on a Windows host.
  3. Create a DB (for example, 'SNCTEST').
  4. Create a table (for example, 'Employee') with two columns: ID and Name.
  5. Insert 100000 rows into the table.
  6. Create a staging table called 'u_employee_staging' extending the 'Import Set Row' table.
  7. Configure JDBC Data Source to the above DB and query the 'Employee' table into the above 'u_employee_staging' table.
  8. Click Load All Records in the related link.

This generates a JDBCProbe output record. Observe that the MID Server will continuously process the output record for several iterations until the last JDBC result for the first iteration is sent back to the instance, which would update the output record to 'Processed'.

Workaround

This problem has been fixed. If you are able to upgrade, review the Fixed In or Intended Fix Version fields to determine whether any versions have a planned or permanent fix.


Related Problem: PRB1368654