home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
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

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 FixList