DB2 10.5 for Linux, UNIX, and Windows

Hidden columns

When a table column is defined with the implicitly hidden attribute, that column is unavailable unless it is explicitly referenced. For example, if a SELECT * query is run against a table, implicitly hidden columns are not returned in the result table. An implicitly hidden column can always be referenced explicitly wherever a column name can be specified.

In cases where columns and their entries are generated by the database manager, defining such columns as IMPLICITLY HIDDEN can minimize any potential negative impact on your applications. For example, a system-period temporal table has three columns whose values are generated by the database manager. The database manager uses these columns to preserve historical versions of each table row. Most business applications would work with the historical data, but would rarely work with these three generated columns. Hiding these columns from your applications could reduce application processing time.

When inserting data into a table, an INSERT statement without a column list does not expect values for any implicitly hidden columns. In such cases, if the input includes a value for an implicitly hidden column, that value does not have a corresponding target column and an error is returned (SQLSTATE 42802). Because an INSERT statement without a column list does not include values for implicitly hidden columns, any columns that are defined as implicitly hidden and NOT NULL must have a defined default value

When populating a table with data from an input file, utilities like IMPORT, INGEST, and LOAD require that you specify whether data for the hidden columns is included in the operation. If a column list is not specified, data movement utilities must use the implicitlyhiddeninclude or implicitlyhiddenmissing file type modifiers when working with tables that contain implicitly hidden columns. You can also use the DB2_DMU_DEFAULT registry variable to set the default behavior when data movement utilities encounter tables with implicitly hidden columns. Similarly, EXPORT requires that you specify whether data for the hidden columns is included in the operation.

The implicitly hidden attribute can be defined on a table column using the CREATE TABLE statement for new tables, or the ALTER TABLE statement for existing tables. If a table is created using a CREATE TABLE statement with the LIKE clause, any implicitly hidden columns in the source table are inherited by the new table. The ALTER TABLE statement can be used to change hidden columns to not hidden or to change not hidden columns to hidden. Altering a table to change the hidden attribute of some columns can impact the behavior of data movement utilities that are working with the table. For example, this might mean that a load operation that ran successfully before the table was altered to define some hidden columns, now returns an error (SQLCODE -2437).

The list of names identifying the columns of a result table from a SELECT query run with the exposed-name.* option does not include any implicitly hidden columns. A SELECT query run with the order-by-clause can include implicitly hidden columns in the simple-column-name.

If an implicitly hidden column is explicitly referenced in a materialized query table definition, that column will be a part of the materialized query table. However the column in the materialized query table does not inherit the implicitly hidden attribute. This same behaviour applies to views and tables created with the as-result-table clause.

An implicitly hidden column can be explicitly referenced in a CREATE INDEX statement, ALTER TABLE statement, or in a referential constraint.

A transition variable exists for any column defined as implicitly hidden. In the body of a trigger, a transition variable that corresponds to an implicitly hidden column can be referenced.

Implicitly hidden columns are not supported in created temporary tables and declared temporary tables.

Hidden columns for a table can be displayed using the DESCRIBE command.