How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

Question

On IBM DB2 I try the following direct SQL:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), 
   user_name VARCHAR(16) NOT NULL, 
   email_address VARCHAR(60), 
   PRIMARY KEY (user_id)
)

Then I get the following error:

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".. SQLCODE=-1666, SQLSTATE=42613, DRIVER=3.63.123 SQL Code: -1666, SQL State: 42613

GENERATED BY DEFAULT is supposed to be supported according to IBM DB2 Documentation.

Is there something incorrect in my SQL?

EDIT:

I used sqlalchemy and ibm_db_sa with the following code:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), 
   user_name VARCHAR(16) NOT NULL, 
   email_address VARCHAR(60), 
   PRIMARY KEY (user_id)
)

However, by adding autoincrement=False the IDENTITY is removed and the SQL executes without problem:

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True, autoincrement=False),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email')
)

which produces:

CREATE TABLE users2 (
    user_id INT NOT NULL, 
    user_name VARCHAR(16) NOT NULL, 
    email_address VARCHAR(60),
    PRIMARY KEY (user_id)
)

Answer 1

Column-organized tables do not support some features, including identity columns. Apparently, in your database the default table organization (the DFT_TABLE_ORG parameter) is set to columns. Either change that or create the row-organized table explicitly:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), 
   user_name VARCHAR(16) NOT NULL, 
   email_address VARCHAR(60), 
   PRIMARY KEY (user_id)
) ORGANIZE BY ROW