DB2 FOR LUW / DB2FORLUW / 970 - DB2

An SQL routine may terminate abnormally, if the routine is 
passed one or more parameters that satisfy the following 
conditions: 
 
- The parameter is a row (or record) type, containing at least 
one array field, whose element type is also a row or array type; 
- The parameter is declared as an IN parameter, and is modified 
by the routine being called; OR, the routine being called has 
been compiled for use with the Optim Data Studio debugger. 
Remember that parameters are IN by default in PL/SQL 
and DB2 SQL PL routines. 
 
Under these circumstances, you may observe a stack trace similar 
to the following: 
 
offset: 00000000000000DC in <sqlvSqlPLBuildArray> 
<sqlvSqlPL.C:938> 
offset: 000000000000060E in <pvmVarTable::shadowParameter> 
<psm_pvm.C:381> 
offset: 0000000000000820 in <pvmVarTable::initialize> 
<psm_pvm.C:947> 
offset: 0000000000000A2B in <PVM::PVM> <psm_pvm.C:4411> 
offset: 000000000000010E in <pvm_entry> <psm_entrypoints.C:244> 
offset: 0000000000000075 in <sqloInvokeFnArgs> <sqloutil.C:1483> 
offset: 000000000000220C in <sqlriInvokerTrusted> 
<sqlriudf.C:8382> 
offset: 00000000000005FF in <sqlriInvokeInvoker> 
<sqlriudf.C:6886> 
offset: 0000000000000105 in <sqlricall> <sqlriudf.C:1818> 
offset: 0000000000000109 in <sqlriSectInvoke> <sqlri_exe.C:921> 
offset: 0000000000000742 in <sqlrr_process_execute_request> 
<sqlrr_sql.C:1558> 
offset: 000000000000023D in <sqlrr_execute> 
<sqlrr_req_sql.C:833> 
offset: 00000000000002D4 in <pvmPackage::executeSection> 
<psm_pvm.C:3011> 
offset: 00000000000001D4 in <PVM::run> <psm_pvm.C:4939> 
offset: 0000000000000127 in <pvm_entry> <psm_entrypoints.C:247> 
 
Here is an example of the problem that uses PL/SQL: 
 
set sqlcompat plsql; 
 
create or replace package FOO as 
 
  type B is record ( 
    x integer 
    ); 
 
  type B_table is table of B index by integer; 
 
  type A is record ( 
    b B_table 
    ); 
 
  procedure add_A(A_in A); 
 
end FOO; 
/ 
 
call sysproc.set_routine_opts('DEBUG OFF'); 
 
create or replace package body FOO is 
 
  procedure add_A(A_in A) is 
    dummy B_table; 
  begin 
    A_in.b := dummy; 
  end; 
 
end FOO; 
/ 
 
create or replace procedure test 
as 
  x FOO.A; 
begin 
  FOO.add_A(x); 
end; 
/ 
 
begin 
  test; 
  test; 
end; 
/ 
 
Procedure FOO.add_A will terminate abnormally if called more 
than once. 
 
A similar example can be constructed using DB2 SQL PL; this 
problem is not specific to PL/SQL.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 6.                       * 
****************************************************************

If it is necessary to modify the value of an IN parameter within 
an SQL routine, make a copy of the variable and work with the 
copy instead.  For example: 
 
create or replace package body FOO is 
 
  procedure add_A(A_in A) is 
    dummy B_table; 
    A_copy A; 
  begin 
    A_copy := A_in; 
    A_copy.b := dummy; 
  end; 
 
end FOO; 
/

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
 

This problem is fixed in DB2 Version 9.7 Fix Pack 6.

not known / see Local fix

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

09.05.2012
19.06.2012
19.06.2012

9.7.FP6