Administration Guide

Using the IMPORT Utility

The IMPORT utility inserts data from an input file into a table or view. If the table or view receiving the imported data already contains data, you can either replace or append the existing data with the data in the file.

Notes:

1. 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 added to.

2. You cannot import data into a summary table.

The following is an example of the command line processor syntax for the IMPORT command:

   db2 import from stafftab.ixf of ixf insert into userid.staff

The following information is required when importing data to a table or view:

• The path and the input file name where the data to import is stored.
• The name or alias of the table or view where the data is imported.
• The format of the data in the input file. This format can be IXF, WSF, DEL, or ASC. See "LOAD, IMPORT, and EXPORT File Formats" for details.
• Whether the data in the input file is to be inserted, updated, replaced, or appended to the existing data in the table or view.
• A message file name.
• When working with typed tables, you may need to provide the method or order by which to progress through all of the structured types. One method is to proceed from top of the hierarchy (or the root table) down the hierarchy (subtables) to the very bottom subtable; then back up to its supertable; down to the next "right-most" subtable(s); then back up to next higher supertable; down to its subtables; and so on. The method would proceed top-to-bottom, left-to-right through all of the supertables and subtables in the hierarchy. The order of going through the tables is called a "traverse" order. This order is important when moving data between table hierarchies because it will determine where the data is moved in relation to other data. The traverse order is the order of subtables within a typed table hierarchy. If you are using PC/IXF format, the default traverse order is recommended. If you are specifying the traverse order, remember that the subtables must be traversed in the PRE-ORDER fashion (that is, each branch in the hierarchy must be traversed to the bottom before a new branch is started).
• When working with typed tables, you may need to provide the subtable list. This list shows which subtables and attributes into which to import data.

When importing into large object (LOB) columns, the data can come either from the same file as the rest of the column data, or from separate files. In the latter case, there is one file for each LOB instance.

The column data in the file contains either the data to load into the column, or a filename where the data to load is stored. The default is the file contains the data to load into the column.

Notes:

1. When LOB data is stored in the file, no more than 32KB of data is allowed. Truncation warnings are ignored.

2. All of the LOB data must be stored in the main file or each LOB stored in separate files. The main file cannot have a mixture of LOB data and file names.

For more information on importing LOBs from files, see the LOBSINFILE option in the Command Reference manual.

You may also provide the following information:

• The method to use for importing the data: column location, column name, or relative column position.
• The number of rows to INSERT before committing the changes to the table. If you periodically do a COMMIT, this reduces the number of rows that are lost if a failure and a rollback occur during the import.
• The number of records in the file to skip before beginning the import. If an error occurs during an import, you may specify this information to restart the import operation immediately following the last row that was successfully imported and committed.
• The names of the columns within the table or view into which the data is to be inserted.

To import data into a new table, you must have SYSADM authority, DBADM authority, or CREATETAB privilege for the database. To replace data in an existing table or view, you must have SYSADM authority, DBADM authority, or CONTROL privilege for the table or view. To append data to an existing table or view, you must have SELECT and INSERT privileges for the table or view.

With IMPORT, there is a possibility of unequal code page situations involving possible expansion or contraction of the character data. Such situations could occur with Japanese or Traditional-Chinese Extended UNIX Code (EUC) and double-byte character sets (DBCS) which may have different length encodings for the same character. An option, NOCHECKLENGTHS, is used to toggle between two situations:

1. Comparison of input data length to target column length is performed before reading in any data. If the input length is larger than the target, NULLs are inserted for that column if it is nullable. Otherwise, the request is rejected. This is the default.
2. No initial comparison is performed and, on a row-by-row basis, an attempt is made to import the data. If the data is too long after translation is complete, the row is rejected. Otherwise, the data is imported. Specifying NOCHECKLENGTHS will enable this behavior.

The IMPORT utility casts user-defined distinct types (UDTs) to similar base data types automatically. This saves you from having to explicitly cast UDTs to the base data types. Casting allows for comparisons between UDTs and the base data types in SQL.

Use the IMPORT utility to re-create a table that was saved by using the EXPORT utility. The table must have been exported to an IXF file. When creating a table from an IXF file, not all attributes of the original table are preserved. For example, the referential constraints, foreign key definitions, and user-defined data types are not retained. If the IXF file was created with the LOBSINFILE option, then the length of the original LOB is lost. Attributes of the original table that are preserved or retained are:

• Column information
  • Names
  • Types including user-defined distinct types. (User-defined distinct types are preserved as their base type.)
  • Lengths (except for lob_file types)
  • Codepages (if applicable)
• Index information
  • Name
  • Creator
  • Column names of key parts (with a restriction if + or - are in the names)
  • Ascending or descending
  • Uniqueness

Using IMPORT with Buffered Inserts

In a partitioned database environment, the IMPORT utility can be enabled to use buffered inserts. This reduces the messaging that occurs when data is loaded, resulting in better performance.

To cause the IMPORT utility to use buffered inserts, the BIND command must be used. The import package, db2uimpm.bnd has to be rebound against the database with the INSERT BUF option. This can be achieved using the following commands:

   db2 connect to your_database
 
   db2 BIND db2uimpm.bnd INSERT BUF

However, any one of the individual inserts that are buffered can fail. It is not possible to report the failing row and error back to the user as IMPORT usually does. Therefore, buffered inserts should only be enabled with the IMPORT utility if the user is not concerned about error reporting.

Import in a Client/Server Environment

When you import a file to a remote database, a stored procedure may be called to perform the import on the server. A stored procedure will not be called when:

• The application and database code pages are different
• The file being imported is a multiple-part PC/IXF file
• The method used for importing the data is either column name or relative column position
• The target column list provided is longer than 4K
• An OS/2 or DOS client is importing a file from diskette
• LOBPATHS or LOBSINFILE is specified
• NULL INDICATORS are specified for ASC files.

When importing using a stored procedure, messages are created in the message file using the default language installed on the server. The messages are in the language of the application if the language at the client and the server are the same.

The import utility creates two temporary files in the tmp directory indicated by the DB2INSTPROF environment variable on the database server. One file is for data and the other file is for messages generated by the import utility.

If you receive an error about writing or opening data on the server, make sure that:

• This directory exists
• Sufficient disk space for the files exists
• Write-permission to this directory for the system administrator exists.

Differences Between the IMPORT and LOAD Utilities

This table gives you a quick comparison between the two utilities highlighting the important differences between them.

The IMPORT utility	The LOAD utility
Significantly slower than the LOAD utility on large amounts of data.	Significantly faster than the IMPORT utility on large amounts of data because of LOAD's writing of formatted pages directly into the database.
Limited intra-partition parallelism exploitation.	Exploitation of intra-partition parallelism. Typically, this requires symmetric multiprocessor (SMP) machines.
No FASTPARSE support.	Support for FASTPARSE datatype. Reduced data checking on user-supplied data.
No CODEPAGE support.	Support for CODEPAGE datatype. Converts character data (and numeric data specified in characters) from the code page given with this datatype to the database code page during the load operation.
Supports hierarchical data.	Does not support hierarchical data.
Creation of table, hierarchy, and indexes supported with PC/IXF format.	Tables and indexes must exist.
Creation of table and indexes supported with IXF format.	Table and indexes must exist.
WSF format is supported.	WSF format is not supported.
No BINARYNUMERICS support.	Support for BINARYNUMERICS datatype.
No PACKEDDECIMAL support.	Support for PACKEDDECIMAL datatype.
Can import into views and tables. (Aliases are supported.)	Can load into tables only. (Aliases are supported.)
The table space(s) that the table and its indexes reside in are online for the duration of the import.	The table space(s) that the table and its indexes reside in are offline for the duration of the load.
All rows are logged.	Minimal logging is performed.
Triggers will be fired.	Triggers are not supported.
If an import is interrupted and a commitcount was specified, the table is usable and will contain the rows that were loaded up to the last commit. The user has the choice to restart the import or use the table as is.	If a load is interrupted and a savecount was specified, the table remains in load pending state and cannot be used until the load is restarted to continue the load or the table space is restored from a backup image created some time before the load.
Space required is approximately the size of the largest index plus about 10%. This space requirement is used from the temporary table spaces within the database.	Space required is approximately the sum of the size of all indexes defined on the table and possibly twice this size. The space required is temporary space outside of the database.
All constraints are validated during an import.	Uniqueness is verified during a load but all other constraints must be checked using the SET CONSTRAINTS statement.
The keys of each row are inserted into the index one at a time during import.	During a load, all the keys are sorted and the index is built after the data has been loaded.
If up-to-date statistics are required after an import, RUNSTATS must be run afterwards.	Statistics can be gathered during the load if all the data in the table is being replaced.
You can import into a host database through DB2 Connect.	You cannot load into a host database.
Files that are imported must reside on the node where import is invoked.	Files/pipes that are loaded must reside on the node where the database resides.
No backup image is required.	The backup image can be created during the LOAD procedure.

[ DB2 List of Books | Search the DB2 Books ]