SEO

#1 (**permalink**) 04-20-2008, 07:23 AM

I am writing a purge routine on a large table where we need to keep a
copy of the purged rows. We also need to re-size the table extents. So
the best bet would be to produce two ASCII files. Because of the size
of the table I'd like to make as few passes through it as possible -
one would be good.

In an ideal world I'd have a piece of code that did something like

foreach row
if <to keep>
unload (append) to ascii file1
else
unload (append) to ascii file2
end foreach

In an even more ideal world I'd do the whole thing in SPL. (HPL would
be even better but the criteria are rather verbose and may need to be
held in a memory-resident table).

Given that we don't live in such Utopian conditions, what approach
would give the best compromise?

Thanks

Andy Kent

#2 (**permalink**) 04-20-2008, 07:23 AM

Hi :-)

What about writing a SP to_keep(...) which returns 1 or 0
based on the data of the row.

then unload
UNLOAD to somefile SELECT to_keep(...),* FROM thetable;

Then use an easy awk script or something alike (on Windows systems
FIND is your friend) to create two files based on the first column.

Regards,
Dirk
--
-- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0
-- Hammer Str. 13 fax: +49 (0)251 28446-55
-- D-48153 Muenster http://www.GunCon.de/
-- "Toto, I don't think we're in Kansas anymore..."

"Andy Kent" <andykent.bristol1095@virgin.net> schrieb im Newsbeitrag
news:1105448438.466964.207560@f14g2000cwb.googlegr oups.com...
> I am writing a purge routine on a large table where we need to keep a
> copy of the purged rows. We also need to re-size the table extents. So
> the best bet would be to produce two ASCII files. Because of the size
> of the table I'd like to make as few passes through it as possible -
> one would be good.
>
> In an ideal world I'd have a piece of code that did something like
>
> foreach row
> if <to keep>
> unload (append) to ascii file1
> else
> unload (append) to ascii file2
> end foreach
>
> In an even more ideal world I'd do the whole thing in SPL. (HPL would
> be even better but the criteria are rather verbose and may need to be
> held in a memory-resident table).
>
> Given that we don't live in such Utopian conditions, what approach
> would give the best compromise?
>
> Thanks
>
> Andy Kent
>

#3 (**permalink**) 04-20-2008, 07:23 AM

Hi Andy.

Taking Dirk's suggestion a bit further, firstly create a FIFO file in Unix using
mknod or mkfifo, then start up an awk script reading from the FIFO, containing
something like:

BEGIN {
FS = "|"
}

{
if (condition)
print > "reload.unl"
else
print | "gzip > purge.unl.gz"
}

Use compress if you don't have gzip. Then in dbaccess run:

UNLOAD TO "fifo" SELECT * FROM tablename

--
Regards,
Doug Lawry
www.douglawry.webhop.org

"Dirk Gunsthövel" <dirk@guncon.de> wrote in message
news:cs12ja$elv$00$1@news.t-online.com...
> Hi :-)
>
> What about writing a SP to_keep(...) which returns 1 or 0
> based on the data of the row.
>
> then unload
> UNLOAD to somefile SELECT to_keep(...),* FROM thetable;
>
> Then use an easy awk script or something alike (on Windows systems
> FIND is your friend) to create two files based on the first column.
>
> Regards,
> Dirk
> --
> -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0
> -- Hammer Str. 13 fax: +49 (0)251 28446-55
> -- D-48153 Muenster http://www.GunCon.de/
> -- "Toto, I don't think we're in Kansas anymore..."
>
> "Andy Kent" <andykent.bristol1095@virgin.net> schrieb im Newsbeitrag
> news:1105448438.466964.207560@f14g2000cwb.googlegr oups.com...
> > I am writing a purge routine on a large table where we need to keep a
> > copy of the purged rows. We also need to re-size the table extents. So
> > the best bet would be to produce two ASCII files. Because of the size
> > of the table I'd like to make as few passes through it as possible -
> > one would be good.
> >
> > In an ideal world I'd have a piece of code that did something like
> >
> > foreach row
> > if <to keep>
> > unload (append) to ascii file1
> > else
> > unload (append) to ascii file2
> > end foreach
> >
> > In an even more ideal world I'd do the whole thing in SPL. (HPL would
> > be even better but the criteria are rather verbose and may need to be
> > held in a memory-resident table).
> >
> > Given that we don't live in such Utopian conditions, what approach
> > would give the best compromise?
> >
> > Thanks
> >
> > Andy Kent

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Unloading to two destinations