DB2 Lock in exclusive mode

sudip_mitra · New User Joined: 05 Apr 2006 Posts: 5 Location: India

Hi,

My requirement to hold a lock in table till last step in a JCL -
Step1, I need to lock a table.
Step2, some functionality exist on that table
Step3, other functionality based on updated table
Step4, I need to release the lock.

For locking I can use Exclusive LOCK comand but after program terminate it automatically release the lock due to RELEASE(COMMIT) in bind card.

Please sugest how can I hold lock in a table throughout a job.

Thanks,
Sudip Mitra

dbzTHEdinosauer · Posted: Thu Dec 09, 2010 8:32 pm

DB2 does not recognize a JOB as a task.

so, you are going to have to use the scheduler (Jobs-started tasks) as a means of prohibiting access to the table.

GuyC · Posted: Fri Dec 10, 2010 2:39 pm

you might want to take a look at clone tables.

suppose : Table TB1 in database.Tablespace DB1,TS1

Preparation (one Time):
1) create a clone : ALTER TABLE TB1 ADD CLONE TB1_FOR_BATCH
you can leave it empty.
(your exclusibe batchprocess will have to SELECT/UPDATE/INSERT TB1_FOR_BATCH)
2) Make the clone unavailable : -STOP DATABASE(db1) SPACENAM(ts1) CLONE

now you have TB1 up and running and TB1_FOR_BATCH unavailable.

batch process :
1) EXCHANGE DATA FOR TABLE TB1 and TB1_FOR_BATCH
=> TB1 is unavailable (sort of an exclusive lock) and TB1_FOR_BATCH is available
2) run the programs against TB1_FOR_BATCH
3) EXCHANGE DATA FOR TABLE TB1 and TB1_FOR_BATCH
=> TB1 is back available for normal processing and TB1_FOR_BATCH is unavailable