COMMIT on Custom Database Table

[Former Member]

Hi All,

I am developing a custom SE38 report with SELECTION-SCREEN .

This Program should delete the data from a custom DB Table.

That DB Table is having huge amount of data (250 Milins) around 2 years of data.

Requirement is I need to make sure that at any point of time, DB table should have the only 7 days of data from Today.

So I am just deleting the data by writing the below query.

1) CALL FUNCTION 'ENQUEUE_E_TABLE' ( To lock the DB Table)

2) DELETE FROM <<Custom DB Table>> WHERE <<Condition>>

3) CALL FUNCTION 'DEQUEUE_E_TABLE' (To Unlock the DB Table)

The issue is that BASIS team claimed that as you have not mentioned any COMMIT statement, our DB Logs are filled up beyond the limit and system crashed.

Can you please advise me, Do I need to write any COMMIT or any Explicit COMMIT happens here.

[iftah_peretz]

Hi,

Yes. insert after line 2 of your logic the command: COMMIT WORK.

This will force the deletion to occur on run time.

Try it and let me know how that worked for you,

Iftah

[Former Member]

As of now there no COMMIT statement and this program is in PROD.

If they run the program, any chance of Explicit COMMIT?.

[ThomasZloch]

This will not be sufficient, you will need to insert a COMMIT WORK after each package of x deleted rows. Let's assume x = 10,000

In order to do this, you would need to select packages of x rows into an internal table (only the primary key fields are required) and then delete based on the internal table contents. Since an ABAP COMMIT WORK would make you lose the DB cursor of the package SELECT statement, you need to use an OPEN CURSOR ... WITH HOLD construct and call function module DB_COMMIT instead of the ABAP commit.

There were several discussions about this, please use above search terms.

If somebody has an easier way to do this and still make sure that DB logs don't overflow, please let us know.

Thomas

[yuri_ziryukin]

Hello Thomas and Chaitanya,

agree with your approach. In majority of cased this is fine.

I have one more idea that might help in case if the huge table should be almost emptied.

1. Select data that have to be left in the table.

2. Copy (insert) this data into a temporary table (can be also a cluster, see EXPORT TO DATABASE command).

3. Drop the original table.

4. Recreate original table.

5. Read data from the temporary table / cluster and insert it into the original table.

This will help to reduce the runtime of the first deletion where a lot of data have to be deleted.

By the way, I don't see reasons of locking the table. Why doing that?

Regards,

  Yuri

[volker_borowski2]

Good approach,

but keep in mind, when rescueing with "select data that has to be left in table"

to watch out for proper processing in each client!!

If you are only processing one client, the others might not be amused.

...and, inserting COMMITs to the DELETE logic will NOT save you from writing DB logs.

The save/drop/create/reload approach is actually doing the "delete" as part of the drop,

which is DDL and requires a whole lot less overhead than deleting records.

Volker