RE: SQL scroll cursor slow performance

> -----Original Message-----
> From: midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx
> [mailto:midrange-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx]On 
> Behalf Of
> Joe Pluta
> Sent: Friday, June 24, 2005 11:17 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL scroll cursor slow performance
> 
> 
> Another issue comes to mind as I watch Darren slowly work towards a
> solution.  Take a look at the fragment of his SQL I copied below:
> 
> >      C+        ifnull(min(FKENGD),' ') as EDSC,
> >      C+        ifnull(min(FKSPND),' ') as SDSC,
> >      C+        ifnull(min(F5COO),' ') as F5COO,
> >      C+        ifnull(min(F2TOUM),' ') as F2TOUM,
> >      C+        ifnull(min(HARPMT),' ') as HARPMT,
> >      C+        ifnull(min(F5SCST),0), ifnull(min(F2FACT),1),
> 
> Note all the IFNULLs?  I know you can declare a column that doesn't
> allow NULL values (NOT NULL CAPABLE).  It would seem to me 
> that, except
> in very rare circumstances, you would declare fields this way 
> and use an
> old-fashioned "no value" value.  That would avoid a lot of the IFNULL
> claptrap you see in SQL code.

I agree that not all fields need/should be null capable.

For example, a COMMENT field.  What advantage could a NULL have here that 
*BLANKS wouldn't?

On the other hand, NULL is important when dealing with constraints.  You might 
not need a value in a particular field, but if it is there, then it better 
match one of the valid values from this other file.

As I mentioned in my other post.  I don't clutter up the "logic" with the 
IFNULLs.  Instead of a huge embedded SQL statement with the IFNULLs, I'd have 
the big statement in it's own UDF table function without the IFNULLs.  Then 
embedded statement simply becomes:

c+    select 
c+      field1, 
c+      ifnull(field2,' ') 
c+      ifnull(field3, 0)
c+      ifnull(field4, ' ')
c+      field5
c+    from table(MyWhateverFunction())


You could actually leave off the ifnulls if you want and either handle the 
NULLs in your program or, I believe, get the equivalent of the "replace NULL by 
default" that everybody's asking for.  Simply fetch into an 
initialized/cleared/reset data structure.  IIRC, a NULL in the results set 
causes SQL not to update the host variable that a non-null value would have 
been placed into.

The question is, is such behavior guaranteed by IBM?  Or is that just the way 
it happens to work now?

If not guaranteed, I'd think hard before using in even a quick & dirty report.  
What are the chances we could get IBM to guaranteed it?  It seems as if it 
could be considered a logical run-time behavior similar to short-circuit 
evaluation.


HTH,
Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121