Re: QAQQINI - Query options file and performance of SQL

The job runs pretty fast - under a couple of seconds.  He was just trying 
to shave off the decimal seconds to get it as fast as native and it was 
the actual access of QAQQINI versus any particular option within QAQQINI.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Wes" <small_du@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/10/2004 02:32 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Fax to

Subject
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.
>
>



--
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.