topic Re: Deleting the Data from Data base table in Application Development and Automation Discussions

Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 11:54:37 GMT

Hi,

I am deleting the data from Data base table.In Dev system as we have very less records, my program is worling fine.

But in Quality system, it is failing why because huge amont of data is present in Data base table.

My Basis team advised me that Delete the records in group and COMMIT the data base table.

Can we delete some 1000 records and COMMIT the data base table and again can we delete some 1000 records and COMIIT the data base table so that we can reduce the load on Server.

If yes,

How can we put Delete Statement in Loop statement.

Would you please advise me on this.

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 12:04:48 GMT

Check whether you can use perform .. on commit or update function module for this task.

Thanks,

Aravind

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 12:19:34 GMT

HI Arvind,

Thank You for your reply. We dont have any Perform On Commit or Update Function Module.

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 12:28:55 GMT

Yes, you can do a delete <dbtab> from <internal table>....

I do mass updates to a non-production system, but not deletes, but it works like this: I have a package size on screen (type I).....

select * from [dbtable name] into [internal table of same type as dbtable]
package size p_pkg "(from screen parameter)
where....(any appropriate where clause).

* do any necessary logic to figure out what to keep or discard from internal table.

Delete [dbtable name] from [internal table].

endselect.

Note: COMMIT is not needed....

Run single-thread...NOT in parallel, since you can get deadlocks from having multiple executions simultaneously!

Edited by: BreakPoint on Mar 15, 2011 1:30 PM

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 12:29:49 GMT

Have a look at the ABAP help on DELETE dbtab.

Should give you an idea how to delete per a certain amount of records.

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 12:46:12 GMT

just write the below code...

select * from [dbtable name] into [internal table of same type as dbtable]

where....(any appropriate where clause).

loop at internal table into work area.

delete dbtable from work area.

endloop.

it will deleted the selected data base table entries.

Regards

ksardar

Re: Deleting the Data from Data base table

RaymondGiuseppi — Tue, 15 Mar 2011 12:51:01 GMT

Are you deleting the whole database table, then DELETE is not the fastest way, you could delete and recreate the table from ddic definition.

Look at FM like [MCS_BIW_DROP_CREATE_TABLE|http://www.sdn.sap.com/irj/scn/advancedsearch?query=mcs_biw_drop_create_table] or manage it yourself with [DB_STORAGE_SAVE|http://www.sdn.sap.com/irj/scn/advancedsearch?query=db_storage_save], [DD_DROP_TABLE|http://www.sdn.sap.com/irj/scn/advancedsearch?query=dd_drop_table], [DD_GET_NAMETAB|http://www.sdn.sap.com/irj/scn/advancedsearch?query=dd_get_nametab] and [DD_CREATE_TABLE|http://www.sdn.sap.com/irj/scn/advancedsearch?query=dd_create_table].

Regards,

Raymond

Re: Deleting the Data from Data base table

AlexanderOv — Tue, 15 Mar 2011 12:53:45 GMT

You might need to make an index for your table if you are using following command

DELETE FROM ... WHERE ...

Re: Deleting the Data from Data base table

ThomasZloch — Tue, 15 Mar 2011 13:13:45 GMT

How much is "huge amount"? How exactly is it "failing", what error message / short dump are you seeing?

Do you want to delete the entire table contents in all clients (see Raymond's reply), or just selected entries?

Thomas

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 13:16:36 GMT

Actually, now i'm curious about the table itself: what's the name of the table you need to delete?

Re: Deleting the Data from Data base table

Former Member — Tue, 15 Mar 2011 13:55:34 GMT

Ksardar, you copied my post as your own work?! And, didn't even change the words! And, then provided a MUCH SLOWER way to accomplish the same delete process!