Federated query problem on db2 udb v8 on luw

Deepak Goyal

Federated query problem on db2 udb v8 on luw
Hi All,

Before I go to my question, please have a look at our environment -

DB2 UDB on AIX 64 bit
3 node ( Data is there on 2 nodes only )
Temp space allocated for 2 nodes (1 and 2) = 120GB each
Temp space allocated for node 0 = 1GB

We have one federated query running on our warehouse that is using a lot of
temp space. This query is fetching the data from OLTP (federated part) and
doing the join with local table. The OLTP table that is used in the query is
having 21 million rows ( and row size = 400 bytes).

This query is using temp space on node 0 (our observation says that it is
fetching federated data from OLTP to temp space before doing join) as our
data don't reside on node 0 we haven't allocated much temp space on the box
( see configuration above). Now query is failing due to "temp file system got
full on node 0". Is there any way we can force the query to use temp space
on node 1 and node 2 ? I haven't worked on federated query a lot so just
curious if there is some other way out or we can tune some parameter on
database side, etc..

Your help is highly apprecaited.

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: Federated query problem on db2 udb v8 on luw
(in response to Deepak Goyal)
Deepak,

I dont know how to force the SQL to use NODE 0 for Storing the temp Results.
but before that, i would like to say something. If its retrieving the data
to DB2 and performs the operation, it will for sure kill the performance. I
would recommend you to do pushdown analysis to make sure the SQLs wll be
performed in Remote Datasource.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ian Bjorhovde

Re: Federated query problem on db2 udb v8 on luw
(in response to SUBSCRIBE DB2-L Muthuraj)
Deepak,

First of all, best practice is to have plenty (i.e. the same amount of
space as any data partition) of temporary space available on your
coordinator partition -- even with good access plans, it's possible
that the coordinator may have to process many rows, requiring a lot of
temporary space.

In your case, I would also look at the explain to see what DB2 is
doing with the federated data -- is it pulling all of the rows from
the federated source to the coordinator partition, and then joining
with your partitioned table *at the coordinator partition*? Or does
the coordinator partition simply fetch the rows from the federated
source and then broadcast them out to the data partitions?



On Mon, Dec 15, 2008 at 12:11 PM, Deepak Goyal <[login to unmask email]> wrote:
> Hi All,
>
> Before I go to my question, please have a look at our environment -
>
> DB2 UDB on AIX 64 bit
> 3 node ( Data is there on 2 nodes only )
> Temp space allocated for 2 nodes (1 and 2) = 120GB each
> Temp space allocated for node 0 = 1GB
>
> We have one federated query running on our warehouse that is using a lot of
> temp space. This query is fetching the data from OLTP (federated part) and
> doing the join with local table. The OLTP table that is used in the query is
> having 21 million rows ( and row size = 400 bytes).
>
> This query is using temp space on node 0 (our observation says that it is
> fetching federated data from OLTP to temp space before doing join) as our
> data don't reside on node 0 we haven't allocated much temp space on the box
> ( see configuration above). Now query is failing due to "temp file system got
> full on node 0". Is there any way we can force the query to use temp space
> on node 1 and node 2 ? I haven't worked on federated query a lot so just
> curious if there is some other way out or we can tune some parameter on
> database side, etc..
>
> Your help is highly apprecaited.
>
> Thanks
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms