By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

Predicate evaluation across federation

P: n/a
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..
Dec 5 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Dec 7 '07 #2

P: n/a
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
Dec 10 '07 #3

P: n/a
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
Dec 11 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.