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
 

 

FOR UPDATE clause in select statement

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

New User


Joined: 05 May 2005
Posts: 30

PostPosted: Thu Jul 21, 2011 10:45 am    Post subject: FOR UPDATE clause in select statement
Reply with quote

Hi,

Could you please tell me, is it possible to use FOR UPDATE clause in single select statement (not in Cursor)?

For: Selete emp-name from Emp-table where Emp-no = 1234 FOR UPDATE;

When I tried to use it in a program I am getting compilation error. Please help me out in this.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Jul 21, 2011 11:01 am    Post subject:
Reply with quote

Yes it is possible to use FOR UPDATE outside the cursor.. provided the select statement fetches only 1 row.

I think compilation error is because of something else... Can you post the exact error...
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: Thu Jul 21, 2011 11:01 am    Post subject:
Reply with quote

Hello,

Are you getting a compilaton error or a db2 pre-compile error?

Which error(s) are being shown?

I suspect part of your problem is the syntax is simply incorrect. Would you not use:
Code:
  UPDATE tbl
  SET whatever
  WHERE. . . .
Back to top
View user's profile Send private message
simha_it

New User


Joined: 05 May 2005
Posts: 30

PostPosted: Thu Jul 21, 2011 12:38 pm    Post subject: Reply to: FOR UPDATE clause
Reply with quote

I am not writing a seperate Update query or not using any cursor. I have a simple select statement in which I want to use FOR UPDATE clause.

What I amtrying to do here is... I am retrieving a single row (say reference number) using select statement from one table (not locking the table here) and assigning that reference num to one customer who read that row recently.
but 2 customers can access the same row (bcoz it is not locked) and same reference number will be assigned to 2 customers. So, to avoid this situation I want to use FOR UPDATE clause in the select stament to lock the table if any customer is accessing that table. I hope it is clear now.
Back to top
View user's profile Send private message
simha_it

New User


Joined: 05 May 2005
Posts: 30

PostPosted: Thu Jul 21, 2011 12:43 pm    Post subject:
Reply with quote

gylbharat wrote:
Yes it is possible to use FOR UPDATE outside the cursor.. provided the select statement fetches only 1 row.

I think compilation error is because of something else... Can you post the exact error...


could you please provide me the syntax how to use FOR UPDATE in simple Select statement.

exact error msg: "FOR UPDATE OF" CLAUSE NOT PERMITTED
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 21, 2011 3:30 pm    Post subject:
Reply with quote

Quote:
could you please provide me the syntax how to use FOR UPDATE in simple Select statement.


you can't. a singleton select is a select.
and update is an update (see post from the Baker St inhabitant for correct syntax).

now, on to your problem:

Quote:
What I amtrying to do here is... I am retrieving a single row (say reference number) using select statement from one table (not locking the table here) and assigning that reference num to one customer who read that row recently.


people have been successfully coding solutions to insure that no two online tasks receive the same number.
if you would take the time to explain what it is that you are trying to do,
(say reference number - does not get it.)
someone can provide a suggestion.

OH, and the next time we ask for the error code, give us the error code,
not the explanation.................
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1232
Location: Israel

PostPosted: Thu Jul 21, 2011 6:31 pm    Post subject:
Reply with quote

gylbharat wrote:
Yes it is possible to use FOR UPDATE outside the cursor.. provided the select statement fetches only 1 row.
Are you sure about that?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Jul 21, 2011 7:03 pm    Post subject:
Reply with quote

Marso wrote:
gylbharat wrote:
Yes it is possible to use FOR UPDATE outside the cursor.. provided the select statement fetches only 1 row.
Are you sure about that?


It is possible when we are running query in QMF. I tried this in cobol program but it failed... So this is not possible in an embedded sql.
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 ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
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 IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


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