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
 
DB2 - Update a table inside a Cursor

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

New User


Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India

PostPosted: Tue May 26, 2009 11:48 am    Post subject: DB2 - Update a table inside a Cursor
Reply with quote

Assume that i have a table
DEV.EMPLOYEE TABLE which contains
GEO_CODE CHAR(2)
COUNTRY_CODE CHAR(3)
EMP_NO CHAR(7)
EMP_NAME CHAR(40)
SALARY DEC(15,2)

DECLARE C1 CURSOR FOR SELECT GEO_CODE, COUNTRY, EMP_NO, EMP_NAME, SALARY FROM DEV.EMPLOYEE WHERE GEO_CODE = 'AP'

OPEN C1

FETCH C1

Then i am doing following Update inside the CURSOR loop

UPDATE DEV.EMPLOYEE SET SALARY = SALARY + 100 where COUNTRY = '744';

Now my question is
when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area?
can i update/delete the same table which are declared in the cursor inside the FETCH loop
after the UPDATE or DELETE inside the fetch operation where will be cursor pointer

icon_rolleyes.gif
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 26, 2009 1:32 pm    Post subject:
Reply with quote

Quote:

when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area?


Its not physical transfer ....

Quote:

can i update/delete the same table which are declared in the cursor inside the FETCH loop

Absolutely YES ...

Quote:

after the UPDATE or DELETE inside the fetch operation where will be cursor pointer

The current row which was fetched ....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue May 26, 2009 4:36 pm    Post subject:
Reply with quote

are you issuing any COMMIT's during this process.
How many times are you executing the SQL to update where COUNTRY = '744'?
Quote:
when we declare the cursor and open it, does the records qualified for the cursor are transferred to temp memory area?

A simple answer to your question will not make you conversant with the technical details.
Suggest that you read the Application Programmers SQL Guide for your site's version of DB2; there is a very good discussion about cursors.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10310
Location: italy

PostPosted: Tue May 26, 2009 4:40 pm    Post subject: Reply to: DB2 - Update a table inside a Cursor
Reply with quote

Quote:
UPDATE DEV.EMPLOYEE SET SALARY = SALARY + 100 where COUNTRY = '744';



I do not see anywhere the update/query related to the cursor processing

for employees with country code 744 the salary will be raised by ( 100 * number of iterations )

It would be wise to provide better details to clarify the sequence of fetch/updates/.....
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 ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm

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