RE: ** Pick Release has poor response time **... -- BPCS-L

We are bpcs o/s version: 6.01.01

This is a complex topic. Look at the archives; follow sql tuning advice; there 
is excellent advice from key contributors. Especially from Al M. and Genyphr.  

Here are some of the easier things you can try that worked for me. Do it on a 
quiet system and make sure you have a backup of the files: 
1. I would purge ZPD independent of anything else. The only thing WE USED IT 
FOR  after an order completes is reprint. I got some improvement gains by 
writing an sql that hard deleted closed records over 30 days old. Then I 
re-org'd zpd. 
2. Are you re-orging files ? I got a huge gain  when I re-orged the IPP file 
weekly. ELA is another one to look at. Do a dspfd ela and look at the number of 
deleted records. If over 15%, do a rgzpfm (quiet system).
3. Do a prtsqlinf ORD550B1 ; look at the spool file.  Look for the estimated 
query run time. Anything over 1 second deserves research.
        SQL4020  Estimated query run time is 2 seconds.
4. If you start deleting any other files; DON"T or BE CAREFUL. Widows and 
orphans are a bad thing in BPCS and will cause problems. I wrote a purge; took 
me a long time to perfect (months of work). But - we have over 800,000 orders 
and 4,000,000 lines; we had to purge. 
5. Find duplicate keys; they will cause an index build.

So - again - here is my disclaimer. The above items did wonders for us for 
response time. They are not meant to be an exhaustive list but are a start. 

IF you really want to short-circuit project time and have a couple $$$; look to 
a third party product like Locksmith from Unbeaten Path...  You owe me Milt. 


-----Original Message-----
From: bpcs-l-bounces+ssegerstrom=intermatic.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+ssegerstrom=intermatic.com@xxxxxxxxxxxx]On Behalf
Of Lat - Sandy Sever
Sent: Wednesday, April 13, 2005 1:28 PM
To: 'Bpcs-L (E-mail)
Subject: [BPCS-L] ** Pick Release has poor response time **...



Hello All,

BPCS Version: 6.01.01
AS/400 Operating System: V4R4
% system ASP used: 47
IPL: 2 times a month

When we run the program for Pick Release ORD550, the processing
keeps getting slower and slower.  We have been running the same
programs for several years.  No updates to BPCS or operating system.
The response time was acceptable a year ago.  We only select one
order at a time.

My only guess is that our databases have been growing and growing,
but I don't know which one(s) would cause this to happen.  How
big of a database is to big?

I've been told not to run the reorg. programs, so I have not done
this.  Should I do this manually?  Which ones?

The order purge program was run a couple years ago. Though ending
in errors, so I haven't ran it since.

Here are some record counts if that helps:
ECL  158,000
ECH   72,000
ESN  130,000
IPP  102,000
ZPD  449,000
LLL   74,000
ELA    1,000
IIM  105,000
ILM      100
ILI  307,000


Any helpful direction would be greatly appreciated, since I'm getting
beat up by the users...

Thanks,
Sandy



 



Hydro Carbide, Inc.
Latrobe, PA  15650
Voice 724-539-9701
Fax    724-539-0326


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

Delivered-To: SSegerstrom@xxxxxxxxxxxxxx