Comparison of the INGEST, IMPORT, LOAD, and EXTERNAL TABLE utilities
The following tables summarize some of the key similarities and differences between the INGEST, IMPORT, LOAD, and EXTERNAL TABLE utilities.
| Table type | INGEST | LOAD | IMPORT | EXTERNAL TABLE |
|---|---|---|---|---|
| Detached table | Not supported | Not supported | Not supported | Not supported |
| Global temporary table | Not supported | Not supported | Not supported | Supported |
| Multidimensional clustering (MDC) or insert time clustering (ITC) table | Supported | Supported | Supported | Supported |
| Materialized query table (MQT) that is maintained by user | Supported | Supported | Supported | Supported |
| Nickname | Supported | Not supported | Supported | Supported |
| Range-clustered table (RCT) | Supported | Not supported | Supported | Supported |
| Range-partitioned table | Supported | Supported | Supported | Supported |
| Summary table | Supported | Supported | Supported | Supported |
| Temporal table | Supported | Supported | Supported | Supported |
| Typed table | Not supported | Not supported | Supported | Not supported |
| Untyped (regular) table | Supported | Supported | Supported | Supported |
| Updatable view (except typed view) | Supported | Not supported | Supported | Supported |
| Table type | INGEST | LOAD | IMPORT | EXTERNAL TABLE |
|---|---|---|---|---|
| Numeric: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, DECFLOAT | Supported | Supported | Supported | Supported |
| Character: CHAR, VARCHAR, NCHAR, NVARCHAR, plus corresponding FOR BIT DATA types | Supported | Supported | Supported | Supported |
| Graphic: GRAPHIC, VARGRAPHIC | Supported | Supported | Supported | Supported |
| Long types: LONG VARCHAR, LONG VARGRAPHIC | Supported | Supported | Supported | Not supported |
| Date/time: DATE, TIME, TIMESTAMP, including TIMESTAMP(p) | Supported | Supported | Supported | Supported |
| BOOLEAN | Supported | Supported | Supported | Supported |
| DB2SECURITYLABEL | Supported | Supported | Supported | Supported |
| LOBs from files: BLOB, CLOB, DBCLOB, NCLOB | Not supported | Supported | Supported | Not supported |
| Inline LOBs | Not supported | Supported | Supported | Supported (up to 64K) |
| XML from files | Not supported | Supported | Supported | Not supported |
| Inline XML | Not supported | Supported | Supported | via XLMPARSE |
| Distinct type | Supported (if based on a Supported built-in data type) | Supported | Supported | Supported |
| Structured type | Not supported | Not supported | Supported | Supported |
| Reference type | Supported | Supported | Supported | Supported |
| VECTOR | Not supported | Supported in Db2® 12.1.3 and later. | Supported in Db2 12.1.2 and later | Supported in Db2 12.1.3 and later. |
| Input type | INGEST RESTART | LOAD RESTART | IMPORT RESTART | EXTERNAL TABLE RESTART |
|---|---|---|---|---|
| Cursor | Not supported n/a |
Not supported n/a |
Not supported n/a |
n/a |
| Device | Not supported n/a |
Not supported n/a |
Not supported n/a |
n/a |
| File | Supported yes |
Not supported n/a |
Supported yes |
yes |
| Pipe | Supported yes |
Not supported n/a |
Not supported n/a |
yes |
| Table type | INGEST | LOAD | IMPORT | EXTERNAL TABLE |
|---|---|---|---|---|
| ASC (including binary) | Supported | Supported | Supported | Supported |
| Db2 for z/OS® UNLOAD format | Not supported | Not supported | Not supported | Not supported |
| DEL | Supported | Supported | Supported | Supported |
| IXF | Not supported | Supported | Supported | Not supported |
| Db2 Binary | Not supported | Not supported | Not supported | Supported |
| Netezza Internal | Not supported | Not supported | Not supported | Supported |
There are a number of other important differences
that distinguish the ingest utility from the load and import utility:
- The ingest utility allows the input records to contain extra fields between the fields that correspond to columns.
- The ingest utility supports update, delete, and merge.
- The ingest utility supports constructing column values from expressions containing field values.
- The ingest utility allows other applications to update the target table while ingest is running.
There are a number of other important differences that distinguish External Tables ingestion from
other utilities:
- External Tables are not a utility but rather provide a full range of SQL during a bulk INSERT FROM SELECT operation.
- External Tables can be used where a TABLE referenced is allows supporting more complex ETL operations including used in UPDATE, DELETE and MERGE statements.
- External Tables allows full online acess to the target table in accordance to standard isolation and locking semantics .