RE: About BPCS V6.1.01 MM

Dean,
The Optimiser logic changes with each release of OS/400, and the 'last
created' thing went a few releases ago, however BPCS customers may have
almost any release out there in the field! But Goran is right, there are
many many variables involved. On the workfiles issue, I would say ECHW etc
need to be cleared nightly anyway....
cheers,
Clare

-----Original Message-----
From: DAsmussen@aol.com [mailto:DAsmussen@aol.com]
Sent: 28 September 2000 04:56
To: BPCS-L@midrange.com
Subject: Re: About BPCS V6.1.01 MM


Goran,

In a message dated 9/27/00 12:54:40 PM Eastern Daylight Time, 
gmihajlo@ssax.com writes:

> Dean,
>  I have to correct you here in one thing, but more to add something else
to 
the
>  intiutive logicals story, which would, I hope, explain why SSA is not 
always
>  doing something our clients expect us to do :-)
>  
>  SQL optimizer does not evaluate access paths based on their creation
date, 
it
>  does that based on a huge number of factors, primarily its estimate of
time
>  needed to access specific set of data i.e. records, which is again based 
on many
>  factors including size of the file, degree of file size change recently, 
current
>  key range estimates, i.e. the number of records having key values equal
to 
'A',
>  'B', etc., current statistical information about the number of logical 
reads of
>  each record from the file, maximum size of the access path (remember
1TByte
>  limit requirement!), whether the access path has been created for a 
logical file
>  or for an SQL index, size of memory pools and main memory in total,
system
>  configuration, CPU size, i.e. CPW value, time needed to build an index 
from the
>  existing index if applicable in this specific implementation of the 
analyzed SQL
>  statement and many, many more.
<<many good things snipped>>

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

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_?

Regards,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-mail:  DAsmussen@aol.com

"Everywhere is walking distance, if you have the time." -- Steven Wright
+---
| 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
+---