DB2 FOR LUW / DB2FORLUW / 970 - DB2

When you create or replace an SQL procedure that contains 
variables anchored to a table row with one or more IMPLICITLY 
HIDDEN columns, DB2 may crash in sqlnqBuildRowTypeDescriptor 
with the following elements present in the stack: 
 
Stack #1             Signal #11 
0        ?unknown 
1        sqlnqBuildRowTypeDescriptor 
2        sqlnqBuildRowTypeDescriptor 
3        sqlnq_psm_sem_act 
4        sqlnp_parser 
5        sqlnp_parser 
6        sqlnp_main 
7        sqlnn_cmpl 
8        sqlnn_cmpl 
9        sqlra_compile_var 
10       sqlra_find_var 
11       sqlra_get_var 
12       sqlri_ddl_get_section 
13       sqlri_ddl_common 
14       sqlriddl 
15       sqlriSectInvoke 
16       sqlrr_execute_immediate 
17       sqlrr_execimmd 
18       sqljs_ddm_excsqlimm 
19       sqljsParseRdbAccessed 
20 
.sqljsParse.fdpr.clone.52__FP13sqljsDrdaAsCbP14db2UCinterfaceP8s 
qeAgentb 
21       @63@sqljsSqlam 
22       @63@sqljsDriveRequests 
23       @63@sqljsDrdaAsInnerDriver 
24       sqljsDrdaAsDriver 
25       sqeAgent::RunEDU 
26       sqzEDUObj::EDUDriver 
27       sqloEDUEntry 
 
Example test : 
 
create table T1( 
  C1 integer, 
  C2 integer, 
  C3 timestamp not null implicitly hidden generated always for 
each row on update as row change timestamp 
  ) % 
 
insert into T1(C1, C2) values (42, 101) % 
 
create or replace procedure proc1 
begin 
  declare anchor1 anchor row T1; 
 
  select * into anchor1 from T1; 
  call dbms_output.put_line(anchor1.C1); 
end %

**************************************************************** 
* USERS AFFECTED:                                              * 
* Customer with stored procedure containing variables anchored * 
* to a table with implicitly hidden columns.                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade do DB2 LUW version 9.7 fixpack 9                     * 
****************************************************************

Instead of anchoring a variable directly to a table row with 
IMPLICITLY HIDDEN columns, create an equivalent row type 
definition that explicitly anchors each field to an individual 
table column, omitting any IMPLICITLY HIDDEN columns.  Then 
declare the variable using the row type. 
 
Using the example from the error description: 
 
create table T1( 
  C1 integer, 
  C2 integer, 
  C3 timestamp not null implicitly hidden generated always for 
each row on update as row change timestamp 
  ) % 
 
insert into T1(C1, C2) values (42, 101) % 
 
create type T1_row as row ( 
  C1 anchor to T1.C1, 
  C2 anchor to T1.C2 
  ) % 
 
create or replace procedure proc1 begin 
  declare anchor1 T1_row; 
 
  select * into anchor1 from T1; 
  call dbms_output.put_line(anchor1.C1); 
end %

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       :

23.04.2013
16.12.2013
16.12.2013

9.7.FP9