IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

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


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
Reply with quote

refer this - 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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts To search DB2 table based on Conditio... DB2 1
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Join files where value in one is betw... DFSORT/ICETOOL 6
Search our Forums:

Back to Top