Equivilant of Oracle's DB Links in DB2

[Nick Palmer]

Hi all,

Is there a DB2 equivilant to Oracle's DB Link functionality ? I have two
DB2 databases and I need to get access to the tables in one from the other.
In Oracle I would just create a DB Link between the two instances. Is such
a thing possible between two DB2 databases ?

Thanks in advance,
Nick

[Larry]

You would need to set up Federated Support using nicknames. See the DB2
UDB Federated Systems Guide and the CREATE WRAPPER, CREATE SERVER,
CREATE NICKNAME etc. commands in the Command Reference.

Larry Edelstein

Nick Palmer wrote:

Hi all,

Is there a DB2 equivilant to Oracle's DB Link functionality ? I have two
DB2 databases and I need to get access to the tables in one from the other.
In Oracle I would just create a DB Link between the two instances. Is such
a thing possible between two DB2 databases ?

Thanks in advance,
Nick

[Nick Palmer]

Larry,

Thanks for the information. Thats exactly what I was looking for.

Nick

"Larry" <la***@nospam.net> wrote in message
news:gV*****************@fe08.lga...

You would need to set up Federated Support using nicknames. See the DB2
UDB Federated Systems Guide and the CREATE WRAPPER, CREATE SERVER,
CREATE NICKNAME etc. commands in the Command Reference.

Larry Edelstein

Nick Palmer wrote:

Hi all,

Is there a DB2 equivilant to Oracle's DB Link functionality ? I have two DB2 databases and I need to get access to the tables in one from the other. In Oracle I would just create a DB Link between the two instances. Is such a thing possible between two DB2 databases ?

Thanks in advance,
Nick

[WantedToBeDBA]

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..

Advance Thx,
Thiru
WantedToBeDBA.

[Mark A]

"WantedToBeDBA" <Wa***********@gmail.com> wrote in message
news:e6**************************@posting.google.c om...

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..

Advance Thx,
Thiru
WantedToBeDBA.

1. Open the DB2 Command Window on machine 1 (the one without db2 prompt).
See DB2 Command Line Tools.

2. db2 catalog tcpip node machine2 remote 10.10.60.16 server 50000

3. db2 catalog db sample as sample2 at node machine2

4. db2 connect to sample2 user xxxxxx (or open Control Center)

Notes:
a) In step 2 "machine2" can be any name you want to identify the DB2
instance on that machine
b) better to use host-name instead of IP addresses in step 2, assuming you
can ping it with host-name
c) In step 4, xxxxx is the user id authorized to connect to database on
machine 2. DB2 will prompt for your password.

Please send $15.00 to paypal account m0****@yahoo.com

[Knut Stolze]

WantedToBeDBA wrote:

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..

Exactly which step is causing you problems? What is the problem (exact
error message would be very helpful)?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Mark B. Townsend]

Mark A wrote:

1. Open the DB2 Command Window on machine 1 (the one without db2 prompt).
See DB2 Command Line Tools.

2. db2 catalog tcpip node machine2 remote 10.10.60.16 server 50000

3. db2 catalog db sample as sample2 at node machine2

4. db2 connect to sample2 user xxxxxx (or open Control Center)

Does this set up the equivalent to DB links however ? For instance, can
the user now 'connect' to the sample database on machine1, and issue a
query against a table that is in sample database on machine2 ? Surely
something else is needed to define/share the meta data between the two
data dictionaries ?

[Serge Rielau]

Mark B. Townsend wrote:

Mark A wrote:

1. Open the DB2 Command Window on machine 1 (the one without db2 prompt).
See DB2 Command Line Tools.

2. db2 catalog tcpip node machine2 remote 10.10.60.16 server 50000

3. db2 catalog db sample as sample2 at node machine2

4. db2 connect to sample2 user xxxxxx (or open Control Center)

Does this set up the equivalent to DB links however ? For instance, can
the user now 'connect' to the sample database on machine1, and issue a
query against a table that is in sample database on machine2 ? Surely
something else is needed to define/share the meta data between the two
data dictionaries ?

Mark,

check the rest of the thread where wrappers, nicknames, etc are debated.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Mark B. Townsend" <Ma***********@oracle.com> wrote in message news:4ILOd.29

Does this set up the equivalent to DB links however ? For instance, can
the user now 'connect' to the sample database on machine1, and issue a
query against a table that is in sample database on machine2 ? Surely
something else is needed to define/share the meta data between the two
data dictionaries ?

That would require setting up federated support with wrappers and aliases.

But the post just said that he wanted to connect to the sample database on
machine 2 from machine 1. For that, I provided everything he needs.

If he also wants to connect to the sample database on machine 1, and then
access the data on another database (local or remote) without connected to
the other database, then federated support is needed.

[Mark Townsend]

Mark A wrote:

But the post just said that he wanted to connect to the sample database on
machine 2 from machine 1. For that, I provided everything he needs.

He used the term db link so I'm assuming he does want full federated
support. I know I'd be interested in seeing the steps involved in
setting up a federated environment

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:vs********************@comcast.com...

Mark A wrote:

But the post just said that he wanted to connect to the sample database on machine 2 from machine 1. For that, I provided everything he needs.

He used the term db link so I'm assuming he does want full federated
support. I know I'd be interested in seeing the steps involved in
setting up a federated environment

I charge $25 for that one. Send me the money at paypal account
m0****@yahoo.com and I will tell you how it is done.

[Serge Rielau]

Mark Townsend wrote:

Mark A wrote:

But the post just said that he wanted to connect to the sample
database on
machine 2 from machine 1. For that, I provided everything he needs.

He used the term db link so I'm assuming he does want full federated
support. I know I'd be interested in seeing the steps involved in
setting up a federated environment

There is quite a bit of good reading on developerWorks. here are two:
http://www-128.ibm.com/developerwork...0304lurie.html
http://www-128.ibm.com/developerwork...0307lurie.html

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Larry]

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Larry Edelstein

Serge Rielau wrote:

Mark Townsend wrote:

Mark A wrote:

But the post just said that he wanted to connect to the sample
database on
machine 2 from machine 1. For that, I provided everything he needs.

He used the term db link so I'm assuming he does want full federated
support. I know I'd be interested in seeing the steps involved in
setting up a federated environment

There is quite a bit of good reading on developerWorks. here are two:
http://www-128.ibm.com/developerwork...0304lurie.html

http://www-128.ibm.com/developerwork...0307lurie.html
Cheers
Serge

[Thiru]

Hi,
I have istalled DB2 V8.1 ESE in WinXp. When i create the wrapper it
say db2net8.dll is missing? How to solve this issue??
Thiru.
WantedToBeDBA.

[Jan M. Nelken]

Thiru wrote:

Hi,
I have istalled DB2 V8.1 ESE in WinXp. When i create the wrapper it
say db2net8.dll is missing? How to solve this issue??
Thiru.
WantedToBeDBA.

From the documentation:

The relational wrappers are a part of ***DB2 Information Integrator*** that is
used with DB2 Universal Database(TM) for Linux, UNIX(R), and Windows(R) and DB2
Universal Database Enterprise Server Edition.
Relational wrappers are wrappers for non-IBM relational databases. In DB2
Universal Database Version 8, relational wrappers are required if you want to
access data that is stored in Microsoft(R) SQL Server, ODBC, Oracle, Sybase, and
Teradata data sources.

Access to data that is stored in IBM(R) databases (DB2 Universal Database and
Informix(R)) is built into DB2 Universal Database for Linux, UNIX, and Windows.

Did you acquired and installed DB2 Information Integrator (currently named
Websphere Information Integrator)?

Jan M. Nelken

[Jonathan Leffler]

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?
Serge is sometimes seen over there; me too, very occasionally.
Serge Rielau wrote:

Mark Townsend wrote:

Mark A wrote:
But the post just said that he wanted to connect to the
sample database on machine 2 from machine 1. For that, I
provided everything he needs.

He used the term db link so I'm assuming he does want full
federated support. I know I'd be interested in seeing the steps
involved in setting up a federated environment

There is quite a bit of good reading on developerWorks. [...]

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[Mark A]

"Jonathan Leffler" <jl******@earthlink.net> wrote in message
news:d3*****************@newsread1.news.pas.earthl ink.net...

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Serge is sometimes seen over there; me too, very occasionally.

Larry did not ask if Serge posts over there. He asked if anyone from IBM
"asks people what the steps are to do things in Oracle."

[Mark Townsend]

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Based on what I've seen of the some of the anti-Oracle competitive
material produced by IBM I would guess not.

[Thiru]

No. We don't have licence for DB2 Information Integrator. Then i have
to try to get some trial version for practice...

Thanks,
Thiru.
WantedToBeDBA.

[Serge Rielau]

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

On a hobby basis I do that. I don't see any harm in fostering a
technical understanding of each others products.
I'd much rather prefer technical questions that flamewars.
We just concluded an, in parts, technical discussion of isolation levels
vs, locking and we compared Orcale Streams and DataGuard with HADR and
Q-replication in c.d.o.server.

Chere
Serge

PS: I presume you meant IBM DB2, because I'm quite certain that there
are at least as many IBM IGS/BCS folks in c.d.o.server as there are in
c.d.ibm-db2

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[DA Morgan]

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Larry Edelstein

You mean like Serge and MarkA?

Seriously ... as long as it isn't trolling what's the issue? Hardly
anyone works in an environment where there is only a single database
vendor. And last time I checked software was a tool ... not a religion.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[Larry]

Not the point, Daniel. The point is it's a bit of an annoyance to have
someone call into question what is involved with doing certain things in
one's product on an internet ng that is supposed to be dedicated to
people getting technical answers.

Larry Edelstein

DA Morgan wrote:

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Larry Edelstein

You mean like Serge and MarkA?

Seriously ... as long as it isn't trolling what's the issue? Hardly
anyone works in an environment where there is only a single database
vendor. And last time I checked software was a tool ... not a religion.

[Serge Rielau]

DA Morgan wrote:

Larry wrote:

Does IBM have anyone who hangs out on comp.databases.oracle.server and
asks people what the steps are to do things in Oracle?

Larry Edelstein

You mean like Serge and MarkA?

To the best of my knowledge Mark A. Isn't an IBM employee.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Larry wrote:

The point is it's a bit of an annoyance to have
someone call into question what is involved with doing certain things in
one's product on an internet ng that is supposed to be dedicated to
people getting technical answers.

The point is that somebody asked specifically how to do something in DB2
which has still has not yet been answered by anyone on this ng, despite
it being dedicated to people getting technical answers. The one answer
that was given was not the correct one for the question posed.

[Serge Rielau]

Mark Townsend wrote:

Larry wrote:

The point is it's a bit of an annoyance to have someone call into
question what is involved with doing certain things in one's product
on an internet ng that is supposed to be dedicated to people getting
technical answers.

The point is that somebody asked specifically how to do something in DB2
which has still has not yet been answered by anyone on this ng, despite
it being dedicated to people getting technical answers. The one answer
that was given was not the correct one for the question posed.

Timeout!
Here is the reaction to the very first answer the original poster got:
"Larry,

Thanks for the information. Thats exactly what I was looking for.

Nick"

So.. either Firefox is messing something seriously up in my thread or
I'm unclear what the two of you are quibbling about.
Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?

If there are any issues with usenet.. I recommend www.individual.net as
a timely mirror and Firefox is doing a fine job showing the threads....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

> To the best of my knowledge Mark A. Isn't an IBM employee.

Cheers

That is correct. I am an independent consultant.

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message

The one answer
that was given was not the correct one for the question posed.

I would say that the question was not correct for he wanted to know.

He just said that he wanted to connect to a database on machine 2 from
machine 1. He did not say he wanted to be connected to a database on machine
1 and use that same connection to access a database on machine 2.

[DA Morgan]

Mark A wrote:

"Mark Townsend" <ma***********@comcast.net> wrote in message

The one answer
that was given was not the correct one for the question posed.

I would say that the question was not correct for he wanted to know.

He just said that he wanted to connect to a database on machine 2 from
machine 1. He did not say he wanted to be connected to a database on machine
1 and use that same connection to access a database on machine 2.

Perhaps this is just another one of those displays of my ignorance but
I thought the subject at the top of this thread defined the intent of
the OP's question. What an Oracle database link does is well documented.
The answer provided, as I understand it, does not do the same thing. So
while it may have answered the OP's question ... it did not address the
subject which is an equivalence to another product's functionality.

But lets not belabour the point ... I think there is room here for
everyone to feel like the communications have been less than clear.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[Mark A]

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108186099.283356@yasure...

Perhaps this is just another one of those displays of my ignorance but
I thought the subject at the top of this thread defined the intent of
the OP's question. What an Oracle database link does is well documented.
The answer provided, as I understand it, does not do the same thing. So
while it may have answered the OP's question ... it did not address the
subject which is an equivalence to another product's functionality.

But lets not belabour the point ... I think there is room here for
everyone to feel like the communications have been less than clear.
--
Daniel A. Morgan

Here is the post I responded to from WantedToBeDBA, who is NOT the creator
of this thread (OP), so he did NOT make up the thread title which mentioned
Oracle DB Links:

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..
----------------------------------------------------------------------------
--------

Based on the above, and based on the fact that WantedToBeDBA did not create
this thread, I believe I answered the question exactly as it was asked.

I am still not absolutely convinced that WantedToBeDBA needs DB2 Federated
support, but it is hard to know based on the information provided. Unless he
needs to include tables from databases on each machine in the same SQL
statement, he probably doesn't need Federated and could suffice with the
instructions I gave him.

So, yes Daniel, your confusion of the OP by Nick Palmer with the post by
WantedToBeDBA that I answered, does display your ignorance of exactly what
happened.

:

[Mark Townsend]

Mark A wrote:

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108186099.283356@yasure...

Perhaps this is just another one of those displays of my ignorance but
I thought the subject at the top of this thread defined the intent of
the OP's question. What an Oracle database link does is well documented.
The answer provided, as I understand it, does not do the same thing. So
while it may have answered the OP's question ... it did not address the
subject which is an equivalence to another product's functionality.

But lets not belabour the point ... I think there is room here for
everyone to feel like the communications have been less than clear.
--
Daniel A. Morgan

Here is the post I responded to from WantedToBeDBA, who is NOT the creator
of this thread (OP), so he did NOT make up the thread title which mentioned
Oracle DB Links:

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..
----------------------------------------------------------------------------
--------

Based on the above, and based on the fact that WantedToBeDBA did not create
this thread, I believe I answered the question exactly as it was asked.

I am still not absolutely convinced that WantedToBeDBA needs DB2 Federated
support, but it is hard to know based on the information provided. Unless he
needs to include tables from databases on each machine in the same SQL
statement, he probably doesn't need Federated and could suffice with the
instructions I gave him.

So, yes Daniel, your confusion of the OP by Nick Palmer with the post by
WantedToBeDBA that I answered, does display your ignorance of exactly what
happened.

What do you take "Can you guys help me establishing db link between
these 2 system." to actually mean ?

[Mark Townsend]

Serge Rielau wrote:

Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?

They show too much detail, in that they show how to sent up a connection
between different vendors databases. The corollorary in Oracle would be
setting up a Transparent Gateway. I'm assuming that it's a simpler to
set up the equivalent of an Oracle to Oracle database link between two
DB2 instances ?

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:42**********@comcast.net...

What do you take "Can you guys help me establishing db link between
these 2 system." to actually mean ?

The exact quote was (you conveniently left out part of it):

"Can you guys help me establishing db link between these 2 system. I want to
access machine 2 from machine 1."

Note the word "machine" used in the second sentence. With the instructions I
gave him, he can be using machine 1 and connect to the database on machine
2.

Nowhere did he say that he needs to reference 2 tables located on different
machines in a single SQL statement. In an application program or script he
could have connected to each database separately if each individual SQL
statement only referenced tables from one location.

In any case, he first needed to do what I told him in order to establish
federated support.

[DA Morgan]

Mark A wrote:

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108186099.283356@yasure...

Perhaps this is just another one of those displays of my ignorance but
I thought the subject at the top of this thread defined the intent of
the OP's question. What an Oracle database link does is well documented.
The answer provided, as I understand it, does not do the same thing. So
while it may have answered the OP's question ... it did not address the
subject which is an equivalence to another product's functionality.

But lets not belabour the point ... I think there is room here for
everyone to feel like the communications have been less than clear.
--
Daniel A. Morgan

Here is the post I responded to from WantedToBeDBA, who is NOT the creator
of this thread (OP), so he did NOT make up the thread title which mentioned
Oracle DB Links:

Hi,

Machine 1
---------
ip: 10.10.60.3
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : WinXP(Sp2)

Machine 2:
----------

ip: 10.10.60.16
DB2 V8.1 installed.
Database Name : sample
Running port : 50000(default)
OS : Win2k Professional

Can you guys help me establishing db link between these 2 system. I
want to access machine 2 from machine 1..
----------------------------------------------------------------------------
--------

Based on the above, and based on the fact that WantedToBeDBA did not create
this thread, I believe I answered the question exactly as it was asked.

I am still not absolutely convinced that WantedToBeDBA needs DB2 Federated
support, but it is hard to know based on the information provided. Unless he
needs to include tables from databases on each machine in the same SQL
statement, he probably doesn't need Federated and could suffice with the
instructions I gave him.

So, yes Daniel, your confusion of the OP by Nick Palmer with the post by
WantedToBeDBA that I answered, does display your ignorance of exactly what
happened.

:

But not of the fact that the answer you provided does not do what the
subject asks.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[DA Morgan]

Mark A wrote:

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:42**********@comcast.net...

What do you take "Can you guys help me establishing db link between
these 2 system." to actually mean ?

The exact quote was (you conveniently left out part of it):

"Can you guys help me establishing db link between these 2 system. I want to
access machine 2 from machine 1."

Note the word "machine" used in the second sentence. With the instructions I
gave him, he can be using machine 1 and connect to the database on machine
2.

Nowhere did he say that he needs to reference 2 tables located on different
machines in a single SQL statement. In an application program or script he
could have connected to each database separately if each individual SQL
statement only referenced tables from one location.

In any case, he first needed to do what I told him in order to establish
federated support.

Which still leaves open the question posed in the subject that no one
has yet to address: Does DB2 have the capability to do so?

Why is it easier to feign mock insult than just to address the question?
A "Yes" or "No" answer would suffice. The syntax if "Yes" would be a plus.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[Mark A]

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108235157.454208@yasure...

But not of the fact that the answer you provided does not do what the
subject asks.
--
Daniel A. Morgan

The person's post I responded to did not create the subject line in the
post. Lot's of people post onto to threads that do not exactly match the
original subject line.

In any event., what I told was the first step needed to establish federated
support (if that is what he actually needs).

[Mark A]

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108235309.408771@yasure...

Which still leaves open the question posed in the subject that no one
has yet to address: Does DB2 have the capability to do so?

Why is it easier to feign mock insult than just to address the question?
A "Yes" or "No" answer would suffice. The syntax if "Yes" would be a plus.
--
Daniel A. Morgan

I came into the thread a bit late, and others already answered that
federated support would handle the situation requested by the OP. This was
first answered by Larry Edelstein on 2/8/2005, about 1 hour after that OP
asked the question .

The fact that the OP was answered correctly is one reason that I presumed
that the post I responded to was a bit different than the OP with the
specific situation cited, even if it was in the same thread.

But to repeat, the answer is yes, you need to configure federated support to
accomplish having one SQL statement that accesses two different databases
(local or remote). Between two DB2 databases, federated support is included
with DB2 Server licenses. If the databases are heterogonous (DB2 and Oracle)
then an add-on DB2 product is needed which cost extra. Not being from IBM, I
don't how much extra.

[Mark Townsend]

Mark A wrote:

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:42**********@comcast.net...

What do you take "Can you guys help me establishing db link between
these 2 system." to actually mean ?

The exact quote was (you conveniently left out part of it):

"Can you guys help me establishing db link between these 2 system. I want to
access machine 2 from machine 1."

No, I quoted specifically (and not at all conveniently) to focus your
attention on what was actually asked. The term db link was used in
question in a thread on how to build the equivalent of Oracle's DB Link.
I doubt many people could misread that to mean something entirely
different.

If you take the "I want to access machine 2 from machine 1." out of
context of the thread, or event the preceding sentence, which is what
you seem to want to do, then wouldn't it simply imply telnet acess or
something similar ?

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:XIydnWLpXreJw5PfRVn-

No, I quoted specifically (and not at all conveniently) to focus your
attention on what was actually asked. The term db link was used in
question in a thread on how to build the equivalent of Oracle's DB Link.
I doubt many people could misread that to mean something entirely
different.

If you take the "I want to access machine 2 from machine 1." out of
context of the thread, or event the preceding sentence, which is what
you seem to want to do, then wouldn't it simply imply telnet acess or
something similar ?

In the post I answered from WantedToBeDBA, he used the phase "db link" (no
caps). Because the name of the Oracle feature you are referring to is "DB
Links" (with caps and with Links as plural) I took the phrase "db link" to
be generic, especially since Larry already said that DB2 Federated
configuration was needed to accomplish what "DB Links" provided.

If WantedToBeDBA had asked, "how do I configure DB2 Federated support for
the above configuration" (he was already told he needed Federated
configuration for DB Links functionality), then my answer would have been
more complete, although the answer I provided is the first step needed.

Are you suggesting that I deliberately mislead WantedToBeDBA, or is this
just payback for making some people on the Oracle newsgroup look like
hypocrites when they bash the TPC? (www.tpc.org). You and Daniel Morgan seem
to be engaging in some coordinated attacks against me that are frankly, very
petty.

[Mark Townsend]

Mark A wrote:

Are you suggesting that I deliberately mislead WantedToBeDBA, or is this
just payback for making some people on the Oracle newsgroup look like
hypocrites when they bash the TPC? (www.tpc.org).
The third alternative could just be that I'm interested in knowing how
something done in Oracle is also done in DB2 ?

So far, I've read all the replies, followed all the links, and I still
don't know. I do now know from your reply this morning that I do need
to "configure federated support". Still not sure what is involved in
that however - definitions of wrappers, nicknames, servers etc ? Seems a
little overkill for a simple DB2 to DB2 link (why do I need a wrapper
and a server definition - won't the nickname suffice ? That's
effectively all that is required in Oracle). Guess I will have the crack
the doc.
You and Daniel Morgan seem
to be engaging in some coordinated attacks against me that are frankly, very
petty.

Stop it. It's pure paranoia.

[Mark Townsend]

> Guess I will have the crack

the doc.

So from what I can see from the doc, the answer is something like this ?

1. Catalog the node

For the example given, perhaps the following command

CATALOG TCPIP NODE machine2 REMOTE 10.10.60.16 SERVER 50000

2. Catalog the remote database

For the example given, perhaps the following command

CATALOG DATABASE sample AS sample2 AT NODE machine2 AUTHENTICATION SERVER

3. Create the wrapper

For the example given, on the OS's given, I don't believe DRDA is
deployed by default, so the default DRDA wrapper cannot be used ?
Instead, perhaps something like this ?

CREATE WRAPPER sample_wrapper LIBRARY ’db2drda.dll’

4. Create the server definition and set the server options

CREATE SERVER sample TYPE DB2/NT VERSION 8.1 WRAPPER sample_wrapper
AUTHORIZATION "xxxx" PASSWORD "yyyyy" OPTIONS (DBNAME ’sample’)

Did I use the right type (i.e is DB2/NT the right type for t a UDB
database running on Win2K professional ?). Obviously I also don't have
the right username/password to use either - but do I need one given that
I defined the authentication model as "AUTHENTICATION SERVER". Question
- can you set this up so that each user of the connection needs to
authenticate themselves with the target database at the time that they
use the connection ?

What other options should be specified here ? I note the PUSHDOWN option
which is analogous to the remote_join hint in Oracle ? Is this pretty
much mandatory to stop data from being shipped in the wrong direction
when joined (or aggregated etc) ?

5. Create the user mappings

This one I didn't grok, but I think it's the answer to the
authentication model question I answered above ? Why does it have to be
defined ahead of time ?

6. Test the connection to the DB2 server

So here I note that I have to specify the server I want ahead of the
query ? So to test I would do something like the following from the
sample database on machine1 ? Hmm - both databases are called smaple -
do I have a name collision problem ?

SET PASSTHRU sample
SELECT count(*) FROM some_remote_table
SET PASSTHRU RESET

How do you do a join across both systems using this syntax ?
7. Create the nicknames for tables and views

OK - so to get around the SET PASSTHRU problem I need to create
'nicknames' for each remote table a la
CREATE NICKNAME DB2SALES FOR SAMPLE.SALESDATA.EUROPE

Do I need to do this for each remote table ? Can I just specify the
target in the SQL statement ? For instance

SELECT count(*)
from customer c,
sample.salesdata.europe s
where c.status = "VALID" and c.id=s.cust_id

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message >

Seems a
little overkill for a simple DB2 to DB2 link (why do I need a wrapper
and a server definition - won't the nickname suffice ? That's
effectively all that is required in Oracle). Guess I will have the crack
the doc.

I didn't design it, so you will have to ask someone else.

[DA Morgan]

Mark A wrote:

You and Daniel Morgan seem
to be engaging in some coordinated attacks against me that are frankly, very
petty.

Actually the last time Mark and I exchanged either words, or emails, was
months ago. And I can tell you rather frankly you not only weren't the
point of our exchange ... we could have talked for weeks and I doubt
you'd have come up. Your sense of self-importance aside ... you just
aren't on the radar screen.

I can only speak for myself as to why this thread is of interest so I
will. Many of my students are simultaneously working in more than one
commercial RDBMS. They often ask questions such as "this is how I do it
in product "A" what is the equivalent in product "B". So a straight,
non-paranoid, response to the question would be of value.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[Mark A]

"DA Morgan" <da******@x.washington.edu> wrote in message
news:1108249709.449640@yasure...

I can only speak for myself as to why this thread is of interest so I
will. Many of my students are simultaneously working in more than one
commercial RDBMS. They often ask questions such as "this is how I do it
in product "A" what is the equivalent in product "B". So a straight,
non-paranoid, response to the question would be of value.
--
Daniel A. Morgan

I did just that. I answered the question that was asked, exactly as it was
asked. The question about the DB2 equivalent to Oracle DB Links was already
answered by someone else long before I posted.

[Serge Rielau]

Mark Townsend wrote:

Serge Rielau wrote:

Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?

They show too much detail, in that they show how to sent up a connection
between different vendors databases. The corollorary in Oracle would be
setting up a Transparent Gateway. I'm assuming that it's a simpler to
set up the equivalent of an Oracle to Oracle database link between two
DB2 instances ?

Here is how I set up local DB2 -> DB2 testing when I need it:

catalog local node local instance regress;

commit;
create wrapper DRDA;
commit;
create server loop type DB2/CS VERSION 8.0 WRAPPER DRDA
AUTHORIZATION "xxxxx" PASSWORD "yyyyy"
options (Node 'LOCAL',DBNAME 'GLOBALDB');

create user mapping for zzzzzz
server loop
options (Remote_authid 'xxxx',
Remote_password 'yyyyy');

-- done.. from now on it's about declaring the tables one wants to see.

create nickname nickname1 for loop.blahschema.blahtable;

To use a remote node here is the syntax diagram (from Command reference
manual):

-CATALOG--+-------+--TCPIP NODE-- nodename --------------------->

'-ADMIN-'

(1)--REMOTE--+- hostname ---+---------SERVER-- service-name ----------> '- IP address -'
--+----------------+--+--------------------------------+-------> '-SECURITY SOCKS-' '-REMOTE_INSTANCE-- instance-name -'
--+---------------------+--+-------------------------------+---> '-SYSTEM-- system-name -' '-OSTYPE-- operating-system-type -'
--+------------------------+----------------------------------><

'-WITH-- "comment-string" -'

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jan M. Nelken]

Serge Rielau wrote:

Here is how I set up local DB2 -> DB2 testing when I need it:

catalog local node local instance regress;

Oh - so this is how you do this. Interesting ....

Jan M. Nelken

[Serge Rielau]

Mark Townsend wrote:

> Guess I will have the crack

the doc.

So from what I can see from the doc, the answer is something like this ?

1. Catalog the node

For the example given, perhaps the following command

CATALOG TCPIP NODE machine2 REMOTE 10.10.60.16 SERVER 50000

2. Catalog the remote database

For the example given, perhaps the following command

CATALOG DATABASE sample AS sample2 AT NODE machine2 AUTHENTICATION SERVER

3. Create the wrapper

For the example given, on the OS's given, I don't believe DRDA is
deployed by default, so the default DRDA wrapper cannot be used ?
Instead, perhaps something like this ?

CREATE WRAPPER sample_wrapper LIBRARY ’db2drda.dll’

DRDA is the only protocol used by DB2 UDB fro LUW . So it's always
there. Se emy other post for simplified notation.
4. Create the server definition and set the server options

CREATE SERVER sample TYPE DB2/NT VERSION 8.1 WRAPPER sample_wrapper
AUTHORIZATION "xxxx" PASSWORD "yyyyy" OPTIONS (DBNAME ’sample’)

Did I use the right type (i.e is DB2/NT the right type for t a UDB
database running on Win2K professional ?). Yes. Syntax diagrams would start looking funny if IBM rode each renaming
bump of MS ;-)

Obviously I also don't have the right username/password to use either - but do I need one given that
I defined the authentication model as "AUTHENTICATION SERVER". Question
- can you set this up so that each user of the connection needs to
authenticate themselves with the target database at the time that they
use the connection ? From the docs it appears this is for whatever CREATE SERVER has to do
on the source system. So once the server is created that's in. No
worries for Joe user.
What other options should be specified here ? I note the PUSHDOWN option
which is analogous to the remote_join hint in Oracle ? Is this pretty
much mandatory to stop data from being shipped in the wrong direction
when joined (or aggregated etc) ? In general you use the default for the options.
DB2 has default settings for different versions of different products
which is why the version needs to be specified.
Tehse options can be very detailed. For example the wrapper would know
in which version of the target HASHJOIn was introduced, ...
If PSHDOWN is disabled that would take out teh main reason to use
Websphere II. So yes. It's pretty much mandatory and it is the default.
5. Create the user mappings

This one I didn't grok, but I think it's the answer to the
authentication model question I answered above ? Why does it have to be
defined ahead of time ? Call it single-sign on.... If DB2 joins data from 5 different DBMS who
wants to type in all those passwords on every connect?
It is interesting to note that user-access to nicknames can never be
encapsulated. I.e. I cannot refer to a nickna in a procedure, grant
execute to public and allow the masses to read.
I used to hate this breakage with normal SQL rules, but in the federated
world this turns out to be a requirement....
6. Test the connection to the DB2 server

So here I note that I have to specify the server I want ahead of the
query ? So to test I would do something like the following from the
sample database on machine1 ? Hmm - both databases are called smaple -
do I have a name collision problem ?

SET PASSTHRU sample
SELECT count(*) FROM some_remote_table
SET PASSTHRU RESET SET PASSTHRU (besides to do a sanity test) is used to specify
statements which are not supported by DB2.
A good example would be running a DDL script. I don't know off hand
whether DB2 supports remote DDL when the target is DB2, but I doubt it..
there are more important things to work on ....
DB2 supports aliasing of DB names so you can catalog the remote database
under a different name locally.
How do you do a join across both systems using this syntax ? Wrong tool. Used for DDL (or if you don't have a nickname?), not for
general DML.
7. Create the nicknames for tables and views

OK - so to get around the SET PASSTHRU problem I need to create
'nicknames' for each remote table a la
CREATE NICKNAME DB2SALES FOR SAMPLE.SALESDATA.EUROPE

Do I need to do this for each remote table ? Can I just specify the
target in the SQL statement ? For instance

SELECT count(*)
from customer c,
sample.salesdata.europe s
where c.status = "VALID" and c.id=s.cust_id

For each table you want to use. Yes.
There is a known requirement to support "3-part" table names.
Really nicknames and 3-part-names address two different issues.
When using a nickname DB2 catalogs all the good stuff about the table in
its local catalog. Including indexes, stats, etc, ...
This cuts down on the compile-time
3-part-table names address a different issues. That would be near random
access to tables, or table which have a volatile schema.
IMHO both approaches are valuable.
A note on the side. AFAIK, for IBM customers homogeneity of the data
sources is the exception. Websphere II is aimed at a heterogeneous
environment.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

DB2 has default settings for different versions of different products
which is why the version needs to be specified.
Doesn't this get ugly if one of the targets is upgraded ? Can you tell
from a global catalog who is impacted if you do change a version ?
Call it single-sign on.... If DB2 joins data from 5 different DBMS who
wants to type in all those passwords on every connect?
I still don't get it. The username/password is not mandatory right ?
Can't the connections be proxied using the privileges of the username
defined in the server definition ? Or does every user have to be
identified everywhere ?
It is interesting to note that user-access to nicknames can never be
encapsulated. I.e. I cannot refer to a nickna in a procedure, grant
execute to public and allow the masses to read.
I used to hate this breakage with normal SQL rules, but in the federated
world this turns out to be a requirement....
Hmm. Oracle has public and private DB Links.

SET PASSTHRU (besides to do a sanity test) is used to specify
statements which are not supported by DB2.
Doesn't the WRAPPER definition do the SQL munge ? Thats what the
(equivalent I think) TGs do in Oracle (of course, Oracle to Oracle you
don't need a TG)
Really nicknames and 3-part-names address two different issues.
When using a nickname DB2 catalogs all the good stuff about the table in
its local catalog. Including indexes, stats, etc, ...
Just cache it the first time the remote object is accessed, just like
you would if it's a local table. And I'm still not sure why you need to
do this for DB2 to DB2. Just have the optimizer access the remote stats
and cost accordingly. No need to munge/translate as the source and
target are both well understood.
A note on the side. AFAIK, for IBM customers homogeneity of the data
sources is the exception.

Yep - I'm guessing they keep running into a lot of Oracle :-)

[Mark Townsend]

Serge Rielau wrote:

Mark Townsend wrote:

Serge Rielau wrote:

Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?

They show too much detail, in that they show how to sent up a
connection between different vendors databases. The corollorary in
Oracle would be setting up a Transparent Gateway. I'm assuming that
it's a simpler to set up the equivalent of an Oracle to Oracle
database link between two DB2 instances ?

Here is how I set up local DB2 -> DB2 testing when I need it:

catalog local node local instance regress;

commit;
create wrapper DRDA;
commit;
create server loop type DB2/CS VERSION 8.0 WRAPPER DRDA
AUTHORIZATION "xxxxx" PASSWORD "yyyyy"
options (Node 'LOCAL',DBNAME 'GLOBALDB');

create user mapping for zzzzzz
server loop
options (Remote_authid 'xxxx',
Remote_password 'yyyyy');

-- done.. from now on it's about declaring the tables one wants to see.

create nickname nickname1 for loop.blahschema.blahtable;

In Oracle that would be

CREATE DATABASE LINK orcl USING 'orcl';

Usage of the form (assuming logged on as SCOTT)

SELECT count(*) FROM dept@orcl;

SELECT count(*)
FROM dept l, dept@orcl r
WHERE l.deptno = r.deptno;

Typically the optimiser (in later versions) will work out which way to
ship the join, rewriting the SQL as necessary, if it gets it wrong you
can hint it;

SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, de**@remote.com
WHERE emp.deptno = dept.deptno;
Note that you can also execute RPCs using a similar notation

being
hire_emp@orcl(:empid)
end;

Can you do this in DB2 ?

Does the IBM implementation also support 2PC ? i.e

BEGIN
UPDATE scott.dept@orcl
SET loc = 'NEW YORK'
WHERE deptno = 10;
UPDATE scott.emp
SET deptno = 11
WHERE deptno = 10;
END;
ROLLBACK;

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message

Can you do this in DB2 ?

Does the IBM implementation also support 2PC ?

Just in case anyone is unaware, Mark Townsend is an employee of Oracle
Corporation, selling his wares on this newsgroup.

I don't recall anyone asking in this thread about how Oracle provides
Federated support.

[Mark Townsend]

Mark A wrote:

"Mark Townsend" <ma***********@comcast.net> wrote in message

Can you do this in DB2 ?

Does the IBM implementation also support 2PC ?

Just in case anyone is unaware, Mark Townsend is an employee of Oracle
Corporation, selling his wares on this newsgroup.

I don't recall anyone asking in this thread about how Oracle provides
Federated support.

Mark - calm down. Take a chill pill, Dude

It's well known who I am on this ng. I've been posting with a
pro-Oracle sentiment here for years, and I even have a mention in the
IBM Informix FAQ where I've been posting for well over a decade.

I'm not trying to sell anybody anything (not even advice at $15 dollars
a pop).

I really _do_ want to know if the IBM DB2 to DB2 federated
implementation supports 2PC and RPCs.

Do I have to go to the manuals again ? Sigh.