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 use update lock in a query?

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

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Thu Jun 22, 2006 12:35 pm    Post subject: How to use update lock in a query?
Reply with quote

Can you please tell me how to use an update lock in a query while selecting from the table.

The query is as below :



EXEC SQL



SELECT SEC_ID_SEED

INTO

:TZWSIDS.SEC-ID-SEED

FROM

TZWSIDS_SECID_SEED



END-EXEC
Back to top
View user's profile Send private message

rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Thu Jun 22, 2006 2:17 pm    Post subject:
Reply with quote

Geeta,

Please be more specific , you mean updating column..........or talking you are talking about the locks held by the db2 during updating......

regards
jai
Back to top
View user's profile Send private message
geeta.mishra

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Thu Jun 22, 2006 5:01 pm    Post subject: Re: How to use update lock in a query?
Reply with quote

Actually I have to lock the table for update.

The scenario is: select a value from the table, and then update this value with the new value.
During the period of select to update I want to put a lock on the table so that over the time no other user can go and update the table.

In what way I can achieve this?
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Thu Jun 22, 2006 5:09 pm    Post subject:
Reply with quote

Geeta,

When you are updating the row in your code, db2 system automatically takes cares of locking . (Depends During bind what type of isolation level you have opted).

Please let me know further info....

Regards
jai
Back to top
View user's profile Send private message
prashantshinde

New User


Joined: 25 Nov 2005
Posts: 44
Location: Pune

PostPosted: Thu Jun 22, 2006 5:25 pm    Post subject:
Reply with quote

I think with Isolation level CS (which is normally used ) the Lock for update by other users is already present ...so check ur isolatin level ..

any ways u can change the isolation level and also invoke diff level lock by writing some sql queries.

Thanks ,
Prashant
Back to top
View user's profile Send private message
geeta.mishra

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Fri Jun 23, 2006 5:25 pm    Post subject:
Reply with quote

Thanks for the information!

I have heard from somewhere that by using 'FOR UPDATE' in a SELECT query itself we can lock the table for update.I tried this with the above query but got a precompilation error.
Do you have any idea about this?
If so,what is the correct syntax for this?


Geeta
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Fri Jun 23, 2006 5:48 pm    Post subject:
Reply with quote

Geeta,

You have got the correct info....

please find the below example.......

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 DSN8710.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8710.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE OF SALARY;

Regards,
jai
Back to top
View user's profile Send private message
prashantshinde

New User


Joined: 25 Nov 2005
Posts: 44
Location: Pune

PostPosted: Fri Jun 23, 2006 6:13 pm    Post subject: Re: How to use update lock in a query?
Reply with quote

Yes FOR UPDATE can be used.

I think FOR UPDATE is used in Cobol-DB2-CICS program , where it is meant for updating or deleting records
I don't have code now..

Correct me if i am wrong!
Back to top
View user's profile Send private message
geeta.mishra

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Fri Jun 23, 2006 6:53 pm    Post subject:
Reply with quote

I haven't declared any cursor in the program(CICS-COBOL-DB2 program).I am using the following query:

EXEC SQL

SELECT SEC_ID_SEED

INTO

:TZWSIDS.SEC-ID-SEED

FROM

TZWSIDS_SECID_SEED

FOR UPDATE OF SEC_ID_SEED

END-EXEC

This query I tried executing with both 'FOR UPDATE' and 'FOR UPDATE OF SEC_ID_SEED' but again getting MXXCC=12.

IS anything wrong with this query?
DO I need to use the cursor to fulfil my purpose?
Back to top
View user's profile Send private message
prashantshinde

New User


Joined: 25 Nov 2005
Posts: 44
Location: Pune

PostPosted: Fri Jun 23, 2006 7:22 pm    Post subject:
Reply with quote

if ur updating single record ..no need to use curssor
but for updating more records use cursor and while defining the cursor give FOR UPDATE ..then select records..without mentioning for update in select statement (as u did in above code)
i think u know code for defining cusor is
just at end and befor end exec mention FOR UPDATE .

try it!
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Sat Jun 24, 2006 12:03 am    Post subject:
Reply with quote

Geetha,

I am surprised to see that you are selecting a column from a table into the host variable without coding a predicate without WHERE CLAUSE?? IF u have delibrately avoided coding predicates, then you shoud think abt cursors.

Anamika
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Sat Jun 24, 2006 12:11 am    Post subject:
Reply with quote

I would like to add that if you do not want to use the "for update" clause in your select statement. Then in your program you can issue the LOCK TABLE sql statment
EXEC SQL
LOCK TABLE TZWSIDS_SECID_SEED
IN EXCLSIVE MODE
END- EXEC
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 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