Re: Slow SFC400 run -- BPCS-L

Hello,

You don't mention what version of BPCS you have, but since you are seeing a
FETCH, I will assume you are at 6002 or higher, since prior releases didn't
use SQL.

You can call this into the Support Center if you have OGS and they can
assist you in analyzing the program in question with DBMON to see if there
is a logical file you are missing and also to ensure you have any
performance BMRs for SFC734B. There are several 'D' performance BMRs listed
for the 6002 and 6004 releases.

If you don't have OGS, then I suggest that you use DBMON or another
performance tool to figure out if you are missing any logical files that
could speed the performance of the FETCH statement. Service offerings are
available from SSA GT if you do not know how to use/analyze DBMON at your
site.

The PRTSQLINF command on the AS/400 will show you all the SQL statements in
the program, as well as the access plan used the last time the statement was
run and access path suggestions in certain conditions. Note that the
run-times listed in PRTSQLINF are only the SQL optimizer's estimates, and
are NOT the actual runtimes for that statement. The estimates are based upon
some default 'weighting' given to various types of SQL statement
construction, file sizes, file key contents (if such data is available to
the optimizer) and chosen access method. Actual runtimes can vary quite a
bit! This is why DBMON is more useful, especially in complex problems.

PRTSQLINF also doesn't let you know/see how many times that statement was
executed in a job run (i.e., once or in a loop, etc..) so therefore the full
impact of a specific SQL statement can not be judged from PRTSQLINF alone. A
statement that runs once for 10 seconds in a job will not impact the
performance of a job that runs for 1/2 hour, as much as a statement that
runs 500 times for 2 seconds each time in that same job will impact
performance.

Thus, tuning the statement that took 2 seconds to run each of 500 times
would have better benefit than tuning the statement that ran once for 10
seconds. This is why DBMON is more efficient for analyzing performance
compared to PRTSQLINF, as it will analyze the entire job rather than each
statement individually.
.
However PRTSQLINF is very useful to run before/after adding a new logical
file to find out whether or not the system decided to use the new logical to
implement a given query. The FETCH of a given cursor uses the access plan
listed in the DECLARE of that cursor.

Thanks,

Genyphyr Novak
SSA GT


----- Original Message -----
From: "Krzysztof Dziubinski" <kdziub@idom.com.pl>
To: <BPCS-L@midrange.com>
Sent: Friday, April 19, 2002 11:29 AM
Subject: Slow SFC400 run


This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Hello,

Our Batch Component Allocation program (SFC400) has slowed down recently. I
checked the call stack of the job running SFC400. The problem is within
SFC734B which is performing SQL FETCH commands intensively. I have no
sources of SFC734 and I can not identify the query. I suppose the reason is
our lot files ILI and ILN contain almost 500 000 records. Is is safe to
delete old inactive records from ILI (ILN) ?

Regards,

K. Dziubinski
--