Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC79499 Status: Closed

DB2 MAY TERMINATE ABNORMALLY WHEN CALLING AN SQL PROCEDURE WITH AN XML IN
PARAMETER

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
When callingl 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.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* SQL procedure with one or more XML parameters                * 
* declared with the IN attribute.                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When callingl an SQL procedure with one or more XML          * 
* parameters                                                   * 
* declared with the IN attribute, DB2 may terminate            * 
* abnormally.                                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB version 9.5 fixpack 9.                    * 
****************************************************************
Local Fix:
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 %
available fix packs:
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC80132 IC84183 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
27.10.2011
23.03.2012
23.03.2012
Problem solved at the following versions (IBM BugInfos)
9.5.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.