RE: Updatable Cursor "Hangs" on last fetch

The program is super fast if the cursor is declared as read-only.  It's
when the cursor is "for update" that it grinds to a halt.

STRDBMON doesn't provide any insight.  The last entry in DBMon relates
to the prior statement.

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.

I probably should highlight that all records have been processed, and
the Fetch that "hangs" should return "End of File".

So far there are no deleted records in this table.  

The table contains a Before and After image of a record which is
populated by a trigger when a record is updated.
Here's what I'm doing - retrieve Before Image, loading specific columns
into a DS.  I then read the related After Image into a DS with the same
structure.  I compare the 2 DSs and then delete both the Before/After
records if they are both the same.  I "could" use another SQL statement
and execute a Delete with specific criteria.  I thought this would be
slower as it would mean extra IO over 45million records.


So I guess my next question is:
Why does changing the Cursor from Read Only to Update cause such a
problem on the Fetch at the end of the recordset.  

Any suggestions would be appreciated.

Regards,
Terry


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy
Sent: 19 October 2005 16:36
To: RPG programming on the AS400 / iSeries
Subject: 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.