Predicate evaluation across federation

[Arun Srinivasan]

I have a query that has 5 predicates, scanning a table of 400+ million
rows and I have built indexes getting help from design advisor and
other tools. The cost of that query is really low. But I federated the
server and created a nickname for that table, and when I give the same
query against the nickname, it tries to fetch the whole table (cost
1.7 billion), and do the predicate evaluation in my smaller test box.
I did build indices in my test box with 'specification only' clause,
and runstats, and other things. I also read about query pushdown, and
hoping that would help, I changed the cpu_ratio parameter in fed
server's config. Still no joy. Please help.. any links would be
appreciated.
Also, how does one quantify cost of a query..

[Knut Stolze]

Arun Srinivasan wrote:

I have a query that has 5 predicates, scanning a table of 400+ million
rows and I have built indexes getting help from design advisor and
other tools. The cost of that query is really low. But I federated the
server and created a nickname for that table, and when I give the same
query against the nickname, it tries to fetch the whole table (cost
1.7 billion), and do the predicate evaluation in my smaller test box.
I did build indices in my test box with 'specification only' clause,
and runstats, and other things. I also read about query pushdown, and
hoping that would help, I changed the cpu_ratio parameter in fed
server's config. Still no joy. Please help.. any links would be
appreciated.
Also, how does one quantify cost of a query..

Maybe you could give us some more details. How did you do the runstats on
the fed server? What's your table/nickname, indexes (on both ends), the
query and the access plan for it? How did you determine that all rows are
to be fetched from the data source to the fed server?

Which version of DB2 are you using on which platform? Is your fed server
and remote data source both a DB2 system?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Arun Srinivasan]

Maybe you could give us some more details. How did you do the runstats on

the fed server? What's your table/nickname, indexes (on both ends), the
query and the access plan for it? How did you determine that all rows are
to be fetched from the data source to the fed server?

Which version of DB2 are you using on which platform? Is your fed server
and remote data source both a DB2 system?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Hi
Both servers are runnning db2, but the federator is on 8.2 while the
remote source is in 9.4. I ran a sql from my test db(not the source)
and did a db2explain, it showed me that the predicate evaluation was
done in remote server. But the way our developers were doing was to
construct this sql on the fly, open a cursor inside a stored
procedure , like for example, their input data matches some rules,
they give one particular sql for constructing a cursor with. If it
uses the remote data source (only that with some predicates) , the
target tries to get entire table and do the predicate evaluation.
I have constructed indexes on this nickname (definition only) , that
didnt help me either. I am running out of options.
The way I find that predicate evaluation is done in target is the
cost of query that runs in my source (145MIL -aah..) and the sql from
the snapshot. The sql doesnt have any predicates, and this escalates
to a table level lock as soon as it starts.
Any help would be appreciated.
Thank you
Arun

[Knut Stolze]

Arun Srinivasan wrote:

Both servers are runnning db2, but the federator is on 8.2 while the
remote source is in 9.4. I ran a sql from my test db(not the source)
and did a db2explain, it showed me that the predicate evaluation was
done in remote server. But the way our developers were doing was to
construct this sql on the fly, open a cursor inside a stored
procedure , like for example, their input data matches some rules,
they give one particular sql for constructing a cursor with. If it
uses the remote data source (only that with some predicates) , the
target tries to get entire table and do the predicate evaluation.
I have constructed indexes on this nickname (definition only) , that
didnt help me either. I am running out of options.
The way I find that predicate evaluation is done in target is the
cost of query that runs in my source (145MIL -aah..) and the sql from
the snapshot. The sql doesnt have any predicates, and this escalates
to a table level lock as soon as it starts.

I guess I'm a bit dense here because I didn't understand the details. Could
you post:
(1) The access plans you got from your separate test and and from the code
in the stored procedure?
(2) The stored procedure code (stripped down to the bare minimum that
exhibits the problem.
(3) A CLI trace from the remote data source where you can see which SQL
statements are being processed. (That's an easy way to verify what the
federated server really sends to the data source.)
In short, while I understand your description, we are simply missing the
details to say anything about it.

p.s: I'm assuming your remote data source is running DB2 V9.5 (and not 9.4)?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany