Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
de9625
Starting Member
17 Posts |
 Posted - 2005-01-26 : 06:46:06
|
| How can I join a table in a Win2k SQLServer with a table in an iSeries DB2?I have to be able to pass parameters to the where stmt to limit the result. I think this have to be a SP so I can execute it from an Web environment or from MS Excel. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-26 : 08:12:16
|
| I'd suggest looking at linked servers, they are described in SQL Server Books Online. They would allow you to create a link to your DB2 setup and query it as if it were another SQL Server table. You can join to it to, BUT...it will be extremely SLOOOOOOOOOOOOOOOOOOOOOOOOW. Doing JOINs across linked servers is not very efficient, even with small sets of data. I would recommend that instead of joining, you use the linked server to grab data from DB2, put it into a SQL Server temp table, and then join to the temp table. It will be much faster, as the JOIN will not be performed over a network connection. |
 |
|
|
de9625
Starting Member
17 Posts |
Posted - 2005-01-26 : 10:05:06
|
| Thx, I found the linked server and have used the linked server wíth a ODBC connection string. I'll start with the join and if/when I get in to trouble I'll consider your advice with temp tables.This is what I have done so far.SELECT t1.companyId, t1.name,t2.addressFROM OpenQuery(DB2SERVER, 'Select companyId, address From Company') as t2inner join CompanyTest as t2 on(t1.companyId = t2.companyId)I'll probably add some order by on the DB2 so I'll get an index file and then wrap everything in a SP. |
|
|
|
kmoskal
Starting Member
1 Post |
Posted - 2005-02-15 : 11:27:04
|
quote:
Originally posted by de9625
Thx, I found the linked server and have used the linked server wíth a ODBC connection string. I'll start with the join and if/when I get in to trouble I'll consider your advice with temp tables.This is what I have done so far.SELECT t1.companyId, t1.name,t2.addressFROM OpenQuery(DB2SERVER, 'Select companyId, address From Company') as t2inner join CompanyTest as t2 on(t1.companyId = t2.companyId)I'll probably add some order by on the DB2 so I'll get an index file and then wrap everything in a SP.
Can you provide some details on how you make the connection? Like where did you get the DB2 OLE DB provider? Also, Did you do anything special when creating the provder string? I'm trying to create a linked server in SQL Server 2000 to our OS/390 Mainframe. |
|
|
|
|
|
|
|
|
Join between SQLServer and DB2