RE: Updatable Cursor "Hangs" on last fetch

Hi Terry,

If 99.9% of the records are going to be deleted from a file with 45M
records, it would seem faster to copy the records you intend to keep into a
temp file, do a CLRPFM, then copy them back.

Peter Dow
Dow Software Services, Inc.
www.dowsoftware.com
909 793-9050 voice
909 793-4480 fax

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Bruce Guetzkow
> Sent: Wednesday, October 19, 2005 9:49 AM
> 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
>
>
> --
> 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.
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.12.4/143 - Release Date: 10/19/2005
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/143 - Release Date: 10/19/2005