I need to connect SQL Server 2000 to DB2 on z/OS through DB2 Connect 8.
I can successfully connect and query data through a System DSN, but
trying to link the server using this DSN and MSDASQL fails (can't seem
to find the DB2 Connect server). I've also tried IBM's OLE/DB driver
(IBMDADB2).

Has anyone successfully linked to DB2 on a mainframe through DB2
Connect 8.x via either ODBC or OLE/DB drivers?

DB2 Connect "talks" DRDA

ODBC and DRDA are two different standards. Please take a look at the
following...

http://www.unixreview.com/documents/s=1471/urm0000ndc/

Microsoft SQL Server 2000 is not a DB2 client. It has facilities for
connecting to remote databases, but you must configure these correctly to
use the IBM DB2 drivers, not the Microsoft SQL Server drivers. MSDASQL is
Microsoft SQL Server driver, not an IBM DB2 driver. The IBM OLE DB driver is
OK, but Microsoft's SQL Server's approach is not completely compatible, IIRC
(but check on a SQL Server forum for the details). Summary: To access IBM
databases, you need to use the corresponding IBM driver. Use of the
Microsoft driver will not and can not work.

Your best bet - assuming you really do need a Microsoft SQL Server in the
equation at all - is to use the IBM ODBC driver from SQL Server. Note that
if you do place the Microsoft code between your application and DB2, that
you will need to address security very carefully, since the MS layer does
not fully support the IBM distributed security model. This too, should be
taken up on a SQL Server forum.

Leaving SQL Server out of the access path (i.e. have your applicaiton talk
to both on their own terms, using the distributed transaction coordinator to
address 2 phase commit) is a lot easier and imposes substantially less
constraints, It's also almost trivial to get working.

"Dad" <dadburnett@gmail.com> wrote in message
news:1122055835.814312.44300@z14g2000cwz.googlegro ups.com...
>I need to connect SQL Server 2000 to DB2 on z/OS through DB2 Connect 8.
> I can successfully connect and query data through a System DSN, but
> trying to link the server using this DSN and MSDASQL fails (can't seem
> to find the DB2 Connect server). I've also tried IBM's OLE/DB driver
> (IBMDADB2).
>
> Has anyone successfully linked to DB2 on a mainframe through DB2
> Connect 8.x via either ODBC or OLE/DB drivers?
>

Mark Yudkin (myudkinATcompuserveDOTcom@boing.org) writes:
> Microsoft SQL Server 2000 is not a DB2 client. It has facilities for
> connecting to remote databases, but you must configure these correctly to
> use the IBM DB2 drivers, not the Microsoft SQL Server drivers. MSDASQL is
> Microsoft SQL Server driver, not an IBM DB2 driver.

Correction: MSDASQL is an OLE DB provider that uses an ODBC driver as
its data source. It is thus not specific for SQL Server, bu can be used
to connect to any data source for which there is an ODBC driver.

Thus when you say:

> Your best bet - assuming you really do need a Microsoft SQL Server in the
> equation at all - is to use the IBM ODBC driver from SQL Server.

You are saying that he should use MSDASQL.

To wit, when you communicate with a linked server from SQL Server, you
always to this over OLE DB. Either then you use a targeted OLE DB provider,
or you use MSDASQL to use an ODBC driver. Usually it's preferrable to
use a targeted OLE DB provider over MSDASQL.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp