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
Work out which invoices are to be deleted, and break them down into
small bundles.
while more invoices
begin work
delete from child_tables where inv in (??????)
delete from master where inv in (?????)
commit workdone
If you keep the number of invoices small enough you can just let this
run as it will not effect the users. Yes it takes a while, and yes
there are quicker ways, but this keeps the system live during the entire
run. I used this approach recently to remove 95% of data from a website
42 tables involved and 15M rows from master, each loop took 45 seconds
and it ran for 30 hours. Depending on how you run your update stats
you might need to keep an eye of the rate of change in the table.
"Gorazd Hribar Rajteri'" wrote:
>
> 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
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
"Gorazd Hribar Rajteri'" wrote: > > 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 Gorazd, there is no 2 GB Limit when unloading IF you avoid the disk driver doing a seek. Thus when you UNLOAD into a pipe and this is even the cat utility (much better is gzip, of course) AND if you mount the receiving filesystem with -largefiles option on SOL8, you are able to unload even 100 GB without any problen at all. You also can look into the HPL (High Performance Loader) manual. HPL unloads are pretty fast (>15000 datapages per second , here) dic_k [ .... snipping to keep that one short ... ] -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe