DB2 Version 10.1 for Linux, UNIX, and Windows

Generated column import considerations

The import utility can be used to import data into a table containing (nonidentity) generated columns whether or not the input data has generated column values.

If no generated column-related file type modifiers are used, the import utility works according to the following rules:

A value is generated for a generated column whenever the corresponding row in the input file is missing a value for the column, or a NULL value is explicitly given. If a non-NULL value is supplied for a generated column, the row is rejected (SQL3550W).
If the server generates a NULL value for a generated column that is not nullable, the row of data to which this field belongs is rejected (SQL0407N). This could happen, for example, if a non-nullable generated column were defined as the sum of two table columns that have NULL values supplied to them in the input file.

There are two ways you can simplify the import of data into tables that contain a generated column: the generatedmissing and the generatedignore file type modifiers.

Importing data without generated columns
The generatedmissing modifier makes importing data into a table with generated columns more convenient if the input data file does not contain any values (not even NULLS) for all generated columns present in the table. For example, consider a table defined with the following SQL statement:

   create table table1 (c1 int,
                        c2 int,
                        g1 int generated always as (c1 + c2),
                        g2 int generated always as (2 * c1),
                        c3 char(1))

A user might want to import data from a file (load.del) into TABLE1, and this data might have been exported from a table that does not have any generated columns. The following is an example of such a file:

   1, 5, J
   2, 6, K
   3, 7, I

One way to import this file would be to explicitly list the columns to be imported through the IMPORT command as follows:

   db2 import from import.del of del replace into table1 (c1, c2, c3)

For a table with many columns, however, this syntax might be cumbersome and prone to error. An alternate method of importing the file is to use the generatedmissing file type modifier as follows:

   db2 import from import.del of del modified by generatedmissing
      replace into table1

Importing data with generated columns
The generatedignore modifier is in some ways the opposite of the generatedmissing modifier: it indicates to the import utility that even though the input data file contains data for all generated columns, the data should be ignored, and values should be generated for each row. For example, a user might want to import the following data from a file (import.del) into TABLE1, as defined previously:

   1, 5, 10, 15, J
   2, 6, 11, 16, K   
   3, 7, 12, 17, I

The user-supplied, non-NULL values of 10, 11, and 12 (for g1), and 15, 16, and 17 (for g2) result in the row being rejected (SQL3550W). To avoid this, the user could issue the following IMPORT command:

   db2 import from import.del of del method P(1, 2, 5)
      replace into table1 (c1, c2, c3)

Again, this approach might be cumbersome and prone to error if the table has many columns. The generatedignore modifier simplifies the syntax as follows:

   db2 import from import.del of del modified by generatedignore
      replace into table1

For an INSERT_UPDATE, if the generated column is also a primary key and the generatedignore modifier is specified, the IMPORT command honors the generatedignore modifier. The IMPORT command does not substitute the user-supplied value for this column in the WHERE clause of the UPDATE statement.