Data Movement Utilities Guide and Reference

Using Import with Generated Columns

The import utility can be used to import data into a table containing (non-identity) generated columns.

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

• A value will be 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.

Two file type modifiers are supported by the import utility to simplify its use with tables that contain 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 may want to import data from a file (load.del) into TABLE1, and this data may 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 may 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
  

• 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 above:

     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 may 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