RE: Large delete

Ok,so you could do this for each table: 1. Unload only the data that you want to keep To avoid the 2GB limit you can use named pipes : A. At the unix prompt, create a mknod file, mknod -m 777 file_name B. Now go into dbaccess / isql and start an unload to this file unload to file_name select * from table C. Open another session, and do the following cat file_name | compress -c > new_file.Z Or you can use gzip instead of compress If even with gzip or compress your file gets bigger than 2GB, you should download the data segmented by date for example, or some other field like an integer key. 2. Get the dbschema of the table. Drop the table. Recreate the table 3. Restore the unloaded data Put your database on Nologging Mode to get a faster data load. gunzip -c unload.file.gz > /tmp/unload.pipe & dbload command with dbload-script. If you have a database with logging you must use dbload unless you have really plenty of logspace (long transactions!). I have heard about a High Performance Loader (command : onpload) but i haven't used it, i am almost sure that this command would be faster than dbload. If the procedure takes a longer time than 3 days (weekend) , ask your boss for a week !!!! :-) Regards -----Mensaje original----- De: Gorazd Hribar Rajteri' [mailto:REMOVE_gorazd.hribar@telekom.si] Enviado el: Viernes, 17 de Octubre de 2003 04:24 p.m. Para: informix-list@iiug.org Asunto: Re: Large delete Maybe I wasn't clear enough: our database normally runs in buffered logging mode. When trying to delete before mentioned data from tables, I got "Long transaction aborted" message. Then I switched to no-logging mode and the problem is simply in duration. I cannot delete all the data necessary within two days period. I'm now running unload, but since all table is too large to fit in 2 GB file size, I have to do it partially, which causes unload to run a very long time. I still have to see if time frame is acceptable. Thank you for your answer! Gorazd "Francisco Roldan" <froldan@5b.com.gt> wrote in message news:bmp0t3$7ef$1@terabinaries.xmission.com... > > 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 sending to informix-list

Thanks very much for all the tips! Gorazd "Francisco Roldan" <froldan@5b.com.gt> wrote in message news:bmq0h8$jka$1@terabinaries.xmission.com... > > Ok,so you could do this for each table: > > 1. Unload only the data that you want to keep > To avoid the 2GB limit you can use named pipes : > A. At the unix prompt, create a mknod file, > mknod -m 777 file_name > > B. Now go into dbaccess / isql and start an unload to this file > unload to file_name > select * from table> > C. Open another session, and do the following > cat file_name | compress -c > new_file.Z > Or you can use gzip instead of compress > > > If even with gzip or compress your file gets bigger than 2GB, you > should > download the data segmented by date for example, or some other field > like > an integer key. > > 2. Get the dbschema of the table. Drop the table. Recreate the table > > 3. Restore the unloaded data > Put your database on Nologging Mode to get a faster data load. > > gunzip -c unload.file.gz > /tmp/unload.pipe & > dbload command with dbload-script. > > If you have a database with logging you must use dbload unless you > have really plenty of logspace (long transactions!). > > I have heard about a High Performance Loader (command : onpload) but > i haven't used it, > i am almost sure that this command would be faster than dbload. > > If the procedure takes a longer time than 3 days (weekend) , ask your boss > for a week !!!! > :-) > > Regards > > > > -----Mensaje original----- > De: Gorazd Hribar Rajteri' [mailto:REMOVE_gorazd.hribar@telekom.si] > Enviado el: Viernes, 17 de Octubre de 2003 04:24 p.m. > Para: informix-list@iiug.org > Asunto: Re: Large delete > > > Maybe I wasn't clear enough: our database normally runs in buffered logging > mode. When trying to delete before mentioned data from tables, I got "Long > transaction aborted" message. Then I switched to no-logging mode and the > problem is simply in duration. I cannot delete all the data necessary within > two days period. > > I'm now running unload, but since all table is too large to fit in 2 GB file > size, I have to do it partially, which causes unload to run a very long > time. I still have to see if time frame is acceptable. > > Thank you for your answer! > > Gorazd > > "Francisco Roldan" <froldan@5b.com.gt> wrote in message > news:bmp0t3$7ef$1@terabinaries.xmission.com... > > > > 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 > > sending to informix-list

Don't do this with unload. This is too slow ! You need to use the HighPerformanceLoader, unloading in parallel to several files or pipes. This will be factors faster than unload. Also use the HPL for loading the data and you will save a lot of time. Best regards Eric -- IT-Consulting Herber Email: <mailto:eric@herber-consulting.de> Mobile: +49 177 2276895 *********************************************** Download the IFMX Database-Monitor for free at: http://www.herber-consulting.de/BusyBee *********************************************** Gorazd Hribar Rajteri' wrote: > Thanks very much for all the tips! > > Gorazd > > "Francisco Roldan" <froldan@5b.com.gt> wrote in message > news:bmq0h8$jka$1@terabinaries.xmission.com... >> >> Ok,so you could do this for each table: >> >> 1. Unload only the data that you want to keep >> To avoid the 2GB limit you can use named pipes : >> A. At the unix prompt, create a mknod file, >> mknod -m 777 file_name >> >> B. Now go into dbaccess / isql and start an unload to this file >> unload to file_name >> select * from table>> >> C. Open another session, and do the following >> cat file_name | compress -c > new_file.Z >> Or you can use gzip instead of compress >> >> >> If even with gzip or compress your file gets bigger than 2GB, you >> should >> download the data segmented by date for example, or some other field >> like >> an integer key. >> >> 2. Get the dbschema of the table. Drop the table. Recreate the table >> >> 3. Restore the unloaded data >> Put your database on Nologging Mode to get a faster data load. >> >> gunzip -c unload.file.gz > /tmp/unload.pipe & >> dbload command with dbload-script. >> >> If you have a database with logging you must use dbload unless you >> have really plenty of logspace (long transactions!). >> >> I have heard about a High Performance Loader (command : onpload) but >> i haven't used it, >> i am almost sure that this command would be faster than dbload. >> >> If the procedure takes a longer time than 3 days (weekend) , ask your > boss >> for a week !!!! >> :-) >> >> Regards >> >> >> >> -----Mensaje original----- >> De: Gorazd Hribar Rajteri' [mailto:REMOVE_gorazd.hribar@telekom.si] >> Enviado el: Viernes, 17 de Octubre de 2003 04:24 p.m. >> Para: informix-list@iiug.org >> Asunto: Re: Large delete >> >> >> Maybe I wasn't clear enough: our database normally runs in buffered > logging >> mode. When trying to delete before mentioned data from tables, I got >> "Long transaction aborted" message. Then I switched to no-logging mode >> and the problem is simply in duration. I cannot delete all the data >> necessary > within >> two days period. >> >> I'm now running unload, but since all table is too large to fit in 2 GB > file >> size, I have to do it partially, which causes unload to run a very long >> time. I still have to see if time frame is acceptable. >> >> Thank you for your answer! >> >> Gorazd >> >> "Francisco Roldan" <froldan@5b.com.gt> wrote in message >> news:bmp0t3$7ef$1@terabinaries.xmission.com... >> > >> > 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 >> >> sending to informix-list