Comparison between the ingest, import, and load utilities
The following tables summarize some of the key similarities and differences between the ingest, import, and load 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 |
| Input type | Ingest Restartable? |
Load Restartable? |
Import Restartable? |
External Table |
|---|---|---|---|---|
| Cursor | not supported n/a |
supported yes |
not supported n/a |
n/a |
| Device | not supported n/a |
supported yes |
not supported n/a |
n/a |
| File | supported yes |
supported yes |
supported yes |
yes |
| Pipe | supported yes |
supported yes |
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 full range of SQL during a bulk INSERT FROM SELECT operation.
- External Tables can be used were 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 .