RE: SQl Access Paths

[WallyC@xxxxxxxxxxxxxxxxxxxx]

Phyllis wrote:

message: 4
date: Wed, 21 May 2003 16:17:50 -0600
from: "Phyllis Field" <pfield@xxxxxxxxxxxx>
subject: SQL Access Paths

We recently upgraded from V6.1.01 Mixed Mode to V6.1.02 Mixed Mode  We have
no custom modifications.  Our operating system is V4R5.  We are not
partitioned.  

I applied the latest Cumm PTFs and the APAR PTFs that dealt with SQL
performance.  I loaded the upgrade into the Test Environment and we ran
testing on it.  Everything seemed to be fine in the Test Environment so I
promoted to Production.   Started receiving complaints about the performance
in Production so I worked with SSA and got some additional BMRs that I again
loaded to Test.  After testing them, performance in test seemed great so I
then loaded these to Production.  Still received complaints on the
performance in Production but was able to isolate the problem to Order
Inquiry (ORD300) when filtering by Customer PO/Customer/Order.   When I
started looking at open files I discovered that the Test Environment was
using the access path for the logical ECHL07 which is keyed by HCPO, HCUST,
HORD thus the great performance in Test.  However, the Production
Environment was using the access path for the logical ECHL09 which is keyed
by HORD,  HCUST, HSAL, HCPO.  Both logicals !
exist in the data library for production and the data library for test.  We
(SSA and I ) did performance monitoring, checked and rechecked and
ultimately found no reason for this - files and programs are at the same
level, no mods.   At SSA's suggestion, I created an index on the ECH file
(ECHL99) and sequenced it by HCPO, HCUST, HORD which is the same as ECHL07.
Production is now using the ECHL99 path and Test is still using the ECHL07
path and performance is great in both environments.  After all this
information, my question is WHY?  Why was the Production Environment using
the ECHL09 access path instead of the ECHL07?


Phyllis Field, CPIM
IS Manager
Specialty Products Co.
pfield@xxxxxxxxxxxx
www.spcperformance.com
303-772-2103


Hi Phyllis,

We have had some really strange things happening with access paths. We are
running 405CD. We wound up going to V5R1 and applying patches to get it
better performance. It really seemed to be more of an IBM than SSA issue for
us?

Wally M Carr
Business Systems Analyst
The Bergquist Company
952.820.6516
WallyC@xxxxxxxxxxxxxxxxxxxx