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

["Hins, Thomas D. \(HSC\)" <Thomas-Hins@ouhsc.edu>]

Here is some info that may help?
When DB2 commits or rolls back a transaction, it automatically closes all DB2 
cursors and removes any prepared statements. Prolifics for DB2 has modified 
this behavior for a DBMS COMMIT statement by declaring all select cursors using 
the WITH HOLD clause. Therefore, fetches can be continued after DBMS COMMIT. To 
begin the fetch after DBMS ROLLBACK, the application must simply re-execute the 
cursor using DBMS EXECUTE; it is not necessary to re-declare the Prolifics 
cursor. 


Tom Hins
Information Technology - Application Solutions
University of Oklahoma Health Sciences Center
P.O. Box 26901
Oklahoma City, OK  73190
Phone:(405) 271-2262 Ext. 50213
Fax: (405) 271-2352
(The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. If you 
are not the intended recipient of this message you are hereby notified that any 
use, review, retransmission, dissemination, distribution, reproduction or any 
action taken in reliance upon this message is prohibited. If you received this 
in error, please contact the sender and delete the material from any computer. 
Any views expressed in this message are those of the individual sender and may 
not necessarily reflect the views of the company.)



-----Original Message-----
From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org
[mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of
Ben L Stevens
Sent: Wednesday, September 29, 2004 10:08 AM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: [sqr-users] SQR Commit problem with DB2



<resent with a little correction>

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)
                let #rcounter = 0
        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

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