On Monday I posted a small quiz about an SQL error reported by DB2. In the comments were guesses about the solution, including generated columns, triggers, or the table actually being a view causing the error. However, the reason for the processing problem is a different one and it was also mentioned in the comments: The presence of an implicitly hidden column.
The concept of implicitly hidden columns was first introduced in DB2 9.5 for the purpose of easier and faster optimistic locking. A column defined as IMPLICITLY HIDDEN is not returned as part of a SELECT statement if not explicitly referenced. In DB2 9.5 and DB2 9.7 this feature was limited to a ROW CHANGE TIMESTAMP column type to implement the optimistic locking. With DB2 10.1 this has changed as you can see at the CREATE TABLE statement and a special page with an introduction to the concept of hidden columns. Now, you can hide all kinds of columns.
Here is how I defined the table that I used in the quiz:
db2 => create table ih(id int unique not null, s varchar(30), s2 varchar(30) not null implicitly hidden) DB20000I The SQL command completed successfully.
As you can see, the third column is hidden. Then I performed an insert which resulted in the reported error:
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
What is interesting to note is the following:
- Best practice and strongly advised is to have a default value defined for hidden columns to not run into such errors.
- In computer science many enumerations start with zero, not one. The column numbers, table identifiers, and tablespace identifiers in DB2 are no exception. Hence is the column with COLNO=2 the THIRD column which could mislead you. In the description for the error SQL0407N is actually a SQL query provided that helps resolve the mystery.
- Similar issues are possible with so-called data movement utilities like IMPORT and LOAD as you might have or not have data for the hidden column.
In the quiz I showed the output of DESCRIBE with only two columns reported.
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
Performing a DESCRIBE TABLE shows all three columns.
db2 => describe table ih
Data type Column Column name schema Data type name Length Scale Nulls
ID SYSIBM INTEGER 4 0 No S SYSIBM VARCHAR 30 0 Yes S2 SYSIBM VARCHAR 30 0 No
3 record(s) selected.
Finally, if you are sick of all the hidden stuff, you can act as a magician and let the columns reappear again:
db2 => alter table ih alter s2 set not hidden
DB20000I The SQL command completed successfully.
db2 => describe select * from ih
Column Information
Number of columns: 3
SQL type Type length Column name Name length
-------------------- ----------- ------------------------------ -----------
496 INTEGER 4 ID 2
449 VARCHAR 30 S 1
448 VARCHAR 30 S2 2
Now I will disappear by altering my state, coffee is waiting…