Commit - Within Loop

[Former Member]

Hello All,

I have been receiving the Runtime error

DBIF_RSQL_INVALID _CURSOR in my Zobject.

On the Production as we are expecting 3 Million records i used the COMMIT WORK statement inside the loop, so that once the record # reaches 10,000 it can free the memory space (Before i added the COMMIT WORK statement i was receiving the DBIF_RSQL_SQL_ERROR).

The main objective of this object is to delete the Orphan entries available in the table ZOMLT.

Code :

FORM DELETE_DATA.

DATA : CNTR TYPE I VALUE '0', "Total deleted records

CNTR1 TYPE I VALUE '0'. "Commit records

SORT ORPHAN_TAB BY MSG_ID.

WRITE 😕 'Message IDs of Orphan entries in ZOMLT'.

ULINE.

IF ORPHAN_TAB[] IS INITIAL.

WRITE 😕 ' - None'.

ENDIF.

LOOP AT ORPHAN_TAB.

WRITE 😕 ORPHAN_TAB-MSG_ID.

SELECT * FROM ZOMLT WHERE MSG_ID = ORPHAN_TAB-MSG_ID.

IF SY-SUBRC = '0'.

--> DELETE ZOMLT.

CNTR = CNTR + 1. "For total # of records

CNTR1 = CNTR1 + 1.

IF CNTR1 EQ 10000.

COMMIT WORK.

CLEAR CNTR1.

ENDIF.

• so that after every 10,000 records deleted from ZOMLT we * are using COMMIT.

ENDIF.

ENDSELECT.

ENDLOOP.

Would be great to have the views to correct the above code.

Thank you,

- PSK

[Former Member]

Hi Sravan,

There is no need for you to do the SELECT since you are just deleting the records from the table.

You can replace the SELECT * ENDSELECT with DELETE FROM ZOMLT WHERE MSG_ID = ORPHAN_TAB-MSG_ID.

For each DELETE, SY-DBCNT will tell you how many records were deleted. You can keep track of number of records and do a commit work as needed.

Regards,

Rao A

[Former Member]

Hi Sravan,

Would like to add to my earlier post.. you cannot do a COMMIT WORK inside a SELECT/ENDSELECT LOOP as the COMMIT WORK closes all open cursors and your cursor position is lost.

Your other alternative is to select all records from ZOMLT table into an internal table first, but that would be very inefficient as are you are not procesing the data from the table at all in your program.

Regards,

Rao A

[Former Member]

Hi, Sravan!

Frequent commits also slower down the overall perfomance, so you can bundle several delete keys into internal table and try to use construction:

DELETE FROM dbtab WHERE f IN itab

Regards,

Maxim.

[Former Member]

Hello All,

Once again I am stuck up with ABAP Dump - DBIF_RSQL_ERROR.

In my code i am fetching data from database tables ZOMLT and ZOMLG. The main objective being delete the orphan entries

available in ZOMLT. (ZOMLT and ZOMLG have 5 primary keys in common)

Step 1 : Based on conditions i have retrieved data from ZOMLT.

Step 2 : Retrieved data from ZOMLG and placed them into i_ZOMLT and i_ZOMLG internal tables respectively.

The total records that are selected are for ZOMLT - *38838 and for ZOMLG - 10622.

I have declared a variable of size 6 as a counter to get the total number of records in internal table, that is the reason

why the * (So, the reords fetched cross the 6 digits).

Step 3 : Then i am looping at i_ZOMLT and reading i_ZOMLG.

If the records available in ZOMLT does not match with the record present in ZOMLT i build the Orphan internal table (IF

SY-SUBRC NE 0 then build Orphan itab).

The total records in the Orphan tab is *17594, this is the place i get the ABAP/4 processor: DBIF_RSQL_SQL_ERROR - no more

locks available.

Once the Prohan table is populated i use the mass delete statement DELETE ZOMLT client specified from ITAB ORPHAN_TAB.

Would be great if your views/suggestions are provided on how to retrieve a smaller data from ZOMLT and ZOMLG and delete the

entries and then proceed for further data.

(The code is provided in my initial post)

Regards,

- PSK

[christian_wohlfahrt]

Hi!

Place the first commit work immediately after your select statements (before loop + comparing). Define your counters like sy-tabix -> no size problems any longer.

When compare is ready:

FREE: i_zomlt, i_zomlg. "to clear zoml*

Then do:

loop at i_orphan.
  add 1 to l_cnt.
  append i_orphan to i_orphan_tmp.
  if l_cnt > 5000. 
    delete zomlt from i_orphan_tmp.
    commit work.
    refresh i_orphan_tmp.
    clear l_cnt.
  endif. 
endloop.
if i_orphan_tmp is not initial.
  delete zomlt from i_orphan_tmp.
  commit work.
  refresh i_orphan_tmp.
endif.

Then your database access shouldn't break to any limits any longer.

Regards,

Christian

[Former Member]

Hello Christian,

Thanks for the posting.

Would be great to know why the lasy lines of the code are required :

if i_orphan_tmp is not initial.

delete zomlt from i_orphan_tmp.

commit work.

refresh i_orphan_tmp.

endif.

it is outside the loop.

Regards,

- PSK

[christian_wohlfahrt]

Hi PSK,

let's say you have to delete 10200 entries. If you delete from DB in groups of 5000 entries, then you shouldn't forget to delete the last 200!

Regards,

Christian

[Former Member]

Thank you Christian.

Implemented the code. Need to check on the QA Instance, thats where the Acid test is.

Regards,

- PSK