INGEST utility restrictions and limitations

There are a number of restrictions that you need to be aware of when using the INGEST utility.

The ability to restart
If the input data source type changes, the INGEST utility might fail to detect the change and produce different output rows than the original failed command.
Table support
  • The INGEST utility supports operations against only Db2® tables.
  • The INGEST utility does not support operations on the following objects:
    • Created or declared global temporary tables.
    • Typed tables.
    • Typed views.
Input types, formats, and column types
  • The INGEST utility does not support the following column types:
    • Large object types (LOB, BLOB, CLOB, DBCLOB).
    • XML.
    • Structured types.
    • VECTOR data type, for users of Db2 12.1.2 and later (SQL2948N).
    • Columns with a user-defined data type that is based on any of the previously mentioned column types.
  • In addition, the INGEST utility has the following restrictions on generated columns:
    • The INGEST utility cannot assign a value to a column that is defined as GENERATED ALWAYS. If the SQL statement on the INGEST command is INSERT or UPDATE and the target table has a GENERATED ALWAYS column, then the insert or update operation fails (SQL0798N). Also, the INGEST command ends unless you either omit the column from the list of columns to update, or specify DEFAULT as the value assigned to the column on the INSERT or UPDATE statement.
    • The INGEST utility cannot assign a combination of default values and specific values to a column defined as GENERATED BY DEFAULT AS IDENTITY or the RANDOM_DISTRIBUTION_KEY of a random distribution table using the random by generation method. If the SQL statement on the INGEST command is INSERT or UPDATE and the target table has a GENERATED BY DEFAULT AS IDENTITY column, then the insert or update operation fails (SQL0407N). Also, the INGEST command rejects the record unless you do one of the following actions:
      • Omit the column from the list of columns to update.
      • On the INSERT or UPDATE statement, specify DEFAULT as the value assigned to the column.
      • Specify an expression that never evaluates to NULL as the value assigned to the column. For example, if the expression is $field1, then $field1 can never have a NULL value in the input records.
Restrictions related to using other Db2 features with the INGEST utility
  • Except for the CONNECT_MEMBER parameter, the SET CLIENT command (for connection settings) does not affect how the INGEST utility connects.
  • The LIST HISTORY command does not display ingest operations.
  • The SET UTIL_IMPACT_PRIORITY command does not affect the INGEST command
  • The util_impact_lim database manager configuration parameter does not affect the INGEST command
  • Except for CURRENT SCHEMA, CURRENT TEMPORAL SYSTEM_TIME, and CURRENT TEMPORAL BUSINESS_TIME, the INGEST utility ignores the settings of most special registers that affect SQL statement execution.
General INGEST utility restrictions
  • If you ingest into a view that has multiple base tables, any base tables that are protected by a security policy must be protected by the same security policy. (You can still have some base tables unprotected but those that are protected must use the same security policy.)
Nickname support
  • If the INGEST command specifies or defaults to the RESTART NEW or RESTART CONTINUE option, and the target table is a nickname or an updatable view that updates a nickname, ensure that the DB2_TWO_PHASE_COMMIT server option is set to 'Y' for the server definition that contains the nickname.
  • You cannot use the SET SERVER OPTION to enable two-phase commit before issuing the INGEST command because that command affects only the CLP connection, whereas the INGEST command establishes its own connection. You must set the server option in the server definition in the catalog.
  • You cannot use the DB2_TWO_PHASE_COMMIT server option with the database partitioning feature. The combination of partitioned database environment mode, a restart-able ingest command, and ingesting into a nickname is not supported.
  • The performance benefit of the utility is reduced when used on nicknames.