SQL Access Paths

["Phyllis Field" <pfield@xxxxxxxxxxxx>]

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