Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Table locking problem

Posted on 2006-11-09
7
Medium Priority
?
1,420 Views
Last Modified: 2008-01-09
I have a VB.NET application that uses OLEDB to connect to DB2.  the DB2 database is also used by another application that uses UNIFACE to establish connection and manage the locking state of the various tables.

When the application that uses uniface is open, I get deadlock errors.

Below is the connection string I am using:

connection.ConnectionString = "Location=""" & IPaddress_tbox.Text & """;Provider=""IBMDADB2.1"";Mode=readwrite;User ID=" & user_tbox.Text & ";Data" & 
" Source=" & database_tbox.Text & ";Password=" & password_tbox.Text

It appears that I am locking exclusive on any table I open for output.  How can I make it so that the sharing mode is such that other applications (not .NET) can be reading/writing at the same time?
0
Comment
Question by:robertjmackay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 17906523
can u post the sqlcode/reason code you are receiving?
sql0911 can have different reason codes, if the reason code is 68, then its lock timeout. If its 2, then its deadlock. I have no idea what uniface does, but looking at the uniface site on Compuware, I dont see how it manages locking. In any case, if you are getting deadlock errors, it means another user has exclusive lock on row that you are trying to read/write. So the problem is with the locking of the other user, not you
0
 

Author Comment

by:robertjmackay
ID: 17906673
Actually,

I am not trying to do an update.  I am executing either a delete or insert statement, which kinda makes me believe that the table is being locked, not the row.

I set the imeout of my executenonquery statement to 60 seconds and I get one of the two errors:

Unhandled Exception: System.Data.OleDb.OleDbException: [DB2/NT] SQL0952N  Processing was cancelled due to an interrupt.  SQLSTATE=57014

or

Unhandled Exception: System.Data.OleDb.OleDbException: [DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17908601
same response, its the other user that has the lock, not you. If you had the table lock, then your statements would be able to complete. Try running your app when the UNIFACE app is not connected to the database.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:robertjmackay
ID: 17914007
It seems that the VB.NET program is triggering the DB2 table into a lock.  How do I change the default isolation level for teh OLEDB provider?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17930332
couple of ways you can do this
if practical, change you sql statements in the application to use the 'with CS' or with 'RS' or with 'UR' or with 'RR' clause at the end of the SELECT statement, or use the for read only clause
update the db2cli.cfg file with the default isolation level for the database
db2 update cli cfg for <dbname> using TXNISOLATION 4 on the machine where the app will be calling the database from
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 2000 total points
ID: 17932879
db2 update cli cfg for SECTION <dbname> using TXNISOLATION 4 on the machine where the app will be calling the database from

      
0
 

Author Comment

by:robertjmackay
ID: 17933044
Thanks,

We have determined that there was a setting in DB2 that was causing the table lock.  Thanks
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question