Re: Poor Performance from join logical files

Hi everyone,

I haven't created a join logical file in a very very long time, which is
why I am asking for your help now. I posted my question last night and no
one has replied. (see below for original posting.)

I am joining 3 files together. When I CPYF ..... NBRRCDS(1) or create a
query without any selection criteria and press F5, I know I will not get
any records right now but it goes through the whole file of 1.7 million
records and takes a long time. Does the join logical file not create an
index that it can use and give me a result right away?

When I query my file, I can see the records go up by 20000 all the way to
the end of the file, which isn't good since there are so many records.

Any help would be appreciated.

Tks,

Glenn Gundermann
ggundermann@xxxxxx
(647) 272-3295

Here is the code:

R CACUB3TR2 JFILE(TK@MST CACUB3CUP BJMAST)

J JOIN(TK@MST CACUB3CUP)
JFLD(TK@CDE B3ECDE)

J JOIN(TK@MST BJMAST)
JFLD(TK@CO BJCOCD)
JFLD(TK@BR BJBRCD)
JFLD(TK@DV BJDVCD)
JFLD(TK@FLY BJYY)
JFLD(TK@FLE BJFILE)
JFLD(TK@FSX BJFSFX)

TK@CDE
TK@CO
TK@BR
TK@DV
TK@FLY
TK@FLE
TK@FSX
BJINV#
TK@TAG
TK@ASN
TK@SQ#
TK@CKD
TK@DLQ
BJSTAG
BJEDIC
BJEDIY
BJEDIM
BJEDID
BJEDIT

K TK@CDE
K TK@CO
K TK@BR
K TK@DV
K TK@FLY
K TK@FLE
K TK@FSX

S TK@TAG COMP(EQ ' ')
TK@SQ# COMP(NE 0)
BJSTAG COMP(EQ 'P')
TK@DLQ COMP(NE 0)
BJEDIC COMP(EQ 0)
BJEDIY COMP(EQ 0)
BJEDIM COMP(EQ 0)
BJEDID COMP(EQ 0)
BJEDIT COMP(EQ 0)

O ALL


Subject: Poor Performance from join logical files
From: "Glenn Gundermann" <ggundermann@xxxxxx>
Date: Mon, 22 Oct 2007 23:31:14 -0400 (EDT)

Hi guys,

I have defined a join logical file without using the Dynamic Select
keyword so I was expecting the access path to be built and and I was
expecting immediate response when doing a CPYF. On the contrary, it
dissappears for 20 minutes.

As a second question, is there a way to specify two different conditions
without using DYNSLT? For now, I have created two join logical files, one
for each condition.

Thanks,

Glenn Gundermann
ggundermann@xxxxxx
(647) 272-3295