|
midrange.com
Dean,
>>Hmmm. On the one hand, I beg to disagree. We were always told that the
>>query optimizer considered only the ten most recently created access paths on
>>the AS/400 prior to starting its own IDX function. On the other hand, newer
>>versions of OS/400 have improved SQL performance, so the optimization rules
>>may have changed. Guess I'll be visiting Mr's. Soltice and Sears at the next
>>COMMON conference...
Double hmmm on my side now(!) - I cannot be sure optimizer did work this way in
the past (i.e. consired only 10 most recent LFs), but for the past several years
while dealing with SQL performance problems in IBM and now in SSA, I have never
noticed in the debug joblog that any of the logicals based on physical file have
not been evaluated other than for reasons I have mentioned last time (shared or
damaged access path). The example bellow is the extract from the debug joblog
with CPI432C message and for one query optimizer access path evaluation I just
run against simple interactive (SELECT) SQL statement. It shows that in total 31
access paths were evaluated which is actually the total number of logicals
dependent on ECL file in question here (as per DSPDBR command info bellow), so
at least now it works as it should, if I may say :-) In addition, first level
text for message CPI432C says as per bellow: 'ALL access paths were considered
for file.....', therefore I would assume this is really what optimizer will do
(i.e. consider really all of them). The only guess I could have with regards to
such comment about 10 most recent logicals, is that although optimizer does
evaluate all access paths available, it does assign to them different weights
based on their creation date, for whatever reason its developers have found
necessary to put in the decision logic algorithm, but this is a pure
speculation. I would like to be with you when discussing this with your friends
at next Common :-)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CPI432C Information 00 09/28/00 09:57:47 QQQIMPLE QSYS 3A1F
QSQOPEN QSYS
Message . . . . : All access paths were considered for
file ECL.
Cause . . . . . : The OS/400 Query optimizer considered
all access paths
built over member ECL of file ECL in library SGPRDRF.
The list below shows
the access paths considered. If file ECL in library
SGPRDRF is a logical
file then the access paths specified are actually built
over member ECL of
physical file ECL in library SGPRDRF. Following each
access path name in the
list is a reason code which explains why the access path
was not used. A
reason code of 0 indicates that the access path was used
to implement the
query. SGPRDRF/ECLL99 17, SGPRDRF/ECLL48 11,
SGPRDRF/ECLL47 11,
SGPRDRF/ECLL44 11, SGPRDRF/ECLL43 11, SGPRDRF/ECLL42 11,
SGPRDRF/ECLL39 11,
SGPRDRF/ECLL38 11, SGPRDRF/ECLL37 11, SGPRDRF/ECLL33 11,
SGPRDRF/ECLL29 11,
SGPRDRF/ECLL28 11, SGPRDRF/ECLL27 11, SGPRDRF/ECLL26 11,
SGPRDRF/ECLL24 17,
SGPRDRF/ECLL23 11, SGPRDRF/ECLL20 11, SGPRDRF/ECLL19 17,
SGPRDRF/ECLL18 17,
SGPRDRF/ECLL17 17, SGPRDRF/ECLL16 17, SGPRDRF/ECLL12 17,
SGPRDRF/ECLL10 11,
SGPRDRF/ECLL09 11, SGPRDRF/ECLL08 11, SGPRDRF/ECLL07 11,
SGPRDRF/ECLL06 17,
SGPRDRF/ECLL04 11, SGPRDRF/ECLL03 11, SGPRDRF/ECLL02 17,
SGPRDRF/ECLL01 11.
The reason codes and their meanings follow: 1 - Access
path was not in a
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9/28/00 Display Data Base Relations
DSPDBR Command Input
File . . . . . . . . . . . . . . . . . . . : FILE ECL
Library . . . . . . . . . . . . . . . . . : *LIBL
Member . . . . . . . . . . . . . . . . . . : MBR *NONE
Record format . . . . . . . . . . . . . . . : RCDFMT *NONE
Output . . . . . . . . . . . . . . . . . . : OUTPUT *
Specifications
Type of file . . . . . . . . . . . . . . . : Physical
File . . . . . . . . . . . . . . . . . . . : ECL
Library . . . . . . . . . . . . . . . . . : SGPRDRF
Member . . . . . . . . . . . . . . . . . : *NONE
Record format . . . . . . . . . . . . . . : *NONE
Number of dependent files . . . . . . . . : 31
<<<<<-------------!!!!
Files Dependent On Specified File
Dependent File Library Dependency JREF Constraint
ECLL99 SGPRDRF Data
ECLL07 SGPRDRF Data
ECLL18 SGPRDRF Data
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>You never really addressed the lack of intuitive logical files story on work
>>files. I understand the _THEORY_ -- work files should have few records and
>>thus should not require optimization. However, at the few full-client
>>accounts at which I've worked, the records in ECHW have tended to remain
>>above the 10K level. No programs to clear these out, no logicals to optimize
>>performance. What's up with _THAT_?
I was only saying 'be cautious' because things are not always that simple, I
hope I don't sound like I am some kind of sworn defender of BPCS who always says
it is a perfect software and there is nothing to improve or change anymore, if
any logical files you create result in improving performance, it is definitely
ok thing to do :-)
Also, I wouldn't be that concerned about files of size 10K or so, this should be
nothing for today's as/400s and should not create a significant performance
overhead. The easiest way to test that is by running DBMON and check for the
time system spent to execute specific SQL statement. Also, another simple way of
checking how useful some logical file would be (assuming that it is the one
picked by optimizer in the actual query implementation), is to delete it and
then see how much time will take to optimizer to build temporary one (message
CPI4321 in the debug joblog) instead.
Regards,
Goran
+---
| 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
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
