DB2 FOR LUW / DB2FORLUW / 970 - DB2

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

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

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

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 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 was first fixed in DB2 UDB Version 9.7 Fix Pack 4

not known / see Local fix

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

23.12.2010
10.05.2011
10.05.2011

9.7.FP4