Runtime Error : DBSQL_DUPLICATE_KEY_ERROR

[Former Member]

Hi,

We are getting the dump DBSQL_DUPLICATE_KEY_ERROR in EM system.

Error analysis

    An exception has occurred which is explained in more detail below. The

    exception is assigned to class 'CX_SY_OPEN_SQL_DB' and was not caught in

     procedure

    "UPDATE_DB_TABLE" "(FORM)", nor was it propagated by a RAISING clause.

    Since the caller of the procedure could not have anticipated this

    exception, the current program was terminated.

    The reason for the exception is:

    When an Open SQL array insert is performed, trying to insert a record

    into the database table "/SAPTRX/EH_STHST" causes the function to terminate if

     a record with the same key already exists.

    (When an Open SQL individual record insert is performed, this situation

    does not cause the function to terminate. SY-SUBRC is set to 4 instead.).

More Info on the Issue

The primary key of /saptrx/eh_sthst table is guid + Sequence no.

To keep multiple values in this table, sequence no. should be different as guid will remain same for each event.

Upon checking, I found out that the system tried to push same guid and sequence no. for one event to /saptrx/eh_sthst table which resulted in these dumps.The logic to change the sequence no. is a standard one and is set to trigger in the set status activity.

Is there any OSS Note/ or any other way already available to mitigate the Issue. ..

Thanks in advance

[former_member190756]

Hello Ratnakar,

this happens only if there is a mismatch between the sequence number in the EH_HDR table and and the EH_STHST.

Normally this is caused when you do own database updates and not going through standard activities and methods.

You should first check if you have such coding doing direct updates on the status.

Best regards,

Steffen

[Former Member]

Hi Steffen,

Thanks for the swift reply.

I will check, the point mentioned by you..but I have few queries related to it.

Firstly, We are not getting this error all the time..

Secondly, We are getting this error only in those cases for which a large no. of line items are there for SO(OTC RDS Implementation)..

Is the Issue might be due to slow updation of /saptrx/eh_hdr before triggering of set status activity in case of huge no. of line items in a SO..

Regards,

Ratnakar

[former_member190756]

Hi Ratnakar,

in the standard the sequence number is set here:

/SAPTRX/CL_EH_PROCESS_MODEL->SET_STATUS.

  my_eh_access->my_data-hdr-last_sthst_nbr = my_eh_access->my_data-hdr-last_sthst_nbr  + 1.
  l_status_hist-seq_nbr = my_eh_access->my_data-hdr-last_sthst_nbr .

Here is guaranteed that the header get the current sequence number.

You should check if you have coding doing something similar.

STATUS_SET could be called form Rule processing and EH Update Activity and EE Monitor Activity. But EH is locked during update i.e. they can't get out of sync when one process takes longer.

You can also check notes for more background info:

SCM-EM-MGR    1481363    3    28    Terminations due to runtime error SAPSQL_ARRAY_INSERT_DUPREC

SCM-EM-MGR    2021795    2    25    Inconsistencies due to manually reprocessing Event Handlers

There is also a report attached to bring the inconsistent EH back to sync. But this does not fix the roor cause.

Best regards,

Steffen

[former_member190756]

I meant this two notes:

SCM-EM-MGR    1856226    2    37    Termin. due to runtime error SAPSQL_ARRAY_INSERT_DUPREC (2)

SCM-EM-MGR    1481363    3    28    Terminations due to runtime error SAPSQL_ARRAY_INSERT_DUPREC

[Former Member]

Hi Steffen,

I or Basis team  did not able to find these notes in service marketplace.

Could you kindly check the note numbers again and send me the content in pdf attachment please.

Thanks in advance..

Regards,

Ratnakar

[former_member190756]

Hi Ratnakar,

just seen that these note are pilot notes. Please open a ticket that they can also be released for your customer number.

Best regards,

Steffen

[Former Member]

Hi Ratnakar,

Some SAP OSS notes are customer specific, those won't be available in SAP Service Marketplace.

As Steffan suggested, you create SAP OSS message and give the SAP OSS message number here to check and address your issue soon. 

Quick Question:

"Firstly, We are not getting this error all the time..

Secondly, We are getting this error only in those cases for which a large no. of line items are there for SO(OTC RDS Implementation)..

Is the Issue might be due to slow updation of /saptrx/eh_hdr before triggering of set status activity in case of huge no. of line items in a SO.."

Can I know how the queue process sequence on SAP ECC side? Are you updating SO header status as well when SO line items were updating for tracking id's?

Regards

GGOPII

[Former Member]

Hi Gopi,

Sorry for late response...

we are facing issue during event message posting for delivery event.

I have observed that we are getting this issue whenever there is huge no. of line items in a SO that is posted earlier.

Regards,

Ratnakar

[Former Member]

Hi Ratna,

We had encountered same on Shipment when we assign more than 50 deliveries and it processed the queue very slow and sometimes got shot dump.  We have fixed through custom event extractors to update in sequence.

Check SAP is giving the solution and it fix the problem or else go to IMG -> Integration with Other mySAP.com Components -> Event Management Interface -> Define Application Interface -> Define SAP EM Extraction Functions -> create custom event extractor for Delivery to update without locking the object or wait to update.  Let me know the sequence what updates in SO and Delivery when there is a dump.  I can try to guide you to fix this if SAP OSS note can't fix this issue.

Regards

GGOPII