RE: SQL Cursor Questions

Nelson,

>I have a couple of questions regarding SQL Cursors.
>
>First, I'm dynamically building a select statement to fetch multiple
records
>from a keyed logical file without an Order by clause.  I'm using the
>Prepare, Declare, Open, Fetch, Close method.  The cursor is positioned
>correctly and returns the correct number of records.  However, even though
>I'm selecting records from a keyed logical file, the records are ordered by
>the physical file's RRN order.  In other words, the access path associated
>with the logical file is not used.  When I execute the very same statement
>interactively, it uses the access path of the logical.  The only difference
>is the cursor is not used in the interactive version.  Is this normal
>operation for cursors or am I doing something wrong? I know I can use an
>Order by clause to put the records in the same order as the logical is
>keyed, but why doesn't it use the logical's access path in the first place?

Because you told it not to.  The invariant rule is: if you want the records
sorted, use an ORDER BY, or a K on the F specs or KFLD on OPNQRYF or FNC on
a sort spec (you get the idea).  If you don't specifically ask for the
records to be in a particular order, you will (probably) get arrival
sequence (no guarantee.)  If you got the records in the logical sequence
interactively, I'd say it's because the optimiser chose that sequence as
"least cost" - probably because it's trying to optimise for fastest display,
not minimum I/O.  That's a wild guess on my part.  Bottom line: no ORDER BY
= no particular order.

>Second, if I use a where clause to set the location of my cursor, I am
also,
>in effect, subsetting the list to that same where clause and all my fetches
>are limited to that subset.  What I really want to do is set the cursor to
a
>particular record, but then be able to fetch records without regard to any
>where clause.  In other words, I want to do a SETLL with a key, and then
>READ or READP without a key.  Is there any way to do this? (besides using
>RPG, of course ;-)

Got me stumped.  The primary use of WHERE is not to position the cursor;
it's to subset records.  What's the business problem?  It's early and I
can't figure out why I'd want to position to customers who's names begin
with "J" and then read the K's and L's.  Why wouldn't I do a re-position
with another WHERE when I want to look for "KING"?  I come from a "machine
is too darned small for the workload" shop and am stuck with the idea that
unnecessary I/O is a Bad Thing.

Buck Calabro
Aptis; Albany, NY
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---