DB2 FOR LUW / DB2FORLUW / 970 - DB2

When calling an SQL procedure with one or more XML parameters 
declared with the IN attribute, DB2 may terminate abnormally if 
one of the following conditions are met: 
 
- The IN parameter can be modified by a statement in the 
procedure.  For example, the parameter appears on the left-hand 
side of a SET statement, or is passed as a parameter to a 
routine that could modify the value. 
 
- The IN parameter appears in a result set query; that is, the 
parameter is used in the declaration of some WITH RETURN cursor 
in the procedure. 
 
DB2 is more likely to terminate abnormally in the case where 
passing the result of a query or some other XML-valued 
expression to the IN parameter.  The problem should not occur if 
you simply pass an XML variable to the parameter.  For example: 
 
create procedure test(IN X XML) 
begin 
  declare C1 cursor with return to caller for 
    select part_no 
    from xmltable('$DOC/*' passing X as "DOC" 
      columns 
        part_no varchar(128) path 'pno'); 
 
  open C1; 
end % 
 
-- Passing a value from XML column PRODUCTS.CONFIGURATION. 
-- This call could trigger the problem: 
call test((select P.configuration from products P where 
id=P.id)) % 
 
-- An alternative case that should not fail: 
create procedure caller() 
begin 
  declare config XML; 
 
  -- Some statements here to give CONFIG a value... 
 
  -- This call to TEST should be safe: 
  call test(config); 
end % 
 
In addition to these factors, the size of the XML document being 
passed also determines whether DB2 will terminate abnormally. 
Small documents (up to roughly 1-2K bytes) may not result in 
abnormal termination, even if the above mentioned problem 
criteria are met.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users with SQL procedures that have xml "IN" PARAMETER.      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW version 97fp6                             * 
****************************************************************

In the declaration of an SQL procedure, avoid modifying any 
XML-typed parameter declared with the IN attribute.  Do not use 
XML IN parameters in cursor declarations.  If necessary, declare 
a local XML variable and copy the IN parameter to the variable, 
then use the local variable instead of the parameter.  For 
example: 
 
create procedure test(IN X XML) 
begin 
  declare X0 XML; 
 
  declare C1 cursor with return to caller for 
    select part_no 
    from xmltable('$DOC/*' passing X0 as "DOC" 
      columns 
        part_no varchar(128) path 'pno'); 
 
  set X0 = X; 
  open C1; 
end %

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
 

not known / see Local fix

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

01.12.2011
13.06.2012
13.06.2012

9.7.FP6