Re: Join Logical on Physical files?

A DCR that I've had since V2R1 or earlier was to be able to create a join
logical file with keys on multiple tables. It's not going to happen. I
get the impression that you are not tied to RPG and RLA but are open to
newer stuff (perhaps in addition to...). If so, continue reading. If
not, well ...

Let's say you have these tables:

orderheader
===========
ordernbr
customernbr


orderline
=========
ordernbr
orderline
itemnbr
qtyordered
shipdate

itemmaster
==========
itemnbr
itemclass

customers
=========
customernbr
zipcode

And you want to know a summary of item classes by zip code.
- Create an index over customers by zipcode
- create an index over itemmaster by itemclass. If you have very few item
classes and a lot of item numbers make that index an EVI.
- create an index over orderheader by customernbr
- create an index over orderline by itemnbr
- create a view that has orderline, orderheader, itemmaster and customers
all joined together. The view will not have ordering.
Now when you do
select itemclass, zipcode, itemnbr, shipdate from myview order by
itemclass, zipcode
it will try to use the existing underlying indexes.
There is a great graphical tool that is free. It is called System i
Navigator for Windows. After you run such a select you can run it through
it's tools and it will give you recommendations and where it takes the
most time. See also it's index advisor.

Here's another $64k question: If I have Referential Integrity set up
between all these tables, will it use that in assisting performance?

Give up the DDS and start using DDL.




Rob Berendt