none
Universe Database (IBM) - Linked server no longer works with MS SQL 2005

    Question

  • Has anyone managed to set up an IBM Universe database environment as a linked server to  MS SQL 2005?

    If so please convey how you got it done. 

     

    Thanks, 

    • Moved by Bob Bojanic Tuesday, April 27, 2010 12:25 AM (From:SQL Server Integration Services)
    Wednesday, September 20, 2006 4:43 PM

All replies

  • We managed to but struggled mightily in the process.  Assuming you have the IBM Client Access ODBC driver installed, the easy way was to create an ODBC DSN and use that for the linked server.  Here's the command we used to create the linked server. 

    EXEC master.dbo.sp_addlinkedserver @server = N'{DSNName}', @srvproduct=N'AS400', @provider=N'MSDASQL', @datasrc=N'{DSNName}'

    You still have to set the security context for the linked server within SSMS.  This worked in our 32-bit dev and stage environments.

    When we migrated to our 64-bit production environment, the 32-bit ODBC drivers didn't work.  We had to use the Microsoft OLEDB Provider for DB2 that available as a free download for enterprise and developer editions (I think). 

    http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en (scroll down to find the download)

    The download installs a wizard driven Data Access Tool that will help you create the connectionstring for your environment.  Here's the command we used.

    EXEC master.dbo.sp_addlinkedserver @server = N'{ServerName}', @srvproduct=N'iSeries', @provider=N'IBMDASQL', @datasrc=N'{ServerName}', @catalog=N'{CatalogName}'

    HTH,

    Thursday, September 21, 2006 1:56 AM
  • Unfortunately, we are using a RISC box and also the Windows version of the Universe product, so the examples above are of less use.  Though you have confirmed for that we are using the correct approach.

     

    Thanks for the assist.

     

    - John

    Wednesday, October 11, 2006 8:06 PM
  •  Martin Mason wrote:

    When we migrated to our 64-bit production environment, the 32-bit ODBC drivers didn't work.  We had to use the Microsoft OLEDB Provider for DB2 that available as a free download for enterprise and developer editions (I think). 

    http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en (scroll down to find the download)

    The download installs a wizard driven Data Access Tool that will help you create the connectionstring for your environment.  Here's the command we used.

    EXEC master.dbo.sp_addlinkedserver @server = N'{ServerName}', @srvproduct=N'iSeries', @provider=N'IBMDASQL', @datasrc=N'{ServerName}', @catalog=N'{CatalogName}'

    HTH,

    Would this work for creating an SSIS Source too?   

    Friday, December 01, 2006 1:47 PM
  •  John Lovrinic wrote:

    Has anyone managed to set up an IBM Universe database environment as a linked server to MS SQL 2005?

    If so please convey how you got it done.

    Thanks,



    Try posting to the "SQL Server Data Access" forum for more answers.
    Friday, December 01, 2006 2:37 PM
  • Hi John -

    Have you managed to setup linked server to Unidate (Universe)? If so, could you please share how you did set it up? We are running Unidata here, and I would like to setup linked server to connect to it. I already setup Unidata ODBC drive, but I can't get the linked server to working.

    Lava

    Wednesday, December 13, 2006 7:33 PM
  • Hi Isalih,

     

    I was wondering where you found an ODBC driver for Unidata?  I'm trying to get into a Unidata database via Access or SQL or whatever will work, but haven't been able to locate the appropriate ODBC driver.

     

    Thanks!

    Jeremy

     

    Monday, October 15, 2007 7:13 PM
  • Jeremy,

     

    Here is a good place to start: http://www-306.ibm.com/software/data/u2/middleware/index.html#odbc

     

     

    Monday, October 15, 2007 7:18 PM
  • John,

     

    I figured out how to setup linked server to connect to Unidata and query directly from SQL... Let me know if you need instruction on how to setup your environment.

     

     

    Friday, November 21, 2008 9:14 PM
  • Isalih,

    I would like to know how you made the connection. I have tried using the oledb2 driver  and I continue to get a message about an invalid parameter.  What is the catalog they are asking for?  I have a Universe database is the the Universe account that has our data?

    Thanks
    Wednesday, December 24, 2008 4:47 PM
  • Isalih,

    could you share with me how you configured your UniData database as a linked server?
    I have the UniOLEDB installed on my SQL Server and just need the correct syntax for the sp_addlinkedserver and any configurations on the UniData server. I appreciate your input!

    Thanks,

    Ralph


    RS
    Wednesday, January 21, 2009 4:26 PM
  • please tell us what you did?
    Thursday, February 18, 2010 8:33 PM
  • I'm also facing a similar problem, I have a DSN ODBC connection with our Universe database properly set up, I can use ms-access to retrive information from that database through the DSN, I'm able also to add a linked server using the following sentenses on my sql 2008 server express

    use master
    EXEC sp_addlinkedserver @server = 'DSNname' ,@srvproduct= '',@provider= 'MSDASQL',@datasrc= 'DSNname'

    But when i check using the following sentence

    EXEC sp_tables_ex @table_server = 'DSNname'
     
     
    I get an error like this:

    OLE DB provider "MSDASQL" of the linked server "DSNname" reply the following mesage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
    OLE DB provider "MSDASQL" of the linked server "DSNname" reply the following mesage "[IBM][UVODBC][0301920]Error ID: 46 Severity: ERROR Facility: DBCAPERR - UCI Error. Func: SQLConnect(); State: IM980; uniVerse code: 0; Msg: [IBM][SQL Client]Remote password is required..".

    It seems I need to set the password some where, but is already set it on the DSN

    Monday, February 22, 2010 12:59 PM
  • My solution works with Unidata access. My environment has UniData database installed on a HP_UX Unix server. First, install UniOLEDB provider on your SQL Server machine.  I'm using UNIOLEDB 6.0. I have tried 7.2 and it does not work in my environment. Use UCI Config Editor to set up and expose UniData Database (or simply edit the uci.config file). You may have to abstract the data objects you are targeting as vsg views, using Unidata Visual Schema generator. We find this abstraction useful in my environment as it exposes underlying multivalued database in a relational format.

    Run the following SQL script inserting credentials appropriate to your environment:

    USE

     

    [master]

    GO

    -- Enable IBM.UniOLEDB provider to be instantiated as an in-process server. Otherwise this will not work

    EXEC

     

    master.dbo.sp_MSset_oledb_prop N'IBM.UniOLEDB', N'AllowInProcess', 1

    GO

    -- Enable only level 0 OLE DB interfaces to be invoked against the provider. Otherwise 4-part named object names will fail

    EXEC

     

    master.dbo.sp_MSset_oledb_prop N'IBM.UniOLEDB', N'LevelZeroOnly', 1

    GO

    IF EXISTS (SELECT * FROM master.sys.servers where name = 'LinkedServerName' and is_linked = 1)

     

    EXEC dbo.sp_dropserver @server=N'LinkedServerName', @droplogins='droplogins';

    GO

     

     

    EXEC

     

    dbo.sp_addlinkedserver

     

     

    @server = N'LinkedServerName'

     

    ,@srvproduct=N'IBM UniOLEDB'

     

    ,@provider=N'IBM.UniOLEDB'

     

    ,@datasrc=N'ServerName Defined with UCI Config Editor'

     

    ,@location=N'Unix Folder Location';

    GO

     

    /* For security reasons the linked server remote logins password is changed with */

     

    EXEC

     

    dbo.sp_addlinkedsrvlogin

     

     

    @rmtsrvname=N'LinkedServerName'

     

    ,@useself=N'False'

     

    ,@locallogin=NULL

     

    ,@rmtuser=N'username to access UniData database'

     

    ,@rmtpassword='password to access UniData database';

    GO

    /* Set options according to your environment */

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'rpc out', @optvalue=N'true'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'collation name', @optvalue=null

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'use remote collation', @optvalue=N'false'

    GO

    EXEC

     

    master.dbo.sp_serveroption @server=N'QOPINTEGRATE', @optname=N'remote proc transaction promotion', @optvalue=N'false'

    GO

     

     

    Wednesday, April 21, 2010 9:44 PM
  • Not sure if Unidata allows for OLEDB, but the DB2OLEDB 2.0 driver for 2008 works wonders for 2005 as long as you are enterprise.  The newer driver made the invalid parameter error go away.  The 2008 feature pack is the one you want (August is what we used).

    Our team likes the new DB2OLEDB because there is no ODBC installation like the prior IBM ODBC driver.

    Wednesday, April 21, 2010 9:55 PM
  • OLEDB works for Unidata as long as users realize that UniOLEDB implementation of the OLEDB standard expects data to be organized in normalized from. In its native form, Unidata files have data columns that contains other data columns - thus its multivalued character. Visual Schema Generator (VSG) mentioned in my previous posting allows multivalued data to be presented in 1NF as views for UniOLEDB consumption. The plus of VSG is that the views created are not materialized, thus there is no data duplication. VSG views merely instructs UniData to normalize data before returning it to consumers.

    So in summary, to create linked servers in SQL Server to a UniData source

    1.) Install UniOLEDB and VSG on the client (i.e. the SQL Server machine)

    2.) Define in uci.config file the data sources you want UniOLEDB consumers to connect by directly editing uci.config or using UCI Editor, installed as part of 1.) above

    3.) Use VSG to normalize UniData data you want exposed to UniOLEDB consumers.

    4.) Define your linked server using code similar to my previous posting.

    5.) You may have to set certain properties of UniOLEDB provider under <Server Objects>\<Linked Servers>\<Providers> node of your SQL Server in Management Studio to get your linked server to respond to all the different iterations of linked server queries (e.g OPENQUERY, Four-part object naming etc.)

    Thursday, April 22, 2010 4:14 PM
  • You can also think of a product called bluefinity

    https://bineeshthomas.wordpress.com/2010/07/27/world-of-multivalue-database-universe-and-ssis-integration/


    Bineesh Thomas BI Architect
    Wednesday, July 28, 2010 8:10 AM
  • hi Data_Gatekeeper

    do you know if the Unioledb.dll will work with sql server 2008 r2(running on windows server 2008 r2)? I have installed Unidata Clients for windows (UniDK) which has version 1.6.2.7377 of the .dll. Restarted the server but the dll does not show up in the sql server linked server providers list.

    Tested on my local which is a 32 bit machine running sql server 2008 r2 and the ibm.UniOledb does show up in the providers list.

    thanks

    Glenn 

    Thursday, January 06, 2011 2:58 AM
  • Hello Glen.

    I have not used UniOLEDB with SS2K8 R2 installed on Win2K8 R2. My use is limited to SS2K8 on a Win2K8 32-bit platform. However I don't see any reason why it should not work in your environment especially on your local machine.

    Ascertain that the UniOLEDB dll is indeed installed on your local. You may have to rumage in Windows registry to aconfirm this. I seem to remember from my programming days a registry key named Inprocess32 under which your dll should have an entry if indeed it was correctly installed.

    Because I constantly integrate data between different platforms, I use a small tool - Universal Data Tool to check connectivity and SQL invocation. You can find a trial copy of this tool at http://www.gpoulose.com/.

    When you launch the tool it will present you with MS's Data Link Properties window. Under the Provider tab, look for UniOLEDB Provider. It should be towards the end of the list. If you find it then fill in the connection properties, test the link to your data store and connect. You will be presented with a query window similar to SQL Server's Query Analyzer where you can write select queries against VSG view materialized from Unidata.

    Good Luck

    Thursday, January 06, 2011 2:52 PM
  • hi Data_GateKeeper,

    thanks for that  - very useful tool! Using the query tool(ADO) I can see the UniOLEDB provider right at the bottom of the list and can connect successfully to the UniData database view tables, views etc and query the database. So Im guessing that the UniOLEDB dll is installed correctly on my local machine.
    The probllem creating the linked server through MSS2K8 still persists. I create the linked server basically using similar code as per your april 2010 post(with some minor changes to suit my environment) but get the following error:


    The OLE DB provider "IBM.UniOLEDB" for linked server "DB_PROD" reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider "IBM.UniOLEDB" for linked server "DB_PROD". (Microsoft SQL Server, Error: 7399)

    Any thoughts\ideas would be greatly appreciated.

    thanks
    Glenn

    Friday, January 07, 2011 3:09 AM
  • hi Data_Gatekeeper,

    I tried setting up on the DEV server  - now getting the following error when testing the link server connection in SS2k8R2(this is running on windows server 2008 r2 so both r 64bit):

    OLE DB provider 'IBM.UniOLEDB' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

    Guessing this is a 32\64 bit issue now and no easy work around - you wouldnt know of any generic 64 bit ODBC drivers that would work with a Unidata 7.2 database?

    thanks
    Glenn 

    Friday, January 07, 2011 5:49 AM
  • Glenn

    Try search on Rocket U2 web site, <cite>www.rocketsoftware.com/u2</cite>   for a 64-bit UniOLEDB Provider or a ODBC driver. I cannot believe they would not have re-written UniOLEDB Provider for 64-bit Windows platform.

    Friday, January 07, 2011 3:06 PM
  • I have installed the Rocket Software Driver which includes both 64-bit and 32-bit versions, however, I have no idea how to create a Linked Server to Universe using the Rocket Software platform since the above thread clearly indicates using the previous IBM Driver.

    Is there any guidance on how to create a SQL Server Linked Server to Universe with the Rocket Software Driver?


    Saturday, June 30, 2012 2:22 AM