Unloading to two destinations
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
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.googlegroups.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
>