Updatable Cursor "Hangs" on last fetch

["Beeson, Terry" <Terry.Beeson@xxxxxxxxxxxxxxxxx>]

Hi All

 

I am writing a purge program that deletes most of the records in table
HS0101, which has 45 million records.  

 

I have defined a cursor as follows

     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

 

I ran it over a subset of 4500 records.  When it got to the end of the
recordset, the FETCH below "hangs".  The jobs CPU is increasing but not
the IO Count.  

     c/exec sql

     c+ fetch from CBI into :BIHS0101

     c/end-exec

 

If I try this on a much smaller size table with only 100 records and I
have no problems.  Also if I change the Cursor to be Read Only, again I
don't have any problems.  This leads me to believe that maybe there's
locking issues... or something.

 

The program was compiled with Commitment Control *NONE.  Full compile
command below

CRTSQLRPGI OBJ(TBEESON/CMPHS0101) +            

             SRCFILE(TBEESON/QRPGLESRC) +      

             SRCMBR(CMPHS0101) COMMIT(*NONE) + 

             CLOSQLCSR(*ENDMOD) DLYPRP(*YES) + 

             DBGVIEW(*SOURCE)                  

 

 

Any ideas would be appreciated.

 

Regards,

Terry