Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to control the UPDATE in Cursor?

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

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 09, 2008 11:34 am    Post subject: How to control the UPDATE in Cursor?
Reply with quote

I have two cursors in my program and updating the one table.

The Table structure is like below

TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 H
ARW_X9_T002 000000 H
ARW_X9_T003 000000 H
ARW_X9_T004 000000 A


I decalared Cursor1 like this and fetching only FLAG=A records from the table.

EXEC SQL DECLARE CNTL_TABLE_CSR1 CURSOR WITH HOLD FOR
SELECT TBE_NME,
TBE_DTE,
CFLAG
FROM ARW_CNTL_TABLE
WHERE FLAG = 'A'
FOR UPDATE OF FLAG
END-EXEC.


Then i am update the Flag value with H
Move 'H' to WS-FLAG.

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET FLAG = :WS-FLAG
WHERE CURRENT OF CNTL_TABLE_CSR1
END-EXEC.

Now i am closeing the cursor.

And the second cursor i declared like below and retriving only FLAH = H records,

EXEC SQL DECLARE CNTL_TABLE_CSR2 CURSOR WITH HOLD FOR
SELECT TBE_NME,
TBE_DTE,
FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'H'
FOR UPDATE OF FLAG
END-EXEC.

Then i update the flag value with A
Move 'A' to WS-FLAG.

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET FLAG = :WS-FLAG
WHERE CURRENT OF CNTL_TABLE_CSR2
END-EXEC.


But i don't want to update the FLAG vale changed from 'A' to 'H' at the first cursor,i need only update the remaing records form the table.

The OUTPUT of the Table should be like this

TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 A
ARW_X9_T002 000000 A
ARW_X9_T003 000000 A
ARW_X9_T004 000000 H


But if i execute the program i am getting the OUTPUT like this


TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 A
ARW_X9_T002 000000 A
ARW_X9_T003 000000 A
ARW_X9_T004 000000 A

Please let me know how to control the second cursor UPDATE?
Back to top
View user's profile Send private message

shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Wed Apr 09, 2008 11:40 am    Post subject: Reply to: How to control the UPDATE in Cursor?
Reply with quote

If you have any other primary keys then you can control otherwise its better to Update first A to X and then H to A and again X to H.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Wed Apr 09, 2008 11:51 am    Post subject: Reply to: How to control the UPDATE in Cursor?
Reply with quote

use only one cursor with ...
WHERE FLAG = 'A' or FLAG = 'H'

select also the flag after the fetch check for

if old-flag = " A"
ws_flag = value 1
else if old-flag = "H"
ws-flag = value 2

update blah bllah

I do not speak cobolese, yust translate to the proper cobolese constructs
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 09, 2008 11:58 am    Post subject:
Reply with quote

as per my requirement i need to use two cursors....please give a solution with more scudeo code?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 09, 2008 12:04 pm    Post subject:
Reply with quote

Hello,

What business requirement dictates that 2 cursors must be used?

It is not a good idea to defend an implementation that is flawed when a solution that meets the business requirement is presented.

Why would you not simplify the code and implement as Enrico has suggested?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Wed Apr 09, 2008 12:17 pm    Post subject: Reply to: How to control the UPDATE in Cursor?
Reply with quote

You cannot do it in a simple way with 2 cursors, take it or leave it
or even if it is possible it is not worth the time it takes to think about it

Your choices are ...
1 as I suggested or
3 as shrivatsa suggested
to make things clear to everybody reading the program

unless You want to play around with isolation levels,
but it would take time to test and analyze the consequences and drawbacks
in this case the program behavior would rely on somebody binding the plan/package with the proper isolation level

it is hard to believe that the use of exactly two cursor is an application design requirement
unless this is some kind of exam icon_cool.gif
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Wed Apr 09, 2008 12:36 pm    Post subject: Re: Reply to: How to control the UPDATE in Cursor?
Reply with quote

enrico-sorichetti wrote:
use only one cursor with ...
WHERE FLAG = 'A' or FLAG = 'H'

select also the flag after the fetch check for

if old-flag = " A"
ws_flag = value 1
else if old-flag = "H"
ws-flag = value 2


Better to follow this as suggested by enrico.
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 09, 2008 12:41 pm    Post subject:
Reply with quote

Thanks all of you....as you people suggested i will implement the my requirement with one cursor...if i have any queries i will post with full information.
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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts PA02 - Programmtically control attent... dodithegreat IMS DB/DC 1 Thu Apr 06, 2017 8:33 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us