DB2 10.5 for Linux, UNIX, and Windows

Identity column import considerations

The import utility can be used to import data into a table containing an identity column whether or not the input data has identity column values.

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.

There are two ways you can simplify the import of data into tables that contain an identity column: the identitymissing and the identityignore file type modifiers.

Importing data without 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 might want to import data from a file (import.del) into TABLE1, and this data might 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 might 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

Importing data with an identity column
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 previously:

   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 might 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 re-create 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).

Note: The CREATE and REPLACE_CREATE options of the IMPORT command are deprecated and might be removed in a future release.