DB2 FOR LUW / DB2FORLUW / 970 - DB2

After a type has been dropped, the subsequent call of a 
procedure that casts a host variable as the dropped type fails 
to regenerate the type. 
 
For example, in the following scenario, SQL901N instead of the 
proper SQL723N (SQL204N) may be returned. 
 
create table t1 (c1 int, c2 int); 
create type r1 as row (c1 int, c2 int); 
create type r2 as row (c1 int, c2 int); 
create type a2 as r1 array[10]; 
 
create function foo() returns a2 
begin 
 declare lv1 a2;-- 
 return lv1;-- 
end; 
 
create or replace procedure poo() 
begin 
 declare s1 varchar(200);-- 
 set s1 = 'delete from t1 where c1 = cardinality(cast (? as 
a2))';-- 
 prepare st from s1;-- 
 execute st using foo();-- 
end; 
 
drop type r1; 
drop type r2; 
 
-- SQL901N 
call poo(); 
 
Here is the error stack: 
---------------------------------------------------------------- 
---------------- 
--sqlnn_cmpl                [300]:rc(-2144272270) Error in 
parser 
--sqlnp_main                [250]:rc(-2144272270) Invoke LPG 
parser (semantic phase) 
--sqlnp_parser              [330]:rc(-2144272270) Call smactn 
 
--sqlnp_smactn              [100]:rc(-2144272270) Processing 
semantic action 
--sqlnq_sem_host            [245]:rc(-2144272270) convert host 
variable type to dat_TYPTYPE 
--sqlnq_convertHVtoDatTyp   [243]:rc(-2144272209) Could not 
fetch type packed descriptor. 
---------------------------------------------------------------- 
---------------- 
--SQL0901N  The SQL statement failed because of a non-severe 
system error. 
--Subsequent SQL statements can be processed.  (Reason 
"sqlnq_convertHVtoDatTyp 
--[243]:rc(-2144272209) Could not fetch".)  SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 7 or later.              * 
****************************************************************

Calling a procedure that depends on a type that has been dropped 
will result in an error SQL723N (SQL204N) anyway.  Users should 
either drop the procedure or recreate the missing type. 
 
If the type is recreated, before calling the user procedure, 
call the following admin procedure to manually regenerate the 
type: 
    call admin_revalidate_db_objects('type', NULL, NULL);

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 has been fixed in DB2 Version 9.7 Fix Pack 7.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC88633 
follow-up : 

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

22.08.2012
20.10.2012
20.10.2012

9.7.FP7