Re: Update on "big file upgrade" approach - long

--
[ Picked text/plain from multipart/alternative ]
Peter,

If I were to try to do this with SQL it would look like this:

insert into TESTLIB2/NEWFILE (select a.*, '' newalpha1, 0 new alpha 2, ...
from TESTLIB1/ORIGFILE a)

but I'd have to list ALL of the new fields with the default value
(represented by the ellipsis above).  I tried to think of a way to do this
with a "structure" for the new fields, rather than having to list them
individually.  I can now write a quick utility to build the PF for the new
fields only (using FFD information from both files), create the gap file in
QTEMP, and I'm off to the races!

There may be a way to do that via SQL also, but I couldn't locate it!  If
there is an SQL way that can use a structure, I'd love to know what it is.
Perhaps the same subquery with 2 select * statements, one for the original
file and one for the "gap" file, but I was able to generate this OPNQRYF
version very quickly.

Michael


> Hi Michael,
>
> What gave you the idea for the OPNQRYF approach?  That's certainly an
> interesting result.
>
> Peter Dow
> Dow Software Services, Inc.
> 909 793-9050 voice
> 909 522-3214 cellular
> 909 793-4480 fax
>
>
> ----- Original Message -----
> From: <Mlpolutta@aol.com>
> Sent: Thursday, November 14, 2002 6:50 AM
> Subject: Update on "big file upgrade" approach - long
>
>
> pgm
>
>             OPNQRYF    FILE((TESTLIB1/ORIGFILE) (GAPFILE)) +
>                          FORMAT(QTEMP/JOINFILE) JFLD((1/KEY1 +
>                          2/KEY1) (1/KEY2 2/KEY2) (1/KEY3 +
>                          2/KEY3) (1/KEY4 2/KEY4)) +
>                          JDFTVAL(*YES) OPNID(JOINOPEN) +
>                          SEQONLY(*YES 108)
>
>             OVRDBF     FILE(NEWFILE) SEQONLY(*YES 108)
>             CPYFRMQRYF FROMOPNID(JOINOPEN) +
>                          TOFILE(TESTLIB2/NEWFILE) MBROPT(*REPLACE)
> return
> endpgm
>
> I created a "gap" file which is an _empty_ PF containing the key fields and
> the "new" fields only.  (In other words, just the fields added to the
> ORIGFILE format.) Joined on the keys, selecting JDFTVAL(*YES) to get every
> record in the original file.  Then simply CPYFRMQRYF from the join to an
> actual PF in the new format.
>
> While I can't really explain why this is so much faster, or why the
parallel
> stuff didn't give the return I wanted, I'm thrilled with
> the result.