ok, long transaction occur because your logical log don't support the amount of data deleted... you have 3 solutions:
- change your database to nolog :
ontape -s -L0 -N <your_database>
ondblog nolog <you_database>
Be careful , doing this, no transaction is allowed , if have others sessions connected you can have some trouble.
2) change your table to RAW (nolog), delete and back to standard:
alter table <xyz> type(raw);
alter table <xyz> type(standard);
3) add more logical logs
this is more complicade and need to change the database configuration... talk with your DBA.
yes, but for this you will need write some code.
You can write a SPL (stored procedure) , write in your desire language or use some utilities like this: International Informix Users Group (ind this same page, looking for "delete", and you will found others scritpts.
There many ways to write this, here is the an example of more simple code:
this is a pseudo-code, based on SPL language
foreach with hold
select rowid into vR from table_xyz where <filters>
if vCounter = 0 then
delete from table_xyz where rowid = vR
if vCounter >= 100 then
This code won't perform very fast, there others ways to do this faster , sending all ROWIDs in the same statement ....
For information about ROWID , read the manual: Using Rowids (IDS)
I tried now using the procedure approach. Now i am getting the error at the very end of stored procedure(Last line End Procedure)
CREATE PROCEDURE spinco_process()
/*I have code here in between*/
I am not sure what syntax error in the last line is?
Also how would you execute the procedure through execute?