Re: QAQQINI - Query options file and performance of SQL

I had very similar problem where a job was taking over 15hrs. When I run it
with the copy of QAQQINI file in QGPL, it runs under 2hrs. I changed the
UDF_TIME_OUT parm value to *MAX from *DEFAULT.

Wes

<rob@xxxxxxxxx> wrote in message
news:OF0080C693.38863BF9-ON05256F0A.007FA2D6-05256F0A.007FC044@xxxxxxxxxx
> Yes the job is finding QAQQINI.  And yes it is in QUSRSYS.  And I think
> it's cool because we did the CRTDUPOBJ.  I forget what we changed.  I
> suppose I could check but I don't think that's related as to why the job
> is bogging down on the access of QAQQINI.
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 09/09/2004 03:02 PM
> Please respond to
> RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
>
>
> To
> "'RPG programming on the AS400 / iSeries'"
<rpg400-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> RE: QAQQINI - Query options file and performance of SQL
>
>
>
>
>
>
> I can't comment on the time it takes to access the QAQQINI file, but by
> default, the query optimizer will always look for a QAQQINI.  Within a
> job,
> you can CHGQRYA QRYOPTLIB('Library that contains your custom QAQQINI
> file')
> to force the optimizer to use your pre-designed options file for this
> rogue
> job.  I'd bet that the optimizer might have some performance issues if it
> can't find a QAQQINI, since it has to determine the default values the it
> needs to use.
>
> Is the job finding a QAQQINI file?  I think that it looks in QUSRSYS by
> default.  We generally have to CRTDUPOBJ from QSYS/QAQQINI into QUSRSYS
> after each release update.  I believe the OS explicitely deletes these
> when
> found.  If versions of QAQQINI from prior releases were restored back into
> QUSRSYS after the upgrade, then you may need to delete these and then
> CRTDUPOBJ to rebuild them.
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: rob@xxxxxxxxx
[mailto:rob@xxxxxxxxx]
> Sent: Thursday, September 09, 2004 1:34 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: QAQQINI - Query options file and performance of SQL
>
>
> Thanks for the references.  Most of the performance concerns raised in
> those articles seem to be that usage of some of the parameters may affect
> performance.  Which makes sense.  There is nothing there that says that
> just having the file there will cause a performance hit.  Or what you can
> do to minimize the hit.
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 09/09/2004 01:10 PM
> Please respond to
> RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
>
>
> To
> "'RPG programming on the AS400 / iSeries'"
<rpg400-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> RE: QAQQINI - Query options file and performance of SQL
>
>
>
>
>
>
> Rob,
>
> Here's a pretty detailed coverage of QAQQINI.
>
http://www-912.ibm.com/s_dir/slkbase.nsf/1ac66549a21402188625680b0002037e/9f
>
>
> bb11f1725466578625694c007511a1?OpenDocument&Highlight=0,QAQQINI
>
> Here's a short (but useful) document from the terraplex...
> http://www-1.ibm.com/servers/enable/site/bi/teraplex/lessons.html
>
> hth,
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: rob@xxxxxxxxx
[mailto:rob@xxxxxxxxx]
> Sent: Thursday, September 09, 2004 11:33 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: QAQQINI - Query options file and performance of SQL
>
>
> Developer here is trying to shave some time off of a SQL operation in an
> imbedded rpg program.  Bulk of the time in the joblog based on the debug
> is spent in various CPI4339 - Query options retrieved file QAQQINI in
> library QUSRSYS.  Sure calls this alot.  Spends more time reading this
> file than access path considerations, OPEN's, PREPARE's, etc.  I can't
> think of anything to do, on my end, to reduce the CPI4339 times.  I
> suppose I need to point this out to IBM as 'an area of concentration of
> effort'.
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
> --
> 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.
>
>
> --
> 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.
>
>
> --
> 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.
>
>
> --
> 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.
>
>
> --
> 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.
>
>