Unanswered: Typed table definition
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
REF USING INT
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...)
DB2 Database Consultant