DB2 - Problembeschreibung
Problem IC78861 | Status: Geschlossen |
UNSUPPORTED SQL PUSHED DOWN TO ORACLE CAUSE ERROR: ORA-00937 | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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 ...) | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * The user who is using an Oracle wrapper. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Federation Server V97FP6 or later. * **************************************************************** | |
Local-Fix: | |
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)) | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows | |
Lösung | |
Firstly fixed in Federation Server V9.7 FP6. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 22.09.2011 06.06.2012 06.06.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP6 | |
Problem behoben lt. FixList in der Version | |
9.7.0.6 |