Thursday, April 1, 2010

(Updated) Strange error on insert into DB2

Some time ago I ran into a strange error when inserting data into a table using DB2 9.7.1 on Windows. To prepare for a database class I wanted to create a table with the 50 best movies ever. I started off with:

create table bestmovies(rank int unique not null, title varchar(100), url varchar(200))
DB20000I  The SQL command completed successfully.

So far, so good. Trying to be a good teacher I planned to show how to document database objects:
comment on table bestmovies is 'Table with the best movies ever,  URL is for imdb links'
DB20000I  The SQL command completed successfully.

Then I inserted some data to try out that it works. For later importing from a file was planned.
insert into bestmovies values(1,'The Shawshank Redemption','')
DB20000I  The SQL command completed successfully.

Doing a multi-insert also worked:
insert into bestmovies values(2,'The Godfather',''),(3,'The Godfather: Part II','')
DB20000I  The SQL command completed successfully.

However, later I ran into a strange error:
insert into bestmovies values(20, 'The Hottie & the Nottie','')
DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned:
SQL0114N  The value(s) violate a semantic condition defined on the table "HLOESER.BESTMOVIES".   SQLSTATE=42603

I couldn't find a description of the error code or the error message in the DB2 Information Center. The error code is not listed. Usually I am not using comments on my regular test tables, so I am not sure what it means. Do you have any clue or give me pointers? This looks very interesting.

Update: After dropping the comment on the table, it now works.