Re: Billing Invoice Process

["Rick J. Carter" <rickcarter@xxxxxxxxxx>]

We built the logicals that were suggested for the billing process and got 
amazing results.  We have an E620 running v4r2 operating system and the 
system has been very sluggish with certain jobs.  After building the 
logicals requested from Nexgen's article, our billing process went from 5-6 
hours down to 20-30 minutes.  THis has been great and it's has been 
performing steady for the last week.
From: Ata510
To: BPCS-L
Subject: Re: Billing Invoice Process
Date: Saturday, February 27, 1999 1:59AM

In a message dated 2/19/99 2:37:38 AM Central Standard Time,
andrew_murphy@uk.xyratex.com writes:

> We have also had problems with the length of time BIL500 takes to run. We
> have
>  been informed by SSA that NO users can be on the system while this job 
runs
> so
>  users like ourselves which run world-wide operations on a single
environment
>  must remove all access to BPCS (including client/server applications) 
while
> it
>  runs. We also use consolidated invoicing which increases run time even
more!
>
>  I will certainly try creating the logicals suggested but should these be
> created
>  as logical files (i.e. DDS) or as SQL indexes (CREATE INDEX .....)? Most
>  documentation I have seen has recommended that indexes are created but
> surely
>  this then loses any select/omit criteria that the logical files being
> replaced
>  had?
>
>  I would be interested to hear if anyone has tried either method.

I have tried both, and if no select/omit criteria are required, then the
behavior of each as regards speed increases is very similar. In this case, 
you
are not replacing logicals, you are adding more over the same files. The
advantage of the logicals is that you have the source around as clear
documentation of what was done, and as an easy way to create the same exact
thing in many environments. If Select/Omit is required, you will have to use 
a
logical -- but the DBMON tool never recommends Select/Omit -- you have to 
use
DBMON with a knowlege of the code and what it is trying to do, as well as 
what
the Query Optimizer is trying to do and why (and out-think the DBMON
recommendations in some cases) to come up with what will ultimately be the
best solution.

In short -- in this case, you could use either method. CREATE INDEX is a 
quick
and dirty way to test and see if it makes a difference in performance. 
 Mostly
because I am AS/400-centric and indexes were imported from Unix they just
don't seem as 'normal' to me -- but they can get the job done. You could ask
on one of the AS/400 Forums about what the technical differences are in the
DB2 database when one versus the other is used, because I really don't know. 

Also when you name the new ones, consider starting at very high numbers 
(like
ECHL99) so that you can easily recognize what parts of the database are your
customizations when it is time to upgrade to another release.
You don't necessarily want to bring them all with you, as they may not be
needed at the next release...and too many logicals over a file can cause
performance degradation for interactive users.

Also, I would take a look at the BMRs listed on OGS, because there are new
performance BMRs written against that program that also recommend logical
files (some of the same mentioned by Walt Schaff at Abbott, in fact). If the
BMRs are ever completed, you might want to just get the official fix 
instead.
Sometimes they alter the SQL in the code so new logicals are not needed.
+---
| 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
+---
+---
| 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
+---