RE: Updatable Cursor "Hangs" on last fetch

Thanks for the suggestion.  That was one of my options, and maybe I'll
end up doing it this way.

My question is:  why does changing the cursor from "read-only" to
"updatable" cause the last fetch (which should return "no more records")
to take so long.  
I know the last Fetch will finish.  It finishes processing the recordset
quite quickly, and then it appears to scan through to the end of the
file (45 million records).  However DBMON shows absolutely nothing on
this fetch.
I change the cursor to Read-Only and the last fetch finishes instantly.
Why??

Any ideas?

Regards,
Terry

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bruce Guetzkow
Sent: 19 October 2005 17:49
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: Updatable Cursor "Hangs" on last fetch

Terry:

<snip>
[Original code:]
     C/EXEC SQL Declare CBI  Cursor  For
     C+ select <various fields>
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B'and ab56trgact = 'U'
     C+     For update

[Suggestion from Charles Wilt:]
     C/EXEC SQL
     C+ delete
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B'and ab56trgact = 'U' 
     C/END-EXEC

Some background info:  This program compares a pair of records, checks
if there are the same, and deletes them if they are.  99.9% of records
will be deleted.
</snip>

If the logic is truly just to delete the record if it is also found in
another file, then you could just do:

     C/EXEC SQL
     C+ delete
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B' and ab56trgact = 'U' 
==>  C+          and exists (
==>  C+                select keyfld_b1, keyfld_b2
==>  C+                  from otherfile
==>  C+                 where keyfld_a1 = keyfld_b1
==>  C+                   and keyfld_a2 = keyfld_b2)  (repeat as needed)
     C/END-EXEC

Just a thought,
--Bruce Guetzkow