Runtime Errors "DBIF_RSQL_SQL_ERROR" Exception "CX_SY_OPEN_SQL_DB"

[Former Member]

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.

</BR>How to correct the error

</BR> Database error text........: "Row or object ZPPPROH in R3P11DATA type *FILE in

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

</BR> 179586/P1111/WP03."

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

</BR> Please check the entries in the system log (Transaction SM21).

</BR>

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

</BR> find an interim solution in an SAP Note.

</BR> If you have access to SAP Notes, carry out a search with the following

</BR> keywords:

</BR>

</BR> "DBIF_RSQL_SQL_ERROR" "CX_SY_OPEN_SQL_DB"

</BR> "ZPPEN902_3" or "MZPPEN902_3_F01"

</BR> "SAVE_OUT"

</BR>

</BR> If you cannot solve the problem yourself and want to send an error

</BR> notification to SAP, include the following information:

</BR>

</BR> 1. The description of the current problem (short dump)

</BR>

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

</BR> (Unconverted)".

</BR>

</BR> 2. Corresponding system log

</BR>

</BR> Display the system log by calling transaction SM21.

</BR> Restrict the time interval to 10 minutes before and five minutes

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

</BR> (Unconverted)".

</BR>

</BR> 3. If the problem occurs in a problem of your own or a modified SAP

</BR> program: The source code of the program

</BR> In the editor, choose "Utilities->More

</BR> Utilities->Upload/Download->Download".

</BR>

</BR> 4. Details about the conditions under which the error occurred or which

</BR> actions and input led to the error.

</BR>

</BR> The exception must either be prevented, caught within proedure

</BR> "SAVE_OUT" "(FORM)", or its possible occurrence must be declared in the

</BR> RAISING clause of the procedure.

</BR> To prevent the exception, note the following:

</BR>System environment

</BR> SAP-Release 700

</BR>

</BR> Application server... "SAPPRD"

</BR> Operating system..... "OS400"

</BR> Release.............. "6.1"

</BR> Hardware type........ "0006000F0DA4"

</BR> Character length.... 16 Bits

</BR> Pointer length....... 64 Bits

</BR> Work process number.. 1

</BR> Shortdump setting.... "full"

</BR>

</BR> Database server... "SAPPRD"

</BR> Database type..... "DB400"

</BR>

</BR> Char.set.... "C"

</BR>

</BR> SAP kernel....... 700

</BR> created (date)... "Aug 24 2010 21:22:26"

</BR> create on........ "AIX 2 5 005DD9CD4C00 (IBM iSeries with OS400)"

</BR> Database version. "DB4_53"

</BR>

</BR> Patch level. 268

</BR> Patch text.. " "

</BR>

</BR> Database............. "V5R3, V5R4, V6R1, V7R1"

</BR> SAP database version. 700

</BR> Operating system..... "OS400 3 5, OS400 4 5, OS400 1 6, OS400 1 7"

</BR>

</BR> Memory consumption

</BR> Roll.... 16192

</BR> EM...... 16759392

</BR> Heap.... 0

</BR> Page.... 57344

</BR> MM Used. 8474832

</BR> MM Free. 4091712

</BR> 875 SELECT SINGLE arbpl INTO lv_arbpl

</BR> 876 FROM zppprod

</BR> 877 WHERE aufnr = wa_dtl-aufnr

</BR> 878 AND vornr = wa_dtl-vornr.

</BR> 879 IF sy-subrc = 0.

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

</BR> 881 AND arbpl = lv_arbpl.

</BR> 882 COMMIT WORK.

</BR> 883 ENDIF.

[Sandeep_Panghal]

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

[Former Member]

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.

[SuhaSaha]

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

[Former Member]

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?

[SuhaSaha]

>

> 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

[Former Member]

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ó

[SuhaSaha]

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

[Former Member]

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.

</BR> CALL FUNCTION 'ENQUEUE_EZPPPROH'

</BR> EXPORTING

</BR> mode_zppproh = 'E'

</BR> mandt = sy-mandt

</BR> aufnr = zsppen002-aufnr

</BR> EXCEPTIONS

</BR> foreign_lock = 1

</BR> system_failure = 2

</BR> OTHERS = 3.

</BR> CALL FUNCTION 'DEQUEUE_EZPPPROH'

</BR> EXPORTING

</BR> mode_zppproh = 'E'

</BR> mandt = sy-mandt

</BR> aufnr = zsppen002-aufnr.

Any suggestion that need to be check?

[Former Member]

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ó