RE: Join files

I assume that when you say there are a joined logical file you mean you
have DDS and have used the CRTLF to created the file.  Also, I assume
that you are using the 2nd file to control record selection from the
first.
This is what I'd do.
1) Create an SQL Index over the 1st file by customer number
2) Create an SQL Index over the 1st file by Customer Name
3) Create an SQL Index over the 2nd file by Customer Number 
4) Place your session in debug mode
5) Run the following SQL statement
    Select * from file_2 inner join File_1 on File_2.custno =
File_1.Custno
     order by name
6) After the results are displayed look at you job log and see what
messages the SQL optimizer has displayed and if you see anything like an
"Create an index path" suggestion create the index and run your
selection again.
The benefit of creating an SQL index over a logical file is that the
system reads the access path about 4 times faster.

Bill Erhardt   
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
lgronewold@xxxxxxxxxxxxxxxxxx
Sent: Tuesday, January 30, 2007 1:08 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Join files


I have a question on Join files:

    The first is on joining files.  I have two physical files that have
been
    joined.  The first one is a customer master file that has one member
and
    582363  total records (no deleted records).  The second file is a
control
    file that has 8 records (40 deleted records) and one member.  A join
logical
    was created over the two physical files.  The customer file is the
first
    file and is joined to the second by customer number.  The files are
sorted
    by customer name and number.  In my program I allow the user to type
in the
    customer name.  I then set lower limits to the join file by the
entered
    information and read it.  I then display information from the join.
The
    problem is that it takes over 5 minutes to display the information.
Does
    anyone have any suggestions on what the problem is?  Is a join a
valid
    option in this program?  There was debate over the use of a join
file.

Lori Gronewold
GBS Developer





***************************Disclaimer***************************
The contents of this e-mail and any file transmitted with it are
confidential and intended solely for the individual or entity to whom
they are addressed.  The content may also contain legal, professional or
other privileged information. If you received this e-mail in error,
please destroy it immediately.  You should not copy or use it for any
purpose nor disclose its contents to any other person. The views stated
herein do not necessarily represent the view of the Company. Please
ensure you have adequate virus protection before you open or detach any
documents from this transmission. The Company does not accept any
liability for  viruses.
Premier Farnell plc
150 Armley Road Leeds
LS12 2QQ
Telephone +44 (0) 870 129 8608
Fax +44 (0) 870 129 8610
Registered in England
Company Number 876412
Registered Office: Farnell House, Forge Lane, Leeds LS12 2NE
************************************************************ 

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.