DB2 includes a set of data movement utilities for moving data in and out of relational tables. You use the DB2 import or load utility to populate DB2 tables (or updatable views) with data that has been exported from other tables, or with other data that resides in files that are external to the database. You can use these utilities to recreate a table, append data to an existing table, or create a completely new table. For information about the other DB2 data movement utilities, refer to the Export topic or to the db2move and db2look topics.

DB2 Import Utility

DB2 import supports four different input file formats, and the behavior of the utility depends to some extent on the format that you choose. The supported file formats are:

  • DEL, or delimited ASCII format
     
  • ASC, or non-delimited ASCII format
     
  • WSF, or work sheet format
     
  • IXF, or integrated exchange format (PC version)

The DB2 import utility can be invoked in one of three ways:

  • You can issue the IMPORT command from a DB2 command line, using the DB2 command line processor (CLP) in command mode, interactive mode, or batch mode.
     
  • You can invoke the IMPORT command from within the DB2 Control Center.
     
  • You can call the db2Import application programming interface (API) from an application written in one of the supported programming languages.

Information and examples provided in the rest of this section are written from the perspective of the IMPORT CLP command, invoked from either a DB2 command line or the DB2 Control Center.

Note: To invoke the DB2 import utility, you must hold SYSADM or DBADM authority, or one or more authorities or privileges on the table or view (or other impacted objects that are explicitly or implicitly) referenced in the IMPORT command.

The required syntax of the IMPORT command is as follows:

IMPORT FROM <file-name> OF <file-type>
   <import-mode> INTO <table-name>

This means that, at a minimum, you need only specify an input file name, the format of that file, the import mode, and the name of the table into which you want to import data. If you do not specify the complete path name for the input file, the import utility uses the current directory.

The supported import modes are:

  • INSERT, which adds data to the table without changing any existing table data
     
  • INSERT_UPDATE, which adds new rows of data to the table, or updates existing table data
     
  • REPLACE, which deletes all existing data from the table and inserts the imported data without changing table or index definitions
     
  • REPLACE_CREATE, which is the same as REPLACE if the table exists; if the table does not exist, creates the table and index definitions, and inserts the imported data (IXF input files only)
     
  • CREATE, which creates the table definition and inserts the imported data (IXF input files only); if the data was exported from a DB2 table, also creates indexes

The format of the input file determines what you can do with the data. If you want to recreate a table, the IXF format will let you use the import utility to do that, because that format preserves the table structure as well as the data. Not all of the table's attributes are captured in an IXF input file.

By default, the import utility is bound with the read stability (RS) isolation level. Concurrently connected applications have write access to the target table of an import operation if the ALLOW WRITE ACCESS clause was specified on the IMPORT command. The default ALLOW NO ACCESS clause prevents such applications from accessing data in the target table; the utility simply requests an exclusive (X) lock on the whole table at the beginning of the operation.

DB2 Load Utility

DB2 load supports four different input formats, and the behavior of the utility depends to some extent on the format that you choose. The supported formats are:

  • DEL, or delimited ASCII format
     
  • ASC, or non-delimited ASCII format
     
  • IXF, or integrated exchange format (PC version)
     
  • CURSOR, a cursor declared against a SELECT or VALUES statement

The DB2 load utility can be invoked in one of three ways:

  • You can issue the LOAD command from a DB2 command line, using the DB2 command line processor (CLP) in command mode, interactive mode, or batch mode.
     
  • You can invoke the LOAD command from within the DB2 Control Center.
     
  • You can call the db2Load application programming interface (API) from an application written in one of the supported programming languages.

Information and examples provided in the rest of this section are written from the perspective of the LOAD CLP command, invoked from either a DB2 command line or the DB2 Control Center.

Note: To invoke the DB2 load utility, you must hold SYSADM or DBADM authority, or LOAD authority on the database and one or more privileges on the table (or other impacted objects) referenced in the LOAD command.

The required syntax of the LOAD command is as follows:

LOAD FROM <file-name | pipe-name | device | cursor-name>
  OF <file-type> <load-mode> INTO <table-name>

This means that, at a minimum, you need only specify an input file name (or pipe name, device, or cursor name), the format of that file, the load mode, and the name of the table into which you want to load data. If you do not specify the complete path name for the input file, the load utility uses the current directory.

The supported load modes are:

  • INSERT, which adds data to the table without changing any existing table data
     
  • REPLACE, which deletes all existing data from the table and inserts the loaded data without changing table or index definitions
     
  • RESTART, which restarts a previously interrupted load operation from the last consistency point
     
  • TERMINATE, which terminates a previously interrupted load operation, and rolls the operation back to the point at which it started; for load INSERT operations, the table is left with all of its original data; for load REPLACE operations, the table is left empty

Concurrently connected applications have read access to pre-existing data in the target table of a load operation if the ALLOW READ ACCESS clause was specified on the LOAD command. The default ALLOW NO ACCESS clause prevents such applications from accessing data in the target table; the utility requests a super exclusive (Z) lock on the whole table at the beginning of the operation.

Differences between import and load

There are a number of significant differences between the DB2 import and the DB2 load utility; understanding these differences will make it easy for you to decide which utility will better serve your needs in a particular scenario.

  • The load utility is much faster than the import utility when large amounts of data must be moved, primarily because the import utility performs row inserts and logs all imported data
     
  • The import utility supports the creation of new tables and indexes (IXF file format)
     
  • The load utility supports moving data into materialized query tables
     
  • The import utility supports WSF (work sheet format)
     
  • The load utility supports a number of file type modifiers that the import utility does not support (for example, FASTPARSE, which reduces data checking and accelerates data movement)
     
  • The import utility supports moving data into updatable views
     
  • The load utility supports the GENERATEDOVERRIDE and IDENTITYOVERRIDE file type modifiers
     
  • The import utility supports triggers

In general, you should use the DB2 load utility when performance is critical, and use the import utility when the target table must be created from the data input file (IXF only); if the target table is a typed table; when you want every inserted record to be logged.

Toad for DB2 may be used to import and load data into tables. Click here for more information.