Hi Phyllis, What was the original problem you were trying to resolve? Did the either a cume from IBM or SSA cause a sudden slow-down in performance? Did you also apply the latest BMRs for that SSA program that was slowest? What SQL statement was the chosen path being used for? That is a key question, as all the other analysis will rest upon the SQL statement itself. It is true that the optimizer will look at the most recently created paths first, and all things being equal it will use the best match it finds first. But you are saying all things were not equal, and that when it used the 2nd path, performance was bad. I have also been told that SQL indexes can be paged into memory in larger 'chunks' so are sometimes preferred over logical files, depending on various factors. Could be: 1. Optimizer is making a mistake (was IBM brought in to review?) since performance is worse for one result and better for another 2. The SQL statement being executed is not specific enough about what it wants (ordering or some other specification) to make a difference to the optimizer for choosing between the 2 paths and the SQL statement could be improved. One would result in an IBM PTF, and the other would result in an SSA BMR. The only way to really find the answer is to find the SQL statement in question, and have either IBM or SSA support analyze why this has happened. All else is conjecture. Thanks, Genyphyr Novak SSA GT ----- Original Message ----- From: "Phyllis Field" <pfield@xxxxxxxxxxxx> To: <BPCS-L@xxxxxxxxxxxx> Sent: Wednesday, May 21, 2003 5:17 PM 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 _______________________________________________ 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.