Delete records in batch

Hi, Environment: OS: HP 11.1 IDS: 11.5 I have table huge log table from which I need to delete records in batch of 10000 records. Instead of one delete for all records I need to delete first 10000 records. if successfull then delete next 10000 records and so on. The delete should be one delete statement for each batch of 10000 records. The sequential delete is not desired for performance issues. The table has no primary and and it is fragmented table. Any approach to achive this? Thanks, Nandkishor

Get my dbdelete utility. You can do this using dbdelete as follows: dbdelete -d mydatabase -t mytable -b 32767 This will delete 8191 rows in a single transaction, a few hundred per delete statement. If any of the deletes fail the last block of 8191 rows will be rolled back and the utility will exit. Dbdelete is included in the package utils2_ak which you can download for free from either the Oninit web site (www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 20, 2009 at 9:14 AM, NANDKISHOR SINGARE <ns.singare@idbi.co.in>wrote: > Hi, > > Environment: > OS: HP 11.1 > IDS: 11.5 > > I have table huge log table from which I need to delete records in batch of > 10000 records. Instead of one delete for all records I need to delete first > 10000 records. if successfull then delete next 10000 records and so on. The > delete should be one delete statement for each batch of 10000 records. The > sequential delete is not desired for performance issues. The table has no > primary and and it is fragmented table. > > Any approach to achive this? > > Thanks, > Nandkishor > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cdfca04af7003047193f88a

Thank you art. But I need to implement this in program. Is this possible using a query or any logic that can be written in SP? Regards, Nandkishor

I don't know if you posted this originally 'cause you didn't quote the original posting (always a good idea since many of us follow in email) so I don't know what version of IDS you are running. If you are using 11.50 you can write an SPL function using Dynamic SQL to do what dbdelete does, otherwise, if you are using an earlier IDS release without Dynamic SQL, you'll have to implement it in your host language (esql/c, c/odbc, java/jdbc, 4gl, etc). If you have to use host language, and you are using ESQL/C, I can license the dbdelete.ec code and depending on the application, such a license might be free. I am also willing to convert the dbdelete code into a function you can call from any program that can call a 'C' language function - but that would be for a small fee. Here's the basic logic that dbdelete uses: 1. open a cursor on a query to retrieve rowids to delete using whatever filters are appropriate 2. FOR loop until all rows have been deleted (IE the rowid cursor returns no more matching rows. 1. loop fetching rowids 1. Begin work 2. build a DELETE statement with the ROWIDs you are fetching in a '... ROWID IN ()' clause 1. Note that my testing shows that DELETE statements longer than 2-4K take too long for the engine to parse, so execute the DELETE when it's about 2K in length. 3. Execute DELETE when it is long enough 4. When you have deleted 10,000 rows this way, COMMIT WORK and break out of the loop 3. close the cursor and go back to 1. through my testing, I have determined that this is the method that is able to delete data fastest with a minimum of impact on the engine. Dbdelete actually uses other optimizations that are not available to SPL like array fetching to fetch the 8191 rowids in a single FETCH rather than having to loop on the FETCH. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 21, 2009 at 5:59 AM, NANDKISHOR SINGARE <ns.singare@idbi.co.in>wrote: > Thank you art. > But I need to implement this in program. Is this possible using a query or > any > logic that can be written in SP? > > Regards, > Nandkishor > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bf548f18e980471a7e433