DB2 FOR LUW / DB2FORLUW / 950 - DB2

A DB2 SQL query might return following error message: 
SQL0440N  No authorized routine named "=" of type "FUNCTION" 
having compatible 
arguments was found.  SQLSTATE=42884 
 
The problems happens to SQL queries with SELECT clause such that 
it has 2 or more equiv JOIN predicates whose (1)one side 
operands are NULL values and (2)the other side are of different 
types that can't be compared. 
Here is an example of such queries: 
===================================== 
create table t1 (c1 int, c2 char(10)); 
create table t2 like t1; 
create table t3 like t1; 
 
with tmp as ( 
select cast(NULL as int) x, 
       cast(NULL as char(10)) y 
from t1 
) 
select 1 
from  tmp, t2, t3 
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y; 
===================================== 
The JOIN predicates "t2.c1=x" and "t2.c2=y" result in the 
problem.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of version 9.5 on Linux, Unix and                  * 
* Windowsplatforms.                                            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* A DB2 SQL query might return following error                 * 
* message:SQL0440N  No authorized routine named "=" of type    * 
* "FUNCTION"having compatiblearguments was found.              * 
* SQLSTATE=42884The problems happens to SQL queries with       * 
* SELECT clause suchthat it has 2 or more equiv JOIN           * 
* predicates whose (1)oneside operands are NULL values and     * 
* (2)the other side are ofdifferent types that can't be        * 
* compared.Here is an example of such                          * 
* queries:=====================================create table t1 * 
* (c1 int, c2 char(10));create table t2 like t1;create table   * 
* t3 like t1;with tmp as (select cast(NULL as int) x,cast(NULL * 
* as char(10)) yfrom t1)select 1from  tmp, t2, t3where         * 
* t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and                  * 
* t2.c2=y;=====================================The JOIN        * 
* predicates "t2.c1=x" and "t2.c2=y" result in theproblem.     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB Version Version 9.5 FixPak 7 or           * 
* higherlevels.                                                * 
****************************************************************

Change the NULL operands into Non-NULL types. 
 
For the example shown in Problem Description, modify the SQL 
query to: 
===================================== 
with tmp as ( 
select nullif(0,0)+0 x, 
       cast(NULL as char(10)) y 
from t1 
) 
select 1 
from  tmp, t2, t3 
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y; 
===================================== 
or 
===================================== 
with tmp as ( 
select cast(NULL as int) x, 
       nullif('','')||'' y 
from t1 
) 
select 1 
from  tmp, t2, t3 
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y 
=====================================

DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 UDB Version 9.5 FixPak 7.

Change the NULL operands into Non-NULL types. 
 
For the example shown in Problem Description, modify the SQL 
 
query to: 
===================================== 
with tmp as ( 
select nullif(0,0)+0 x, 
 
       cast(NULL as char(10)) y 
from t1 
) 
select 1 
from  tmp, t2, t3 
 
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y; 
===================================== 
or 
===================================== 
with tmp as ( 
select cast(NULL as int) x, 
 
       nullif('','')||'' y 
 
from t1 
) 
select 1 
from  tmp, t2, t3 
 
where t2.c1=t3.c1 and t2.c2=t3.c2 and t2.c1=x and t2.c2=y 
=====================================

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

20.08.2010
25.10.2010
25.10.2010

9.5.