Re: SQL Access Paths

[Mhamilton8685@xxxxxxx]

Phyllis,

I remember reading somewhere a few years ago that the AS400 searches the 
access paths on files starting with the most recently created to find one to 
use.  When it finds one that can be used it stops looking.  This could be the 
reason for your problem.  You probably could have deleted the ECHL07 logical 
and rebuilt it and had the same affect as creating a new logical.

Myron Hamilton

In a message dated 5/21/2003 5:26:12 PM Central Daylight Time, 
pfield@xxxxxxxxxxxx writes:

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