Re: Large Delete Problem
In article <332DC504.27A@ix.netcom.comcom>,
Hi Mohammed,
1. Try a 4GL Cursor with counter and restart. For example,
LABEL restart :
BEGIN WORK
DECLARE del_curs CURSOR FOR
SELECT one_column # Preferably Index column for key-only scan (?)
FROM table
FOR UPDATE
LET l_count = 0
LET l_del_at_a_time = 2000
FOREACH del_curs
LET l_count = l_count + 1
DELETE FROM table
WHERE CURRENT OF del_curs
IF l_count = l_del_at_a_time THEN
EXIT FOREACH
END IF
END FOREACH
COMMIT WORK
IF l_count = l_del_at_a_time THEN # There may be more to delete
GOTO restart
END IF
I don't think that performance would be much worse than a plain
old DELETE FROM table.
2. If performance is an issue, you could consider a shell script
taking 'database' and 'table' as arguments, which would
work with dbschema and dbaccess to drop and recreate the table
and all its indexes.
3. Still more complex, but more reliable is to write a 4gl using
syscolumns & sysindexes to handle the drop and create (we have
something on those lines for our update stats 4gl.
HTH
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
>
> 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.
> 2. We would prefer a controlled delete rather than a drop or
create
>the table.
>
> 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.
>
>
> Thank you.
> Bappy
----------------------
Rudy Fernandes (ICP)
GIC, Kuwait
----------------------