IBM Support

IC94476: SQL0901 ERROR IN DPF WHEN COMPILING A QUERY WITH AN EQUALITY PREDICATE REFERENCING A NULL CONSTANT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

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

Local fix

  • 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".
    

Problem summary

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

Problem conclusion

  • Problem first fixed in DB2 version v9.7 fp 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC94476

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-30

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

  • APAR is sysrouted FROM one or more of the following:

    IC91683

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC94476

Modified date: 16 December 2013