RE: [sqr-users] SQR run in DB2 with commits in the code

["Tritt, Merrill" <MerrillTritt@spherion.com>]

Ashish, it would probably go something like this:

begin-procedure myprocedure
let $OldEmplid = ''
let #counter = 0

begin-select
pd.emplid
pd.name
pd.postal
from ps_personal_data pd

 do procedure-that-requires-database-insert-update-delete

 let $OldEmplid = &pd.emplid

 let #counter = #counter + 1
        if #counter = 500          !Change the increment to suit your needs
                let #counter = 0
                commit
        end-if

 where pd.emplid > rtrim($OldEmplid,' ')
 order by pd.emplid

end-procedure myprocedure


Merrill




-----Original Message-----
From: Ashish Bhatt [mailto:ashish_bhatt@hotmail.com]
Sent: Thursday, February 26, 2004 11:49 AM
To: Tritt, Merrill
Subject: RE: [sqr-users] SQR run in DB2 with commits in the code


Tritt, thanks for your reply.

Have you done this before? Do you have some sample code?

Please reply.

Thanks in advance.

Have a nice day and great week.

Regards.

Ashish Bhatt.
716 841 4349


>From: "Tritt, Merrill" <MerrillTritt@spherion.com>
>Reply-To: sqr-users@sqrug.org
>To: <sqr-users@sqrug.org>
>Subject: RE: [sqr-users] SQR run in DB2 with commits in the code
>Date: Thu, 26 Feb 2004 11:12:46 -0500
>
>Issuing a COMMIT on a DB2 database will reset the cursor and cause the 
>program to loop.  The only workaround would be to look at what you're doing 
>and possibly code that into the select along with a counter to issue the 
>COMMIT at a specific interval.  For instance, if you're inserting rows into 
>another table, maybe put a NOT EXISTS in your select.  This way, only those 
>rows that did not get processed before the COMMIT was issued will get 
>selected in subsequent cursor resets.  Sooner or later, you'll run down to 
>0 rows selected and you'll fall through the procedure.
>
>Merrill
>
>
>-----Original Message-----
>From: Ashish Bhatt [mailto:ashish_bhatt@hotmail.com]
>Sent: Thursday, February 26, 2004 9:24 AM
>To: sqr-users@sqrug.org
>Subject: [sqr-users] SQR run in DB2 with commits in the code
>
>
>Hi SQR Gurus,
>
>I hope you are doing fine today.
>
>Has anyone of you have run SQR on DB2 database with the commit at regular
>interval in the SELECT in the SQR code?
>
>We are facing serious infinite looping issue.
>
>Please reply.
>
>Have a nice day and great week.
>
>Regards.
>
>Ashish Bhatt.
>716 841 4349 (Work)
>
>_________________________________________________________________
>Take a break! Find destinations on MSN Travel. 
>http://www.msn.com.sg/travel/
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users

_________________________________________________________________
Get 10mb of inbox space with MSN Hotmail Extra Storage 
http://join.msn.com/?pgmarket=en-sg



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users