MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Issue with Table locks

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
pkarthik@email.com
Beginner


Joined: 29 Mar 2005
Posts: 34
Topics: 18
Location: Canada

PostPosted: Fri May 17, 2019 1:04 pm    Post subject: Issue with Table locks Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Fri May 17, 2019 1:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pkarthik@email.com
Beginner


Joined: 29 Mar 2005
Posts: 34
Topics: 18
Location: Canada

PostPosted: Fri May 17, 2019 1:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group