Solved

Table locking problem

Posted on 2006-11-09
7
1,405 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 

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 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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