RE: LF on 2 files with 'UNION' -- MIDRANGE-L

Sudha


You've created a multiple-format logical. You need to have both files with
the same format name and the same fields - basically one is a dup of the
other, just in a different library or with different file names (or
different members of the same file).

Then, something like this

CRTLF FILE(YOURLIB/YOURLF)
      SRCFILE(YOURSRCLIB/QDDSSRC)
      DTAMBRS((YOURLIB/FILE1) (YOURLIB/FILE2))

or

CRTLF FILE(YOURLIB/YOURLF)
      SRCFILE(YOURSRCLIB/QDDSSRC)
      DTAMBRS((YOURLIB/FILE1 (MBR1 MBR2)))

will create a single-format logical that has data from multiple members.
SQL can process this.

A view won't do it.

Views can be created form UNIONs in V5R2, not in V5R1.

Luck

Vern

At 01:34 PM 11/6/2002 -0600, you wrote:

The 2 files have the same format name and are created from the same dds
but the field name prefix are different.

In the program I have it as
  select afield1,afield2,afield3 from filea where afield1 <> ' ' ...
   union select bfield1,bfield2,bfield3 from fileb where bfield1<> ' ' ...

which works fine.

I did something like the following that errored out.

 create view filevew  as
  select afield1,afield2,afield3 from filea
   union select bfileld1,bfield2,bfield3 from fileb

 UNION and UNION ALL for CREATE VIEW not valid.


I then created lf with 2 record formats with same selected fields as above :

        R AREC  PFILE(FILEA)
          AFIELD1 R
          AFIELD2 R
        AFIELD3 R
        K AFIELD1
        K AFIELD2
      K AFIELD3

        R BREC  PFILE(FILEB)
          AFIELD1 R
          AFIELD2 R
        AFIELD3 R

        K BFIELD1
        K BFIELD2
      K BFIELD3

But when I do a select I get :

select * from LFFILE1
File LFFILE1 in SUDLIB has more than one format.

Thanks,
Sudha


-----Original Message-----
From: rob@dekko.com [mailto:rob@dekko.com]
Sent: Wednesday, November 06, 2002 12:54 PM
To: midrange-l@midrange.com
Subject: Re: LF on 2 files with 'UNION'


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I ran this following script from iSeries Navigator:
CREATE TABLE rob.sud1 (column1 INTEGER NOT NULL, column2 VARCHAR(100)
ALLOCATE(20));
CREATE TABLE rob.sud2 (column1 INTEGER NOT NULL, column2 VARCHAR(100)
ALLOCATE(20));
INSERT INTO rob.sud1 VALUES(1,'AAA');
INSERT INTO rob.sud1 VALUES(2,'BBB');
INSERT INTO rob.sud2 VALUES(3,'CCC');
INSERT INTO rob.sud2 VALUES(4,'DDD');
CREATE VIEW rob.sudviewa AS
  SELECT column1, column2 FROM rob.sud1
  UNION
  SELECT column1, column2 FROM rob.sud2;
SELECT * FROM rob.sudviewa;
And I got all four rows.

I tried this simple program:
     FSUDVIEWA  IP   E             DISK    RENAME(SUDVIEWA:SUDVIEWAR)
     FQSYSPRT   O    F  132        PRINTER
     ISUDVIEWAR     01
     OQSYSPRT   D    01
     O                       COLUMN1
     O                       COLUMN2
and I get:
Message Type . . . . . . . . . . . . . :   RNX
Additional Message Info  . . . . . . . :   1011
Message Data . . . . . . . . . . . . . :
             Undefined record type is found in file SUDVIEWA.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Vern Hamberg <vhamberg@centerfieldtechnology.com>
Sent by: midrange-l-admin@midrange.com
11/06/2002 01:12 PM
Please respond to midrange-l

        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        Re: LF on 2 files with 'UNION'


Sudha

Do the 2 files have the same format, same field names? Just different sets
of data? Then you can build a single-format logical (which SQL can handle)
and specify the 2 files as the based-on files of the CRTLF command.

IIRC, a problem with views, performance-wise, is that the view LF really
stores only the SELECT statement and maybe an access plan, not the actual
access path. So the SELECT is run every time you access the view. This may
be why RPG can't use it, since native IO doesn't know how to run SQL
statements.

HTH

Vern

At 11:48 AM 11/6/2002 -0600, you wrote:
>I am trying to fine tune a performance issue I have in my project where I
>am using a 'Union' over 2 large files.  This returns me a distinct result
>of selected fields from both the files, which is wonderful.  But the time
>taken is quite significant since each call creates a union. I cannot
>create a view on Union, so I am trying to find an easier way to do this
>like say via maybe a LF.  Is this possible?  Else, creating 2 separate
>indexes/views on the 2 files on the selected fields will help?
>
>TIA,
>Sudha
>
>Sudha Ramanujan