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','http://www.imdb.com/title/tt0111161/')
DB20000I  The SQL command completed successfully.

Doing a multi-insert also worked:
insert into bestmovies values(2,'The Godfather','http://www.imdb.com/title/tt0068646/'),(3,'The Godfather: Part II','http://www.imdb.com/title/tt0071562/')
DB20000I  The SQL command completed successfully.

However, later I ran into a strange error:
insert into bestmovies values(20, 'The Hottie & the Nottie','http://www.imdb.com/title/tt0804492/')
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.

6 comments:

Anonymous said...

Open PMR for this issue

Henrik Loeser said...

Sure, never thought of this. For the "feature" or the missing documentation?

Henrik

Anonymous said...

Maybe DB2 balked at The Hottie & The Nottie being one of the 100 best movies and just chucked out a random error code?

Just joking... let us know how this resolves after opening the PMRs.

Anonymous said...

Its april joke guys. I tried it and it works fine on 9.7.1

Woz said...

It works on IDS.

Vineet said...

It works fine on DB2 9.7

LinkWithin

Related Posts with Thumbnails