SELECT COUNT(*) with subselect not pushed down

Technote (FAQ)


Question

When issuing a SELECT(*)....that contains a subselect on MS SQL Server or DB2/i nickname, why isn't the query pushed down to datasource?

An example query is:

select count(*) from
( select int1, SUM(COALESCE(int2, 0)) AS sum2, SUM(COALESCE(int3, 0)) AS sum3
from nktab1 group by int1, int2 ) A

Answer

The above query is rewritten to bring back the data and evaluate the select at the Federation Server because the GROUP BY is blocked for being pushed down. To resolve, set the server option DB2_GB_MORE_THAN_NON_AGGR to 'Y' and retry.

You can issue the following statement:

ALTER SERVER <server_name> OPTIONS( ADD DB2_GB_MORE_THAN_NON_AGGR 'Y').


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Federation Server
Data Sources and Wrappers - Microsoft SQL Server

Software version:

9.7

Operating system(s):

AIX, HP-UX, Linux, Linux Red Hat - zSeries, Linux SUSE - zSeries, Linux x86, Solaris, Windows

Software edition:

All Editions

Reference #:

1443146

Modified date:

2012-12-17

Translate my page

Machine Translation

Content navigation