ETL9.2:One Instance ST_LOADER Fails with Unique Constraint Error and Other Duplicate Errors. (Doc ID 2036852.1)

Last updated on JULY 21, 2023

Applies to:

Symptoms

Frequent ST LOADER errors are occurring. One Instance ST_LOADER Fails with Unique Constraint Error causing all subsequent ST_LOADER threads to fail as they seem to process the same error employee again. The first fail or error often is due to a duplicate punch or an out of sequence punch on the time-sheet. What accentuates the problem is the way all subsequent ST_LOADER jobs try to reprocess all punches again from the failed Instance and error out. This perpetual ripple effect has created some duplicate punches on the time-sheet also.

Data is coming via xml message from tcd. TL_ST_LOADER are being triggered one after another by the messages to process large amount of data. They way duplicate punches happens is as documented below. First tl_st_loader is processing the punches and to  mark  the punches as  processed updates st_instance to y in intermediate tables. Then second one is  running and processing another set of data , while second one is running the  third one comes and before second one can mark the st_instance to y indicating punches has been loaded the third one picked up same transactions along with it's own and causes duplicates.

We would like to have a better understanding of the Program and also would be interested to know how we could prevent the new ST_LOADERS from erroring out.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.