Hi All,

I have a encountered an annoying problem (with error SQL0440N) trying to implement an object view on a non-object table when the table columns are user defined distinct types. What is bizarre is that the everything works when the table user defined column types are replaced by the underlying primitive types. The following code demonstrates the problem:

CODE:
create distinct type BulidingName_t as varchar(60) with comparisons;
create distinct type Postcode_t as varchar(10) with comparisons;

create type Address_t as
(
buildingName BulidingName_t
,postcode Postcode_t
)
ref using bigint
mode DB2SQL;

create table AddressData
(
buildingName BulidingName_t
,postcode Postcode_t
,id bigint not null generated always as identity (start with 1, increment by 1)
,constraint Constraint_ID_PK primary key (id)
);

create table AddressData2
(
buildingName varchar(60)
,postcode varchar(10)
,id bigint not null generated always as identity (start with 1, increment by 1)
,constraint Constraint_ID_PK primary key (id)
);

create view Addresses of Address_t
mode DB2SQL
(
ref is oid user generated
)
as select
Address_t(id) oid
,buildingName
,postcode
from
AddressData;

create view Addresses2 of Address_t
mode DB2SQL
(
ref is oid user generated
)
as select
Address_t(id) oid
,buildingName
,postcode
from
AddressData2;

commit work;

END CODE

The data file is as follows:

data.txt:

"Building Name","Postcode"
"6","AB10 1AA"
"12","AB10 1AF"
"3","AB10 1AG"
"21","AB10 1AH"
"7","AB10 1AJ"
"6","AB10 1AL"
"8","AB10 1AN"

It is loaded into AddressData and AddressData2 with the commands

db2 import from data.txt of del modified by coldel, skipcount 1 insert into addressdata

db2 import from data.txt of del modified by coldel, skipcount 1 insert into addressdata2

both table get populated and all is good so far. I then select the data from the two views:

db2 select * from addresses2

OID BUILDINGNAME POSTCODE
-------------------- ------------------------------------------------------------ ----------
1 6 AB10 1AA
2 12 AB10 1AF
3 3 AB10 1AG
4 21 AB10 1AH
5 7 AB10 1AJ
6 6 AB10 1AL
7 8 AB10 1AN


However, when I issue the same command for the view with UDT columns I get the following:

db2 select * from addresses

SQL0440N No authorized routine named "BULIDINGNAME_T" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Admittedly, this behaviour doesn’t make much sense to me. It seems to suggest that DB2 can’t cast from a BULIDINGNAME_T to a BULIDINGNAME_T going from a non-typed table to a typed view. Is there something I’m missing or is this a bug?

Any help or insights will be greatly appreciated.

Many Thanks,
S.