IBM Support

IC73482: SQL0901N using host variable to specify pattern string to INSTR,LIKE, POSSTR, POSITION, LOCATE, or LOCATE_IN_STRING

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • If you call the INSTR function and specify the pattern
    expression using a host variable, DB2 may report error SQL0901N
    with reason "UDF search: Null qnc or qun pointer".  The host
    variable must be a local variable for the error to occur; it
    will not occur if the variable is a procedure or function
    parameter.  The error will not occur if you specify the pattern
    expression using a string constant.
    
    The same error may occur if you use a host variable to specify
    the pattern expression for LIKE, POSSTR, POSITION, LOCATE, or
    LOCATE_IN_STRING.
    
    This example fails with SQL0901N, because variable V_DEL is used
    to supply the pattern expression to INSTR:
    
    begin
      declare v_del varchar(10) default '|';
      declare v_pos integer;
    
      set v_pos = INSTR('a|bb|ccc|dddd|eeeee', v_del);
    end
    
    This example succeeds, because it uses a string constant to
    specify the pattern expression:
    
    begin
      declare v_pos integer;
    
      set v_pos = INSTR('a|bb|ccc|dddd|eeeee', '|');
    end
    

Local fix

  • If the pattern expression will never change, use a string
    constant to specify the pattern.  If the pattern is variable,
    use dynamic SQL to construct the statement containing the INSTR
    function.  For example:
    
    set serveroutput on %
    
    begin
      declare stmt_text varchar(255);
      declare v_del varchar(10) default '|';
      declare v_pos integer;
      declare S1 statement;
    
      set stmt_text = 'set ? = INSTR(''a|bb|ccc|dddd|eeeee'', ?)';
    
      prepare S1 from stmt_text;
      execute S1 into v_pos using v_del;
    
      call dbms_output.put_line(v_pos);
    end %
    DB20000I  The SQL command completed successfully.
    
    2
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * If you call the INSTR function and specify the pattern       *
    * expression using a host variable, DB2 may report error       *
    * SQL0901N with reason "UDF search: Null qnc or qun            *
    * pointer".  The host variable must be a local variable for    *
    * the error to occur; it will not occur if the variable is a   *
    * procedure or function parameter.  The error will not         *
    * occur if you specify the pattern expression using a          *
    * string constant.                                             *
    *                                                              *
    * The same error may occur if you use a host variable to       *
    * specify the pattern expression for LIKE, POSSTR,             *
    * POSITION, LOCATE, or LOCATE_IN_STRING.                       *
    *                                                              *
    * This example fails with SQL0901N, because variable V_DEL is  *
    * used to supply the pattern expression to INSTR:              *
    *                                                              *
    * begin                                                        *
    * declare v_del varchar(10) default '|';                       *
    * declare v_pos integer;                                       *
    *                                                              *
    * set v_pos = INSTR('a|bb|ccc|dddd|eeeee', v_del);             *
    * end                                                          *
    *                                                              *
    * This example succeeds, because it uses a string constant to  *
    * specify the pattern expression:                              *
    *                                                              *
    * begin                                                        *
    * declare v_pos integer;                                       *
    *                                                              *
    * set v_pos = INSTR('a|bb|ccc|dddd|eeeee', '|');               *
    * end                                                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 UDB version 9.7 fix pack 4.                   *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.7 Fix Pack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC73482

  • 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

    2010-12-23

  • Closed date

    2011-05-10

  • Last modified date

    2011-05-10

  • 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

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC73482

Modified date: 10 May 2011