DB2 FOR LUW / DB2FORLUW / 970 - DB2

Unsupprted SQL may be pushed down to remote data source with 
server option: db2_select_scalar_SQ='Y', as a result, federation 
returns SQL1822N error: 
 
SQL1822N  Unexpected error code "937" received from data source 
"ORACLE_SERV". Associated text and tokens are "ORA-00937: 
not a single-group group function ".  SQLSTATE=560BD. 
 
If set this option to 'N', the ORA-00937 error will not happen, 
but the entire SQL will not be pushed down. 
 
 
Reproduce steps: 
---------------------------------------------------------------- 
connect to test 
 
alter server sudb options (DB2_MAXIMAL_PUSHDOWN  'Y', 
db2_select_scalar_SQ 'Y') 
 
set passthru sudb 
drop table t1 
drop table t2 
create table t1 (c1 int, c2 int) 
create table t2 (c1 int, c2 int) 
 
insert into t1 values(1, 1) 
insert into t2 values(1, 1) 
 
select (select max(c1) from t1 ) from  t2 
 
set passthru reset 
 
drop nickname un1 
drop nickname un2 
 
create nickname un1 for SUDB.IIFVT14.T1 
create nickname un2 for SUDB.IIFVT14.T2 
 
--test udb, triggering "SQL1822N  Unexpected error code "-119" 
received  from data source "SUDB" 
 
select ( select COALESCE(count(1),0.0) from un1 where c1=1) + 
(select COALESCE(count(1),0.0) from un2 where c1=2) from 
sysibm.sysdummy1 
 
!db2trc on -f my 
 
explain plan for  select ( select COALESCE(count(1),0.0) from 
un1 where c1=1) + (select COALESCE(count(1),0.0) from un2 where 
c1=2) from sysibm.sysdummy1 
 
!db2trc off 
!db2trc fmt my my.fmt 
 
!db2exfmt -d test -1 -o udb_plan 
select ((( select count(1) from t1 )) + count(1)) from t2 
 
connect reset 
 
This cause is that, udb and oracle do not support a semantics of 
"select scalar_subquery + count(1) form ...)

**************************************************************** 
* USERS AFFECTED:                                              * 
* The user who is using an Oracle wrapper.                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Federation Server V97FP6 or later.                * 
****************************************************************

1. Set db2_select_scalar_SQ to 'N' 
or 
2. Modify the original SQL to: 
select COALESCE(count(1),0.0) from ((select c1,c2 from un1) 
union all (select c1,c2 from un2))

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Firstly fixed in Federation Server V9.7 FP6.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.09.2011
06.06.2012
06.06.2012

9.7.FP6