Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to know the no of Rows affected with CURSOR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vamseepotti

New User


Joined: 21 Oct 2003
Posts: 45

PostPosted: Thu Mar 04, 2004 9:50 am    Post subject: How to know the no of Rows affected with CURSOR
Reply with quote

Imagine your CURSOR updated 200 rows and COMMIT takes place. Then abend occurred at 201?215. How u know the number of rows updated by ur cursor and where abend occurred. Now, how u modify ur program which donot update the first 200 rows?
Back to top
View user's profile Send private message

mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Fri Mar 12, 2004 7:52 pm    Post subject:
Reply with quote

Hello vamseepotti,

Code the cursor as below:

Code:

DECLARE MYCURSOR CURSOR FOR
SELECT COLUMN1, COLUMN2
FROM MYTABLE
WHERE MYKEY > :WS-MY-KEY



Initially move LOW-VALUES to WS-MY-KEY.

After every commit, save the key in check point area.

During restart, move the saved key value to WS-MY-KEY and after the open and fetch of the cursor you will have the next record.

Hope this helps,

Regards
Mayuresh Tendulkar
Back to top
View user's profile Send private message
sosu4u

New User


Joined: 02 Nov 2007
Posts: 2
Location: bangalore

PostPosted: Mon Jan 11, 2010 3:44 pm    Post subject: Reply to: How to know the no of Rows affected with CURSOR
Reply with quote

we will know how many records updated or deleted from sqlerrd(3). by checking value in sqlerrd (3) we can start retrieving data from that position.

i think it may help for you.

regards,
suresh
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Jan 11, 2010 4:03 pm    Post subject:
Reply with quote

suresh, you are correct. to keep count use sqlerrd(3).

but I think Mayuresh has a better method of restart,
although,
I would
  • keep the checkpoint area in a db2 table
  • after the cursor update, I would move the last key updated to a db2 checkpoint table and then commit
.

at end of successful job, I would move a low-key to the checkpoint row, update and commit.

to restart, I would first read my checkpoint row and use the last saved key.

that way at the start of a job, the last saved key would be
  • low if last run of program was successful
  • last commited key after an abended run.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2442
Location: Netherlands, Amstelveen

PostPosted: Mon Jan 11, 2010 4:12 pm    Post subject:
Reply with quote

After 6 years this is still not solved?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Jan 11, 2010 5:10 pm    Post subject:
Reply with quote

but you are (were) the last poster to this thread!
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon Jan 11, 2010 7:32 pm    Post subject:
Reply with quote

I hope the original poster has not been sitting at his/her terminal all these years waiting for an answer. icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us