RE: QAQQINI - Query options file and performance of SQL

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.