Distinct data type

[shsandeep]

1. I defined a distinct data type 'Surrogate_Key' using the below
statement:
set current schema Myschema;
CREATE DISTINCT TYPE Surrogate_Key AS INTEGER WITH COMPARISONS;

2. Created a table that uses the above data type
CREATE TABLE COV_TYPE (
COV_TYP_DT DATE,
COV_ID Myschema.Surrogate_Key
);

Now, when I do a 'describe table COV_TYPE' it shows LENGTH 0 for 'COV_ID'
column.
As a result, I am unable to insert any data into this column. Any solution
to get around this issue??
Thanks.

Cheers,
San.

[Serge Rielau]

shsandeep wrote:

1. I defined a distinct data type 'Surrogate_Key' using the below
statement:
set current schema Myschema;
CREATE DISTINCT TYPE Surrogate_Key AS INTEGER WITH COMPARISONS;

2. Created a table that uses the above data type
CREATE TABLE COV_TYPE (
COV_TYP_DT DATE,
COV_ID Myschema.Surrogate_Key
);

Now, when I do a 'describe table COV_TYPE' it shows LENGTH 0 for 'COV_ID'
column.
As a result, I am unable to insert any data into this column. Any solution
to get around this issue??

Looks like a bug in CLP. Do: DESCRIBE SELECT * FROM COV_TYPE
that works..
(Note that you can use the ADMIN_CMD() procedure to integrate DESCRIBE
into any SQL interface).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[shsandeep]

Here are the results for 'describe table' and 'describe select * from
twd_company'
What should i do? It does not allow me to enter data into the user defined
distinct types 'surrogate_key'.

db2 => describe table twd_company

Column Type Type
name schema name Length Scale
Nulls
------------------------------ --------- ------------------ -------- -----
------
COMP_CDE SYSIBM CHARACTER 1 0
No
COMP_NK_ID SYSIBM INTEGER 4 0
No
COMP_STRT_DT SYSIBM DATE 4 0
No
COMP_DESC SYSIBM VARCHAR 30 0
Yes
COMP_END_DT SYSIBM DATE 4 0
Yes
COMP_SANCTION_EOY_IND SYSIBM CHARACTER 1 0
Yes
COMP_GRP_NUM SYSIBM VARCHAR 8 0
Yes
COMP_BNK_BSB_CDE SYSIBM VARCHAR 6 0
Yes
COMP_BNK_ACC_CDE SYSIBM VARCHAR 9 0
Yes
COMP_HI_ID DEVSTGEV SURROGATE_KEY 0 0
No
SRCE_SYS_CDE SYSIBM VARCHAR 9 0
No
LOAD_ID DEVSTGEV SURROGATE_KEY 0 0
Yes
CUR_REC_IND DEVSTGEV FLAG 0 0
Yes

13 record(s) selected.

db2 => describe select * from twd_company

SQLDA Information

sqldaid : SQLDA sqldabc: 1472 sqln: 26 sqld: 13

Column Information

sqltype sqllen sqlname.data
sqlname.length sqllonglen sqldatatype_name.data
sqldatatype_name.length
-------------------- ------ ------------------------------
-------------- ---------- ---------------------------
-----------------------
452 CHARACTER 1 COMP_CDE
8 0 SYSIBM .CHARACTER 18
496 INTEGER 4 COMP_NK_ID
10 0 SYSIBM .INTEGER 16
384 DATE 10 COMP_STRT_DT
12 0 SYSIBM .DATE 13
449 VARCHAR 30 COMP_DESC
9 0 SYSIBM .VARCHAR 16
385 DATE 10 COMP_END_DT
11 0 SYSIBM .DATE 13
453 CHARACTER 1 COMP_SANCTION_EOY_IND
21 0 SYSIBM .CHARACTER 18
449 VARCHAR 8 COMP_GRP_NUM
12 0 SYSIBM .VARCHAR 16
449 VARCHAR 6 COMP_BNK_BSB_CDE
16 0 SYSIBM .VARCHAR 16
449 VARCHAR 9 COMP_BNK_ACC_CDE
16 0 SYSIBM .VARCHAR 16
496 INTEGER 4 COMP_HI_ID
10 0 DEVSTGEV.SURROGATE_KEY 22
448 VARCHAR 9 SRCE_SYS_CDE
12 0 SYSIBM .VARCHAR 16
497 INTEGER 4 LOAD_ID
7 0 DEVSTGEV.SURROGATE_KEY 22
501 SMALLINT 2 CUR_REC_IND
11 0 DEVSTGEV.FLAG 13

[shsandeep]

ignore the mail above...
its working fine now, there was a RI issue due to which columns were not
being populated.
but i would still like the 'describe table' output to show INTEGER 4
rather than the distinct data type 'Surrogate_Key'.
Any solutions for this?

cheers,
sandeep.

[Serge Rielau]

shsandeep wrote:

ignore the mail above...
its working fine now, there was a RI issue due to which columns were not
being populated.
but i would still like the 'describe table' output to show INTEGER 4
rather than the distinct data type 'Surrogate_Key'.
Any solutions for this?

Yes, open a PMR...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab