DB2 add Primary Key to existing table created from Jupyter Lab via SQL magic 'PERSIST'

Question

I created sample table from Jupyter Lab notebook via:

file_csv = 'data.csv'
df = pd.read_csv(file_csv)
df.insert(0, 'KEY_ID', df.index)

Then added table to DB2 on IBM cloud via:

%sql PERSIST df

Looking at that table in DB2 console it is created w/o primary key - so I tried to remedy it:

ALTER TABLE DF
    ALTER COLUMN KEY_ID
    SET NOT NULL;

And set KEY_ID as primary key:

ALTER TABLE DF
    ADD PRIMARY KEY(KEY_ID);

But DB2 stubbornly refuses with following error:

InternalError: (ibm_db_dbi.InternalError) ibm_db_dbi::InternalError: Exception('Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0668N Operation not allowed for reason code "7" on table "MYID9999.DF". SQLSTATE=57016\r SQLCODE=-668')
[SQL: ALTER TABLE DF
ADD PRIMARY KEY(KEY_ID);]
(Background on this error at: http://sqlalche.me/e/2j85)

Tom N · Accepted Answer · 2020-05-29 22:35:49Z

1

OK - I actually solved it while typing the question - but I will post it anyway. You need to REORG the table (I guess after resetting 'KEY_ID' column props to NOT NULL) and it only worked from when run like this:

CALL SYSPROC.ADMIN_CMD ('REORG TABLE DF')

But after that ALTER command works like a charm.

answered May 29, 2020 at 22:35

Tom N

499 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DB2 add Primary Key to existing table created from Jupyter Lab via SQL magic 'PERSIST'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related