February 4, 2008 at 4:31 pm
I'm trying to set up a linked server to a DB2 database using the new 'Microsoft OLEDB Provider for DB2' as provided by MS on the Feature Pack download site. My box is Windows2003 and SQL2005. The DB2 database is on a Solaris/Sun system. I can't seem to make a good linked server no matter what I put into the fields. I'm using the syntax on connectionstring.com for my Provider String:
Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=myUsername;Password=myPassword;
not knowing exactly what my Catalog is or my Package Collection. I can get the IP for the Network Address and I have a valid UserID and Password. I'm also not sure what I should be using for the Product Name and Data Source on the Linked Server Properties window.
Any help would be greatly appreciated.
February 18, 2008 at 5:41 am
The Initial Catalog would be the name of the DATABASE.
Package Collection is normally the same as your schema, but can be different.
Default Schema = SCHEMA name 🙂
If you don't know what you should be using here, it's difficult to 'guess'. What I recommend you do is create the UDL using "Data Access Tool" that comes with the provider. It will fill in all the parameters for you. The tool will create the packages that are required, and you can use it to test the connectivity. You can access the help files for each dialog in the tool also for help while configuring the UDL. Also, from within the tool, you can perform a sample query which will retrieve a list of tables from the system catalogs suing the default schema property configured in the data source.
After running the tool on my system to connect to DB2/NT, I have the following UDL string, which I can then use for the linked server.
[p]Provider=DB2OLEDB;User ID=MYID;Password=MYPASSWORD;Initial Catalog=DATABASENAME;Network Transport Library=TCP;Host CCSID=1252;PC Code Page=1252;Network Address=SERVERNAME;Network Port=50000;Package Collection=SCHEMA;Default Schema=SCHEMA;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=SCHEMA;DBMS Platform=DB2/NT;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;[/p]
For the actual linked server, I end up with this:
[p]EXEC master.dbo.sp_addlinkedserver @server = N'DB282', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'DB282', @provstr=N'Provider=DB2OLEDB;User ID=MYID;Password=MYPASSWORD;Initial Catalog=DATABASENAME;Network Transport Library=TCP;Host CCSID=1252;PC Code Page=1252;Network Address=SERVERNAME;Network Port=50000;Package Collection=SCHEMA;Default Schema=SCHEMA;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=SCHEMA;DBMS Platform=DB2/NT;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;'[/p]
HTH,
Charles
February 18, 2008 at 7:07 am
Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=myUsername;Password=myPassword;
Any help would be greatly appreciated.
Hi there,
see also
(DB2 docs are ok)
Provider: IBMDADB2 when using IBM, DB2OLEDB for MS I guess
Don't worry about Package collection; default schema in IBM is your user id - but when using fully qualified table names don't worry either.
catalog or dsn name is the database name or alias as in the catalog of your db2.
Hmmm, is it a host DB2 ??
the default package collection would be NULLID probably (all the LUW access packages are usually bound with NULLID). This should be the default, so don't worry.
February 18, 2008 at 7:27 am
February 18, 2008 at 11:35 am
Q. Have you attempted to fix the problem with unix server? if you have no rights to the windows from the unix box, it does not matter what you do to connect to it. unix will not let you go thru.
hope it help. let me know if I can help.
"We never plan to Fail, We just fail to plan":)
February 19, 2008 at 3:20 am
It is probably worth talking to your DB2 DBAs. They should have more experience of client connectivity to DB2 than a pure SQL Server DBA.
You may also want to look at the IBM DB2 Connect product. DB2 Connect is chargeable, but may give you a 'richer and more performant experience' than the MS product.
If all you want to do is connect to DB2 and run a few queries, then the MS product could be all you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 19, 2008 at 9:37 am
I would agree you should talk with your DB2 DBAs. However, you only need DB2 Connect to reach a Z/OS mainframe or AS/400 not a Sun/Solaris box.
February 19, 2008 at 3:34 pm
I guess I'll have to talk to the DB2 DBA. I've tried just about every combination possible from the data that I have. I also checked that MS article and I had already read it and tried to link using the info on that page. Luckily, this db platform is moving to Oracle in the next 6 months. However, I would still like to make a Linked Server connection before the end of that 6 months.......... I've got data to deliver.
May 8, 2008 at 1:55 pm
Hi;
I too have recently begun the attempt at setting up a Linked Server from SQL2000 to DB2.
I downloaded the Host Integration end user client to obtain and install the DB2OLEDB dll etc.
I ran the utility to create and test a UDL... No problem
I created a Linked Server;
Provider Name: Microsoft Ole Db Provider for DB2
Put in Provider Options (Allow in Process checked)
and I tried a million, maybe more combinations of things.
The best result I get when I refresh the tables within EM is wait for 4-5 seconds and
Error 7330: Could not fetch a row from OLEDB provider unknown
I also tried from QM a query against the schema I use in the FN Provider string. The message there is somewhat more informative.... but not that much more helpful.
Could not fetch a row from OLE DB provider 'DB2OLEDB'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 51002, SQLCODE: -805]
OLE DB error trace [OLE/DB Provider 'DB2OLEDB' IRowset::GetNextRows returned 0x80040e14].
I tried pointing the Linked Server datasource at the UDL file with no success.
HELP!!! PLEASE
May 8, 2008 at 2:16 pm
Ray,
You don't mention which platform DB2 is running on, is it Z/OS, Unix or something else?
Nevertheless, the -805 means that the packages that the client is calling are not found on the DB2 server. Perhaps the packages that ship with Host Integration haven't been bound yet to DB2? I know with DB2 Connect (a competitor to Host Integration), that the first time the client tries to connect to DB2 the packages are bound to DB2. This means the user doing the connection and thus the bind needs to have the proper DB2 authority to bind packages. I suggest you get with the DB2 DBA.
May 8, 2008 at 10:21 pm
I found the IBM OLEDB driver, installed it and it worked right off. There are alot less parms to set and the provider string is simple. Unfortunately I can't access my home email from work, and this is set up. I will send email with more details tomorrow. Thanks
May 9, 2008 at 5:00 am
Ray,
From the data access tool for HIS, run the package creation piece, and let me know if that works. If that fails, it's a permission issue with the user ID, and you need to use the tool with a user ID that has permissions to create packages.
Charles
May 21, 2008 at 1:05 pm
Hello,
I am trying to setup replication between SQL 2005 and DB2 with microsoft OLE DB provider for DB2. I am using the data access tool to setup the connection.
Provider=DB2OLEDB;User ID=XXXXX;Initial Catalog=db2taloc;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=XXXX.XXXX.XXXX.XXX;Network Port=2111;Package Collection=MYPKG;Default Schema=MYPKG;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=MYPKG;DBMS Platform=DB2/MVS;Defer Prepare=False;Persist Security Info=False;Connection Pooling=True;Derive Parameters=False;
I am able to successfully test the conenction. When I try to run a sample query, the whole window dissappears. Also when i choose this new data source in the GUI to setup replication to NON SQL Server subscribers, it fails with an error that it cannot conenct to the data source.
Any help would be appreciated.
Thanks
Prakash
Prakash B
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply