[marin]

I have a problem with Informix. I have a large table (about 100.000 rows). I need to delete about 70.000 from the table running a sql query like
delete from mytable where idcount<70000

It deletes but ... the records are still there. Is there a command like purge or something? Maybe a compress command or something that could help me?
Thanks

[eherber]

alter index <idxname> to cluster;

OR

alter fragment on table <table_name> init in <new_dbspace>;

However make sure that you have enough logical-log
space or turn the database logging off for this task
if possible.

[dananio]

Please check it out , your transaction isn't rollback

[marin]

I think that there is no rollback. I have read a lot from the internet and I have found that the only way to really purge the deleted records are to export the current info, drop the table and restore .

[Roelwe]

This is impossible,

if you delete rows out of an infrmix database they are deleted.

why don't you try it this way:

begin work;
delete from table where count < 70000;
commit;

You could always first check how many rows and what rows will be deleted. Use
select count(*) from table where count < 70000

Unlike Oracle, Informix will fill up this deleted space with the next inserts in your table. No space is wasted.
No online purge is available in informix. There is always an exclusive lock on the table when you do a reorganization.
ALTER FRAGMENT is the best and easiest way.

[marin]

Because I'm new to informix I cannot say that it isn't true. Can you write a complete command for alter fragment.

All that I do is

delete from events where id<50.000

[Roelwe]

Are you sure you put a dot?
50.000 or 50000?

Makes a big difference...

You can find the complete SQL syntax at the syntax guide. Go to
http://www.ibm.com/software/data/informix
library
Documentation on Informix Products.
Informix Dynamic Server (Your Version).

An example would be, if your table is not fragmented...

ALTER FRAGMENT ON events INIT IN dbspace;

where dbspace is the location where you want to put the table.
If your feeling is that it's a big table, you could fragment it over different dbspaces. 100000 rows is not so big though. Depends a bit on the size of the row.