Deleting millons of rows

Hi, I need to delete a lot of records of one table, but I got the long transaction aborted error. Is there a way to delete this records without changing the log transaction mode of the database? Thanks in advance.

Couple of strategies: 1 - nibble. Delete 5000 at a time 2 - rewrite the table: create table of keys to delete create new RAW table insert into new table select * from old table where not exists (select 0 from key_table where old_table.key=key_table.key) alter table new_table type standard 3 - don't put yourself into a situation where you have to, partition table by your delete criteria and just detach fragments that you need to delete. j. On 9/25/17 4:02 PM, jorge valenzuela wrote: > Hi, > I need to delete a lot of records of one table, but I got the long transaction > aborted error. Is there a way to delete this records without changing the log > transaction mode of the database? > > Thanks in advance. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

What happend if this table is growing by a secuence and I delete the first rows? Will be a problem with the secuence? > El 25/09/2017, a las 13:17, Jack Parker <jack.parker4@verizon.net> escribió: > > Couple of strategies: > > 1 - nibble. Delete 5000 at a time > > 2 - rewrite the table: > > create table of keys to delete> > create new RAW table > > insert into new table select * from old table where not exists > (select 0 from key_table where old_table.key=key_table.key)> > alter table new_table type standard> > 3 - don't put yourself into a situation where you have to, partition > table by your delete criteria and just detach fragments that you need to > delete. > > j. > >> On 9/25/17 4:02 PM, jorge valenzuela wrote: >> Hi, >> I need to delete a lot of records of one table, but I got the long > transaction >> aborted error. Is there a way to delete this records without changing the > log >> transaction mode of the database? >> >> Thanks in advance. >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I meant to delete the old records. > El 25/09/2017, a las 13:50, jorge valenzuela <jorgervt@gmail.com> escribió: > > What happend if this table is growing by a secuence and I delete the first > rows? Will be a problem with the secuence? > >> El 25/09/2017, a las 13:17, Jack Parker <jack.parker4@verizon.net> escribió: >> >> Couple of strategies: >> >> 1 - nibble. Delete 5000 at a time >> >> 2 - rewrite the table: >> >> create table of keys to delete>> >> create new RAW table >> >> insert into new table select * from old table where not exists >> (select 0 from key_table where old_table.key=key_table.key)>> >> alter table new_table type standard>> >> 3 - don't put yourself into a situation where you have to, partition >> table by your delete criteria and just detach fragments that you need to >> delete. >> >> j. >> >>> On 9/25/17 4:02 PM, jorge valenzuela wrote: >>> Hi, >>> I need to delete a lot of records of one table, but I got the long >> transaction >>> aborted error. Is there a way to delete this records without changing the >> log >>> transaction mode of the database? >>> >>> Thanks in advance. >>> >>> >>> >> > ******************************************************************************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Jorge. The standard tool is dbdelete in this open source ESQL-C package by Art Kagel: ftp://ftp.iiug.org/pub/informix/pub/utils2_ak.gz Alternatively, it's easy enough to write a stored procedure to commit every few thousand rows. I have a general purpose one called spl_dbdelete which uses dynamic SQL (needs IDS 11+) so that you pass in the table name and criteria. Perhaps I'll make my next article about that: https://www.oninitgroup.com/technical-articles Contact me directly if you want it sooner. Regards, Doug