RE: Updatable Cursor "Hangs" on last fetch

If there is not a view over the table that might give you a significant
boost. I rarely use selection on views (unless it's a commonly used
selection, i.e. if there are a lot of processes that use "AB56TRGBA =
'B'" then I might).  Otherwise I generally would have a view using the
selection fields defined as order by values.

GL ;-)


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Beeson, Terry
Sent: Wednesday, October 19, 2005 10:23 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Updatable Cursor "Hangs" on last fetch

Hi Tommy

The code after the fetch is 
     EndDo;   // before Image loop
Which then goes back to 
     Dow sqlcod = 00000;

I put this in debug and it actually stayed at the "Fetch" option.  Since
posting I've been doing some more tests and found that if I selected
records at the very end of the file - it takes a while to get there but
once it finds the records, it then finishes very quickly.  I'm beginning
to think it might be an index issue so STRDBMON is my next port of call.

Regards,
Terry

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy
Sent: 19 October 2005 16:07
To: RPG programming on the AS400 / iSeries
Subject: RE: Updatable Cursor "Hangs" on last fetch

What is the code after the fetch??   


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces+tommy.holden=hcahealthcare.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+tommy.holden=hcahealthcare.com@xxxxxxxxxxxx] On
Behalf Of Beeson, Terry
Sent: Wednesday, October 19, 2005 10: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

 

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.