Where can I find the source of structured type?

[nekiv90]

Greetings,

I was able to create the structured type:

CREATE TYPE address_t AS
( street char(30),
city char(15),
state char(10),
postcode smallint )
MODE DB2SQL;
Where can I find the source of the above?

I could not locate its source from any of these system tables:
syscat.datatype s, syscat.transfor ms and sysibm.user_def ined_types.

This is on W2K, DB2 V8.2

Thanks!

[Serge Rielau]

ne*****@hotmail .com wrote:

Greetings,

I was able to create the structured type:

CREATE TYPE address_t AS
( street char(30),
city char(15),
state char(10),
postcode smallint )
MODE DB2SQL;
Where can I find the source of the above?

I could not locate its source from any of these system tables:
syscat.datatype s, syscat.transfor ms and sysibm.user_def ined_types.

This is on W2K, DB2 V8.2

Thanks!

Can you clarify what you mean by "source"?
If you are looking for "CREATE TYPE..." you will be disappointed.
Teh two views that hold the information are SYSCAT.DATATYPE S and
SYSCAT.HIERARCH IES.
You shouldn't look at SYSIBM.* tables. They are not documented so
developers like me don't have to worry about breaking customers like you
every time you migrate an app to a new realease.

Have you tried db2look. It's teh standard tool that reverse engineers
SQL from the catalogs. IIRC structired types are supported.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[nekiv90]

Hi Serge,

By source,I meant the 'CREATE TYPE' statement.

As you mentioned, I search syscat.hierarch ies which has no entries.
DATATYPES has entry for the type defined but no details of 'CREATE
TYPE'.

db2look only returns table DDL, not the structure type, something like
this:

CREATE TABLE "myschema"."A1" (F1 address_t, F2 integer) in userspace1;

I expect the 'CREATE TYPE' ddl to be kept somewhere at least for
maintenance purpose, say what if we want to resize the 'street' col in
future? Where can we dig out the structured type DDL?

Thks!
Serge Rielau wrote:

ne*****@hotmail .com wrote:

Greetings,

I was able to create the structured type:

CREATE TYPE address_t AS
( street char(30),
city char(15),
state char(10),
postcode smallint )
MODE DB2SQL;
Where can I find the source of the above?

I could not locate its source from any of these system tables:
syscat.datatype s, syscat.transfor ms and sysibm.user_def ined_types.

This is on W2K, DB2 V8.2

Thanks!
Can you clarify what you mean by "source"?
If you are looking for "CREATE TYPE..." you will be disappointed.
Teh two views that hold the information are SYSCAT.DATATYPE S and
SYSCAT.HIERARCH IES.
You shouldn't look at SYSIBM.* tables. They are not documented so
developers like me don't have to worry about breaking customers like

you every time you migrate an app to a new realease.

Have you tried db2look. It's teh standard tool that reverse engineers SQL from the catalogs. IIRC structired types are supported.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

http://publib.boulder.ibm.com/infoce...e/r0002051.htm

-e
Extract DDL statements for database objects. DDL for the following
database objects are extracted when using the -e option:

* Tables
* Views
* Automatic summary tables (AST)
* Aliases
* Indexes
* Triggers
* Sequences
* User-defined distinct types
* Primary key, referential integrity, and check constraints
* User-defined structured types
* User-defined functions
* User-defined methods
* User-defined transforms
* Wrappers
* Servers
* User mappings
* Nicknames
* Type mappings
* Function templates
* Function mappings
* Index specifications
* Stored procedures
I'm quite certian I have seen structured type output from db2look....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[nekiv90]

My overlook!

Should have used 'db2look -d sample -e' instead of 'db2look -d sample
-t A1 -e'

Thanks!