Re: Archive Informix
Looks like what you are looking for is a way to export older data for archival
purposes then delete these rows. You can use dbexport to export a snapshot of
the entire database (dbimport to reload). You can get Jonathan Leffler's sqlcmd
package which contains a utility, sqlunload, which can unload a single table
(or the results of a specific query, to a delimited text file in any of several
formats, and the corresponding sqlreload utility. You can also get my ul
utility which can make a binary unload of the results of a query or an entire
table in a portable binary format and reload the data when needed. The sqlcmd
package and the utils2_ak package which contains my ul utility are both
available for download from the IIUG Software Repository. (Note ul does not yet
handle blobs or UDT's but you can complete that code if you need it and are
comfortable with ESQL/C.)
Art S. Kagel
----- Original Message -----
From: Raimon Sabate <rsabate@gematic.es>
At: 2/20 11:19
> I need to know if there are procedures, shells or products for archiving on
> tape, informix tables containing milions of rows in order to clean Database.
> Also we need to get this historical information when we need it.
>
> Sorry for my english. I kwon i must improve it.
>
> Raimon Sabaté
> rsabate@gematic.es
Must have missed the original note.
What I've done successfully in the past - and someone recommended on cdi a
few days ago is to:
fragment the table in question by date.
Periodically (when the need for archive hits)
detach the oldest fragment(s) with the data to be archived.
attach a new set of fragments for the future dates.
You're table has been down for seconds and is now back up without the old
data and ready to grow more.
At your leisure unload (using HPL by my preference) the detached fragment
(which became a table BTW) to file->tape
or directly to tape. I would opt for file then to tape, only to get the
load off the database as soon as possible.
Drop the old table.
If you want the old data, you can reload it later back into a table, or
access it directly off of tape.
Many, many, many moons ago I ran into a situation where a bill of materials
needed to be archived and restored at some point - in a world where the
database schema was frequently changing. That was more fun, but that's
another story...
cheers
j.
----- Original Message -----
From: "ART KAGEL, ...." <KAGEL@bloomberg.net>
To: <ids@iiug.org>
Sent: Thursday, February 20, 2003 11:32 AM
Subject: Re: Archive Informix [444]
>
> Looks like what you are looking for is a way to export older data for
archival
> purposes then delete these rows. You can use dbexport to export a
snapshot of
> the entire database (dbimport to reload). You can get Jonathan Leffler's
sqlcmd
> package which contains a utility, sqlunload, which can unload a single
table
> (or the results of a specific query, to a delimited text file in any of
several
> formats, and the corresponding sqlreload utility. You can also get my ul
> utility which can make a binary unload of the results of a query or an
entire
> table in a portable binary format and reload the data when needed. The
sqlcmd
> package and the utils2_ak package which contains my ul utility are both
> available for download from the IIUG Software Repository. (Note ul does
not yet
> handle blobs or UDT's but you can complete that code if you need it and
are
> comfortable with ESQL/C.)
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Raimon Sabate <rsabate@gematic.es>
> At: 2/20 11:19
>
> > I need to know if there are procedures, shells or products for archiving
on
> > tape, informix tables containing milions of rows in order to clean
Database.
> > Also we need to get this historical information when we need it.
> >
> > Sorry for my english. I kwon i must improve it.
> >
> > Raimon Sabaté
> > rsabate@gematic.es
>
>
>
>