Implicitly hidden columns in DB2

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...