IBM Support

DB2LUW Generated Column cosideration in v10.5 vs v11 with BLU

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/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

"Generated columns (including GENERATED AS IDENTITY) and structured type columns are not supported."

 

vs. in V11 KC,

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

"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."

 

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11139884