Equivilant of Oracle's DB Links in DB2

[Serge Rielau]

Re: Equivilant of Oracle's DB Links in DB2

Mark Townsend wrote:[color=blue][color=green]
> > Guess I will have the crack[/color]
>[color=green]
>> the doc.[/color]
>
>
> 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’[/color]
DRDA is the only protocol used by DB2 UDB fro LUW . So it's always
there. Se emy other post for simplified notation.[color=blue]
>
> 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 ?).[/color]
Yes. Syntax diagrams would start looking funny if IBM rode each renaming
bump of MS ;-)

Obviously I also don't have[color=blue]
> 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 ?[/color]
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.
[color=blue]
> 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) ?[/color]
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.
[color=blue]
> 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 ?[/color]
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....
[color=blue]
> 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[/color]
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.
[color=blue]
> How do you do a join across both systems using this syntax ?[/color]
Wrong tool. Used for DDL (or if you don't have a nickname?), not for
general DML.
[color=blue]
> 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[/color]
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

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

Serge Rielau wrote:
[color=blue]
> DB2 has default settings for different versions of different products
> which is why the version needs to be specified.[/color]

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 ?
[color=blue]
> Call it single-sign on.... If DB2 joins data from 5 different DBMS who
> wants to type in all those passwords on every connect?[/color]

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 ?
[color=blue]
> 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....[/color]

Hmm. Oracle has public and private DB Links.
[color=blue]
>
> SET PASSTHRU (besides to do a sanity test) is used to specify
> statements which are not supported by DB2.[/color]

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)
[color=blue]
> 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, ...[/color]

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.
[color=blue]
> A note on the side. AFAIK, for IBM customers homogeneity of the data
> sources is the exception.[/color]

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

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

Serge Rielau wrote:[color=blue]
> Mark Townsend wrote:
>[color=green]
>> Serge Rielau wrote:
>>[color=darkred]
>>> 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?[/color]
>>
>>
>>
>> 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 ?
>>[/color]
>
> 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;
>
>[/color]


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, dept@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;

[Mark A]

Re: Equivilant of Oracle's DB Links in DB2

"Mark Townsend" <markbtownsend@ comcast.net> wrote in message[color=blue]
>
> Can you do this in DB2 ?
>
> Does the IBM implementation also support 2PC ?[/color]

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 Townsend]

Re: Equivilant of Oracle's DB Links in DB2

Mark A wrote:[color=blue]
> "Mark Townsend" <markbtownsend@ comcast.net> wrote in message
>[color=green]
>>Can you do this in DB2 ?
>>
>>Does the IBM implementation also support 2PC ?[/color]
>
>
> 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.
>
>[/color]

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.

[Mark A]

Re: Equivilant of Oracle's DB Links in DB2

"Mark Townsend" <markbtownsend@ comcast.net> wrote in message
news:raKdnYfMw_ 5kK5PfRVn-[color=blue]
> 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.
>[/color]
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.

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

[color=blue]
> 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.[/color]

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.

[color=blue]
> 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.[/color]

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
[color=blue]
> 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.[/color]

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

[Mark A]

Re: Equivilant of Oracle's DB Links in DB2

"Mark Townsend" <markbtownsend@ comcast.net> wrote in message
news:420EBEE9.8 070701@comcast. net...[color=blue]
>
> Funny. FYI - My new definition of DB2 is now an unconnectable
> receptacle, and I've been writing it off for years.
>[/color]
You must have not been able to complete the write-off, since you are still
posting in this thread.

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

Mark A wrote:
[color=blue]
>
> You must have not been able to complete the write-off, since you are still
> posting in this thread.
>
>[/color]
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.

[Larry]

Re: Equivilant of Oracle's DB Links in DB2

Mark Townsend wrote:[color=blue]
> Mark A wrote:
>[color=green]
>>
>> 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).[/color]
>
>
> The third alternative could just be that I'm interested in knowing how
> something done in Oracle is also done in DB2 ?[/color]

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.
[color=blue]
>
> 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.[/color]

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.

[color=blue]
>[color=green]
>> You and Daniel Morgan seem
>> to be engaging in some coordinated attacks against me that are
>> frankly, very
>> petty.[/color]
>
>
> Stop it. It's pure paranoia.
>[/color]
Larry E.

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

Comments inline

Larry wrote:[color=blue]
> Mark Townsend wrote:
>[color=green]
>> Mark A wrote:
>>[color=darkred]
>>>
>>> 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).[/color]
>>
>>
>>
>> The third alternative could just be that I'm interested in knowing how
>> something done in Oracle is also done in DB2 ?[/color]
>
>
> 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.[/color]

At the time the thread started I didn't know anything about this area of
DB2.
[color=blue]
> 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.[/color]

Sorry to offend you.
[color=blue][color=green]
>>
>> 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.[/color]
>
>
> 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.[/color]

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.
[color=blue]
> And you don't draw any comparisons
> in terms of functionality.[/color]

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 ?
[color=blue]
> 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?[/color]

Sure. In fact, plently. And both of us have seen the documents from IBM
that do just this.
[color=blue]
> Do you think it is really easy to install, set
> up, and configure Oracle RAC and get it optimally tuned?[/color]

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)
[color=blue]
> 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?[/color]

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.
[color=blue]
> 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.[/color]

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.

[Serge Rielau]

Re: Equivilant of Oracle's DB Links in DB2

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:[color=blue]
> Serge Rielau wrote:
>[color=green]
>> Mark Townsend wrote:
>>[color=darkred]
>>> 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 ?
>>>[/color]
>>
>> 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;
>>
>>[/color]
> 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;[/color]
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?[color=blue]
>
> SELECT /*+DRIVING_SITE( dept)*/ * FROM emp, dept@remote.com
> WHERE emp.deptno = dept.deptno;[/color]
If I were Noons I would have to throw a brick now.
[color=blue]
> Note that you can also execute RPCs using a similar notation
>
> being
> hire_emp@orcl(: empid)
> end;[/color]
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.
[color=blue]
> 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;[/color]
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

[Larry]

Re: Equivilant of Oracle's DB Links in DB2

Mark Townsend wrote:[color=blue]
> Comments inline
>
> Larry wrote:
>[color=green]
>> Mark Townsend wrote:
>>[color=darkred]
>>> 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 ?[/color]
>>
>>
>>
>> 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.[/color]
>
>
> At the time the thread started I didn't know anything about this area of
> DB2.
>[color=green]
>> 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.[/color]
>
>
> Sorry to offend you.
>[color=green][color=darkred]
>>>
>>> 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.[/color]
>>
>>
>>
>> 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.[/color]
>
>
> 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.
>[color=green]
>> And you don't draw any comparisons in terms of functionality.[/color]
>
>
> 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 ?
>[color=green]
>> 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?[/color]
>
>
> Sure. In fact, plently. And both of us have seen the documents from IBM
> that do just this.[/color]

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.
[color=blue]
>[color=green]
> > Do you think it is really easy to install, set[/color]
>[color=green]
>> up, and configure Oracle RAC and get it optimally tuned?[/color]
>
>
> 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)
>[color=green]
> > Do you think[/color]
>[color=green]
>> 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?[/color]
>
>
> 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.[/color]

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?
[color=blue][color=green]
>> 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.[/color]
>
>
> Works for me.
>
> But I will
> 1) Read everything in this ng that mentions Oracle[/color]

No problem.
[color=blue]
> 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.[/color]

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.
[color=blue]
> 3) Correct any other misconceptions/misinformation/negative positioning
> about Oracle expressed in this ng.[/color]

Fair enough.
[color=blue]
> 4) Express an opinion if asked to.
>[/color]
Fair enough.
[color=blue]
> If this involves editorial or leading comments then so be it. There are
> always killfiles.
>[/color]
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.

[Mark Townsend]

Re: Equivilant of Oracle's DB Links in DB2

[color=blue]
> And the comparative # of lines in the Oracle setup script for the TPC-C?[/color]

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

For the 10 Tb TPC-H, they seem to be similar - 10K odd for Oracle, 12K
odd for DB2.
[color=blue]
> 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.[/color]

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

[Larry]

Re: Equivilant of Oracle's DB Links in DB2



Mark Townsend wrote:
[color=blue]
>[color=green]
>> And the comparative # of lines in the Oracle setup script for the TPC-C?[/color]
>
>
> Around 23,000 for the Oracle TPC-C
>[color=green]
>> And would you also care to provide the # of lines of setup scripts for
>> both vendors from the 2004 TPC-H benchmarks?[/color]
>
>
> For the 10 Tb TPC-H, they seem to be similar - 10K odd for Oracle, 12K
> odd for DB2.[/color]


And for the 300GB TPC-H?
[color=blue]
>[color=green]
>> 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.[/color]
>
>
> I would be more than happy to get into this discussion. Does it, and did
> they ?
>[/color]
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.