RE: V5R3 Performance Issue -- MIDRANGE-L

Jim,

I believe I mentioned the debug option to those working on the issue but I 
don't know if it was heeded or not.

I can forward your comments about the PTF's.  Maybe that will lead to something.

As the original architect of the program I did use the visual explain tool to 
try and optimize the SQL used.  There was a problem in that I can't run it 
against our production database (see previous post about authority).  That 
means I get results for a 1.4 million record data set versus a 400 million 
record data set.  Not very precise.  Some non-invasive tests were run against 
the production data set anyway but the tests were me timing it with my watch.  
Some of this has improved since the original implementation but at the time 
this is how it was.

All good suggestions.  Thanks.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
JOberholtzer@xxxxxxxxxxxx
Sent: Wednesday, May 18, 2005 11:56 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: V5R3 Performance Issue







<snip>
any chance a logical view has been deleted that the sql used, or
that the select or order by in the sql stmt has changed? Perhaps
not a v5r3 issue? Did anything change in your memory pools?
Settings for cache?
</snip>

I would agree with the comment.  Put these up in debug and watch the query
engine results.  Also, at DB group 4 there are some busted PTFs that
primarily are for restoring objects but I have also seen systems that have
SQL statements abort, and then gobble up massive amounts of Temporary
Storage.  Causes immense fault/page rates in the *MACHINE pool.  Only way I
know of to clear it is an IPL.   Check the temp storage with WRKSYSSTS  It
shows up in unprotected used.  If that is high, there may be a correlation.

Another issue is make sure you are now using the SQE query engine.  There
is a fine Redbook on the topic.  "Preparing for and Tuning the V5R2 SQL
Query Engine on DB2 Universal
Database for iSeries"   -  SG24-6598-00.  Check that out as well.  Lots of
great ways to use the graphical interface to DB/2 to get it really running
well.


Jim Oberholtzer
Senior Solutions Architect
Computech Resources, Inc.

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



Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.