Data Movement Utilities Guide and Reference

Using Import with Identity Columns

The import utility can be used to import data into a table containing an identity column. If no identity-related file type modifiers are used, the utility works according to the following rules:

• If the identity column is GENERATED ALWAYS, an identity value is generated for a table row whenever the corresponding row in the input file is missing a value for the identity column, or a NULL value is explicitly given. If a non-NULL value is specified for the identity column, the row is rejected (SQL3550W).
• If the identity column is GENERATED BY DEFAULT, the import utility makes use of user-supplied values, if they are provided; if the data is missing or explicitly NULL, a value is generated.

The import utility does not perform any extra validation of user-supplied identity values beyond what is normally done for values of the identity column's data type (that is, SMALLINT, INT, BIGINT, or DECIMAL). Duplicate values will not be reported. In addition, the compound=x modifier cannot be used when importing data into a table with an identity column.

Two file type modifiers are supported by the import utility to simplify its use with tables that contain an identity column:

• The identitymissing modifier makes importing a table with an identity column more convenient if the input data file does not contain any values (not even NULLS) for the identity column. For example, consider a table defined with the following SQL statement:

     create table table1 (c1 char(30),
                          c2 int generated by default as identity,
                          c3 real,
                          c4 char(1))
  

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

     Robert, 45.2, J
     Mike, 76.9, K
     Leo, 23.4, 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, c3, c4)
  

  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 identitymissing file type modifier as follows:

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

• The identityignore modifier is in some ways the opposite of the identitymissing modifier: it indicates to the import utility that even though the input data file contains data for the identity column, the data should be ignored, and an identity value 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:

     Robert, 1, 45.2, J
     Mike, 2, 76.9, K   
     Leo, 3, 23.4, I
  

  If the user-supplied values of 1, 2, and 3 are not to be used for the identity column, the user could issue the following IMPORT command:

     db2 import from import.del of del method P(1, 3, 4) replace into table1 (c1, c3, c4)
  

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

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

  When a table with an identity column is exported to an IXF file, the REPLACE_CREATE and the CREATE options of the IMPORT command can be used to recreate the table, including its identity column properties. If such an IXF file is created from a table containing an identity column of type GENERATED ALWAYS, the only way that the data file can be successfully imported is to specify the identityignore modifier. Otherwise, all rows will be rejected (SQL3550W).