Where can I find the source of structured type?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nekiv90@hotmail.com - 09 Apr 2005 12:50 GMT

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.datatypes, syscat.transforms and sysibm.user_defined_types.

This is on W2K, DB2 V8.2

Thanks!

Reply to this Message

Serge Rielau - 09 Apr 2005 13:38 GMT

> Greetings,
>
[quoted text clipped - 15 lines]
>
> 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.DATATYPES and
SYSCAT.HIERARCHIES.
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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

nekiv90@hotmail.com - 12 Apr 2005 01:56 GMT

Hi Serge,

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

As you mentioned, I search syscat.hierarchies 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!

> > Greetings,
> >
[quoted text clipped - 25 lines]
>
> Have you tried db2look. It's teh standard tool that reverse engineers

> SQL from the catalogs. IIRC structired types are supported.
>
> Cheers
> Serge

Reply to this Message

Serge Rielau - 12 Apr 2005 03:38 GMT

http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/core/
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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

nekiv90@hotmail.com - 12 Apr 2005 07:53 GMT

My overlook!

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

Thanks!

Reply to this Message