IBM Support

IT24439: SQL0440N ERROR CAN OCCUR WHEN QUERIES CONTAIN OUTER JOINS AND INNER JOINS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • User might run into SQL0440N errors - "No authorized routine
    named "no promotion" of type "FUNCTION" having compatible
    arguments was found" when running a query with the following
    characteristics:
    
    1. that query contains at least an output join that have
    equality join predicates
    2. one or both sides of this output join contain inner joins
    that have equality join predicates
    3. the columns in the inner join predicates are not in the same
    data type as the equivalent columns in the outer join predicates
    4. the columns in the inner join predicates cannot be promoted
    or adjusted to the equivalent columns in the outer join
    predicates
    
    workaround:
    db2set DB2COMPOPT=NOOJSJ
    

Local fix

  • db2set DB2COMPOPT=NOOJSJ
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT24439

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-03-20

  • Closed date

    2018-11-27

  • Last modified date

    2018-11-27

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

  • 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

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 November 2018