Very Computer

Hi,

I'm hammering my head into the DB2 UDB v5.2 Administration guide and into the
DB2 UDB v5.2 SQL reference but some of the object-relational things don't
work like I expect them to work. Can please somebody assist me with some
hints? We're on DB2 UDB v5.2 (9073).

1.) Ok let's start with the following type:

 CREATE TYPE COUNTRY_t AS
    (TEXT VARCHAR(250))
    WITHOUT COMPARISONS
    NOT FINAL
    MODE DB2SQL;

2.) Let's create a table from this type. But this one is not allowed. The
PRIMARY KEY constraint is rejected but I don't know why. There's no PRIMARY
KEY on this table. The docs don't show an example on this one but the syntax
diagrams shows it as valid:

 CREATE TABLE COUNTRY OF COUNTRY_t
    (REF IS OID USER GENERATED,
     TEXT WITH OPTIONS NOT NULL DEFAULT,
     CONSTRAINT COUNTRY_p PRIMARY KEY (OID));

3.) Even this one is rejected:

 CREATE TABLE COUNTRY OF COUNTRY_t
    (REF IS OID USER GENERATED,
     TEXT WITH OPTIONS NOT NULL DEFAULT,
     CONSTRAINT COUNTRY_u UNIQUE (TEXT));

4.) If I omit the CONSTRAINTS everything works:

 CREATE TABLE COUNTRY OF COUNTRY_t
    (REF IS OID USER GENERATED,
     TEXT WITH OPTIONS NOT NULL DEFAULT);

5.) Now start to create a trigger for the OID. It doesn't work. I think it
should work. How to create a trigger to generate unique keys for OID:

 CREATE TRIGGER COUNTRY
    NO CASCADE
    BEFORE INSERT ON COUNTRY
    REFERENCING NEW AS NEWROW
    FOR EACH ROW
    MODE DB2SQL
    SET OID=COUNTRY_t(GENERATE_UNIQUE());

6.) This one works but seems to do the same like the rejected trigger shown
in 5.)

 INSERT INTO COUNTRY (OID,BEZEICHNUNG)
    VALUES (COUNTRY_t(GENERATE_UNIQUE()),'Germany');

Are the object-relational capabilities of DB2 UDB v5.2 (9073) ready yet? I
tried some more things like create a FOREIGN KEY constraint (doesn't work),
CAST a GENERATE_UNIQUE result to type COUNTRY_t (doesn't work) ... and a lot
things more.

Many thanks in advance.

 ____________________________________________________________________________
 PGP Public Key Fingerprint: 79 5B DF 50 21 62 E6 28  C8 17 91 C7 C7 5B CD 22

 Visit me at http://www.hawi.de
 Harald (HaWi) Wilhelm

I didn't see a response to this, although I might have missed one over
Christmas.  Here is some clarification that I got from a senior SQL
guy at the Toronto lab.

Quote:>1.) Ok let's start with the following type:

> CREATE TYPE COUNTRY_t AS
>    (TEXT VARCHAR(250))
>    WITHOUT COMPARISONS
>    NOT FINAL
>    MODE DB2SQL;

>2.) Let's create a table from this type. But this one is not allowed. The
>PRIMARY KEY constraint is rejected but I don't know why. There's no PRIMARY
>KEY on this table. The docs don't show an example on this one but the syntax
>diagrams shows it as valid:

> CREATE TABLE COUNTRY OF COUNTRY_t
>    (REF IS OID USER GENERATED,
>     TEXT WITH OPTIONS NOT NULL DEFAULT,
>     CONSTRAINT COUNTRY_p PRIMARY KEY (OID));

The example in 2) actually works on V5.2.  It does issue a warning
because the OID column has an implicitly generated unique index that
is then "taken over" for use by the primary key constraint.  Here is
the warning:

SQL0598W  Existing index "SWAGRMAN.SQL981214171436470" is used as the
index for the primary key or a unique key.  SQLSTATE=01550

Quote:>3.) Even this one is rejected:

> CREATE TABLE COUNTRY OF COUNTRY_t
>    (REF IS OID USER GENERATED,
>     TEXT WITH OPTIONS NOT NULL DEFAULT,
>     CONSTRAINT COUNTRY_u UNIQUE (TEXT));

The example in 3) fails because the column is too long.  The maximum
length that can be created is actually VARCHAR(247) NOT NULL since 4
bytes are taken for the type identifier and another 4 bytes for the
varchar overhead (see CREATE INDEX for the information that indicates
the restriction for indexes on typed tables is less than regular
tables).

Quote:>4.) If I omit the CONSTRAINTS everything works:

> CREATE TABLE COUNTRY OF COUNTRY_t
>    (REF IS OID USER GENERATED,
>     TEXT WITH OPTIONS NOT NULL DEFAULT);

>5.) Now start to create a trigger for the OID. It doesn't work. I think it
>should work. How to create a trigger to generate unique keys for OID:

> CREATE TRIGGER COUNTRY
>    NO CASCADE
>    BEFORE INSERT ON COUNTRY
>    REFERENCING NEW AS NEWROW
>    FOR EACH ROW
>    MODE DB2SQL
>    SET OID=COUNTRY_t(GENERATE_UNIQUE());

   The example in 5) fails because triggers are not supported on typed
   tables in Version 5.2 (see CREATE TRIGGER, ON table-name clause for
   restriction).  Error returned is SQL0270N with reason code 9.

   There are further restrictions including not supporting referential
   constraints (foreign keys) and check constraints on typed tables in
   Version 5.2.

Hope this helps!

John
--
John Russell