topic Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB" in Application Development and Automation Discussions

Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 03:00:19 GMT

Hello gurus,

I facing the runtime error DBIF_RSQL_SQL_ERROR with Exception CX_SY_OPEN_SQL_DB while trying to updating ZTable.

It's shown as following. Please help.

How to correct the error

Database error text........: "Row or object ZPPPROH in R3P11DATA type *FILE in

use. MSGID= Job=181841/P1111/WP01. Record 39380 in use by job

179586/P1111/WP03."

Internal call code.........: "[RSQL/UPDT/ZPPPROH ]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

"DBIF_RSQL_SQL_ERROR" "CX_SY_OPEN_SQL_DB"

"ZPPEN902_3" or "MZPPEN902_3_F01"

"SAVE_OUT"

If you cannot solve the problem yourself and want to send an error

notification to SAP, include the following information:

1. The description of the current problem (short dump)

To save the description, choose "System->List->Save->Local File

(Unconverted)".

2. Corresponding system log

Display the system log by calling transaction SM21.

Restrict the time interval to 10 minutes before and five minutes

after the short dump. Then choose "System->List->Save->Local File

(Unconverted)".

3. If the problem occurs in a problem of your own or a modified SAP

program: The source code of the program

In the editor, choose "Utilities->More

Utilities->Upload/Download->Download".

4. Details about the conditions under which the error occurred or which

actions and input led to the error.

The exception must either be prevented, caught within proedure

"SAVE_OUT" "(FORM)", or its possible occurrence must be declared in the

RAISING clause of the procedure.

To prevent the exception, note the following:

System environment

SAP-Release 700

Application server... "SAPPRD"

Operating system..... "OS400"

Release.............. "6.1"

Hardware type........ "0006000F0DA4"

Character length.... 16 Bits

Pointer length....... 64 Bits

Work process number.. 1

Shortdump setting.... "full"

Database server... "SAPPRD"

Database type..... "DB400"

Char.set.... "C"

SAP kernel....... 700

created (date)... "Aug 24 2010 21:22:26"

create on........ "AIX 2 5 005DD9CD4C00 (IBM iSeries with OS400)"

Database version. "DB4_53"

Patch level. 268

Patch text.. " "

Database............. "V5R3, V5R4, V6R1, V7R1"

SAP database version. 700

Operating system..... "OS400 3 5, OS400 4 5, OS400 1 6, OS400 1 7"

Memory consumption

Roll.... 16192

EM...... 16759392

Heap.... 0

Page.... 57344

MM Used. 8474832

MM Free. 4091712

875 SELECT SINGLE arbpl INTO lv_arbpl

876 FROM zppprod

877 WHERE aufnr = wa_dtl-aufnr

878 AND vornr = wa_dtl-vornr.

879 IF sy-subrc = 0.

>>>>> UPDATE zppproh SET plnst = 'CNF' WHERE aufnr = wa_dtl-aufnr

881 AND arbpl = lv_arbpl.

882 COMMIT WORK.

883 ENDIF.

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Sandeep_Panghal — Fri, 11 Mar 2011 03:39:31 GMT

Are you trying to update the key fields with update and while doing so inserting duplicates in this ztable??

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 04:29:46 GMT

Thank you very much for your reply,

The program is only trying to update one field in ZTable and that field is a status field (not a key field).

When this runtime error occurs, it will not update data in ZTable and make incorrect data to the report.

So, I have check in QA System, trying to run many sessions of this program but no runtime error occur. (Only happen to PRD)

Any solution for solve this problem?

Please help.

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

SuhaSaha — Fri, 11 Mar 2011 05:09:40 GMT

Hello Verdam,

Best approach would be to catch the EXCEPTION & log it for further analysis.

DATA: gx_sql_excp TYPE REF TO cx_sy_open_sql_db,
      gv_err_txt  TYPE string.

TRY .
    UPDATE zppproh SET plnst = 'CNF' WHERE aufnr = wa_dtl-aufnr.
  CATCH cx_sy_open_sql_db INTO gx_sql_excp .
    gv_err_txt = gx_sql_excp->get_text( ).
    WRITE: / gv_err_txt. CLEAR gv_err_txt.
ENDTRY.

BR,

Suhas

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 06:38:04 GMT

Thank you for your help,

I think this is the best way to catch this runtime error. But if the runtime error rise up and catch block working with displaying some message, it is still not to update status field. The users have to re-excute program again.

Any suggestion?

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

SuhaSaha — Fri, 11 Mar 2011 06:44:22 GMT


> But if the runtime error rise up and catch block working with displaying some message, it is still not to update status field. The users have to re-excute program again.

Hello Verdam,

Ofcourse the data won't be updated in the database. There is a reason why the database is not allowing the data to be updated & we need to know the reason

Current ST22 log does gives us the details about the exception being raised & no further details are available. If you incorporate the TRY ... CATCH block the exception message will give more insight & can be used for further analysis.

BR,

Suhas

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 06:44:26 GMT

Hi Verdam,

*FILE in use. MSGID= Job=181841/P1111/WP01. Record 39380 in use by job 179586/P1111/WP03

This is a deadlock, two process (WP01 and WP03) are trying to change record.

Is the AUFNR and ARBPL the primary key of table?

Or update where AUFNR and ARBPL provoke more changes than one record?

Maybe you need to review the process around zppproh table to ensure fast and unique updates.

Kind regards, Fernando Da Ró

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

SuhaSaha — Fri, 11 Mar 2011 06:53:44 GMT

Hello Fernando,

*FILE in use. MSGID= Job=181841/P1111/WP01. Record 39380 in use by job 179586/P1111/WP03

My bad! Should have read the ST22 analysis thoroghly. The OP has to lock the records before trying to update them which will remove the "lock conflicts".

BR,

Suhas

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 08:24:32 GMT

Thank you for all reply,

I have check the table and source code, it has the lock object and enqueue/dequeue for this table. Testing multi concurrent updating at QAS is fine.

CALL FUNCTION 'ENQUEUE_EZPPPROH'

EXPORTING

mode_zppproh = 'E'

mandt = sy-mandt

aufnr = zsppen002-aufnr

EXCEPTIONS

foreign_lock = 1

system_failure = 2

OTHERS = 3.

CALL FUNCTION 'DEQUEUE_EZPPPROH'

EXPORTING

mode_zppproh = 'E'

mandt = sy-mandt

aufnr = zsppen002-aufnr.

Any suggestion that need to be check?

Re: Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

Former Member — Fri, 11 Mar 2011 11:35:46 GMT

Hi VerdamK,

Talk some background info about this table and these updates...

The error occured at database level not abap, follow some questions:

- Is it common this concurrency of two process trying to update same record?

- the enqueue objects/functions are in use? I couldn't see it on your dump...

- monitor on SM50 / SM66 the test run of concurrent executions, look for long execution database access, sequential read...

- where the problem happens? Production? If yes, isn't a test... What is the difference of real world from QAS?

- the normal standard programming don't use UPDATE like you did but select SINGLE the record, enqueue it, call an update function, commit, dequeue... the update will happens on another WP type UPD and you can monitor it through SM13. Your coding call a update function or the UPDATE run syncronous in the DIA WP?

Regards, Fernando Da Ró