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

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.

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

Wally M Carr
Business Systems Analyst
The Bergquist Company

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.