|
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.