Data movement utilities and hidden columns
A hidden column is a table column defined with the
attribute. That column is unavailable unless explicitly referenced.
For example, if a
SELECT * query is run
against a table, implicitly hidden columns are not returned in the
result table. To be able to access this column, its name should be
explicitly referenced in the
IMPORT, INGEST, and
LOAD utilities require you to 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
implicitlyhiddenmissing file type modifiers
when working with tables that contain implicitly hidden columns. You
can also use the
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.
If you have a table, table1, with a hidden column c3, the following command shows loading data to hidden columns by explicitly stating the hidden column name using the insert into clause.
db2 load from delfile1 of del insert into table1 (c1, c2, c3)
Alternatively, you can specify the
implicitlyhiddeninclude modifier when the
input file contains data for the hidden columns, or the
implicitlyhiddenmissing modifier when the
input file does not.
db2 load from delfile1 of del modified by implicitlyhiddeninclude insert into table1
Additionally, you can use the
DB2_DMU_DEFAULT registry variable on the
server-side to set the behavior when data movement utilities encounter
tables with implicitly hidden columns.
db2set DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE db2 load from delfile1 of del insert into table1