Re: Large Delete Problem

Mohammad Sayeed <msayeed@ix.netcom.comcom> wrote: :Hi Everyone, : I have a situation where my application is trying to do a large delete :(actually it's a delete with no where clause) and its hitting a "long :transaction abort". : : System: risk6000 4.1 : ------- : Informix Online 7.2 : Application using ODBC CLI And the language was? : What we already know: : --------------------- : 1. Database configauration can be changed to avoid a long : transaction abort. : 2. Since statement in question is deleting everything in the : table we can drop and create the table. : : The problem: : ----------- : 1. We donot have any control over the database configuration. : I cannot make any assumtions of a certain log file size or space : either. Strange. Who do control this? That person should accomodate your application. : 2. We would prefer a controlled delete rather than a drop or : create the table. Drop and create table will allways be extremely much faster. : The question: : ------------ : Does anyone know of a way doing a delete, say, 2000 rows at a : time? : Mind the fact the table is not ordered in any way. The standard way is something like (in Informix Stored Procedure Language): let l_counter = 0 begin work foreach with hold select primary_key into l_primary_key from tablename let l_counter = l_counter + 1 if l_counter = 2000 then commit work begin work l_counter = 0 end if delete from tablename where primary_key = l_primary_key end foreachcommit work with exception handling added. Names starting with l_ are local variables you must declare appropriately. The same thing can be written in any other language as well or you can actually create this as a stored procedure and execute it. NB! The above is speed typed late at night and may contain *any* number of serious bugs resulting in a complete crash of your whole database and the building you are in colapsing down on you. Use at your own risk. : Thank you. : Bappy Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org