473,854 Members | 1,480 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 25730
"Mark Townsend" <ma***********@ comcast.net> wrote in message >
Seems a
little overkill for a simple DB2 to DB2 link (why do I need a wrapper
and a server definition - won't the nickname suffice ? That's
effectively all that is required in Oracle). Guess I will have the crack
the doc.

I didn't design it, so you will have to ask someone else.
Nov 12 '05 #41
Mark A wrote:
You and Daniel Morgan seem
to be engaging in some coordinated attacks against me that are frankly, very
petty.


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

I can only speak for myself as to why this thread is of interest so I
will. Many of my students are simultaneously working in more than one
commercial RDBMS. They often ask questions such as "this is how I do it
in product "A" what is the equivalent in product "B". So a straight,
non-paranoid, response to the question would be of value.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)
Nov 12 '05 #42
"DA Morgan" <da******@x.was hington.edu> wrote in message
news:1108249709 .449640@yasure. ..

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


I did just that. I answered the question that was asked, exactly as it was
asked. The question about the DB2 equivalent to Oracle DB Links was already
answered by someone else long before I posted.
Nov 12 '05 #43
Mark Townsend wrote:
Serge Rielau wrote:
Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?

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


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

catalog local node local instance regress;

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

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

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

create nickname nickname1 for loop.blahschema .blahtable;

To use a remote node here is the syntax diagram (from Command reference
manual):
-CATALOG--+-------+--TCPIP NODE-- nodename --------------------->

'-ADMIN-'

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

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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #44
Serge Rielau wrote:
Here is how I set up local DB2 -> DB2 testing when I need it:

catalog local node local instance regress;


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

Jan M. Nelken
Nov 12 '05 #45
Mark Townsend wrote:
> Guess I will have the crack
the doc.

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

1. Catalog the node

For the example given, perhaps the following command

CATALOG TCPIP NODE machine2 REMOTE 10.10.60.16 SERVER 50000

2. Catalog the remote database

For the example given, perhaps the following command

CATALOG DATABASE sample AS sample2 AT NODE machine2 AUTHENTICATION SERVER

3. Create the wrapper

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

CREATE WRAPPER sample_wrapper LIBRARY ’db2drda.dll’

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

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

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

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

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

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

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

OK - so to get around the SET PASSTHRU problem I need to create
'nicknames' for each remote table a la
CREATE NICKNAME DB2SALES FOR SAMPLE.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

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

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

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


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

Nov 12 '05 #47
Serge Rielau wrote:
Mark Townsend wrote:
Serge Rielau wrote:
Mark, did you receive the two links I provided in response to your
request? If yes, do they or do they not provide sufficient detail?


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


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

catalog local node local instance regress;

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

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

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

create nickname nickname1 for loop.blahschema .blahtable;

In Oracle that would be

CREATE DATABASE LINK orcl USING 'orcl';

Usage of the form (assuming logged on as SCOTT)

SELECT count(*) FROM dept@orcl;

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

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

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

being
hire_emp@orcl(: empid)
end;

Can you do this in DB2 ?

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

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


Nov 12 '05 #48
"Mark Townsend" <ma***********@ comcast.net> wrote in message

Can you do this in DB2 ?

Does the IBM implementation also support 2PC ?


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

I don't recall anyone asking in this thread about how Oracle provides
Federated support.
Nov 12 '05 #49
Mark A wrote:
"Mark Townsend" <ma***********@ comcast.net> wrote in message
Can you do this in DB2 ?

Does the IBM implementation also support 2PC ?

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

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


Mark - calm down. Take a chill pill, Dude

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

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

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

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

Nov 12 '05 #50

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
9751
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
11027
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
10682
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...
1
7917
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
5743
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...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4159
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.