RE: Large delete

You can avoid the long delete duration by setting off logging on the database : ontape -N <DATABASE> Before doing this, check out the logingg mode of your database (as root): onmonitor -> Status -> Databases U= Unbuffered Logging B = Buffered Logging N = No Logging My advice is that after deleting the rows you want, unload the data that you still want on the database, and reload it . It is for avoiding the fragmentation caused by deleting a log of rows. To reactivate the logging on the database could be on two ways, depending of your choice (Buffered or Unbuffered) : BUFFERED LOGGING : ontape -s -L 0 -B <DATABASE>' UNBUFFERED LOGGING : ontape -s -L 0 -U <DATABASE>' On both options, this command will ask you to make a level 0 backup, but you can set up the tape device to /dev/null (not recommended) or actually make a real level 0 backup (recommended). For avoiding to reach the 2GB limit when unloading data you can use the date field on your table (if you have one, i suppose to) and unload the data by month for example. Or you can download the data to a named pipe and use gzip. This solution has been completely explained several times on this list, you can search on the database list archive for this explanations. Hope this help you. Regards -----Mensaje original----- De: Gorazd Hribar Rajteri' [mailto:REMOVE_gorazd.hribar@telekom.si] Enviado el: Viernes, 17 de Octubre de 2003 03:14 a.m. Para: informix-list@iiug.org Asunto: Large delete Hi guys! We're using IDS 7.31.FD3 on Sparc Solaris 8 (32-bit). Database design has no foreign keys. All tables are nonfragmented, with dbspace scattered over multiple disks. Indexes are in separate dbspace. Note that upgrade is not an option here. My task is to delete old invoices and related tables. In order to accomplish this job, I was given identical server with up-to-date restore of production database to test various strategies. The problem is: invoice table has 22,500,000 records; invoice lines are in two separate tables first having 95,500,000 records and second having 31,750,000 records; both invoice lines tables are connected (in application) to general ledger through a separate table having 177,000,000 records. I have managed to delete invoices using fragmentation and detaching appropriate fragment. Other tables are giving me a headache. Our system is *not* 24/7 but rather 24/5 (I have two days over weekend to do the job). Invoice lines tables are too big to do an unload (resulting file exceeds 2 GB limit). I interrupted DELETE statement on invoice lines table after running it for more than 40 hours. I haven't tested the following scenario: establish foreign keys relationships with on delete cascade option between invoice and all other tables, fragment invoice table to contain to-be-deleted records in separate fragment and detach that fragment. ========= QUESTION: ========= Does anybody knows, what will happen to subordinate tables having foreign key constraints when fragment on invoice table will be detached? Any other ideas are greatly appreciated! Gorazd sending to informix-list