Rules for inserting data into a ROWID column

A ROWID column contains unique values that identify each row in a table. Whether you can insert data into a ROWID column and how that data gets inserted depends on how the column is defined.

A ROWID column is a column that is defined with a ROWID data type. You must have a column with a ROWID data type in a table that contains a LOB column. The ROWID column is stored in the base table and is used to look up the actual LOB data in the LOB table space. In addition, a ROWID column enables you to write queries that navigate directly to a row in a table. For information about using ROWID columns for direct-row access, see Specifying direct row access by using row IDs.

Before you insert data into a ROWID column, you must know how the ROWID column is defined. ROWID columns can be defined as GENERATED ALWAYS or GENERATED BY DEFAULT. GENERATED ALWAYS means that Db2 generates a value for the column, and you cannot insert data into that column. If the column is defined as GENERATED BY DEFAULT, you can insert a value, and Db2 provides a default value if you do not supply one.

INSERT INTO T2 (INTCOL2,ROWIDCOL2)
  SELECT * FROM T1;

If ROWIDCOL2 is defined as GENERATED ALWAYS, you cannot insert the ROWID column data from T1 into T2, but you can insert the integer column data. To insert only the integer data, use one of the following methods:

• Specify only the integer column in your INSERT statement, as in the following statement:

  INSERT INTO T2 (INTCOL2)
    SELECT INTCOL1 FROM T1;

• Specify the OVERRIDING USER VALUE clause in your INSERT statement to tell Db2 to ignore any values that you supply for system-generated columns, as in the following statement:

  INSERT INTO T2 (INTCOL2,ROWIDCOL2) OVERRIDING USER VALUE
    SELECT * FROM T1;