Large Delete Problem

Hello to all, I have do delete a lot of rows in a database from within an batch-application. This results with the error '-458 Long transaction aborted'. I can not drop and recreate the table because only a part of the rows are to be deleted. I have no influence on size and number of the log-files in the target machine, i just can demand a minimum amount of log-space which won't be sufficient for any delete. Has anybody an idea of an workarround for this problem? Performance has to be considered. Is there a construct like DELETE FROM mytable WHERE "first 2000 rows";in INFORMIX? One possible solution for this problem ist this, which Rudy Fernandes <rferdy@kuwait.net> did post at 1997/03/18 (Yes, I did have in intensive look at dejanews): >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. I could easyly port this to ESQL/C, but how much worse will the performance be? Has anybody experience with this workarround? Thank you in advance -- Mathias Cukina COMLINE GmbH Dortmund/Germany EMail mathias.cukina@comline.de

Mathias Cukina wrote: > Hello to all, > I have do delete a lot of rows in a database from within an > batch-application. This results with the error '-458 Long transaction > aborted'. > I can not drop and recreate the table because only a part of the rows > are to be deleted. > I have no influence on size and number of the log-files in the target > machine, i just can demand a minimum amount of log-space which won't be > sufficient for any delete. > Has anybody an idea of an workarround for this problem? Performance has > to be considered. > Is there a construct like > DELETE FROM mytable WHERE "first 2000 rows";> in INFORMIX? No. > One possible solution for this problem ist this, which Rudy Fernandes > <rferdy@kuwait.net> did post at 1997/03/18 (Yes, I did have in intensive > look at dejanews): > >1. Try a 4GL Cursor with counter and restart. For example, [SNIP] > >I don't think that performance would be much worse than a plain > >old DELETE FROM table. > > I could easyly port this to ESQL/C, but how much worse will the > performance be? Has anybody experience with this workarround? Get my dbdelete.ec utility which is in my submission to the IIUG Software Repository called utils2_ak. Dbdelete uses multiple connections to the server to avoid unneccessary locking and deletes adjustable batches of rows in a single transaction which is committed then a new transaction is begun to delete remaining batches. The fetch connection that gets the list of rows to delete uses the IDS Fetch Array feature to fetch up to 32K of rowids or keys in a single operation (though the default of 2K seems to give the best performance in testing) then dynamically builds a single delete statement where rowids are available with a large IN() clause to delete up to 8195 rows in a single statement. Performance is excellent if rowids are available and reasonable where a key list has to be used instead (only for fragmented tables without ROWIDs added). Art S. Kagel

Mathias, Yes, I tried this when I had a similar problem (didn't want to change the logs and program was aborting with long transaction). It worked fine - program would commit when the count of deletes MOD some_number was 0 and it didn't have much worse performance than the "big delete". It didn't have a GOTO in it though... but that's another question. Salut, Andrew. Mathias Cukina wrote in message <36AE0DBC.F02FE984@comline.de>... >Hello to all, > >I have do delete a lot of rows in a database >This results with the error '-458 Long transaction aborted'. >One possible solution for this problem ist this, which Rudy Fernandes ><rferdy@kuwait.net> did post >>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. > >I could easyly port this to ESQL/C, but how much worse will the >performance be? Has anybody experience with this workarround?

We get round this is one case by : 1) getting the minimum & maximum rowid for the data you are trying to delete: e.g. select min(rowid), max(rowid) from mytable where ... 2) deleting entries based on ranges of rowid, e.g. delete from mytable where rowid >= minrowid and rowid < minrowid + 10000 and.... delete from mytable where rowid >= minrowid + 10000 and rowid < minrowid +20000 and .... until you go past maxrowid. This will be slower than just deleting them all or droping the table, but it will get around long transactions, and you don't have to delete every row in the table for this to work. Andrew Lawrenson -- - Remove nospam. from address when replying by email - Any Opinions Expressed within are Mine and not necessarily those of my Employer Mathias Cukina wrote in message <36AE0DBC.F02FE984@comline.de>... >Hello to all, > >I have do delete a lot of rows in a database from within an >batch-application. This results with the error '-458 Long transaction >aborted'. >I can not drop and recreate the table because only a part of the rows >are to be deleted. >I have no influence on size and number of the log-files in the target >machine, i just can demand a minimum amount of log-space which won't be >sufficient for any delete. > >Has anybody an idea of an workarround for this problem? Performance has >to be considered. > >Is there a construct like > DELETE FROM mytable WHERE "first 2000 rows";>in INFORMIX? > >One possible solution for this problem ist this, which Rudy Fernandes ><rferdy@kuwait.net> did post at 1997/03/18 (Yes, I did have in intensive >look at dejanews): > >>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. > >I could easyly port this to ESQL/C, but how much worse will the >performance be? Has anybody experience with this workarround? > > >Thank you in advance >-- >Mathias Cukina >COMLINE GmbH >Dortmund/Germany > >EMail mathias.cukina@comline.de > >

Here is the shell script we use to delete all the rows in a specific table. dbname_dst=DATABASE@SYSTEM i=$1 # ----------------------------------------------------------------------------- # suppression des donnees # ----------------------------------------------------------------------------- # Creation de la procedure stockee echo "create procedure p_del()" > p_del.sql echo "define i,j integer;" >> p_del.sql echo "let j=1;" >> p_del.sql echo "foreach with hold select rowid into i from $i" >> p_del.sql echo " if j=1 then" >> p_del.sql echo " begin work;" >> p_del.sql echo " end if;" >> p_del.sql echo " delete from $i" >> p_del.sql echo " where rowid=i;" >> p_del.sql echo " let j=j+1;" >> p_del.sql echo " if j=200 then" >> p_del.sql echo " commit work;" >> p_del.sql echo " let j=1;" >> p_del.sql echo " end if;" >> p_del.sql echo "end foreach;" >> p_del.sql echo "if j > 1 then " >> p_del.sql echo " commit work;" >> p_del.sql echo "end if" >> p_del.sql echo "end procedure;" >> p_del.sql # Execution de la procedure dbaccess $dbname_dst p_del.sql dbaccess $dbname_dst << EOF execute procedure p_del(); drop procedure p_del;EOF

Depending on the balance of rows to keep and delete, you could create a duplicate table and insert the rows you wish to keep then drop original table and rename the duplicate to the original name. Jorge Mathias Cukina wrote: > Hello to all, > > I have do delete a lot of rows in a database from within an > batch-application. This results with the error '-458 Long transaction > aborted'. > I can not drop and recreate the table because only a part of the rows > are to be deleted. > I have no influence on size and number of the log-files in the target > machine, i just can demand a minimum amount of log-space which won't be > sufficient for any delete. > > Has anybody an idea of an workarround for this problem? Performance has > to be considered. > > Is there a construct like > DELETE FROM mytable WHERE "first 2000 rows";> in INFORMIX? > > One possible solution for this problem ist this, which Rudy Fernandes > <rferdy@kuwait.net> did post at 1997/03/18 (Yes, I did have in intensive > look at dejanews): > > >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. > > I could easyly port this to ESQL/C, but how much worse will the > performance be? Has anybody experience with this workarround? > > Thank you in advance > -- > Mathias Cukina > COMLINE GmbH > Dortmund/Germany > > EMail mathias.cukina@comline.de -- Jorge Torralba Intel Corporation Technology and Manufacturing Grp HF2-71 Sys. MFG, Information Technology 5200 NE Elam Young Pkwy (503)696-4587 Hilssboro, OR 97124 jorge.torralba@intel.com ============================================================= Any views or opinions expressed by me do not reflect those of Intel Corp **** **** **** * * * * * * **** * * * * *** *** * * **** **** **** * * * * * * * * * * *** *** * * * * * ** * * * * * * * * *** * * * *** * * * * * * * * * * * * * * * * * * * * * * * *** * * * * * * * * * * * * * * * * * * * * * * * * *** *** * * * * * * * * * * * * * **** **** **** ******* ******** **** * * * ****** * * ******

Depending on the balance of rows to keep and delete, you could create a duplicate table and insert the rows you wish to keep then drop original table and rename the duplicate to the original name. Jorge Mathias Cukina wrote: > Hello to all, > > I have do delete a lot of rows in a database from within an > batch-application. This results with the error '-458 Long transaction > aborted'. > I can not drop and recreate the table because only a part of the rows > are to be deleted. > I have no influence on size and number of the log-files in the target > machine, i just can demand a minimum amount of log-space which won't be > sufficient for any delete. > > Has anybody an idea of an workarround for this problem? Performance has > to be considered. > > Is there a construct like > DELETE FROM mytable WHERE "first 2000 rows";> in INFORMIX? > > One possible solution for this problem ist this, which Rudy Fernandes > <rferdy@kuwait.net> did post at 1997/03/18 (Yes, I did have in intensive > look at dejanews): > > >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. > > I could easyly port this to ESQL/C, but how much worse will the > performance be? Has anybody experience with this workarround? > > Thank you in advance > -- > Mathias Cukina > COMLINE GmbH > Dortmund/Germany > > EMail mathias.cukina@comline.de -- Jorge Torralba Intel Corporation Technology and Manufacturing Grp HF2-71 Sys. MFG, Information Technology 5200 NE Elam Young Pkwy (503)696-4587 Hilssboro, OR 97124 jorge.torralba@intel.com ============================================================= Any views or opinions expressed by me do not reflect those of Intel Corp **** **** **** * * * * * * **** * * * * *** *** * * **** **** **** * * * * * * * * * * *** *** * * * * * ** * * * * * * * * *** * * * *** * * * * * * * * * * * * * * * * * * * * * * * *** * * * * * * * * * * * * * * * * * * * * * * * * *** *** * * * * * * * * * * * * * **** **** **** ******* ******** **** * * * ****** * * ******