MVSFORUMS.com

pkarthik@email.com · Posted: Fri May 17, 2019 1:04 pm Post subject: Issue with Table locks

Hi,

I have a table that has 8 million records. We are performing an initial load in a staging table with 30+ columns. we have 5 COBOL DB2 programs that are updating different columns in a row. DBA is suggesting us to add a table lock with commit frequency of 10000 records before performing updates to the table. But my other processes has to wait until table lock was released. Can I reduce the commit frequency and remove the table locks. So all 5 programs can run in parallel because Columns updated by other programs will be different and records also may be same record.
on an average each program will update 500k to 2 million records.

Please suggest. I am hoping that there may be a better solution to my problem.
_________________
For any type of complex problems there will be multiple easiest solutions

kolusu · Posted: Fri May 17, 2019 1:15 pm Post subject:

pkarthik@email.com,

Read this excellent 4 part Craig S. Mullins article DB2 Locking, Part 1: An Overview

part 4 has the description of page locks and row locks
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

pkarthik@email.com · Posted: Fri May 17, 2019 1:58 pm Post subject:

Thanks Kolusu.

I will discuss with my team and DBA to apply page lock or row lock instead of Table lock. Also try to reorder the records to update in different order to avoid contention.

Mullins article was very detail and self explanatory. Thanks once again

Regards,
Karthik P
_________________
For any type of complex problems there will be multiple easiest solutions