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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.