Re: Slow SFC400 run

[Doug Schneider <dschneider@xxxxxxxxxxxxxxx>]

This is a multi-part message in MIME format.
--
Also, with V4R5 CA Express OPS Navigator, there is a plug-in called
VisualExplain.  Also, there is a redbook on the use of Visual Explain.
You can define the files in VE, and then execute the SQL statements.
This will tell you if a full table scan is being done, and if need be,
recommend a LF view.  Also, VE will create the LF, and then reexecute
the SQL statements, and check out the improvement.

DBMON is an effective tool, but it can be a long drawn out process.

Genyphyr Novak wrote:
>
> 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
> --
>
> _______________________________________________
> This is the SSA's BPCS ERP System (BPCS-L) mailing list
> To post a message email: BPCS-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/bpcs-l
> or email: BPCS-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/bpcs-l.
--
Content-Description: Card for Doug Schneider

[ dschneider.vcf of type text/x-vcard deleted ]
--