RE: SETLL in SQL ?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: BirgittaHauser

(snipping a WHOLE bunch of really great information, because I need to read
it all in more detail)

> > > From my testing, I know that new records will appear in a cursor as they
> are added, so that's not an issue.
>
> That's not always true. SQL tends to create temporary objects, that are
> only actualized if an OPEN statement gets executed.

Interesting.  I did a very simple test with just a declare and an open and
SQL picked up new records.  Take the example below:

-------------
exec sql declare c cursor for
  select alpha from data where alpha = 'a';
exec sql open c; 
dsply ' ' result;
x = -1;
dou sqlcod <> 0; 
  x += 1; 
  exec sql fetch next from c into :myfld; 
enddo; 
exec sql close c; 
dsply (%char(x) + ' records found.'); 
*Inlr = *on; 
-------------

I started the program with one record in the file that matched the WHERE
clause.  When the program stopped at the first DSPLY opcode, I then added a
second record that matched the WHERE clause.  I then resumed the program and
it counted both records.


> But it is possible to force the optimizer to use life data, by either
> compiling a program with embedded SQL with the Option ALWCPY = *NO or
> specifying a cursor as sensitive. SQL performance will be better if the
> optimizer can use temporary objects.

In my example above I left all the defaults on the CRTSQLRPGI command, which
included ALWCPY(*OPTIMIZE).  I suppose that maybe it worked fine in this
case because my selection was so simple, but had I done something a bit more
complex it might not have worked.

Joe