473,839 Members | 1,485 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 25707
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 ?

Nov 12 '05 #31
"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.
Nov 12 '05 #32
Mark A wrote:
"DA Morgan" <da******@x.was hington.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.wash ington.edu
(replace 'x' with 'u' to respond)
Nov 12 '05 #33
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.wash ington.edu
(replace 'x' with 'u' to respond)
Nov 12 '05 #34
"DA Morgan" <da******@x.was hington.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).
Nov 12 '05 #35
"DA Morgan" <da******@x.was hington.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.
Nov 12 '05 #36
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 ?

Nov 12 '05 #37
"Mark Townsend" <ma***********@ comcast.net> wrote in message
news:XIydnWLpXr eJw5PfRVn-

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.

Nov 12 '05 #38
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.

Nov 12 '05 #39
> 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 "AUTHENTICA TION 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_tab le
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.SALESDAT A.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.salesdat a.europe s
where c.status = "VALID" and c.id=s.cust_id
Nov 12 '05 #40

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

Similar topics

4
3334
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
2394
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
9854
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
9696
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
10903
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10584
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...
1
10645
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10290
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
9425
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...
0
5681
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4482
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

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.