Beginning from Kingston Patch 4+ and Jakarta Patch 9+, coalesce jobs get stuck in Running because the Field Normalization configuration fails to coalesce to a single reference.
Steps to Reproduce
Create five records in location table as follows (with name as Location1, Location2, Location3, Location4, Location5).
Create one more record with name as LocationCentral in location table.
Note that there are six records on location table.
Go to Normalization and create a normalization called "Location Name Normalization".
For more information, see the product documentation topic Normalization Data Services.
Set the following properties:
- Table: cmn_location
- Field: Name
- Coalesce each normal: Checked
Save the records and do not click on Collect Pending Values in related links.
Under the Normalized Value tab, click New to create a new entry, provide the following values for it, and save it.
- Value: Test
- Coalesce to: LocationCentral
In the Alias tab on the form that appears, click on Aliases under related links and add all the values created earlier (Location1 through Location5).
Go back to Normalization and activate and run the jobs.
After a while, check the locations.
Note that location1 through location5 are normalized to LocationCentral but you see all six records.
The expectation is to have one single record because Coalesce was selected.
If you go to the data jobs, note that all of these records are stuck in Running.
Navigate to the Run Job business rule on the fn_extant_data_normalization table.
Change the When field from async to "after".
Replace the script with the contents of the attached text file (workaround_br_replacement_script (1).txt).
For more information, see the documentation topic Coalesce records on a normal value.
Related Problem: PRB1286063