473,854 Members | 1,731 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Equivilant of Oracle's DB Links in DB2

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
63 25734
"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
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
"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
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
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 "alternativ e". 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
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 "alternativ e". 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
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
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 "alternativ e". 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
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


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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3335
by: davidshook | last post by:
I am a begginer with not too much time on my hand. I do some html pages with Dreamweaver and with some minor Flash actionscript and I have a minor ability (with the help of lots of tutorials) to do some PHP. I love Dreamweaver MX since it realy simplifies the visual part of the process of making a page and also help with code typos since it generates alot of the basic code automatically (for example, I don't have to know how to write a...
1
3347
by: valerian dinca | last post by:
Hi, This is my latest article for your consideration. If you think that this article could interest your subscribers it is free to reprint in your ezine and/or newsletter as long as you: 1. Print the article in its entirety. 2. Print the resource box with the article in it's entirety. Please notify me if you use the article.
4
2822
by: Jeff | last post by:
Hello all, I've created a COM dll for a number-crunching program. However when I tell the program what function to use from the DLL, it claims that it cant find the function. I have the original .dll code in C++, however I'm not a C++ guy :-(. It appears that all the functions are declaired in the .def (definition file). When I compile it, the program reconizes the functions without a problem. My question is, what is the .def...
6
1437
by: Trint Smith | last post by:
This is how I did this sql server 2000 string in vb.net: "FROM TBL_TravelMain WHERE TravelMain_Mlv = '" & MLVTrimString & "'" In C# you can't use the & something &. How do I put this in C#, or what do I replace the & with? Thanks, Trint .Net programmer
0
2396
by: rn5a | last post by:
In a shopping cart app, assume that a user has placed 4 orders (each order has a corresponding OrderID which will be unique). When he comes to MyCart.aspx, by default, the details of his last order he had placed will be displayed in a DataList. Also assume that the OrderID of the last order is 13. The details of the earlier orders placed by a particular user (when the user places more than 1 order) can be viewed by clicking links. The...
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10685
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9518
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7918
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4563
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4162
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.