?
Solved

Table locking problem

Posted on 2006-11-09
7
Medium Priority
?
1,412 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

771 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