Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    31

    Unanswered: Force all connections to use Uncommitted Read from MS SQL to DB2

    Hi all,

    We use the Microsoft OLE DB Provider for DB2 when connecting from MS SQL to DB2 (z/OS), but I've had no luck finding some way to force all connections to use the Isolation Level UNCOMMITTED READ (MSUR001).

    The process we're running is only reading data from DB2, but using the MS SQL tools it's not always simple to add WITH UR to the queries. And I'd like to use UR to avoid locking in DB2 as load processes run. I hoped there might be some way to force this either through the connection string or possibly something else, but I'm having no luck finding it, if it's even possible.

    Thanks for any advice.
    Last edited by samalex01; 11-24-14 at 12:25. Reason: Typo

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If the z/os db2 allows a connect proc, you might try writing one

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You should be able to add "TxnIsolation = ReadUncommitted" either to the driver configuration file or to the connection string.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2013
    Posts
    31
    Thanks everyone. I'm still green when it comes to DB2 and MS SQL talking to one another, but in my research I did find how to set this in the Connection String, though it differs alittle from what db2mor posted. I'm using sp_addlinkedserver from MS SQL to add a link to DB2 from MS SQL, and one of the parameters of the connection string is IsoLvl where I'm giving it UR instead of NC. Here's an example:

    EXEC master.dbo.sp_addlinkedserver @server = N'TestDB2', @srvproduct=N'Microsoft OLE DB Provider for DB2', @provider=N'DB2OLEDB', @provstr=N'Initial Catalog=TestDB2;Data Source=TestDB2;HostCCSID=1252;Network Address=10.0.0.23;Network Port=4321;Package Collection=NULLID;Default Schema=TEST;IsoLvl=UR', @catalog=N'DB2'

    When I make a Select through MS SQL via this link I'm still using MSCS001 instead of MSUR001. Is this the correct way to put this in?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It is typically recommended to use the IBM OLEDB provider for DB2 instead of Microsoft. Obviously, DB2 documentation may not be applicable to the Microsoft provider.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •