On 5/25/2010 3:01 PM, Luis Rodriguez wrote:
Buck,
My apologies, Did not want to be facetious (or insulting in any way).
My iSeries Nav version is a little outdated (V5R4) and the darn thing is
in Spanish but, for the complicated icon a loose translation of the help
text states that:
"The *complicated Expression* icon represents a derivation formed by various
operands and operations. For example: 2*(3+5)/4"
Hope this helps and, yes, I agree that it is a funny thing to find on a VE
diagram (which, by itself, can be very complicated, too!).
No offence taken my friend! I saw your emoticon and thought I'd be
funny too. Crossing the language barrier isn't easy when both speak the
same native tongue, it's even harder when one they don't.
For the record, as near as I can tell, this particular complicated icon
comes from a UDF. The UDF is called FULLNAME and it returns a varchar
(full name) when given the person's title (Mr, Mrs, etc), first, middle,
last names and pedigree (Jr, Sr, etc). The parameters to the UDF are
also varchar; the columns being passed to it are not. The parameters to
the UDF accept the NULL value; the database columns are NOT NULL CAPABLE.
I'm doing: SELECT ..., FULLNAME('', firstname, middleinitial, lastname,
'') as personname...
and the optimiser is seeing that as:
SELECT FULLNAME(0:(Char Cast(' ', Char Var Len, max = 1 NOT NULL
Capable. ) AS Char Var Len, max = 10 Is NULL Capable. ),(Char
Cast(PERSON_4.FIRSTNAME, Char NOT NULL Capable. ) AS Char Is NULL
Capable. ),(Char Cast(PERSON_4.MIDDLEINITIAL, Char NOT NULL Capable. )
AS Char Is NULL Capable. ),(Char Cast(PERSON_4.LASTNAME, Char NOT NULL
Capable. ) AS Char Is NULL Capable. ),(Char Cast(' ', Char Var Len, max
= 1 NOT NULL Capable. ) AS Char Var Len, max = 10 Is NULL Capable. )) FROM
I can see why this seems complicated. On a deliberately positive note,
clicking the icon displayed enough of the inferred statement text that I
can track down what it's about. That's very nice. I was thinking of
FULLNAME as being tied to a particular table - after all, the name is
only stored in one place - but having that lone 'complicated' icon off
on a separate branch clarifies how the optimiser sees it. Exactly the
job of a visual explain.
As far as the help not working, I'm used to it. After a while, say half
a day of working with the SQL Script window, the help just stops
responding. Alt-tab doesn't take me to it; closing or minimising
windows one at a time doesn't reveal it. No idea what that's about, I
don't use the help often enough in a day to know exactly what breaks it.
Every app these days seems to use a new javaw.exe, and a new demand on
memory. I think that's just the nature of modern PC software.
I'm going to go spend some time with the DB2 performance manual. The
last time I looked at that, it still said 'AS/400' on the cover!
Thanks everybody for all the help and suggestions!
--buck
midrange.com
