Technical Blog Post
Abstract
DB2LUW Generated Column cosideration in v10.5 vs v11 with BLU
Body
An example BLU CREATE TABLE query like following,
CREATE TABLE BLUTAB ( COL1 INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ) ORGANIZE BY COLUMN;
Will fail under DB2 V10.5 with following messages :
CREATE TABLE BLUTAB ( COL1 INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ) ORGANIZE BY COLUMN
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1666N The table definition statement failed because some functionality was
specified in the table definition that is not supported with the table type.
Unsupported functionality: "GENERATED". SQLSTATE=42613
Whereas it will succeed in V11 :
CREATE TABLE BLUTAB ( COL1 INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ) ORGANIZE BY COLUMN
DB20000I The SQL command completed successfully.
This is a feature which is relaxed from V10.5 to V11 with BLU.
And, it's documented in the Db2 Knowledge Center (KC).
Here it is in V10.5 KC under "Considerations for column-organized tables":
https://www.ibm.com/support/kn
"Generated columns (including GENERATED AS IDENTITY) and structured type columns are not supported."
vs. in V11 KC,
https://www.ibm.com/support/kn
"A statement to create a column of a column-organized table can specify a generated-clause of the form GENERATED AS (generation-expression) or GENERATED AS IDENTITY . However, it cannot specify other generated-clauses such as GENERATED AS ROW BEGIN/END or GENERATED AS TRANSACTION START ID."
UID
ibm11139884