RE: Updatable Cursor "Hangs" on last fetch

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Terry,

Looks like you're getting somewhere in your original program.

Sounds like it may be doing a table scan and there's a lot of deleted
records already in the table.  That would lead to the fetch taking a
long time to return.

However, I wanted to point out that what you are doing is not the best
way to handle this.

Why are you using a cursor at all?

Simply issue:
     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

If you're look at other fields, in the RPG to determine if you want to
delete or not, simply and that criteria to your WHERE clause.


HTH, 

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx] On Behalf Of
Beeson, Terry
Sent: Wednesday, October 19, 2005 11:04 AM
To: RPG programming on the AS400 / iSeries
Subject: Updatable Cursor "Hangs" on last fetch

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