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

Question about FOR UPDATE OF & WHERE CURRENT OF


IBM Mainframe Forums -> FAQ & Basics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vk_technology
Currently Banned

New User


Joined: 29 Dec 2006
Posts: 1
Location: Pune

PostPosted: Tue Jan 02, 2007 6:30 pm
Reply with quote

why we need both "FOR UPDATE OF" and "WHERE CURRENT OF" clause?

Please give me proper justification.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Tue Jan 02, 2007 6:45 pm
Reply with quote

A little more info would help.....
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Wed Jan 03, 2007 11:46 am
Reply with quote

Hi There,

This is from FM

Quote:
Updating a column: You can update columns in the rows that you retrieve. Updating a row after you use a cursor to retrieve it is called a positioned update. If you intend to perform any positioned updates on the identified table, include the FOR UPDATE clause. The FOR UPDATE clause has two forms:

The first form is FOR UPDATE OF column-list. Use this form when you know in advance which columns you need to update.
The second form is FOR UPDATE, with no column list. Use this form when you might use the cursor to update any of the columns of the table.
For example, you can use this cursor to update only the SALARY column of the employee table:

EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE OF SALARY;
If you might use the cursor to update any column of the employee table, define the cursor like this:

EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE;
DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list. Therefore, if you intend to update only a few columns of a table, your program can run more efficiently if you include a column list.

The precompiler options NOFOR and STDSQL affect the use of the FOR UPDATE clause in static SQL statements. For information about these options, see Table 63. If you do not specify the FOR UPDATE clause in a DECLARE CURSOR statement, and you do not specify the STDSQL(YES) option or the NOFOR precompiler options, you receive an error if you execute a positioned UPDATE statement.

You can update a column of the identified table even though it is not part of the result table. In this case, you do not need to name the column in the SELECT statement. When the cursor retrieves a row (using FETCH) that contains a column value you want to update, you can use UPDATE ... WHERE CURRENT OF to identify the row that is to be updated.
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 -> FAQ & Basics

 


Similar Topics
Topic Forum Replies
No new posts Sort w/OUTREC Question DFSORT/ICETOOL 2
No new posts To get previous month from current ti... SYNCSORT 7
No new posts Write a current record plus previous ... DFSORT/ICETOOL 4
No new posts DB2 SQL query to read and update data... DB2 12
No new posts Assembler test under mask question PL/I & Assembler 5
Search our Forums:

Back to Top