MVSFORUMS.com

[eshwar]

i need to handle locking of the table exclusively in cics program while reading table.

i know two ways to do this .

1.
we can use RR isolation instead of CS isolation level but this is not prfarable as it raises performence issue since it locks all pages .
2.
we can use lock table statement in program but this also not preferable as it raises performence issue since it locks entire table space

can any body suggest me to do in better way so that there will not be any performence issue.

Regards,
Eshwar.

[Terry_Heinze]

I don't understand. You want to "lock the table exclusively", but don't want your locking to have performance issues. That seems contradictory. What do you want to lock? The table? The page? The row you're reading? If you are doing "read only", do a SELECT with the FOR READ ONLY clause and use CS and do not specify WITH UR.
_________________
....Terry

[dbzTHEdinosauer]

as terry said, why are you worried about locking a table?

first, why is this true:

[eshwar]

i have a situation where ID gets created by adding 1 to the max of (ID) of table. when two users creating ID at the same time will get the same ID .since two parallel cics sessions read the same table to get the max of (id) from the table . obviously this should not happen thats why i would like to have lock for the row which has been read so that the other user who is creating at the same will not be able to access the same row of the table. hope u got my requirement now. can u pls suggest is there any way to have row level locking of table in program itself.

[dbzTHEdinosauer]

you can specify row level locking during the table create.

again, program initiated locking (explicitly executing 'lock' sql) is not the way to go.

you should also look into identity columns.

or a loop:
do until sqlcode = 0
select maxid
update maxid where maxid = prev-maxid
endloop

though this code is a primative solution, you have a situation where the fault lies in the design.
_________________
Dick Brenholtz
American living in Varel, Germany

[eshwar]

this situation can be handled using ENQ ., it wil lock the resource which is given in ENQ command .this we need to code just before getting max id of the table .once the insert is successful on a table we can release the reource using DEQ.

[RMi]

Eshwar,

I have a question here on the ENQ. Does ENQ work across CICS regions? You stated that your concern is to avoid two different CICS regions getting the same value through the MAX function.

[dbzTHEdinosauer]

you could use a cursor for update, which would 'lock' your row until the update current cursor and commit (or end of task).

the cursor would effectively enq your row, thus saving you the additional code of deq.

since the cursor lock on the row is db2, it would therefore be effective in any environemnt (batch contention, cross region, etc...)

but you have not responded this week, assume you have your solution.
_________________
Dick Brenholtz
American living in Varel, Germany

[videlord]