IBM Support

LI74671: SQL COMPILATION FAILS WITH SQL0401N OR SQL0440N WHEN QUERY USES BUILT-IN FUNCTION WITH STRING UNIT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A query may fail to compile with SQL0401N or SQL0440N when the
    query uses one of the following built-in string manipulation
    functions:
    
        SYSIBM.CHARACTER_LENGTH
        SYSIBM.INSERT
        SYSIBM.LCASE
        SYSIBM.LEFT
        SYSIBM.LENGTH
        SYSIBM.LOCATE
        SYSIBM.LOWER
        SYSIBM.OVERLAY
        SYSIBM.POSITION
        SYSIBM.RIGHT
        SYSIBM.SUBSTRING
        SYSIBM.UCASE
        SYSIBM.UPPER
    
    The problem only occurs if one of the above string function
    invocations includes the optional string unit parameter
    (CODEUNITS16, CODEUNITS32 or OCTETS). For example:
    
    select * from t1 where substring (c1, 1, 5, codeunits16) = ''
    and c2=6939
    
    SQL0401N  The data types of the operands for the operation "="
    are not compatible.  SQLSTATE=42818
    
    select * from t1 where c2=7453 and substring (c1, 1, 5, octets)
    = ''
    
    SQL0440N  No authorized routine named "SUBSTRING" of type
    "FUNCTION" having compatible arguments was found.
    SQLSTATE=42884
    

Local fix

  • If possible, avoid the use of the string unit.
    

Problem summary

  • Users Affected:
    Users who are using one of the built-in string manipulation
    functions.
    
    Problem Description:
    If this APAR is not applied, it is possible that a query may
    fail to compile with SQL0401N or SQL0440N.
    
    Problem Summary: See Error Description for details.
    

Problem conclusion

  • First fixed in DB2 Version 9.5, Fixpack 5
    

Temporary fix

  • n/a.
    

Comments

APAR Information

  • APAR number

    LI74671

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-06-12

  • Closed date

    2010-02-04

  • Last modified date

    2010-02-04

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

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

    IC62075 IC71942

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       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":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 February 2010