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]
Monday, July 2, 2012
Subscribe to:
Post Comments (Atom)
LinkWithin
Posts
7 comments:
1st guess: col#2 contains a generated clause
2nd guess: there is an insert trigger creating this mess :)
Try again... ;-)
Henrik
One or maybe more columns are created with NOT NULL and IMPLICITLY HIDDEN options.
Hi Henrik,
Is "ih" an updateable view pointing to a table which has further columns including one or more not-null?
One of the suggested solutions is correct. The definition of the TABLE "ih" is relatively simple.
I posted a new article discussing the background. The article is linked above now.
I missed that was only one column. Nice quiz :)
Post a Comment