RE: [sqr-users] SQR Commit problem with DB2

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

You can't use a COMMIT in an SQR on DB2.  It will reset the cursor and loop.  I 
posted a workaround here (essentially make it restartable):

http://www.sqrug.org/sqr-users/sqrusers_04q1/msg00346.html


Please let me know if it helps.


Merrill




-----Original Message-----
From: Ben L Stevens [mailto:ps_benj@yahoo.com]
Sent: Wednesday, September 29, 2004 10:52 AM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: [sqr-users] SQR Commit problem with DB2


SQR commit problem with DB2 

PeopleTools 8.45.03


I have an SQR with the following logic: 

Process-Main

begin-select 

TABLE1.FIELD1
TABLE1.FIELD2
TABLE1.FIELD3
....

        DO Calculate-some-variables
        Do insert-a-row-in-another-table

        let #rcounter = #rcounter + 1 
        if #rcounter > 999
                commit (or do commit-transaction)
        end-if 

from TABLE1 

end-select



I have to process over 2MM rows from the select.  

The problem is, after the COMMIT, the select SQL starts over at row 1. 

I've tried placing the commit in the insert-a-row-in-another-table
procedure, same problem.

I'm using DB2 8.1 FP4 on AIX 64

Suggestions welcome, thanks.





_______________________________________________
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