AW: Join files

Hi Bill,

The benefit of creating an SQL index over a logical file is that the

system reads the access path about 4 times faster.

Sorry, but it is not possible to create an SQL index over a DDS described
physical file, at least they will not share access path. An DDS described
logcial file can share access path with an SQL defined index, but not the
inverse. An SQL index has per default a page size of 64K while a DDS
described logical file only has a page size of 8K. If an DDS described
logical file shares the access path with an existing SQL index, the larger
page size of the index will be inherited to the DDS described logical file.

BTW an SQL index will only share access path with an other SQL index if the
key fields are an exact match, i.e. the same key fields in the same
sequence. A DDS described logical file will share the access path with an
other DDS described logical file or an SQL index, if the same or less key
fields are specified in the same sequence.

... What if she wants to read the joined data with native I/O? 

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Erhardt, Bill
Gesendet: Tuesday, January 30, 2007 19:24
An: RPG programming on the AS400 / iSeries
Betreff: 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.