Re: ACR900 - too long

[Phil.Catlin@xxxxxxxxxxx]

All...

What Davide suggests will certainly work, but if you have iSeries 
Navigator installed it is much simpler!

You need to make sure you have all the options for Navigator installed on 
your desktop.  Once that is done, expand the system that BPCS is running 
on, expand the "databases" icon, and you will see an entry for SQL 
Performance Monitors  (you can also expand the "Schemas" or "Libraries" 
icons depending on your version.  Then you will see performance monitors 
under the "database tasks" window at the bottom)

Starting this monitor performs the same steps Davide suggests below, 
however with a GUI interface and no manual SQL...

Also check out "explainable statements".  This will take a complex SQL 
statement and graphically show you the steps the OS took to get your 
answer.  You will be able to see from it where indexes may help at 
intermediate points of the query.

Additional documentation is on the IBM website for your version.

Phil Catlin




"Davide Roveda" <Davide.Roveda@xxxxxxxxxxxxx> 
Sent by: bpcs-l-bounces+phil.catlin=formica.com@xxxxxxxxxxxx
03/18/2006 11:22 AM
Please respond to
"SSA's BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>


To
"SSA's BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>
cc

Subject
Re: [BPCS-L] ACR900 - too long






Hello

The way to make a real check of SQL performance is: Let Iseries do for 
you: 

In this way:

Start DEBUG:
STRDBG UPDPROD(*YES)

Start the DataBase Monitor
STRDBMON OUTFILE(SSARD/ORD550DBM)  TYPE(*DETAIL)

EXECUTE THE PROCESS that has to be checked:

Obtain the log on file:

DSPJOBLOG OUTPUT(*OUTFILE) 
          OUTFILE(WORKLIB/ORD550MSG)

Analyze:

Count of SQL
SELECT COUNT(*), QQC103, QQ1000 FROM ORD550DBM WHERE QQC103 <>'QAQQINI' 
AND QQC103 <> '-' AND QQI4 <> 65536 GROUP BY QQC103, QQ1000 ORDER BY 
QQC103, QQ1000

How many times a SQL run

Evaluate time consuming
SELECT QQI4, QQC103, QQ1000 FROM ORD550DBM WHERE QQC103 <> 'QAQQINI' AND 
QQC103 <> '-' AND QQI4 <> 65536 AND QQI4 > 1  ORDER  BY QQI4 DESC

QQI4 are Millisecond.
 
Verify DB Lf Missing
>From the dspjoblog on outfile:

SELECT QMHMID, QMHMDT FROM ORD550MSG WHERE QMHMID = 'CPI432F' OR  QMHMID = 
'CPI4321'

This shows the key missed, LF rebuild. Create the LF via DDS or SQL Path.

Regards

-----Original Message-----
From: bpcs-l-bounces+davide.roveda=ssaglobal.com@xxxxxxxxxxxx 
[mailto:bpcs-l-bounces+davide.roveda=ssaglobal.com@xxxxxxxxxxxx] On Behalf 
Of DeeDee Virgei
Sent: giovedì 16 marzo 2006 14.29
To: SSA's BPCS ERP System
Subject: Re: [BPCS-L] ACR900 - too long

That really isn't that many records.  We have 1,041,661 RAR records and
our ACR900 job ran between 5 and 6 minutes last close...  We are on
V4.05 CD, haven't added any user logicals over the RAR, haven't
customized ACR900 (Al, we did remove the RGZPFM ITH command from INV901C
to make it run much faster).  ACR900 use to run a lot longer (around an
hour I think) before we got our i5 (520) hardware; prior iSeries was a
270... 

My guess is that it may be the release of BPCS and/or an older iSeries
that may be causing it to run so slow...  Just a guess; hope this helps.


Best Regards,

DeeDee Virgei
Project Leader

Nelson Stud Welding, Inc.

-----Original Message-----
From: bpcs-l-bounces+deedee.virgei=nelsonstud.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+deedee.virgei=nelsonstud.com@xxxxxxxxxxxx] On
Behalf Of Otto Hason
Sent: Wednesday, March 15, 2006 11:45 PM
To: SSA's BPCS ERP System
Subject: Re: [BPCS-L] ACR900 - too long

Hallo Steve,
there are about 150000 records in RAR, 2 deleted (today).
Otto
  ----- Original Message -----
  From: Steve Segerstrom
  To: SSA's BPCS ERP System
  Sent: Wednesday, March 15, 2006 8:58 PM
  Subject: Re: [BPCS-L] ACR900 - too long



  How many records are on the rar file?

  -----Original Message-----

  Hi all,
  BPCS v 6.0.04
  we are unlucky with ACR900 - month end close. Before 3 years it took
1700 s CPU time, now it consumes about 8000 s.
--
This is the SSA's BPCS ERP System (BPCS-L) mailing list To post a
message email: BPCS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/bpcs-l.

Delivered-To: deedee.virgei@xxxxxxxxxxxxxx