RE: Join 2 files with 1 key from each file.

["Rusling, John B. (Alliance)" <jbrusling@xxxxxxxxxxxxxxx>]

Thanks Charles..


> message: 4
> date: Wed, 4 May 2005 14:06:20 -0400
> from: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
> subject: RE: Join 2 files with 1 key from each file.
> 
> Have you read this?:
> http://www-1.ibm.com/servers/enable/site/bi/strategy/index.html

Not yet.

> 
> Have you run the query under debug or from iSeries Navagator using
Visual
> Explain?

Yes, it's finding indexes it needs.  I'm frustrated because it seems
Like you should be able to have a key from each file.
If I can think of it, why the h*11 can't db2 do it?

> 
> What version of OS/400?

5.2

> 
> How large are the files, how large of a result set are you expecting?

S1 is 2.2 million recs, s3 is 2.7 million recs.
I expect a large result set.  I'm using the
For Read Only Optimize For 10 Rows at the end of the statement and
That made a noticeable difference before I introduced the secondary
Order By.

> 
> 
> Charles Wilt
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
> 
> 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Rusling, John B.
> > (Alliance)
> > Sent: Wednesday, May 04, 2005 1:22 PM
> > To: rpg400-l@xxxxxxxxxxxx
> > Subject: Join 2 files with 1 key from each file.
> >
> >
> > I have 2 files I want joined and indexed on 1 key from each of the 2
> > files.
> >
> > The 2 files are named s1 and s3, both have the common field of
Order#
> > (on) in them
> >
> > s1 JOIN s3 ON s1on = s3on .
> >
> > I want the joined file indexed by s1name, then s3itno
> >
> > ORDER BY s1name, s3itno .
> >
> >
> > I'm using embedded sql in rpg and everything works fine except the
> > response
> > time on one of our production machines gets to be 5+ minutes.!
> >
> > ---->
> > I want to have a prebuilt, index or logical so that when this
> > program is
> > used, the response time will fall into the acceptable range.
> >
> > I've built most every combination of logical file (using dds) to
this
> > end
> > but the response time is still horrid.
> >
> > The s1 file has 2.25 million records and the s3 file has about 2.8
> > million.
> >
> > Can anyone give me some guidance on this please?  I've tried
> > everything
> > I can think of for the last day or 2 and response time is
> > still crappy.
> >
> >
> > Thanks for any help at all,
> >
> > John B.
> >
> > --
> > 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.
> >
> >
> 
> 
>