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.Surrog ate_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. 4 1948
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.Surrog ate_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
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_E OY_IND SYSIBM CHARACTER 1 0
Yes
COMP_GRP_NUM SYSIBM VARCHAR 8 0
Yes
COMP_BNK_BSB_CD E SYSIBM VARCHAR 6 0
Yes
COMP_BNK_ACC_CD E 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_nam e.data
sqldatatype_nam e.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_E OY_IND
21 0 SYSIBM .CHARACTER 18
449 VARCHAR 8 COMP_GRP_NUM
12 0 SYSIBM .VARCHAR 16
449 VARCHAR 6 COMP_BNK_BSB_CD E
16 0 SYSIBM .VARCHAR 16
449 VARCHAR 9 COMP_BNK_ACC_CD E
16 0 SYSIBM .VARCHAR 16
496 INTEGER 4 COMP_HI_ID
10 0 DEVSTGEV.SURROG ATE_KEY 22
448 VARCHAR 9 SRCE_SYS_CDE
12 0 SYSIBM .VARCHAR 16
497 INTEGER 4 LOAD_ID
7 0 DEVSTGEV.SURROG ATE_KEY 22
501 SMALLINT 2 CUR_REC_IND
11 0 DEVSTGEV.FLAG 13
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: nfrodsham |
last post by:
In Microsoft's help literature, it states:
"You can filter out non-unique rows by using the DISTINCT option of an
aggregate function"
I am trying to do this in Access 2003 with the COUNT aggregate
function, but there is no reference, at least that I can find anywhere,
of how to do this. I have multiple lines fields for which I would like...
|
by: shsandeep |
last post by:
Can a distinct data type be altered?
Eg: Suppose 'Surrogate_Key' is defined as CHAR(1).
Can it be altered to CHAR(2)?
And will the changes be automatically propagated to all columns that use
'Surrogate_Key' data type?
Thanks.
|
by: RussCRM |
last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes.
I've been using
Here’s the situation:
I have two main tables:
Guest (stores data such as GuestID, First Name, Last Name, etc.)...
|
by: ebusiness |
last post by:
I am trying to create nicknames for tables in a remote database. The
federated database and remote database are both DB2 UDB V8.2, both OS
are AIX.
Could anyone tell me how to create a nickname for a remote table with
distinct data type? I try to create a type maaping for the remote
distinct data type, but the system always returns the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |