Archiving Table

Hi, I hav a table on a chunk, this table gets full on frequent basis I want to derive a method where when, 100000 rows are entered it should delete the first 10000 rows. and before deleting I want to back them up. I am using Informix IDS 9.40. Please help I am stuck up with this. Is it possible to archive a single chunk. Thanks Amit.

Look into onbar. Backing up single chunks is possible. Look at fragmentation... If you could figure out how to fragment in a way to support your idea... You could just detach the frag, back it up, and drop it...?.... -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of AMIT DIXIT Sent: Monday, November 08, 2004 5:23 AM To: ids@iiug.org Subject: Archiving Table [3640] Hi, I hav a table on a chunk, this table gets full on frequent basis I want to derive a method where when, 100000 rows are entered it should delete the first 10000 rows. and before deleting I want to back them up. I am using Informix IDS 9.40. Please help I am stuck up with this. Is it possible to archive a single chunk. Thanks Amit. ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Hi, archiving a single chunk is not possible. You can archive a single dbspace using ON-Bar (not with ontape utility as far as I know). However, even that will not help you that much, because in the venet you would have to do a warm restore of that dbspace. That however must include logical restore and logical recover to get the dbspace up-to-date with the other dbspaces, i.e. the data would get deleted again during the rollforward phase ... (You would have to do a point-in-time restore on a separate instance or even system to get to the deleted data. This probably is too tedious for a couple of data rows ...) As soon as available (with 9.50.UC1 next year), you could use TLR (Table Level Restore) to restore old data that was later on deleted (by specifying a proper point-in-time for the restore avoiding that the data gets deleted again). For the time being I think the easiest is something along the following lines: - have a serial column in the table, - whenever the serial increases across an "interval border", unload the rows you want to delete with a statement like: UNLOAD TO file_name SELECT * FROM table_name WHERE serial_col > x AND serial_col <= y;with "x" being the lower and y being the upper boundary of the day ... - after that unload finished successfully, you can delete the data (using the same WHERE clause). There was a discussion about what happens when a serial overflows (i.e. it wraps around?), but I forgot the essence. And I think there's an "8-byte serial" data type that will keep things running a bit longer before such thing is happening. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions forum.subscriber@iiug.org wrote on 08.11.2004 12:22:31: > Hi, > I hav a table on a chunk, this table gets full on frequent basis > I want to derive a method where when, 100000 rows are entered it should > delete the first 10000 rows. > and before deleting I want to back them up. > > I am using Informix IDS 9.40. > > Please help I am stuck up with this. > Is it possible to archive a single chunk. > > Thanks > Amit. >

I don't know what you're using to fill this table, but there's nothing to stop you counting the rows, unloading and deleting if necessary then inserting the new ones. Or you could embed the count/unload/delete logic in a cron job. You don't say what device or format you want to backup these rows to. It sounds like you're intending to keep all the rows somewhere, in which case you could add new rows to that store first, delete if necessary from your table, then add the new rows to the table. Saves the unload stage and means that the backup is always complete... Andy. >From: "AMIT DIXIT" <amdixit_x@hssworld.com> >To: ids@iiug.org >Subject: Archiving Table [3640] Date: Mon, 8 Nov 2004 06:22:31 -0500 >(EST) > >Hi, > I hav a table on a chunk, this table gets full on frequent basis > I want to derive a method where when, 100000 rows are entered it should > delete the first 10000 rows. > and before deleting I want to back them up. > > I am using Informix IDS 9.40. > > Please help I am stuck up with this. > Is it possible to archive a single chunk. > > Thanks > Amit. > _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger

Amit You can create a sequence in circle of 10,000 and a trigger on the table that for every row get nextval from the sequence if it get to 10,000 then you do the delete and backup procedure. I recommend that the delete and backup procedure will be handle synchronic, put row into side table and from other process do the delete and backup. You can backup using onpload. Uri AMIT DIXIT wrote: >Hi, > I hav a table on a chunk, this table gets full on frequent basis > I want to derive a method where when, 100000 rows are entered it should > delete the first 10000 rows. > and before deleting I want to back them up. > > I am using Informix IDS 9.40. > > Please help I am stuck up with this. > Is it possible to archive a single chunk. > > Thanks > Amit. > > >*********************************************** >This Mail Was Scanned By Mail-seCure System in > Matrix Herzeliya >*********************************************** > > > >