Re: SQL Access Paths -- BPCS-L

are the files sizes the same, test and production? the SQL optimizer
considers file size also and this may cause a difference. you might want
to run the program in debug mode and look at the job log to see any SQL
performance recommendations. there are numerous IBM manuals and redbooks
that try to explain the SQL optimizer. and there are many support
programs from IBM and others that try to analyze situations and make
recommendations. bottom line is that this is a subject all by itself. 



>>> pfield@xxxxxxxxxxxx 05/21/03 03:17PM >>>
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

_______________________________________________
This is the SSA's BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l 
or email: BPCS-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.