View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
rajandhla
Active User
Joined: 18 Oct 2005 Posts: 182 Location: Luton UK
|
|
|
|
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 |
|
|
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? |
|
Back to top |
|
|
rajandhla
Active User
Joined: 18 Oct 2005 Posts: 182 Location: Luton UK
|
|
|
|
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 |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
rajandhla
Active User
Joined: 18 Oct 2005 Posts: 182 Location: Luton UK
|
|
|
|
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 |
|
|
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! |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
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! |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
|