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

["Kent Schaetzly" <KSchaetzly@eprod.com>]

Its been a while, but if memory serves me DB2 closes open cursors on explicit 
commit.  You could try something like where table1.field1 > v_hold_field1, if 
#rcounter > 999 let v_hold_field1 = &field1, include an order by field1 and 
your select should pick up where it left off.  It is Q&D, there are probably 
more eloquent solutions, maybe someone knows a DB setting that can be changed 
within the SQR. ?

-----Original Message-----
From: sqr-users-bounces+kschaetzly=eprod.com@sqrug.org
[mailto:sqr-users-bounces+kschaetzly=eprod.com@sqrug.org]On Behalf Of
Ben L Stevens
Sent: Wednesday, September 29, 2004 9: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