Blog
DB2utor

Categories

August 18, 2009

Implicitly Hidden Columns

For years DBAs have advised against the use of SELECT * in production applications. The problem is when a new column is added to a table, SELECT * picks up this new column. This forces you to change all of the programs that use your table -- even those that don't need the new column. And changing all programs poses a significant maintenance problem, because more downtime is needed to move in your modifications. All programs that use tables modified by SELECT * statements must go through a compile process, which also requires a rebind.

I still occasionally see applications containing SELECT * statements. It's unfortunate that some developers haven't gotten the word.

Fortunately, the Implicitly Hidden clause makes it easier to use SELECT *. Introduced with DB2 9 for z/OS, Implicitly Hidden can be added on the column specification of a CREATE or ALTER TABLE statement. By coding Implicitly Hidden, DB2 doesn't return the column in the result set unless it's explicitly referenced.

For example, in this table, COL2 is implicitly hidden, so it doesn't appear in the result set of a SELECT * statement:

  CREATE  TABLE T1
   ( COL1     CHAR ( 01 ) NOT NULL
   , COL2     CHAR ( 15 ) IMPLICITLY HIDDEN
   );

For shops using SELECT *, this is greatly beneficial. Your DBAs can add new columns to tables without having to change all of the programs that use the modified tables.

Implicitly Hidden comes with some restrictions, however. It can't be used on a column defined as ROWID or on a distinct type based on a ROWID. Additionally, Implicitly Hidden can't be specified on all columns of a table. Doing so triggers SQLCODE -20422, as seen here:

  CREATE  TABLE T1
   ( COL1   CHAR ( 01 ) IMPLICITLY HIDDEN
   , COL2   CHAR ( 15 ) IMPLICITLY HIDDEN
   );
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -20422, ERROR:  A CREATE TABLE, OR DECLARE GLOBAL TEMPORARY  TABLE STATEMENT FOR TEST ATTEMPTED TO CREATE A TABLE WITH ALL THE  COLUMNS DEFINED AS HIDDEN

Please share your experiences with this new feature by posting a comment.