Hi !
I want to delete about 30 000 000 rows from table.
I can't do this in one transaction.
Additionaly, table has not any unique identifier.
In MS SQL I'd like to use "SET ROWCOUNT"
But INFORMIX ?
What must I do ?

30 million rows, in one transaction?

Well, "I'm not surprised." Can you imagine what the system would have to do, what it would have to copy and keep and then throw-away, in order to roll-back that?!

Time to cook up a new strategy. Your computer will love you for it.

For bulk operations like that, you simply need to arrange things so that you purge about 100,000 records per transaction and then commit. Or maybe 10,000. You'll also need to section your data in some way so that each delete-query only passes through a portion of the database: some way of apportioning it so that: you don't uselessly troll through already-visited territory and yet, the fact that you're deleting a bunch of records won't cause any candidates to be overlooked.

Transactions are important, on the one hand, because they imply locking and buffering which is very critical for all kinds of bulk-data operations. But they also imply "rolling data back," which can quickly balloon into rollback-files of unmanageable size. (They can also lock-out other users, in the manner of a 9-zillion pound gorilla with huge feet.) So you need to devise a strategy that balances between these issues.

Rodion:

30 million rows to delete ?!!!

Ok... By the way what is the overall rows present in the table? What I'm trying to say is that what remains after this mammoth delete operation is less that what has been pruned off, it would be better to copy the balance to a flat file using UNLOAD statement or to another base table; and ultimately drop the original table itself in one shot.

Or you may decide to go on rowid basis to implement the delete job. You need to generate a flat file with the specification similar to below:

output to 'deljob' without headings select 'delete from tablex where rowid=',rowid,' ;' from tablex ;

Once this flat file is ready you can split it using OS utility. e.g.

split -l100000 deljob

will generate 300 files approximately, assuming you have around 30 million lines in deljob file.

Submit the split files to dbaccess for execution.

Regards,
Shriyan

"Vision is the art of seeing things invisible."

Thank You
I solved this problem


CREATE PROCEDURE My_delete(
rowcount INTEGER, _date datetime year to second
)
RETURNING datetime year to second

define _time datetime year to second;


DEFINE count INTEGER;

LET count = 0;
FOREACH cur1
FOR SELECT time INTO _time
FROM My_table
where time < _date

delete from My_table
where current of cur1;

LET count = count + 1;
IF count >= rowcount THEN
EXIT FOREACH;
END IF;
END FOREACH;
END PROCEDURE;