Trying to add column of CLOB type to table in DB2

Question

I need to add a column of type "CLOB" to the existing table in the database, so I am using the below query:

alter table foldet add("FOLDER_FIELD_VALUE_TWO" CLOB);

but I am getting the below error

Error starting at line : 5 in command - alter table foldet add("FOLDER_FIELD_VALUE_TWO" CLOB) Error report - SQL Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CLOB;DER_FIELD_VALUE_TWO";JOIN , DRIVER=3.63.75

How to add a column to the table?

You can quickly look up the meaning of an SQLCODE value by running the command `db2 ? sql`, e.g. `db2 ? sql104`. Alternatively, you can find all these codes in the [manual](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/rsqlmsg.html?lang=en) — mustaccio, Jan 19 '16 at 17:26

score 2 · Accepted Answer · answered Jan 20 '16 at 01:49

You don't say what DB2 version or platform sends the error response, but the SQLSTATE/SQLCODE values should be consistent across essentially all of them. Looking up SQLSTATE 42501 in DB2 for i 7.2, the given reason is:

The authorization ID does not have the privilege to perform the specified operation on the identified object.

IOW, you don't have sufficient authority to change the structure of the table when you connect with the userid that received the error.

SQLSTATE 42501 is from you latest comment. From your question, SQLSTATE 42601 is:

A character, token, or clause is invalid or missing.

Your comment describes how you cleared that up, and the answer from @SimeonVanov correctly addresses that.

score 1 · Answer 2 · answered Jan 19 '16 at 15:31

1

ALTER TABLE table_name

ADD column_name datatype

This is the syntax for adding column to a table. So if you get rid of the brackets I think it should be ok.

answered Jan 19 '16 at 15:31

Simeon Vanov

361
3
13

i have removed the brackets from the query alter table foldet add FOLDER_FIELD_TWO CLOB; but still i am getting the below error Error starting at line : 5 in command - alter table foldet add FOLDER_FIELD_TWO CLOB; Error report - SQL Error: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=IPASS;ALTER TABLE;FOLDET, DRIVER=3.63.75 – vemak Jan 19 '16 at 15:43

score 1 · Answer 3 · answered Jan 20 '16 at 01:36

You're getting the error (-551) because you don't have the ALTER authority on the table. It's all there in the error message, as shown in the manual.

SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=IPASS;ALTER TABLE;FOLDET, DRIVER=3.63.75
                   ^^^^                           ^^^   ^^^^^^^^^^    ^^^
                error code                       user   privilege    table

Trying to add column of CLOB type to table in DB2

3 Answers3