Re: Invoice Billing Process

Hello,

Logical files are not always the answer to every SQL issue. In some cases
(certain SQL constructions) no logical is possible to build to improve it,
and the SQL must be re-written in order to improve performance. However, in
this case, if this program did actually work faster on a prior OS release,
this would suggest that the new operating system is actually optimizing that
statement differently now. If that is the case, it could be that different
logicals are now required to run the statement faster. OR the slow-down
could be unrelated to the OS upgrade and maybe something on your system has
changed such as file sizes growing or the way the program is being run is
different from past runs (different end-user choices which would cause a
different set of SQL statements to execute).

This example (how logicals that used to work fine to run a given SQL
statement now may not be getting picked to run the same statement on a new
OS release)  also illustrates why a logical file "to improve performance"
which someone suggests on a mailing list may not work for everyone on every
OS release or BPCS release. Therefore, treat any suggested logicals you find
on this list with care; don't blindly apply them  -- test the program(s) you
think they are supposed to help by taking before/after run-times using the
same test data, in order to make sure they actually speed something up. If
it doesn't improve things, don't keep it on your system.

Sometimes at a new OS/400 release, re-tuning of SQL is required due to
optimizer changes, and there is simply no avoiding that. The PTFs listed in
the Info APAR for BPCS on the IBM web site are for fixing SQL bugs. But some
new OS/400 releases have optimizer enhancements which may make changes to
the optimizer logic, and this might cause different logicals to be required
to run the same statement efficiently compared to what used to work the
previous release. Thus, some programs, on a case by case basis, may have to
be re-tuned if significant optimizer changes are made at the new operating
system release and cause old logicals to be ignored and new, non-existent
logicals to be required.

In addition to all of this, certain types of SQL constructions simply won't
cause the optimizer to suggest a logical for you, because it isn't capable
of predicting one in that specific case. Often if you look at the statement
carefully and understand how the optimizer makes its choices, you can try to
build one that you think it might use, and then run tests to see if it picks
the new logical. You would use some of the reason codes listed for the
logical it did use, etc. to try to understand what it needs.

You can call this into the SSA GT Support Center as a performance issue with
that particular BPCS program if you have an OGS contract. Then, you can send
the DBMON file along with a description of how you are running the program
in for further analysis if you don't want to do all this analysis yourself.
The other advantage to reporting this issue to Support Center is that the
logical may be added to base BPCS via a BMR, rather than requiring you to
keep it as a modification on your system.

Thanks,

Genyphyr Novak
SSA GT

----- Original Message -----
From: <RickCarter@holley.com>
To: <bpcs-l@midrange.com>
Sent: Friday, April 26, 2002 4:24 PM
Subject: Invoice Billing Process


> I posted this question yesterday but got no hits, so I'm sending again in
> hopes that yesterday's message failed to reach the entire list.  The list
> is usually great about offering solutions to our beloved BPCS
applications.
>
> BPCS 6.1.01 Mixmode - AS400
>
> We currently process around 800 invoices each night with our central
> billing run of BIL500.   The processing time for this job has recently
> grown to about 1 hr. and 40 minutes.  I can't remember when the
performance
> slowed on this job but we did recently implement V4R5.  We did however
> apply all recommended PTF's etc per the BPCS list.
> STRDBMON against the BIL501B job shows some high index counts on a couple
> of files but does not recommend any logicals to build.  I suspect we need
> to build something but don't have a clue what to build.  We are also
> looking that STRDBG on this job to see if we can determine where the
> performance hit is.
>
> Any ideas on what we can do to improve performance on this job.
> How accurate is DBMON on these jobs when it illustrates a high index count
> but shows no logical fields to build.
> Any suggestions outside of buying some bolton software to make this work
> better.
>