RE: Always index if Event Approve -- BPCS-L

The following are IBM AS/400 manuals (with specific chapters in them) that
offer information on how to improve query performance of any type (embeded
SQL statements, interactive SQL, Query/400 queries, QM queries and OPNQRYF
queries, because they all use the same database interface) on AS/400
system:

1.   DB2 for AS/400 SQL Programming
     Version 4
     Document Number SC41-5611-00
     Chapter 23.  DB2/400 Data Management and the Query Optimizer

2.   DB2 for AS/400 Database Programming
     Version 4
     Document Number SC41-5701-00
     Appendix D.  Query Performance: Design Guidelines and Monitoring

3.   Query/400 Use
     Version 4
     Document Number SC41-5210-00
     Appendix C.  Performance Tips and Techniques

---------------------- Forwarded by Goran Mihajlovic/SSA/US on 10/08/99
02:24 PM ---------------------------


Kusman Lim <KUSMAN@takeda-id.com> on 10/08/99 05:51:47 AM

Please respond to BPCS-L@midrange.com

To:   "'BPCS-L@midrange.com'" <BPCS-L@midrange.com>
cc:    (bcc: Goran Mihajlovic/SSA/US)
Subject:  RE: Always index if Event Approve




Clare;

Do you have any document for this database tuning guide ?

Thanks

Kusman Lim
PT Takeda Indonesia
MIS Department


-----Original Message-----
From: Clare.Holtham@ssa.co.uk [mailto:Clare.Holtham@ssa.co.uk]
Sent: Friday, October 08, 1999 2:20 PM
To: BPCS-L@midrange.com
Subject: RE: Always index if Event Approve


Hello Kusman Lim,
The SQL Optimizer in OS/400 will dynamically create an index it needs if
it
does not exist, this is what you can see happening with the GHH file.
This
increases response times and takes CPU resource. To stop this happening
you
simply create the required index using CREATE INDEX in SQL or create a
logical.
To find out the key you can run the database monitor STRDBMON and query
the
file that this creates, looking for records where QQIDXA = 'Y' (Index
advised). You can also run this as part of the performance monitor
across
the whole system, though you should limit this to 20 minutes or so at
first, and create any indexes recommended especially where the files are
large or the estimated processing time more than 1 second or the same
index
is created many times, this will help performance for the rest of BPCS.
You can use the command PRTSQLINF to see what the SQL statements in an
individual program are doing.
This 'database tuning' is an essential exercise for every client who has
BPCS V5 or V6 or other software containing SQL, and has to be done by
you
as your data and your use of the data and the software is unique to your
company and will require a different set of indexes to other clients.
Hope this helps,
cheers,
Clare




Kusman Lim <KUSMAN@takeda-id.com> on 08/10/99 04:19:48

Please respond to BPCS-L@midrange.com

To:   "'BPCS-L@midrange.com'" <BPCS-L@midrange.com>
cc:    (bcc: Clare Holtham/UK/SSA_EUROPE)
Subject:  RE: Always index if Event Approve




George

Every time I check on WRKACTJOB I found the function mentioned IDX-GHH.

Kusman Lim
PT Takeda Indonesia
MIS Department


-----Original Message-----
From: George Sagen [mailto:gsagen@primesourcetech.com]
Sent: Thursday, October 07, 1999 9:21 PM
To: BPCS-L@midrange.com
Subject: RE: Always index if Event Approve


I'm going to speculate about why this is a problem to you...

I remember on that version that if we ever ended up with a large number
of
records in Approve Events and wanted to post a series of them, we would
double click the first one then click 'POST'. The window would vanish,
the
GHH record status was updated in the GHH table from '2' (Unposted) to
'3'
(Posted), then the list of events to be approved would be refreshed
before
allowing the user to move on to posting the next record. The longer the
list
of records to be approved, the longer it took to refresh the list, and
the
list was refreshed each time we hit post. The status flag is not indexed
in
the GHH (GHH.HHSTAT) or the GLH (GLH.LHSTS), so I suppose it takes
longer to
filter the list than it would if indexed. Have you identified that it is
the
updating of indexes that takes so long? Is it possibly the re-reading
that
takes so long?

Our workaround was to go into the [Options], [Filters] pull-down menu
and
set the List Capacity to 1. The list refreshed much quicker allowing us
to
post multiple records much faster.

The general problem of posting a series of events is solved in 6.04 by
the
Multi-Post feature that allows you to select a range of events to
approve in
batch.

George Sagen
BPCS Application Consultant
gsagen@primesourcetech.com <mailto:gsagen@primesourcetech.com>
http://www.primesourcetech.com

PrimeSource Technologies, L.L.C.
7373 East Doubletree Ranch Road
Suite 150
Scottsdale, AZ 85258
(801)360-6360 Direct & VM


.  -----Original Message-----
.  From: uucp@Uucp1.mcs.net [mailto:uucp@Uucp1.mcs.net]On Behalf Of
Kusman
.  Lim
.  Sent: Thursday, October 07, 1999 3:32 AM
.  To: 'BPCS-L@midrange.com'
.  Subject: Always index if Event Approve
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---






+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---


+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---