|
|
|
|
|
|
| Author |
Message |
Mark Townsend
*nix forums Guru Wannabe
Joined: 02 Apr 2005
Posts: 213
|
 Posted: Sat Feb 12, 2005 4:14 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Serge Rielau wrote:
| Quote: | 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 ? |
|
| Back to top |
|
 |
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 5:25 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"Mark Townsend" <markbtownsend@comcast.net> wrote in message
news:420E373E.109@comcast.net...
| Quote: |
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. |
|
| Back to top |
|
|
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Sat Feb 12, 2005 6:08 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark A wrote:
| Quote: | "DA Morgan" <damorgan@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
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Sat Feb 12, 2005 6:11 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark A wrote:
| Quote: | "Mark Townsend" <markbtownsend@comcast.net> wrote in message
news:420E373E.109@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
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 6:20 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"DA Morgan" <damorgan@x.washington.edu> wrote in message
news:1108235157.454208@yasure...
| Quote: | 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). |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 6:32 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"DA Morgan" <damorgan@x.washington.edu> wrote in message
news:1108235309.408771@yasure...
| Quote: | 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. |
|
| Back to top |
|
|
Mark Townsend
*nix forums Guru Wannabe
Joined: 02 Apr 2005
Posts: 213
|
| Posted: Sat Feb 12, 2005 6:50 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark A wrote:
| Quote: | "Mark Townsend" <markbtownsend@comcast.net> wrote in message
news:420E373E.109@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 ? |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 7:11 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"Mark Townsend" <markbtownsend@comcast.net> wrote in message
news:XIydnWLpXreJw5PfRVn-
| Quote: |
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. |
|
| Back to top |
|
|
Mark Townsend
*nix forums Guru Wannabe
Joined: 02 Apr 2005
Posts: 213
|
| Posted: Sat Feb 12, 2005 7:24 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark A wrote:
| Quote: |
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.
| Quote: | 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. |
|
| Back to top |
|
|
Mark Townsend
*nix forums Guru Wannabe
Joined: 02 Apr 2005
Posts: 213
|
| Posted: Sat Feb 12, 2005 8:02 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
| Quote: | 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 |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 8:59 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"Mark Townsend" <markbtownsend@comcast.net> wrote in message >
| Quote: | 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. |
|
|
| Back to top |
|
|
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Sat Feb 12, 2005 10:11 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark A wrote:
| Quote: | 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
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
Vincent M
*nix forums Guru
Joined: 22 Feb 2005
Posts: 1332
|
| Posted: Sat Feb 12, 2005 10:16 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
"DA Morgan" <damorgan@x.washington.edu> wrote in message
news:1108249709.449640@yasure...
| Quote: |
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. |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Sat Feb 12, 2005 10:19 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Mark Townsend wrote:
| Quote: | 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):
| Quote: | -CATALOG--+-------+--TCPIP NODE-- nodename ---------------------
'-ADMIN-' |
(1)
| Quote: | --REMOTE--+- hostname ---+---------SERVER-- service-name ----------
'- IP address -' |
| Quote: | --+----------------+--+--------------------------------+-------
'-SECURITY SOCKS-' '-REMOTE_INSTANCE-- instance-name -' |
| Quote: | --+---------------------+--+-------------------------------+---
'-SYSTEM-- system-name -' '-OSTYPE-- operating-system-type -' |
| Quote: | --+------------------------+----------------------------------
'-WITH-- "comment-string" -' |
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Jan M. Nelken
*nix forums Guru Wannabe
Joined: 26 May 2005
Posts: 126
|
| Posted: Sat Feb 12, 2005 10:36 pm Post subject:
Re: Equivilant of Oracle's DB Links in DB2
|
|
|
Serge Rielau wrote:
| Quote: | 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 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 3:12 am | All times are GMT
|
|
Debt Consolidation | Credit Cards | Banruptcy | Proxy | Loan
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
