SQL Performance Improvements Suggestions -- BPCS-L

We are running 6.0.02 PLf, C/S, Mar'98 cum.  We have an S20 Model 2165 with
1024 MB of memory and 37Gb of disk running config'd for Raid-5.  This is our
production machine.  Our Conference Room Pilot machine was a 9402 Model 400
with 128 Mb of memory and 27 GB disk also as Raid-5.

In our discussions with SSA about performance, I received a list of 27 files
from Jack Tyse that SSA had identified as needing logicals created.  These I
created using DDS and are stored in the BPCSUSRF library.  

The other recommendation was to run the command PRTSQLINF "after" running
any program that "seemed" to take longer than what you were expecting.
Looking through the report that was generated, I would look for a line
saying 
"SQL4012   Access path created from keyed file IPPL15 for file 1."  If this
line did not appear in the listing, it meant an existing logical was used.
A few lines above this would be the SQL Declare statement and in there you
would find the "order by" phrase, which is how the logical key is
constructed.  This may seem like a lot of work but we went through the order
entry, billing, and some of the CEA programs looking for logicals to make
permanent.  Until I see a performance problem, I will not do this again.
Actually, I've done this once in the 7+ months we have been live.  I think
it took me longer to find were I had "filed" these instructions to write
this e-mail than the time it would take you to determine which logicals need
to be created.

I believe it is worth the effort involved.  Of course, the deciding factor
in whether or not to add a logical is the system overhead involved in
keeping "another" view updated.  That each user will have to determine based
on their own circumstances.

Dilbert's Words Of Wisdom:
"I love deadlines.  I especially like the whooshing sound they make as they
go flying by."

Dennis Munro    
Badger Mining Corporation
dmunro@badgerminingcorp.com <mailto:dmunro@badgerminingcorp.com> 


        -----Original Message-----
        From:   Rick J. Carter [SMTP:rickcarter@holley.com]
        Sent:   Thursday, February 18, 1999 1:23 PM
        To:     BPCS-L@midrange.com
        Subject:        Billing Invoice Process

        AS/400 Mix mode v 6.0.02

        Our current invoice process that runs nightly is running for about
5-6 
        hours.  This is the BIL500 job stream.  We only run invoices in
batch once 
        per day.  THis job runs quite long no matter the number of invoices
being 
        processed.  Most of the processing appears to be in t he CEA related
portion 
        of the job stream.  Does anyone no of any logicals, etc that could
help 
        improve this job performance.
+---
| 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
+---