Linked Server on SQL2000 to DB2 - desperate...

[Bretl Andy]

Hi All,
I am close before throwing the machine out of the window.
I tried to establish a linked server on our SQL2000 server pointing to a DB.

EXEC sp_addlinkedserver
@server = 'DB2',
@srvproduct = 'IBM OLE DB Provider for DB2 Servers',
@provider = 'IBMDADB2',
@provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'

EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'

If I query the LS using query analyzer or the Enterprise Manager both start
to hang, never responding again.
To be honest I am quite new to this topic but I read a lot and also tried a
lot ;)

Some articles talk about SNA Servers to be installed first, others about
some client tools.
I am confused by now...

Could anybody please be so kind and tell me how to connect the DB2 as a
linked server?
Is my statement wrong, and if so how should it look like? Why does the
application start to hang? Do I really need to install additional software
to do so?

Our DB2 expert also taked about software necessary to access the DB2
mainframe. Any ideas?
Thanks a lot in advance and belive me, any hints are highly appreciated ;)

Seize the Day

Andy

Andreas Bretl
an***********@brainlab.com

[Simon Hayes]

"Bretl Andy" <an***********@brainlab.com> wrote in message
news:2r*************@uni-berlin.de...

Hi All,
I am close before throwing the machine out of the window.
I tried to establish a linked server on our SQL2000 server pointing to a
DB.

EXEC sp_addlinkedserver
@server = 'DB2',
@srvproduct = 'IBM OLE DB Provider for DB2 Servers',
@provider = 'IBMDADB2',
@provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'

EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'

If I query the LS using query analyzer or the Enterprise Manager both
start
to hang, never responding again.
To be honest I am quite new to this topic but I read a lot and also tried
a
lot ;)

Some articles talk about SNA Servers to be installed first, others about
some client tools.
I am confused by now...

Could anybody please be so kind and tell me how to connect the DB2 as a
linked server?
Is my statement wrong, and if so how should it look like? Why does the
application start to hang? Do I really need to install additional software
to do so?

Our DB2 expert also taked about software necessary to access the DB2
mainframe. Any ideas?
Thanks a lot in advance and belive me, any hints are highly appreciated ;)

Seize the Day

Andy

Andreas Bretl
an***********@brainlab.com

This explanation might clarify the question of installing SNA/HIS client
components vs the IBM client drivers:

http://groups.google.com/groups?hl=e...40cpmsftngxa07

Have you checked that you can successfully connect from the SQL Server
server to DB2 using some kind of DB2 client tool, or even just a script? If
that's OK, then at least you know the provider is installed and working, so
the issue is likely to be the linked server definition.

You might want to check the @provstr parameter - the syntax is specific for
each OLE DB provider, so you should make sure that it's really a valid
string for the IBM provider. There's a sample here:

http://www.connectionstrings.com

Simon

[Bretl Andy]

First of all, Thanks a lot Simon for the hint,
unfortunately I still can not connect to the db2. But now I have another
errormessage never seen before:
"The provider reported an unexpected catastrophic failure."
This seems to happen during inizialisation??
I used the following registration:
EXEC sp_addlinkedserver
@server = 'DB2',
@srvproduct = 'Microsoft OLE DB Provider for DB2',
@catalog = 'JDE_PROD',
@provider = 'IBMDADB2',
@provstr='Provider=IBMDADB2;Database=JDE_PROD;HOST NAME=xxxx;PROTOCOL=TCPIP;P
ORT=5000;uid=xxxx;pwd=xxxx;'

If I read the article correctly it should work using the DSN pointing to the
db2 which is installed on the SQL server.
But as mentioned, the error above occures or it seems that the
EnterpriseManager hangs when accessing tabes in the linked-server.
Thanks a lot Simon,

Seize the Day

Andy

Andreas Bretl
an***********@brainlab.com

[Neil Pike]

Bretl,

If you want to use the IBM provider then you need to have IBM's DB2 Connect
product installed on the SQL Server.

If you want to use the MS provider, then you need to have Host Integration
Server 2004 (latest version on SNA Server) installed on the SQL Server as a
"nodeless" install - this is basically a server install without choosing to
install the SNA Server service.

Do you have either of these pre-requisites installed and tested first?

(If you go the MS route it's worth posting future Q's in the Host Integration
Server newsgroup on the MS public servers - you're likely to get better help
there)

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforu...?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index....partmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

[Bretl Andy]

First of all thanks to all who tried heling me with this problem.
Actually it seemes to be a problem with the current fix-pack installation on
the JDE Database-Driver (DB2-ODBC).
Maybe it was damaged or really out of date so it could not communicate with
our SQL Server 2000.
After installing the current fixpack I was able to implement linked-server
over ODBC without problem.

What a hassle when they do not keep their system installation up to date.

Thanks again and have a geat week,
Andy Bretl
an***********@brainlab.com