Prev: Problem with to_date-function on AIX
Next: DB2 access with NON-LOGABLE userid
From: esmith2112 on 16 Mar 2010 11:03
I have a query that is not executing as expected when running it
through a federated database. I've narrowed down the part that causes
it to fail and am wondering if anyone knows why it fails, if there's a
better way to do it.

I have 3 DB2 databases all on the same physical server (AIX 5.3 with
DB2 9.5). One is for current data, a second which is a clone of the
first to archive old data, and a third which is a federated database
with nicknames pointing to the tables of the first two database where
we write queries to merge data from both. When executing my query
directly against either of the first two databases, I get the results
expected. The skeleton of the query is something similar to this:

===============================
with t1 as (select columns from myschema.mytable1),
t2 (as select morecolumns from myschema.mytable2)

select T1.co1, T1.col2, T2.col1,
case
when t2.co1 = 20 and exists (select 1 from myschema.mytable3 t3
where t3.col1 = t1.col1) then 'ABC'
else t2.col1
end as mycol
from t1
left join t2 on t1.col1 = t2.col1
===============================

It produces the 173 records I expect. However if substitute the
nicknames the table names and submit it against the federated
database, it produces zero rows. I discovered that if I comment out
the CASE statement, that I get back all 173 rows again. Is there a
restriction against using an EXISTS clause in this fashion?

Any insight would be welcome.

Thanks,
Evan
From: Serge Rielau on 16 Mar 2010 21:38
On 3/16/2010 11:03 AM, esmith2112 wrote:
> I have a query that is not executing as expected when running it
> through a federated database. I've narrowed down the part that causes
> it to fail and am wondering if anyone knows why it fails, if there's a
> better way to do it.
>
> I have 3 DB2 databases all on the same physical server (AIX 5.3 with
> DB2 9.5). One is for current data, a second which is a clone of the
> first to archive old data, and a third which is a federated database
> with nicknames pointing to the tables of the first two database where
> we write queries to merge data from both. When executing my query
> directly against either of the first two databases, I get the results
> expected. The skeleton of the query is something similar to this:
>
> ===============================
> with t1 as (select columns from myschema.mytable1),
> t2 (as select morecolumns from myschema.mytable2)
>
> select T1.co1, T1.col2, T2.col1,
> case
> when t2.co1 = 20 and exists (select 1 from myschema.mytable3 t3
> where t3.col1 = t1.col1) then 'ABC'
> else t2.col1
> end as mycol
> from t1
> left join t2 on t1.col1 = t2.col1
> ===============================
>
> It produces the 173 records I expect. However if substitute the
> nicknames the table names and submit it against the federated
> database, it produces zero rows. I discovered that if I comment out
> the CASE statement, that I get back all 173 rows again. Is there a
> restriction against using an EXISTS clause in this fashion?
>
> Any insight would be welcome.
Please open a PMR. If there were a restriction you would get an error
message.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

 | 
Pages: 1
Prev: Problem with to_date-function on AIX
Next: DB2 access with NON-LOGABLE userid