By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,670 Members | 1,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,670 IT Pros & Developers. It's quick & easy.

Equivilant of Oracle's DB Links in DB2

P: n/a
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
Nov 12 '05
Share this Question
Share on Google+
63 Replies


P: n/a
"Mark Townsend" <ma***********@comcast.net> wrote in message
news:raKdnYfMw_5kK5PfRVn-
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.

1. Maybe you are well-known to some on this newsgroup as an Oracle employee,
but not known to everyone. I think you should include that information at
the end of your posts just like the IBM employees do.

2. If this were the Oracle forum, then Daniel (Headmaster) Morgan would tell
you to RTFM's and don't come back until you have a specific question about
what you read.

3. I checked my paypal account, and can you believe that guy has still not
paid the $15 yet? I guess I will have to write that one off as an
uncollectible receivable.
Nov 12 '05 #51

P: n/a
1. Maybe you are well-known to some on this newsgroup as an Oracle employee,
but not known to everyone. I think you should include that information at
the end of your posts just like the IBM employees do.
Well - some do (Knut), some don't (Larry), some do or don't depending on
which account/machine they are posting from (Serge).

I have a rule in that I only try to post on my own time, not Oracle's (I
don't really think they pay me to dick around on the ngs).

So as such I'm nearly always at home when I post, hence on my home
machine, which has no signature on it all, cause thats just plan
pretentious.

No conspiracy theory.

2. If this were the Oracle forum, then Daniel (Headmaster) Morgan would tell
you to RTFM's and don't come back until you have a specific question about
what you read.
I did read the manual. I can't see anything in the Federated System
Guide about Remote Procedure Calls or Two Phase Commit (at least, not in
the index). It's possible that I need a jargon pointer.

Anyhow - just checked the section on transactional control and have my
answer about 2PC, thank you. Still looking for anything on RPCs - care
to give me a (freebie) pointer
3. I checked my paypal account, and can you believe that guy has still not
paid the $15 yet? I guess I will have to write that one off as an
uncollectible receivable.


Funny. FYI - My new definition of DB2 is now an unconnectable
receptacle, and I've been writing it off for years.

Nov 12 '05 #52

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

Funny. FYI - My new definition of DB2 is now an unconnectable
receptacle, and I've been writing it off for years.

You must have not been able to complete the write-off, since you are still
posting in this thread.
Nov 12 '05 #53

P: n/a
Mark A wrote:

You must have not been able to complete the write-off, since you are still
posting in this thread.

Well - Serge and I often continue our diatribes in private when needed.
It's a little hard to post offline to a non-existent email account.

Nov 12 '05 #54

P: n/a
Mark Townsend wrote:
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 ?


You know what Mark...there is another "alternative". The fourth
alternative is that you could be trying usurp a legitimate innocent
technical Q&A to deliberately draw a negative conclusion about what in
your opinion is something that you don't like about DB2. That is what I
meant when I said originally that I wonder if IBM employees hang around
Oracle newsgroups trying to draw negative attention to Oracle features
and functions. It's downright obnoxious if you ask me.

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.
See ... even here ... you're being negative and critical. So you've
apparently concluded that this is too involved or complex for you. But
you aren't even drawing any kind of comparison in terms of what happens
under the covers with both products and you conveniently manage to leave
out any drawbacks about Oracle links. And you don't draw any comparisons
in terms of functionality. All you care about is picking apart whatever
you can to make negative statements about DB2. Does Oracle have nothing
that can be picked on? Do you think it is really easy to install, set
up, and configure Oracle RAC and get it optimally tuned? Do you think
it's easy to set up the objects, logical volumes, tables, etc in the
TPC-H benchmark for Oracle as documented in the full disclosure reports?

Please ... there is nothing wrong with asking legitimate technical
questions on an internet NG. But please stop adding your editorial and
leading comments. People in glass houses shouldn't throw stones.

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.

Larry E.

Nov 12 '05 #55

P: n/a
Comments inline

Larry wrote:
Mark Townsend wrote:
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 ?

You know what Mark...there is another "alternative". The fourth
alternative is that you could be trying usurp a legitimate innocent
technical Q&A to deliberately draw a negative conclusion about what in
your opinion is something that you don't like about DB2.


At the time the thread started I didn't know anything about this area of
DB2.
That is what I
meant when I said originally that I wonder if IBM employees hang around
Oracle newsgroups trying to draw negative attention to Oracle features
and functions. It's downright obnoxious if you ask me.
Sorry to offend you.

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.

See ... even here ... you're being negative and critical. So you've
apparently concluded that this is too involved or complex for you. But
you aren't even drawing any kind of comparison in terms of what happens
under the covers with both products and you conveniently manage to leave
out any drawbacks about Oracle links.


There's really not that many drawbacks with DB links, except for one
minor one and one major one. The minor one I've already identified in
this thread - sometimes you need to hint the optimizer so that it
optimizes the distributed query correctly. In earlier releases this used
to be pretty much all the time, in later releases, not so much. The
major one is truely major, and relates to a possible security hole, so
I'm not going to discuss it in this ng. Needless to say, most Oracle
users are familiar with the major one, and have taken the necessary
measures to avoid it (and the problem is fixed in 10g).

And I do think the DB2 setup is a little over-engineerd for the simple
DB2 to DB2 case. But I do understand that this case represents the
smaller subset of a great capability, so as such the simple case pays
the price for the greater good. I also think that with Oracle, the
situation is slightly reversed. I would argue that for DB2 users, most
of the external databases they need to interface with in their
environment are _not_ DB2 LUW, so for DB2 users the simple case is not
the common case. For Oracle users, however, most of the other databases
they need to interface with are in fact Oracle. In whcih case the simple
case is indeed the common case. So it's horses for courses.
And you don't draw any comparisons
in terms of functionality.
I still don't know if RPCs are supported or not. I now know that 2PC
isn't but I don't regard that as a huge problem in th real world. On
other areas, the functionality seems reasonably similar, except that the
DB2 implementation seems slightly more suited to 'static' pre-defined
access as opposed to 'dyanmic' ad hoc access. I suspect this is
something to do with the binding of plans ahead of time, which is a
major difference between the IBM and Oracle approach. Would you agree ?
All you care about is picking apart whatever
you can to make negative statements about DB2. Does Oracle have nothing
that can be picked on?
Sure. In fact, plently. And both of us have seen the documents from IBM
that do just this.
Do you think it is really easy to install, set
up, and configure Oracle RAC and get it optimally tuned?
Surprisingly enough, yes. It's really no more difficult than tuning a
system for an SMP environment. And the first time you set up a cluster
it's a steep learning curve, but once you have done 1 or 2, and know
what pitfalls to avoid, it's reasonably easy (although I would agree
non-trivial)
Do you think
it's easy to set up the objects, logical volumes, tables, etc in the
TPC-H benchmark for Oracle as documented in the full disclosure reports?
Not at all. A benchmark takes many, many months to set up and tune. For
both products. Some of the big H's can take over a year to get right. I
do think the 120,000 lines of setup script in the recent DB2 TPC-C is a
little over the top however.
Please ... there is nothing wrong with asking legitimate technical
questions on an internet NG. But please stop adding your editorial and
leading comments. People in glass houses shouldn't throw stones.


Works for me.

But I will
1) Read everything in this ng that mentions Oracle
2) Explain how Oracle does something if somebody asks how to do the
equivalent in DB2, and any answer seems to be based on a bad
understanding of what Oracle actually does.
3) Correct any other misconceptions/misinformation/negative positioning
about Oracle expressed in this ng.
4) Express an opinion if asked to.

If this involves editorial or leading comments then so be it. There are
always killfiles.

Nov 12 '05 #56

P: n/a
Prelude:
If I were Daniel I would have to ask you whether this is homework, you
knwo that? Well actually I woudln't ask I woudl simple presuem it was ;-)

Mark Townsend wrote:
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;

Life is easy if you only have to live with yourself :-)
When you do a database link, i sthsi link setup by the DBA (and used by
everyone subsequently) or is it per connect?
SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, de**@remote.com
WHERE emp.deptno = dept.deptno; If I were Noons I would have to throw a brick now.
Note that you can also execute RPCs using a similar notation

being
hire_emp@orcl(:empid)
end; You can definitely do it with PASSTHRU.
Without PASSTHRU DB2 supports "function mappings".
Procedure mapping is a known requirement.
Like Oracle (unlike Sybase/MS SQL Server) DB2 distinguishes between
functions and procedures.
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;

This form of sequential update would be more something for a CONNECT
TYPE 2.
But I presume you are gearing towards updating multiple targets in the
same SQL Statement (such as driven by a trigger).

Datajoiner can do that. We found some holes and we sent the federated
team back to the drawing board. They are still scribbling :-)
If I'm not mistaken the one missing piece in the DJ->W II migration.
Most Websphere II users place more importance on query performance than
transparent multi target update.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #57

P: n/a
Mark Townsend wrote:
Comments inline

Larry wrote:
Mark Townsend wrote:
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 ?
You know what Mark...there is another "alternative". The fourth
alternative is that you could be trying usurp a legitimate innocent
technical Q&A to deliberately draw a negative conclusion about what in
your opinion is something that you don't like about DB2.

At the time the thread started I didn't know anything about this area of
DB2.
That is what I meant when I said originally that I wonder if IBM
employees hang around Oracle newsgroups trying to draw negative
attention to Oracle features and functions. It's downright obnoxious
if you ask me.

Sorry to offend you.

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.


See ... even here ... you're being negative and critical. So you've
apparently concluded that this is too involved or complex for you. But
you aren't even drawing any kind of comparison in terms of what
happens under the covers with both products and you conveniently
manage to leave out any drawbacks about Oracle links.

There's really not that many drawbacks with DB links, except for one
minor one and one major one. The minor one I've already identified in
this thread - sometimes you need to hint the optimizer so that it
optimizes the distributed query correctly. In earlier releases this used
to be pretty much all the time, in later releases, not so much. The
major one is truely major, and relates to a possible security hole, so
I'm not going to discuss it in this ng. Needless to say, most Oracle
users are familiar with the major one, and have taken the necessary
measures to avoid it (and the problem is fixed in 10g).

And I do think the DB2 setup is a little over-engineerd for the simple
DB2 to DB2 case. But I do understand that this case represents the
smaller subset of a great capability, so as such the simple case pays
the price for the greater good. I also think that with Oracle, the
situation is slightly reversed. I would argue that for DB2 users, most
of the external databases they need to interface with in their
environment are _not_ DB2 LUW, so for DB2 users the simple case is not
the common case. For Oracle users, however, most of the other databases
they need to interface with are in fact Oracle. In whcih case the simple
case is indeed the common case. So it's horses for courses.
And you don't draw any comparisons in terms of functionality.

I still don't know if RPCs are supported or not. I now know that 2PC
isn't but I don't regard that as a huge problem in th real world. On
other areas, the functionality seems reasonably similar, except that the
DB2 implementation seems slightly more suited to 'static' pre-defined
access as opposed to 'dyanmic' ad hoc access. I suspect this is
something to do with the binding of plans ahead of time, which is a
major difference between the IBM and Oracle approach. Would you agree ?
All you care about is picking apart whatever you can to make negative
statements about DB2. Does Oracle have nothing that can be picked on?

Sure. In fact, plently. And both of us have seen the documents from IBM
that do just this.


Not the point. Let's leave this for the sales/marketing documents and
not look to badmouth each other in internet NGs whose purpose are to
provide un- biased information and answer questions.
> Do you think it is really easy to install, set
up, and configure Oracle RAC and get it optimally tuned?

Surprisingly enough, yes. It's really no more difficult than tuning a
system for an SMP environment. And the first time you set up a cluster
it's a steep learning curve, but once you have done 1 or 2, and know
what pitfalls to avoid, it's reasonably easy (although I would agree
non-trivial)
> Do you think

it's easy to set up the objects, logical volumes, tables, etc in the
TPC-H benchmark for Oracle as documented in the full disclosure reports?

Not at all. A benchmark takes many, many months to set up and tune. For
both products. Some of the big H's can take over a year to get right. I
do think the 120,000 lines of setup script in the recent DB2 TPC-C is a
little over the top however.


And the comparative # of lines in the Oracle setup script for the TPC-C?
And would you also care to provide the # of lines of setup scripts for
both vendors from the 2004 TPC-H benchmarks?
Please ... there is nothing wrong with asking legitimate technical
questions on an internet NG. But please stop adding your editorial and
leading comments. People in glass houses shouldn't throw stones.

Works for me.

But I will
1) Read everything in this ng that mentions Oracle


No problem.
2) Explain how Oracle does something if somebody asks how to do the
equivalent in DB2, and any answer seems to be based on a bad
understanding of what Oracle actually does.
Not sure what you mean by this but if someone asks how to do something
in DB2, I'm not sure it freely invites someone to comment on how it's
done in Oracle. I don't think this was the case here.
3) Correct any other misconceptions/misinformation/negative positioning
about Oracle expressed in this ng.
Fair enough.
4) Express an opinion if asked to.
Fair enough.
If this involves editorial or leading comments then so be it. There are
always killfiles.

I am not averse to productive exchange of information, Mark. It's just
that I don't hang out on the Oracle NGs and look to dump on Oracle. I
try to be respectful, and I try to help people who ask questions in this
NG to the best of my ability. So much of this is subjective. So much of
this is one vendor has this function that is lacking or complex, while
the other vendor has another that is lacking or complex. For you to
single out one area of DB2 and exploit it without drawing a fair
in-depth comparison and without admitting that Oracle has it's own
weaknesses is really not very impartial, objective, or fair. Perhaps,
for example, that the implementation of DB2 Federated support underneath
the covers performs some function or provides some degree of performance
optimization that Oracle links doesn't? There must be a reason why the
architects chose to implement it in this way. Yet ... on the surface ...
you create the subjective impression to all who read the NG that the
DB2 implementation is complex.

Nov 12 '05 #58

P: n/a
And the comparative # of lines in the Oracle setup script for the TPC-C?
Around 23,000 for the Oracle TPC-C
And would you also care to provide the # of lines of setup scripts for
both vendors from the 2004 TPC-H benchmarks?
For the 10 Tb TPC-H, they seem to be similar - 10K odd for Oracle, 12K
odd for DB2.
Perhaps,
for example, that the implementation of DB2 Federated support underneath
the covers performs some function or provides some degree of performance
optimization that Oracle links doesn't? There must be a reason why the
architects chose to implement it in this way.


I would be more than happy to get into this discussion. Does it, and did
they ?

Nov 12 '05 #59

P: n/a


Mark Townsend wrote:
And the comparative # of lines in the Oracle setup script for the TPC-C?

Around 23,000 for the Oracle TPC-C
And would you also care to provide the # of lines of setup scripts for
both vendors from the 2004 TPC-H benchmarks?

For the 10 Tb TPC-H, they seem to be similar - 10K odd for Oracle, 12K
odd for DB2.

And for the 300GB TPC-H?
Perhaps,
for example, that the implementation of DB2 Federated support
underneath the covers performs some function or provides some degree
of performance optimization that Oracle links doesn't? There must be a
reason why the architects chose to implement it in this way.

I would be more than happy to get into this discussion. Does it, and did
they ?

Not the point, Mark. If you're more than happy to get into that
discussion, then you should have demonstrated that when you made your
first posts instead of just inserting your editorial comments.

Nov 12 '05 #60

P: n/a
Larry wrote:

And for the 300GB TPC-H?

Larry - Are these questions purely rhetorical or do you really not know ?

By eyeball and quick back of the envelope calculation, the best 300Gb
DB2 result has about 19K of scripts in the appendices, the closest 300Gb
Oracle result has about 11K.
Not the point, Mark. If you're more than happy to get into that
discussion, then you should have demonstrated that when you made your
first posts instead of just inserting your editorial comments.


I think my first editorial comment was "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)." at which stage I would have hoped that a "It's for
performance reasons, dummy" conversation could have started. I mean, I
did ask, right ?

So let me ask again. What value does defining the wrapper and server
have when the target is a known entity ? You know the protocal, you know
the version, you know the dialect and data type support, and you know
the optimizer capabilities of the target. Why externalise these ? Is it,
as I summised, that the greater case (access to unknown entities) has
subsumed the simple case (which, BTW, is a perfectly valid approach), or
is there indeed some performance/ease of use/adherence to CS best
practices value that I am missing ?

Cmon. Throw me a bone. Teach an old product manager a few new tricks. I
love to beat up on our developers and tell them how things _should_ be done.

Nov 12 '05 #61

P: n/a
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;

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" -'


Thanks.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Nov 12 '05 #62

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote in
news:37*************@individual.net:
If there are any issues with usenet.. I recommend
www.individual.net


Hey Serge (and all),

FWIW, individual.net is becoming a pay-for site. They've been free
for such a long time, I'm amazed they've lasted this long.

Regards,
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com
Nov 12 '05 #63

P: n/a
Pablo Sanchez wrote:
Serge Rielau <sr*****@ca.ibm.com> wrote in
news:37*************@individual.net:

If there are any issues with usenet.. I recommend
www.individual.net

Hey Serge (and all),

FWIW, individual.net is becoming a pay-for site. They've been free
for such a long time, I'm amazed they've lasted this long.

Regards,

Yup, got the email a couple of days ago and decided to cough up the
money. A reliable newsserver is worth 10Euro/year.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #64

63 Replies

This discussion thread is closed

Replies have been disabled for this discussion.