| Author |
Topic |
|
Auric
Yak Posting Veteran
70 Posts |
 Posted - 2005-12-19 : 08:54:58
|
| Hi allI am trying to configure a linked server in SQL to connect to a DB2 database. I cannot figure out what provider string etc I should use to connect to it properly. Any help would be greatly appreciated. Select * from users where clue > 0 |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-19 : 09:28:58
|
| Auric,Are you using DB2 on open systems or on the Mainframe? If Mainframe, do you have DB2 Connect or Host Integration Server?Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
 |
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-19 : 09:44:35
|
| I have IBM DB2 Connect, and yes its hosted on an AS400Select * from users where clue > 0 |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-19 : 11:43:38
|
| So far..no dice, I'm recreating the connection on my local PC to see if ic an get it to work that way.Select * from users where clue > 0 |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-19 : 17:39:46
|
| Here's the create statement that I have. This is for an ODBC connection for the DB2 Instance DBBPEXEC master.dbo.sp_addlinkedserver @server = N'DBBP', @provider=N'MSDASQL', @datasrc=N'DBBP'GOHTHJon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-20 : 10:58:34
|
| baker, so I just replace the DBBP with the name of my ODBC datasource?Select * from users where clue > 0 |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-21 : 09:54:26
|
| I downloaded the trial of host integration server to try out, if it works for what we want to do I think I can convince the company to spring for it.Select * from users where clue > 0 |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-21 : 16:07:27
|
| Auric, Sorry I didn't get back with you. Yes, that is what I would suggest. Did you try that? Did it work?Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-21 : 16:31:59
|
| not exactly... No problems about getting back, i've been busy as well. I'm starting to get frustrated with this projectSelect * from users where clue > 0 |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-27 : 11:37:45
|
| Well essentially if you create an ODBC data source that can connect through DB2 Connect, you should be able to create a linked server (either from the GUI or cmdline). Can you verify that your ODBC connection works?Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-28 : 09:28:23
|
| Jon, I have an ODBC Connection that uses the IBM DB2 driver, as well, I have created a linked server in the GUI using that ODBC conn. I created. I can issuea select * from openquery(linked,'select * from www.dsz090') fine , but I cannot do any sort of insert or update query.Select * from users where clue > 0 |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-28 : 13:39:01
|
| Yes, that has been my experience, too. I called MSFT PSS on this issue and it was their opinion that it was a DB2 Connect issue. I was actually able to get it to insert 1 row or update one row, but multi-row statements failed. An ODBC trace on the client and DB2 Connect server showed the cursor buffer was getting corrupted. The DB2 connect admins were not willing to work any further on it, and priorities changed, so we never solved it. If you do, please post here.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-12-28 : 14:58:20
|
| Wonderful... well this project is sort of going to take a bit of a back burner for a bit. I really wanted to get it done before the new year, but apparantly it is going to be an issue.Select * from users where clue > 0 |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2006-01-05 : 14:10:42
|
| bakerjon,DB2 Personal Connect version 8.2 works ... I was able to connect and insert data now.Select * from users where clue > 0 |
|
|
|
|
DB2 linked server...