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.
Example
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
DB2 Version 10.1 for Linux, UNIX, and Windows
