2

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) ) 

1 Answer 1

2

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 

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.