Data Movement Utilities Guide and Reference

Using Load with Generated Columns

The load utility can be used to load data into a table containing (non-identity) generated columns. It does not, however, currently support the generation of non-identity column values within the utility, so loading into a table with such columns will always leave the table in check pending state. To take the table out of check pending state and force the generation of values, issue:

   SET INTEGRITY FOR tablename IMMEDIATE CHECKED FORCE GENERATED;

To accept whatever values were loaded, issue:

   SET INTEGRITY FOR tablename GENERATED COLUMN IMMEDIATE UNCHECKED;

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

• For generated columns that are nullable, if a NULL value is supplied for the column in the data file, it is accepted and loaded into the table. For generated columns that are not nullable, if a NULL value is supplied for the column in the data file, the default value of the generated column's data type will be loaded. For both nullable and non-nullable generated columns, non-NULL values in the data file result in the row being rejected (SQL3550W).
• After the load operation has completed, if the table is taken out of check pending state using the SET INTEGRITY statement with the FORCE GENERATED option, loaded values are replaced with generated values. If the FORCE GENERATED option is not used, the loaded values remain if:
  • They do not violate any generated column constraints
  • The GENERATED COLUMN IMMEDIATE UNCHECKED option is used.
  If the GENERATED COLUMN IMMEDIATE UNCHECKED option is not used, rows that have generated column constraint violations are put into an exception table, if one is provided.

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

• The generatedmissing modifier makes loading 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 load TABLE1 with data from a file (load.del) that has 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 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, c2, c3)
  

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

     db2 load from load.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 load utility that even though the input data file contains data for all generated columns present in the target table, the data should be ignored, and NULLs should be loaded into each column. For example, a user might want to load TABLE1, as defined above, from a data file (load.del) containing the following data:

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

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

• The generatedoverride modifier is used for loading user-supplied values into a table with generated columns. This can be quite useful when migrating data from another database system, 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 non-nullable generated columns are rejected (SQL3116W).