Importing data

The import utility inserts data from an external file with a supported file format into a table, hierarchy, view, or nickname. The load utility is a faster alternative, but the load utility does not support loading data at the hierarchy level.

Before you begin

Before invoking the import utility, you must be connected to (or be able to implicitly connect to) the database into which you want to import the data. If implicit connect is enabled, a connection to the default database is established.

Utility access to Db2® for Linux®, UNIX, or Windows database servers from Db2 for Linux, UNIX, or Windows clients must be a direct connection through the engine. Utility access cannot be through a Db2 Connect gateway or loop back environment.

Since the utility issues a COMMIT or a ROLLBACK statement, complete all transactions and release all locks by issuing a COMMIT statement or a ROLLBACK operation before invoking import.

Note: The CREATE and REPLACE_CREATE parameters of the IMPORT command are deprecated and might be removed in a future release.


Restrictions

The following restrictions apply to the import utility:
  • If the existing table is a parent table containing a primary key that is referenced by a foreign key in a dependent table, its data cannot be replaced, only appended to.
  • You cannot perform an import replace operation into an underlying table of a materialized query table defined in refresh immediate mode.
  • You cannot import data into a system table, a summary table, or a table with a structured type column.
  • You cannot import data into declared temporary tables.
  • Views cannot be created through the import utility.
  • Referential constraints and foreign key definitions are not preserved when creating tables from PC/IXF files. (Primary key definitions are preserved if the data was previously exported by using SELECT *.)
  • Because the import utility generates its own SQL statements, the maximum statement size of 2 MB might, in some cases, be exceeded.
  • You cannot re-create a partitioned table or a multidimensional clustered table (MDC) by using the CREATE or REPLACE_CREATE import parameters.
  • You cannot re-create tables containing XML columns.
  • You cannot import encrypted data.
  • The import replace operation does not honor the Not Logged Initially clause. The REPLACE parameter for the IMPORT command does not honor the NOT LOGGED INITIALLY (NLI) clause for the CREATE TABLE statement clause or the ACTIVATE NOT LOGGED INITIALLY clause for the ALTER TABLE statement. If an import with the REPLACE action is performed within the same transaction as a CREATE TABLE or ALTER TABLE statement where the NLI clause is invoked, the import does not honor the NLI clause. In this scenario, all inserts are logged.

    Workaround 1: Delete the contents of the table by using the DELETE statement, then invoke the import with INSERT statement.

    Workaround 2: Drop the table and re-create it, then invoke the import with INSERT statement.

The following limitation applies to the import utility: If the volume of output messages generated by an import operation against a remote database exceeds 60 KB, the utility keeps the first 30 KB and the last 30 KB.

Procedure

To invoke the import utility:
  • Issue an IMPORT command in the command line processor (CLP).
  • Call the db2Import application programming interface (API) from a client application.
  • Open the task assistant in IBM® Data Studio for the IMPORT command.

Examples

A simple import operation requires you to specify only an input file, a file format, an import mode, and a target table (or the name of the table that is to be created).

For example, to import data from the CLP, enter the IMPORT command:
db2 import from filename of fileformat import_mode into table
where filename is the name of the input file that contains the data you want to import, fileformat is the file format, import_mode is the mode, and table is the name of the table that you want to insert the data into.
However, you might also want to specify a messages file to which warning and error messages are written. To do that, add the MESSAGES parameter and a message file name. For example:
db2 import from filename of fileformat messages messagefile import_mode into table