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
>


DB2 & UDB email list listserv db2-l LazyDBA home page