DB2 Version 10.1 for Linux, UNIX, and Windows

Ingest utility restrictions and limitations

There are a number of restrictions that you should be aware of when using the ingest utility.

Restartability
  • If input data source type changed, the ingest utility might not be able to detect the change and will produce different output rows than the original failed command.
Table support
  • The ingest utility supports operations against only DB2® for Linux, UNIX and Windows tables.
  • The ingest utility does not support operations on:
    • 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
    • columns with a user-defined data type based on any of the types listed previously
  • In addition, the ingest utility has the following restrictions on generated columns:
    • The ingest utility cannot assign a value to a column 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, the insert or update operation fails (SQL0798N) and the INGEST command ends unless you do one of the following:
      • 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.
    • The ingest utility cannot assign a combination of default values and specific values to a column defined as GENERATED BY DEFAULT AS IDENTITY. If the SQL statement on the INGEST command is INSERT or UPDATE and the target table has a GENERATED BY DEFAULT AS IDENTITY column, the insert or update operation fails (SQL0407N) and the INGEST command rejects the record unless you do one of the following:
      • 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, which means that the combination of partitioned database environment mode, a restartable ingest command, and ingesting into a nickname is not supported.
  • The performance benefit of the utility is reduced when used on nicknames.
Related reference:
INGEST command