Generated column load considerations

You can load data into a table containing (nonidentity) generated columns whether or not the input data has generated column values. The load utility generates the column values.

There are three mutually exclusive ways you can simplify the loading of data into tables that contain a generated column: the generatedmissing, the generatedignore, and the generatedoverride file type modifiers:

Loading data without 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))

If you 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, see the following example:

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

This command will result in the three columns of data file being loaded into c1, c2, and c3 of TABLE1. Due to the generatedmissing modifier, values for columns g1 and g2 of TABLE1 will be generated automatically and will not map to any of the data file columns.

Loading data with generated columns

The generatedignore modifier 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 the computed values should be loaded into each generated column. For example, if you want to load TABLE1, as defined previously, 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) if no generated-column related file type modifiers are used. 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 might 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

This command will result in the columns of data file being loaded into c1 (with the data 1, 2, 3), c2 (with the data 5,6,7), and c3 (with the data J, K, I) of TABLE1. Due to the generatedignore modifier, values for columns g1 and g2 of TABLE1 will be generated automatically and the data file columns (10, 11, 12 and 15, 16, 17) will be ignored.

Loading data with user-supplied values

The generatedoverride modifier is used for loading user-supplied values into a table with generated columns. This can be useful when migrating data from another database system, or when loading a table from data that was recovered using the RECOVER DROPPED TABLE option of 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).

When this modifier is used, the table is placed in the Set Integrity Pending state after the load operation. To take the table out of Set Integrity Pending state without verifying the user-supplied values, issue the following command:

   SET INTEGRITY FOR table-name GENERATED COLUMN IMMEDIATE
     UNCHECKED

To take the table out of the Set Integrity Pending state and force verification of the user-supplied values, issue the following command:

  SET INTEGRITY FOR table-name IMMEDIATE CHECKED

If a generated column is in any of the partitioning, dimension, or distribution keys, the generatedoverride modifier is ignored and the load utility generates values as if the generatedignore modifier is specified. This is done to avoid a scenario where a user-supplied generated column value conflicts with its generated column definition, which would place the resulting record in the wrong physical location, such as the wrong data partition, MDC block, or database partition.

Note: The LOAD utility does not support generating column values when one of the generated column expressions contains one of the following:

• a user-defined function that is a compiled compound SQL
• a user-defined function that is FENCED

If you attempt to load into such tables the load operation fails. However, you can provide your own values for these types of generated columns by using the generatedoverride file type modifier.