How to use update lock in a query?

geeta.mishra · New User Joined: 27 Dec 2005 Posts: 21 Location: Bangalore

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

rajandhla · Posted: Thu Jun 22, 2006 2:17 pm

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

geeta.mishra · New User Joined: 27 Dec 2005 Posts: 21 Location: Bangalore

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?

rajandhla · Posted: Thu Jun 22, 2006 5:09 pm

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

prashantshinde · New User Joined: 25 Nov 2005 Posts: 49 Location: Pune

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

geeta.mishra · New User Joined: 27 Dec 2005 Posts: 21 Location: Bangalore

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

rajandhla · Posted: Fri Jun 23, 2006 5:48 pm

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

prashantshinde · New User Joined: 25 Nov 2005 Posts: 49 Location: Pune

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!

geeta.mishra · New User Joined: 27 Dec 2005 Posts: 21 Location: Bangalore

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?

prashantshinde · New User Joined: 25 Nov 2005 Posts: 49 Location: Pune

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!

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

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

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

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