DB2 FOR LUW / DB2FORLUW / A10 - DB2

Using CREATE OR REPLACE FUNCTION allows one to replace a 
function signature that is system-generated for a user-defined 
type. Such a function cannot be dropped and therefore should not 
allow the function to be replaced.  Here is a simple example: 
 
create type schema.rrr3 as integer 
DB20000I  The SQL command completed successfully. 
 
-- drop of a generated cast function fails as expected 
drop function rrr3(int) 
DB21034E  The command was processed as an SQL statement because 
it was not a 
valid Command Line Processor command.  During SQL processing it 
returned: 
SQL0658N  The object "SCHEMA.RRR3" cannot be explicitly dropped 
or altered. 
SQLSTATE=42917 
 
-- generated cast function can be used and generates the same 
value as the argument. 
values rrr3(5) 
 
1 
----------- 
          5 
 
  1 record(s) selected. 
 
-- the generated function gets replaced (should fail, but does 
not) 
create or replace function rrr3(p1 int) returns rrr3 begin end 
DB20000I  The SQL command completed successfully. 
 
-- the replaced function gets invoked since it returns null 
instead of 5 
values rrr3(5) 
 
1 
----------- 
          - 
 
  1 record(s) selected. 
 
-- this breaks the CAST specification from integer to distinct 
type 
values cast(5 as rrr3) 
SQL0461N  A value with data type "SYSIBM.INTEGER" cannot be CAST 
to type 
"SWAGRMAN.RRR3".  SQLSTATE=42846

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 fix pack 3                       * 
****************************************************************

Drop and recreate the distinct type

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 version 10.1 fix pack 3

not known / see Local fix

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

13.03.2013
07.10.2013
07.10.2013