× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. BPCS-L
  3. May 2003

Re: SQL Access Paths (Plug)

Phyllis,

I highly suggest you take us up on the offer for CCS to provide you with a
performance analysis and capacity plan. This takes a lot of guesswork out
of planning for resources and making sure there are not gotchas in regards
to the capacity for the system.......

CCS offers this service for no charge to our installed clients, and as a
slight fee (fully reimbursable against future business) for prospects.

Mitchell K. Kleinman
Executive Vice President
CCS - an IBM Premier Business Partner
949-476-0874 (Main)
949-261-3298 (Direct)
949-261-9164 (Office Fax)
425-940-1954 (eFax)
Mitch@xxxxxxxxxxxxxxx



|---------+---------------------------->
|         |           "Phyllis Field"  |
|         |           <pfield@xxxxxxxxx|
|         |           com>             |
|         |           Sent by:         |
|         |           bpcs-l-bounces@xx|
|         |           drange.com       |
|         |                            |
|         |                            |
|         |           05/23/2003 01:40 |
|         |           PM               |
|         |           Please respond to|
|         |           "SSA's BPCS ERP  |
|         |           System"          |
|         |                            |
|---------+---------------------------->
  
>------------------------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                                 |
  |       To:       "SSA's BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>               
                                                 |
  |       cc:                                                                   
                                                 |
  |       Subject:  Re: SQL Access Paths                                        
                                                 |
  
>------------------------------------------------------------------------------------------------------------------------------|




Thanks for the responses!

Genyphyr, the original problem was poor performance after we upgraded from
V6.1.01 to V6.1.02.  I did not notice a particular cume or BMR that caused
this  - the performance was great in V6.1.01 and poor in V6.1.02.  Yes, I
did apply the latest BMRs from SSA for the program that seemed to be the
slowest.  SSA Helpline was very helpful to me in this matter but ultimately
they could not say why this was happening.  I have given the SQL statement
(prtsqlinf) to the person at SSA that I was working with but I don't think
it sheds much light on the issue.

Myron, after your response I checked the create times.  In production, the
ECHL09 file was created after the ECHL07 file but the same held true in the
test environment where the access path for the ECHL07 file was selected.

I also checked the file sizes as Chick suggested.  Both of the production
files in question are larger (data size and # of records) than the test
files but in both environments the ECHL07 file is larger (data size) than
the ECHL09.

As Wally and Genyphyr suggest, this could very well be an IBM issue.   I'll
contact their support line.  Also, I am upgrading to V5R2 this summer which
may take care of everything.

For now the 'dummy' index has solved the problem with this program.  I just
like to know 'why' in case it happens again.

Thanks again!
Phyllis





----- Original Message -----
From: "Genyphyr Novak" <novakg@xxxxxxxx>
To: "SSA's BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>
Sent: Thursday, May 22, 2003 7:42 AM
Subject: Re: SQL Access Paths


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


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

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-2024 by midrange.com 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.