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