August 12, 2003 at 5:37 am
I am trying to create a DB2 linked server in my SQL2000 machine.
I have specified the following in the 'Linked Server Properties'
Provider Name: IBM OLEDB Provider for DB2( which is the only one available).
Product Name:DB2OLEDB
Data Source : <name of db2 database>
provider string: <empty>
I cannot connect to the server. I have tried to put the provider string as "DB2OLEDB" but it did not work. I am not really sure what to put there.
I have looked into the microsoft site on how to do it. I found KB Article #218590 which uses SNA for that but it did not work ( although I managed to create a UDL file on the Host Integration Server machine) I am not sure what to do with it.
Is there any other way to make it work. any articles and feedback on this issue is highly appreciated.
August 12, 2003 at 5:39 am
I got the following error when I tried to connect:
could not obtain the schema rowset for OLE DB provider 'UNKNOWN'. The Provider supports the interface, but returns a failure code when it is used
August 12, 2003 at 9:32 pm
I'm a novice but... I went serious rounds with this and was never able to get the OLEDB Provider working. Host/SNA isn't available to us. We ended up putting DB2 client stuff on the server to install and configure the IBM DB2 ODBC Driver. We then did a linked server using the ODBC Provider and the DSN the DB2 config created.
At that point we could see tables in EM but couldn't run normal queries in QA because four part names refused to work. The only thing that worked was the openquery syntax (although performance against large DB2 tables sucks because it doesn't effectively handle a SQL to DB2 join). This is the 'solution' we ended up with.
P.S. - This has turned into a personal vendetta so if anyone has a better way... PLEASE speak up!
August 12, 2003 at 11:00 pm
Thanks for the feedback.
what I understood from your email is that you installed DB2 client on the machine and used the ODBC connection string when configuring the linked server.
so can you please help me out on the "New linked server properties":
Product Name
DataSource
ProviderString
I did not get IBM ODBC Driver in the provider selection, rather IBM OLE DB Providers.
This is my first attempt in trying to do that and the main reason for me is to run distributed quires between my SQL server and DB2 .
Were you able to achieve anything by the solution.
August 13, 2003 at 7:55 am
We too had no luck with DB2 Linked server. We also tried HIS (Host/SNA) with some success (could not get stored procedures to work). We have had GREAT success with a Real time mirroring/date replication product (DB2Motion), we are able to bring over all (1.5+ millions rows) the data (Db2/AS400) and access it directly from SQL Server where performance has not been an issue. I highly recommend the following product:
http://www.hitsw.com/products_services/db2motion/db2motion.html
August 13, 2003 at 8:28 am
Hi
I was successful with the following set-up:
Install Client Access with ODBC driver
Set-up ODBC connection to AS/400
For the linked server use "Microsoft OLE DB Provider for ODBC Driver"
Product Name: Whatever identifies your AS/400 connection
DataSource: Name of the ODBC connection
ProviderString: UID="CA user name"; PWD="CA user password"
The CA user has to have "All Objects" right on the AS/400!
You can see the tables, schemes and catalogues if you drill down from the linked server, but you can't see the fields in the DTS query designer. So you have to type the SQL statement.
QA also works with ProductName.Catalogue.Scheme.Table. So, no problems with four part names.
regards
Matthias
August 20, 2003 at 9:56 am
Sorry to respond so late...
I would desperately like to have four part names work. Maybe I missed something, can you clarify? Our DB2 people were clueless and acted like I had leprosy or something.
1) You mention installing Client Access. Is that a generalization or specific DB2 software? We installed DB2 Connect Personal Edition 7.2, DB2 Runtime Client 7.2, and setup the DSN with the DB2 Client Configuration Assistant.
2) What would product name/AS400 connection identifier and catalog be termed in the DB2 world? I have a host name, database name, system name, instance name, and port number to choose from.
3) Is there any way I can tell if I have All Objects rights without having to ask? (Unfriendly support people.) Is that a pretty high level of authority? I know I just have read only.
August 20, 2003 at 11:07 am
Hi
you should know that DB2 people don't know that there are other databases than DB2.
1. Client Access is the IBM program for accessing the AS/400 system. But I think it should work with all ODBC driver for DB2. Unfortunately I'm not familiar with your piece of software.
2. IBM Client Access comes with the so called AS400 Operations Navigator where you can set-up your system. You can do it by the IP address or better by the system name. But then you have to add to the file "hosts." on your local machine (delfault C:\WINNT\SYSTEM32\DRIVERS\ETC under Windows 2000) the IP address and system name.
It is not mandatory to use the same name as the AS/400 system name but it is less confusing.
When you set-up the ODBC driver you can add the desired libraries to the library list just separated by a blank.
(Don't forget to set the connection type to Read-Only in order to avoid that you make some changes to your DB2 databases accidentially). As name of the ODBC driver you can use whatever you want.
The product name in the properties of the linked server is whatever you want to use. I called it ERPLIVE because the ODBC driver contained all libraries of our ERP live system.
The Data Source is the name of the ODBC driver and the provider string contains the user id and the password.
If you after the configuration of the linked server drill down from the linked server into the tables you will see a table with Name (table name on AS/400), scheme (library on AS/400) and Catalog (AS/400 system name or however you have named your system in the Operations Navigator).
So, the four part name would be ERPLIVE.Catalog.Scheme.Name.
With IBM Client Access you just need the IP address of your AS/400. That's all. But I think it needs some configuration on the AS/400 to use Client Access.
3. "All Objects" is a quite high level.
We found this fact accidentially because I failed also with my log-on, but one guy in my team - a former RPG programmer - still had "All Object" rights and it worked with his log-on.
We convinced our AS/400 people to set-up the user DWarehouse with "All Object" rights but without the ability to log-on.
Regards
Matthias
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply