Re: Performance solutions - more than one kind exist

Al,
You do not need to recompile programs for the new indexes you create! It is
really simple - use SQL Create Index to create them, observing whatever
naming convention you feel will avoid clashes with existing logicals, and
putting them in whatever place you deem best (they do not have to be in the
BPCS library list - the SQL optimiser will find them from its own cross
reference files in Qsys), and that's it.
Also you don't need consultants to dial in and do the analysis - its easy
to do it yourself. Nexgen have a guide to using DBMon on their website, you
can run it as part of the Performance Monitor across the whole system (but
not for more than about 20 minutes or you will fill up the file!) and then
query the file and print all records where QQIDXA (index advised) is 'Y',
print the field which tells you what index to use (but truncate it as it is
1000 chars!), and the edtimated run time and total records in the file, job
name, file, library, index used if any, etc. Sort your query by library (if
you have different BPCS environments), file and index. You can the identify
the top ones to create as either the file is very large or the estimated
time is more than 1 second or it is used many times in one or more jobs.
Typically as we all know BPCS will read all records and do the same thing
for each, and this could include having the SQL optimiser dynamically
create an index and then throw it away for each record read. This uses lots
of CPU, so if you create the index you will save both processing time and
CPU for the rest of the box. Of course there is more, but it is a good
start to run the above process iteratively across BPCS at different times
of day durinng a whole month's BPCS processing to eliminate the worst
bottlenecks. It is not a solution to simply use indexes that other
consultants or other clients have identified - your business's use of BPCS
is unique, and that is why you have to do the exercise, SSA can't just ship
the indexes you need.
hope this helps,
Clare


+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---