Anyone using typed tables? I'd be interested to hear people's experiences.

Now the harder question - I'm having a problem with a definition.

CREATE TYPE PRODUCT_T AS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR(250),
COMMENT_DESC VARCHAR(3000),
EFFECTIVE_DATE DATE
)
REF USING INT
MODE DB2SQL;

--> success

CREATE TABLE PRODUCT OF PRODUCT_T (
PRODUCT_ID WITH OPTIONS
GENERATED BY DEFAULT AS IDENTITY (START WITH +1, INCREMENT BY +1, NO CACHE),
REF IS oid USER GENERATED);

--> fails with:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DEFAULT AS IDENTITY (START WITH +1, " was found following "WITH OPTIONS BY". Expected tokens may include: "<references_spec>". SQLSTATE=42601

According to the SQL ref for "CREATE TABLE", the "WITH OPTIONS" clause can be followed by "column-options", which includes "GENERATED BY...".

Other column-options work, ie. if I use "NOT NULL" it succeeds, but the generated column seems to throw it for a loop.

(BTW - I know the oid will already sort of serve this purpose, but there's a reason for this in our environment...)

Thanks,
Jon