Re: Invoice Billing Process

["Genyphyr Novak" <novakg@xxxxxxxx>]

Hello FWSoftware,

My point in the last posting I made was to mention that even in these cases
of using the DEBUG joblog messages, or analyzing the DBMON file to find out
what the optimizer recommends as logical files, there are going to be
certain SQL constructions for which the SQL optimizer cannot make a
recommendation to you or for which it won't make the most beneficial
recommendation. It makes recommendations out of need for something, rather
than to tell you the 'most optimal of all possible ways' to run a query.

In such cases, you need to use your (or someone you hire) knowledge of the
way the optimizer works to try to build a logical which it will use, and in
other cases you need to understand when it is necessary to re-write the SQL
(or to call SSA GT Support and make sure a BMR is entered so that SSA GT
corrects the SQL).

One simple example is that if a query would run faster with Index-Only
access (eliminates one extra read to the database, because the position-to
and read can be done from the same path) you will need to put all the fields
retrieved by the SQL statement into the logical file yourself - the
optimizer won't tell you this.

You will have to realize when this might help performance in a particular
case and build for yourself a 'test' logical containing the extra fields
required in the SELECT on the query, and which do not assist whatsoever in
positioning to the records (loops with lots of FETCH statements are very
good places to try for index-only access). Then test the new logical, and
see if the query uses it and if it causes index only access to happen, and
of course, decide if it helps performance or not.

There are several other cases where the optimizer won't tell you every
single thing that could make the query run better/faster, and therefore you
need to understand many things in combination: the query, the system, the
QAQQINI file settings available, the types of processors you have on your
system and the way the SQL optimizer is working at your OS/400 release to
get the most performance out of your box. Or alternatively, hire a
consultant who does understand such things to come out and assist you in
tuning your system; such as SSA GT professional services (this consulting is
recommended for overall performance tuning, rather than for performance BMR
support of a specific slow program in BPCS, where SSA GT Support can be
called).

Thanks,

Genyphyr Novak
SSA GT

----- Original Message -----
From: <FWSoftware@aol.com>
To: <bpcs-l@midrange.com>
Sent: Saturday, April 27, 2002 12:23 AM
Subject: Re: Invoice Billing Process


> --
> [ Picked text/plain from multipart/alternative ]
> Use STRDBG *YES (update production files). Should give you Index
> recommendations in the joblog. Also, DBMON works perfect but you need to
know
> Query's that use the DBMON file to it's fullest.
>
>
> _______________________________________________