Re: sql join performance issue

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

 As Charles said, at 6.1 the default for IGNORE_DERIVED_INDEX is now 
*YES - but if he's used a QAQQINI in an earlier life, it might be set to 
*NO.

On 8/30/2010 4:03 PM, DeLong, Eric wrote:
> Are you using a QAQQINI with this job?  By default, jobs will use a
> QAQQINI file from QUSRSYS, unless you issue a CHGQRYA to cause your job
> to use a specific version.  If you have a QAQQINI in QUSRSYS, check to
> see if it contains IGNORE_DERIVED_INDEX *YES.
>
> See:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/r
> zajq/qryopt.htm
>
> If you do not have a QAQQINI in QUSRSYS, then you could CRTDUPOBJ from
> the template in QSYS.
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/r
> zajq/createqaqqini.htm
>
> hth,
> -Eric
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> franz400@xxxxxxxxxxxx
> Sent: Monday, August 30, 2010 3:11 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: sql join performance issue
>
> Ran with CQE (i should have checked it first) with reason "Derived Key
> or Select/Omit Index
> There are a large nbr of select/omit indexes (30 or so,i've already
> slapped their hands) but the index does exist with no select omit of
> FILEA by order and FILEB by order - there is a "delete code" in FILEA
> which is what all the select/omits are about.
> Jim
>
> ---- "DeLong wrote:
> > Are all tables referenced in your statement the actual PF objects?
> Does
> > the needed index for FILEB use select/omit logic?  Does Visual Explain
> > say it's using SQE or CQE?
> >
> > -Eric
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> > franz400@xxxxxxxxxxxx
> > Sent: Monday, August 30, 2010 1:33 PM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: sql join performance issue
> >
> > Reviewing a performance issue, .net app opening a cursor that has sql
> > selects that are inserted into a temp table. Two large orders files
> > (FILEA&  FILEB) and a temp file (FILEC) with the single record of the
> > Cust and Region selected.
> > FILEA inner join FILEB on aORDER = bORDER inner join FILEC on aCUST =
> > cCUST,aREGION = cREGION
> >
> > Visual Explain is saying FILEB is a table scan even though it has an
> > index of ORDER (there is 1 rec per order in "a" and also "b").
> > Not understanding why it needed a table scan of a million recs to get
> 80
> > records for a cust/region
> >
> > Jim Franz
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.