DB2 FOR LUW / DB2FORLUW / 970 - DB2

You might encounter an SQL0901 error with reason "getting 
canonical type length" and producing the following stack trace 
 
sqlnn_cmpl 
sqlng_main 
sqlng_main 
sqlng_build_thread 
sqlng_process_return_op 
sqlng_process_TQ_op 
sqlng_build_thread 
sqlng_process_pipe_op 
sqlng_process_mate_op 
sqlng_process_hsjn_op 
sqlng_build_thread 
sqlng_process_TQ_op 
sqlng_build_thread 
sqlng_build_TQB_op 
sqlng_populate_COLBLOCK_ob 
 
when compiling a query consisting of a non-collocated join 
between two or more tables that are hash distributed across two 
or more database partitions, and the join columns also equate to 
the NULL constant.  The following is an example that might 
produce the SQL0901 error: 
 
create table i1 (a int, b int) distribute by hash (a); 
create table i2 (a int, b int) distribute by hash (a); 
 
select * from i1,i2 where i1.b=i2.b and i2.b = null ; 
 
In this example, the join is not collocated, so the optimizer 
can consider both a broadcast join or a repartition join.  The 
decision is cost-based, and the problem only occurs if the 
repartition join is chosen.

**************************************************************** 
* USERS AFFECTED:                                              * 
* The problem can occur in a DPF environment.                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Problem first fixed in DB2 version v9.7 fp 9                 * 
****************************************************************

You might be able to avoid the error by re-ordering the 
predicates in the WHERE clause such that the equality predicate 
referencing the NULL constant is included before the join 
predicate referencing the same column.  Using the example in the 
error description, re-ordering the predicates as follows could 
avoid the error:  "where i2.b = null and i1.b=i2.b".

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
 

Problem first fixed in DB2 version v9.7 fp 9

not known / see Local fix

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

30.07.2013
16.12.2013
16.12.2013

9.7.FP9