Latest versionsfixlist
11.1.0.6 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
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC70667 Status: Closed

A DB2 SQL query might return SQL0440N error.

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* 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.                                                * 
****************************************************************
Local Fix:
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 
=====================================
available fix packs:
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

Solution
First fixed in DB2 UDB Version 9.5 FixPak 7.
Workaround
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 
=====================================
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
20.08.2010
25.10.2010
25.10.2010
Problem solved at the following versions (IBM BugInfos)
9.5.
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.7 FixList
9.5.0.7 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.