Monday, July 2, 2012

A small DB2 SQL quiz - why the SQL error?

I have been playing with some new DB2 10.1 features and thought that a small SQL quiz at the beginning of the week is appropriate. Consider the following SQL statements and the related output:

db2 => insert into ih values(1,'a')
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=257, COLNO=2" is not allowed.  SQLSTATE=23502
db2 => select * from ih

ID          S                            
----------- ------------------------------

  0 record(s) selected.

db2 => describe select * from ih

 Column Information

 Number of columns: 2

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 496   INTEGER                   4  ID                                        2
 449   VARCHAR                  30  S                                         1



How was the table defined or why is it giving an error? Any guesses? Post your answer and explanation as comment. I will post the solution in a couple days. [Edit: done and linked]

7 comments:

dr_te_z said...

1st guess: col#2 contains a generated clause
2nd guess: there is an insert trigger creating this mess :)

Henrik Loeser said...

Try again... ;-)

Henrik

ada$ko said...

One or maybe more columns are created with NOT NULL and IMPLICITLY HIDDEN options.

Norberto said...

Hi Henrik,

Is "ih" an updateable view pointing to a table which has further columns including one or more not-null?

Henrik Loeser said...

One of the suggested solutions is correct. The definition of the TABLE "ih" is relatively simple.

Henrik Loeser said...

I posted a new article discussing the background. The article is linked above now.

ada$ko said...

I missed that was only one column. Nice quiz :)

LinkWithin

Related Posts with Thumbnails