Short dump - CX_SY_OPEN_SQL_DB during MODIFY statement

[Former Member]

In production system, sometimes it dumps with this error:

Runtime Errors         DBIF_RSQL_INVALID_RSQL 
Exception              CX_SY_OPEN_SQL_DB

And here is the code cause this problems:

CALL FUNCTION 'ENQUEUE_E_TABLE'
  EXPORTING 
  mode_rstable = 'E'
  tabname     = 'ZVKN_TRPHIST'.


MODIFY zvkn_trsphist FROM TABLE lt_trsphist. <<<<<< Error


CALL FUNCTION 'DEQUEUE_E_TABLE'
  EXPORTING
  mode_rstable = 'E'
  tabname = 'ZVKN_TRPHIST'.


IF sy-subrc EQ 0.
  return-type = 'S'.
ELSE.
  ROLLBACK WORK.
  return-type = 'E'.
ENDIF.

I have tried to use ENQUEUE statement with WAIT but no succeed. How to manage with no dump?

[nishantbansal91]

Dear I Namie,

This type of dump occurs if your column contain unexpected value than data element.

Suppose your COL-A type Int and it contain Character. That type this dump will occur.

Please check your code i think in some cases might be wrong data pass to your program.

Thanks

Nishant

[Former Member]

Hi, thanks for your reply.

My table has 80 fields and I accessed through Remote Desktop so that I have not checked all the field if there is a type conflict during processing.

I have viewed the data element of this table and notice there are Char, Numc, and Quan Element.

Can these data type occur dump?

[former_member195402]

Hi,

please try with COMMIT WORK AND WAIT when SY-SUBRC = 0.

Otherwise there might be an issue, if you are processing those code lines a second time.

Regards,

Klaus

[Former Member]

Hi, thanks for your reply.

Did you mean that adding COMMIT WORK AND WAIT when ENQUEUE is success and before modify statement?

I not get it clearly. I use the code below. Is this ok to transport to Production?

CALL FUNCTION 'ENQUEUE_E_TABLE'

    EXPORTING

      MODE_RSTABLE         = 'E'

      TABNAME              = 'ZVKN_TRPHIST'

*   VARKEY               =

*   X_TABNAME            = ' '

*   X_VARKEY             = ' '

*   _SCOPE               = '2'

      _WAIT                = 'X' 

*   _COLLECT             = ' '

    EXCEPTIONS

      FOREIGN_LOCK         = 1

      SYSTEM_FAILURE       = 2

      OTHERS               = 3

             .

   IF SY-SUBRC <> 0.

     return-type = 'E'.

     return-number = sy-msgno.

     return-id = sy-msgid.

     CALL FUNCTION 'MESSAGE_TEXT_BUILD'

       EXPORTING

         msgid               = sy-msgid

         msgnr               = sy-msgno

         msgv1               = sy-msgv1

         msgv2               = sy-msgv2

         msgv3               = sy-msgv3

         msgv4               = sy-msgv4

       IMPORTING

         message_text_output = return-message.

     ROLLBACK work.

     RETURN.

   ELSE.

     COMMIT WORK AND WAIT.

     MODIFY zvkn_trsphist FROM TABLE lt_trsphist.

     CALL FUNCTION 'DEQUEUE_E_TABLE'

       EXPORTING

         mode_rstable = 'E'

         tabname      = 'ZVKN_TRPHIST'.

     RETURN-type = 'S'.

   ENDIF.

[nishantbansal91]

Dear I Namie,

I think before transporting request to PRD you have to first analysis the issue what is the root cause for that issue. Pass the wrong value in DEV system in Debugging mode than you will come to know.

Thanks

Nishant

[former_member195402]

Hi,

I would prefer the COMMIT WORK AND WAIT after the MODIFY. So the system lock table entries will be released, too.

Regards,

Klaus

[Former Member]

Hi Nishant,

I can not procedure to demostrate the error in Dev System. That's why I have to try and transport to PRD to see the result

BR,

Nam

[Former Member]

This is the error log in ST22.

it error in line MODIFY,  I dont know whether COMMIT WORK after MODIFY is execute or not?

And I also use DEQUEUE_E_TABLE. What is difference between COMMIT WORK and DEQUEUE_E_TABLE here? I think DEQUEUE_E_TABLE also release the lock tables.

[former_member195402]

Hi,

the COMMIT is commented out and without a WAIT.

Please use COMMIT WORK AND WAIT immediately after the MODIFY command. Maybe the DEQUEUE is no longer possible or needed then.

Regards,

Klaus

[Former Member]

I use this code but have no luck

MODIFY zvkn_trsphist FROM TABLE lt_trsphist.

IF sy-subrc EQ 0.

    commit work and wait.

    return-type = 'S'.

ELSE.

    ROLLBACK work.

    return-type = 'E'.

ENDIF.

[former_member195402]

Hi,

please check your internal table lt_trsphist. Check that all key fields are filled in all lines and there are no lines with duplicate key fields. If you have many entries you can sort it before searching duplicate keys in debugger.

Regards,

Klaus

[Former Member]

Hi,

I will try to do this. But I want to clarify somethings. As I understand, if the lt_trsphist has duplicated key fields item, I will get error everytime I run this FM. But this FM caused error in discrete time, I don't find the logic of its appearance.

[former_member195402]

Hi,

lt_trsphist is named like a local table in a FORM.

If it is globally defined, there might be data remaining from the last call of the fm. But also if you cann another fm to create lrows of lt_trsphist, there might a data refresh issue in this called fm, so you will get data twice.

Or you have a database select without a needed DISTINCT option. There are many reasons possible, It depends on your creation logic of the lt_trsphist rows.

Regards,

Klaus

[Former Member]

I have tried this but no luck.

This error didn't appear in a single process. It takes serveral days in PRD to occurs this error.

Do you have any suggestion about database config or something else?

[former_member210963]

Hi Narnie,

When your using modify statement use the transporting key word to pass exact values to modify.

Thanks,

Sivanadh

[Former Member]

Hi Sivanadh,

Thanks for your reply. In my situation, this table has many fields and I can not manage which field will be transported or not at this point. This is difficult.

I suppose problem is not caused by data type conflict.

[Former Member]

Hi,

Sorry for the lately reply. More than a month with this problem, I think this problem is gone and I can mark the answer here. But yesterday I got this log in SM21. I can not reprocedure this problem again

And still around the code above.

The first thing I want to clarify here is

What is the problem "Database error with SEL access to table"?

When and Why does it happen? (I didn't find any document relate to this keywords)

And How can I determine the root cause in this situation?

[raymond_giuseppi]

First could you wrap the MODIFY statement in a TRY/CATCH /ENDTRY.

TRY.
  MODIFY zvkn_trsphist FROM TABLE lt_trsphist. <<<<<< error
      catch cx_sy_open_sql_db into oref.
  text300 = oref->get_text( ).
  MESSAGE text300 TYPE 'S' DISPLAY LIKE 'E'.
  sy-subrc = 12.
ENDTRY.

So you could get immediately a clear error message.

Then on which database are you working, on Oracle, this is error ORA-00060 some deadlock during update, insure there are no duplicates in your internal table comparing primary keys only, if this is not the case also insure there are no other reports updating this table without taking care to lock table.

Hints:

• If duplicates a better coding in previous code or a SORT/DELETE ADJACENT DUPLICATES COMPARING statements could solve it
• If this is an actual lock problem, either have the other developper adapts his program, or try to wrap your update now monitored in a DO 10 TIMES/ENDDO exiting the loop in case of succes and raising error at end if sy-subrc NE 0.

Regards,

Raymond

[Former Member]

Hi, I have tried to implement this approach in old version. This error disappeared in ST22.

Recently, in SM21, there is a deadlock error related to this table. When I change this program not to catch exception like this, I notice that in SM21 a shortdump appear with deadlock error. I think when using Try Catch Exception, we just hide this error from user or shortdump to not be stored or logged in ST22. By the way, this error is still there and logged by SM21 (Database error with SEL access error). (this system use MaxDB)

Can you please explain for me, where this error begin, before or after MODIFY? I think of this situation:

1> when I locked this object using ENQUEUE but this FM was timeout, then the object was locked by others. That's why MODIFY statement caused error.

2> This ENQUEUE statement worked well, but after that the lock was not released. And after sometimes, after some job, MODIFY statement caught an lock object and caused error (Because this error appears unpredictable, discrete time)

[raymond_giuseppi]

Error is raised in the database server not the application server during execution of the generated SQL INSERT/UPDATE statements.

Catching the error, just allows to retry later, the database fails and records the error, but your program is not interrupted and is given a new chance to success later.

Hint: Enqueue at table level is only suitable for some Customizing table (e.g. SM30 managed) but is not suitable for master data or application data, for those, a lock by keys is required. Build a lock object with SE11 on this table/primary keys and lock only required records IN EVERY PROGRAM with the generated Enqueue FM.

Lock are released by DEQUEUE FM and COMMIT/ROLLBACK WORK.

Regards,

Raymond

[Former Member]

Thank you for you hints.

I have tries several ways to fix it but no luck.

This error didn't appear in a single process. It takes serveral days in PRD to occurs this error.

Do you have any suggestion about database config or something else?