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
 

 

Can we use FOR UPDATE OF clause with Join in Cursor?

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

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Wed Aug 03, 2011 9:39 pm    Post subject: Can we use FOR UPDATE OF clause with Join in Cursor?
Reply with quote

Hi All icon_smile.gif,

Below is the cursor...

Code:

EXEC SQL                                         
   DECLARE CUR1 CURSOR FOR               
   SELECT A.LBS                   
         ,A.FDR                       
         ,A.FTR                       
         ,A.FJN                       
         ,B.PBS                   
         ,B.PFI                         
         ,B.PBN                       
    FROM  TABLE1 A INNER JOIN                 
          TABLE2 B                             
    ON (A.FDR = B.FDR  AND
        A.FTR = B.FTR  AND
        A.FJN = B.FJN  AND
        A.FC  = B.FC   AND
        A.BC  = B.BC)     
 WHERE (B.PBS = '1'    OR 
        B.PBS = '3')               
   FOR UPDATE OF  A.LBS           
                 ,B.PBS           
                 ,B.PFI                 
                 ,B.PBN
END-EXEC.                       


Is the Cursor syntactically correct?

At some point i would like to update field LBS of Table1 and PBS, PFI and PBN of Table 2 like below... Please advise, if they are correct...

Code:

EXEC SQL                 
     UPDATE TABLE1 SET   
            LBS = 'C'     
     WHERE CURRENT OF CUR1
END-EXEC.                 
                         
EXEC SQL                 
     UPDATE TABLE2 SET   
            PBS = :WS-PBS
           ,PFI = :WS-PFI
           ,PBN = :WS-PBN
     WHERE CURRENT OF CUR1
END-EXEC.                 


Thanks!
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Aug 03, 2011 10:35 pm    Post subject:
Reply with quote

well, a quick pre-compile would have told you
exactly what the manual would have told you.

no

a join creates a read-only-cursor (on the mainframe)
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Aug 04, 2011 9:19 am    Post subject:
Reply with quote

Okay Thanks...

Could you please advise, how to achieve that using SQL...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 04, 2011 12:11 pm    Post subject:
Reply with quote

code a singleton update for table1 and a singleton update for table2,
each with a where clause that isolates the exact row for that table (i.e. primary key).

you may have to expand your select in your cursor in order to insure that the required columns are returned in the result set and available for the update where clauses.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Aug 04, 2011 12:33 pm    Post subject:
Reply with quote

Fetch the data from the cursor into host variables then run the update query by specifying the Where conditions.
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Aug 04, 2011 2:29 pm    Post subject:
Reply with quote

Thanks both for the help...

Could you please advise, if we can give "For Fetch Only" Clause in Cursor declartion?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Aug 04, 2011 3:00 pm    Post subject:
Reply with quote

refer this - http://ibmmainframes.com/about31975.html
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Aug 04, 2011 3:42 pm    Post subject:
Reply with quote

gylbharat,

Thanks for the link...

But the reason i asked for is because in Cursor declaration we are joining two tables but down we want to update few columns that are retrieved through Join operation.

Since we can't use "For Update Of" clause in Cursor using Join. Can we use "For Fetch Only" to retrieve rows in bulk and update required columns using singleton update for each table?

Thanks!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 04, 2011 4:42 pm    Post subject:
Reply with quote

Ramanan-R wrote:
gylbharat,

Thanks for the link...

But the reason i asked for is because in Cursor declaration we are joining two tables but down we want to update few columns that are retrieved through Join operation.

Since we can't use "For Update Of" clause in Cursor using Join. Can we use "For Fetch Only" to retrieve rows in bulk and update required columns using singleton update for each table?

Thanks!


though FOR FETCH ONLY explicitly states that it is a read-only cursor,
a cursor containing a join is not an ambiguous cursor,
it is implicitly read-only.

do you mean rowset retrieval? yes.

the singleton updates are predicated on where clause conditions,
which would be controlled thru use of host-variables.
keep in mind a cobol table item either indexed or subscripted
is not a valid host variable.
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Thu Aug 04, 2011 5:23 pm    Post subject:
Reply with quote

Thanks Dick,

I thought if we specify "For Fetch Only" in cursor declaration then we can't update columns using singleton update...

Dick, Is there a difference between "For Read Only" and "For Fetch Only"...

Thanks!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 04, 2011 5:33 pm    Post subject:
Reply with quote

Ramanan-R,

this is not a beginners forum.
members here are expected to at least know the fundamentals and
access and read the manuals.

I don't believe that FOR FETCH ONLY is a valid clause.
FOR READ ONLY is the clause.

not that it matters, you can refer to the manual
- links at top of everypage in this website -

the type of cursor - read-only, update - only affects what sql you can apply against the cursor.
has no effect on the table for sql that is not cursor related.
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


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