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

Hi Sandy,
 
I would recommend taking a look at SQL first. BPCS is notorious for its poorly 
written embedded SQL. 
If modifying programs is out of the question I would go with performance 
enhancing SQL indexes, either DDL indexes or EVI indexes. 
 
DDL index:
Not DDS but DDL indexes. Logical file based DDS indexes use a Page size of 
4-8K, DDL indexes use 64K page size. This larger page size makes indexes more 
attractive to the query optimizer and allows the database paging to be more 
efficient on a query. SQL fetches utilizing DDL indexes can move 8x times the 
data back and forth into memory. So, when data is retrieved into memory DDL 
indexes can transfer 64K were as DDS indexes can only transfer 4K before having 
to return to DASD for more data. 
 
DDL indexes is a file Index object type created using SQL, unlike a DDS index 
that is created with DDS source file code that is compiled. 
Example:
CREATE INDEX BRP62F/IWIU03 ON MXP62F/IWI (WID,WPROD,WWHS)
 
Using IBM Database Monitor utility -Or- other SQL performance based tools you 
will be able to pinpoint SQL optimizer performance bottlenecks.  
 
EVI index:
If you want to be real forward thinking, you can try an encoded vector index 
(EVI).  
EVI indexes are the steroids of indexing. 
An encoded vector index (EVI) is an index object that is used by the query 
optimizer and database engine to provide fast data access in decision support 
and query reporting environments. EVIs are a complementary alternative to 
existing index objects (binary radix tree structure - logical file or SQL 
index) and are a variation on bitmap Indexing. Because of their compact size 
and relative simplicity, EVIs provide for faster scans of a table that can also 
be processed in
Parallel.
  
Other methods for improved performance: 
1) Allocate additional memory for the subsytem from the other batch pools. High 
Paging in this subsystem? 
 
2) Recompile Programs objects that are larger than 16mega bytes. Programs 
objects larger than 16mb are unable to store an SQL access plan.
 
3) For performance reasons IBM recommended re-compiling programs with embedded 
SQL with a new compilation default ALWCPYDTA(*OPTIMIZE). The majority of BPCS 
programs where compiled with parameter allow copy of Data YES; ALWCPYDTA(*YES). 
By Recompiling the program and specifying ALWCPYDTA (*OPTIMIZE) Parameter, some 
complex queries can perform better by using a sort or hashing method to 
evaluate the query instead of using or creating an index. By using the sort or 
hash, the database manager is able to separate the row selection from the 
ordering and grouping process. This separation allows the use of the most 
efficient index for the selection.
 
4) Change all Physical and Logical file file Access Path Sizes from 4 gigabyte 
to 1 terabyte
 
5) Look at the target release of BPCS programs. Some have early OS/400 Target 
release. By specifying TGTRLS(*CURRENT) the SQL precompiler can generate code 
that will take advantage of SQL performance enhancements available in the 
current release of OS/400.
  
You can contact me off line for more information. 
 
Regards,
Dave Trevino
Framework iSystems (866) 305 0759
WWW.FRAMEWORKIS.COM
 
 
 
-----Original Message-----
From: Lat - Sandy Sever <sandys@xxxxxxxxx>
To: 'Bpcs-L (E-mail) <bpcs-l@xxxxxxxxxxxx>
Sent: Wed, 13 Apr 2005 14:28:13 -0400
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: FWSoftware@xxxxxxx