Thanks Serge,
Major allegations sustained apparently, since you offer up 3 options
that are not heterogeneous joins. Nor does your explanation point to or
hint at the existence of a true heterogeneous join. I don't have a
federated database, this is Linux Express UDB on an IBM Blade center.
The goal is this, make SQL*SERVER and UDB communicate nicely.
Serge, who wrote the IBM OLE DB driver? Was it someone in the Toronto
lab? If so, ask them how it works. If not, find out who did and in
California.
The point here is that unless I am very mistaken, IBM and Microsoft have
said in their various docs and help screens and perhaps advertisements
(certainly sales force and affiliated partners) that heterogeneous joins
work when you have a query in SQL*Server that joins a local to
sql*server table with a remote UDB table and that the relevant where
clause constraints for the remote table are supposed to be passed to
UDB.
But, the fact seems to be that neither the IBM OLE DB driver nor any
other that I know of yet, will do anything but confuse the SQL*Server
Query Planner/Optimizer or whatever parses the query for remote
execution.
And so when that confusion happens the planner just opts out of trying
to send the constraints along to the remote table and just grabs the
whole remote table.
This is not a good thing when the remote table is large.
Thanks,
Kevin Knaus
-----Original Message-----
From: Serge Rielau
[mailto:db2udbdba-ezmlmshield-x15119120.[Email address protected]
Sent: Thursday, September 29, 2005 7:29 PM
To: LazyDBA Discussion
Subject: Re: Anyone anywhere ever make a heterogeneous join work from
SQL*Server to IBM DB2/UDB?
Major allegations:
This is Websphere II (formerly known as DB2 II)? (Which release?)
You have a local table udbtable and a remote table on SQL server.
You are connected to SQL Server through OLEDB.
I see three ways how this query can execute:
1. WS II It can haul in the whole SQL Server table to DB2 and perform
the
join locally.
2. It can push the local table to SQL server, run the join remote
3. It can keep pinging the the SQL Server table with one value from the
udb
table at a time.
What does federated explain show?
Cheers
Serge
Serge Rielau
DB2 Solutions Development
DB2 Universal Database for Linux, Unix, Windows
IBM Toronto Lab
Tel.: (905) 413 3907 Internet: [Email address protected]
Fax: (905) 413 4840 T/L: 969 3907
"Kevin Knaus " <db2udbdba-ezmlmshield-x68433702.[Email address
protected]
wrote on 09/29/2005 06:40:01 PM:
> This is a direct request of the many IBM tech support and Toronto Lab
> people who monitor this group... and of anyone else in the world.
>
> I would like to be able to know if anyone in the world has ever made a
> heterogeneous join work. Something like this....
>
> Select a.col1, a.col2, b.col5, b.col7
> From sqlservertable a, udbtable b
> Where a.colx = b.coly
>
> The issue is that such queries are supposed to work through OLEDB
> drivers. But, they do not. For example, according to IBM and based on
a
> support research by Microsoft, the above query would confuse
Microsofts
> Query optimizer and not know how to pass the remote table references
> constraint to the remote table. The effect is that the Query Plan in
> SQL*Server will just bring back the entire remote table and do the
join
> locally to Sql*Server.
>
> The above type query also causes SQL*Server to not release memory
> resources. The original problem report was about that memory leak. The
> resolution proposed by Microsoft to IBM was that it doesn't work, so
use
> OPEN QUERY instead. Case closed.
>
> Classic case of agreeing to declare victory in the face of abject
> failure.
>
> The key is supposedly in "using the right OLEDB driver" yet none are
> explicity mentioned. Thus my request. Has anyone in the world ever
> gotten a heterogeneous join to work correctly, meaning with the remote
> constraints passed along as they should be.
>
> Thanks,
>
> Kevin Knaus
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page