Data Movement Utilities Guide and Reference

Using Load with Identity Columns

The load utility can be used to load 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 load utility makes use of user-supplied values, if they are provided; if the data is missing or explicitly NULL, a value is generated.

The load 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.

Three (mutually exclusive) file type modifiers are supported by the load utility to simplify its use with tables that contain an identity column:

• The identitymissing modifier makes loading 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 varchar(30),
                          c2 int generated by default as identity,
                          c3 decimal(7,2),
                          c4 char(1))
  

  A user may want to load TABLE1 with data from a file (load.del) that has 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 load this file would be to explicitly list the columns to be loaded through the LOAD command as follows:

     db2 load from load.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 loading the file is to use the identitymissing file type modifier as follows:

     db2 load from load.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 load 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 load TABLE1, as defined above, from a data file (load.del) containing the following data:

     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 LOAD command:

     db2 load from load.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 load from load.del of del modified by identityignore replace into table1
  

• The identityoverride modifier is used for loading user-supplied values into a table with a GENERATED ALWAYS identity column. This can be quite useful when migrating data from another database system, and the table must be defined as GENERATED ALWAYS, or when loading a table from data that was recovered using the DROPPED TABLE RECOVERY option on the ROLLFORWARD DATABASE command. When this modifier is used, any rows with no data (or NULL data) for the identity column are rejected (SQL3116W).

  Note:

  When using this modifier, it is possible to violate the uniqueness property of GENERATED ALWAYS columns.