IBM Books

Command Reference

IMPORT

Inserts data from an external file with a supported file format into a table, hierarchy, or view. A faster alternative is LOAD.

Authorization

Required Connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command Syntax



>>-IMPORT FROM--filename--OF--filetype-------------------------->
 
>-----+-----------------------------+--------------------------->
      |            .-,-----------.  |
      |            V             |  |
      '-LOBS FROM-----lob-path---+--'
 
>-----+-----------------------------------+--------------------->
      |              .-----------------.  |
      |              V                 |  |
      '-MODIFIED BY-----filetype-mod---+--'
 
>-----+-----------------------------------------------------------------------------------------------+>
      |                 .-,---------------------------.                                               |
      |                 V                             |                                               |
      '-METHOD--+-L--(-----column-start--column-end---+---)--+-----------------------------------+-+--'
                |                                            |                     .-,----.      | |
                |                                            |                     V      |      | |
                |                                            '-NULL INDICATORS--(-----n---+---)--' |
                |       .-,--------------.                                                         |
                |       V                |                                                         |
                +-N--(-----column-name---+---)-----------------------------------------------------+
                |       .-,------------------.                                                     |
                |       V                    |                                                     |
                '-P--(-----column-position---+---)-------------------------------------------------'
 
>-----+-----------------+---+------------------+---------------->
      '-COMMITCOUNT--n--'   '-RESTARTCOUNT--n--'
 
>-----+-------------------------+------------------------------->
      '-MESSAGES--message-file--'
 
>-----+--+-INSERT---------+--INTO--+-table-name--+------------------------------+-+-------------------+>
      |  +-INSERT_UPDATE--+        |             |    .-,----------------.      | |                   |
      |  +-REPLACE--------+        |             |    V                  |      | |                   |
      |  '-REPLACE_CREATE-'        |             '-(-----insert-column---+---)--' |                   |
      |                            '-| hierarchy description |--------------------'                   |
      |                                                                                               |
      '-CREATE--INTO--+-table-name--+------------------------------+----------+---| tblspace-specs |--'
                      |             |    .-,----------------.      |          |
                      |             |    V                  |      |          |
                      |             '-(-----insert-column---+---)--'          |
                      '-| hierarchy description |--+-AS ROOT TABLE----------+-'
                                                   '-UNDER--sub-table-name--'
 
>-----+--------------------+-----------------------------------><
      '-| datalink-spec |--'
 
hierarchy description
 
    .-ALL TABLES---------.
|---+-| sub-table-list |-+--+----+------------------------------>
                            '-IN-'
 
>----HIERARCHY--+-STARTING--sub-table-name--+-------------------|
                '-| traversal-order-list |--'
 
sub-table-list
 
       .-,--------------------------------------------------.
       V                                                    |
|---(-----sub-table-name--+------------------------------+--+---)-->
                          |    .-,----------------.      |
                          |    V                  |      |
                          '-(-----insert-column---+---)--'
 
>---------------------------------------------------------------|
 
traversal-order-list
 
       .-,-----------------.
       V                   |
|---(-----sub-table-name---+---)--------------------------------|
 
tblspace-specs
 
|---+--------------------------------------------------------------------------------------+->
    '-IN--tablespace-name--+----------------------------+---+---------------------------+--'
                           '-INDEX IN--tablespace-name--'   '-LONG IN--tablespace-name--'
 
>---------------------------------------------------------------|
 
datalink-spec
 
    .-,--------------------------------------------------------------------------------------------------.
    V                                                                                                    |
|------(--+-----------------+--+----------------------------------+---+--------------------------+---)---+->
          '-DL_LINKTYPE URL-'  +-DL_URL_REPLACE_PREFIX--"prefix"--+   '-DL_URL_SUFFIX--"suffix"--'
                               '-DL_URL_DEFAULT_PREFIX--"prefix"--'
 
>---------------------------------------------------------------|
 

Command Parameters

FROM filename
Specifies the file that contains the data being imported. If the path is omitted, the current working directory is used.
OF filetype
Specifies the format of the data in the input file:

For more information about file formats, see the Appendix C. IMPORT/EXPORT/LOAD Utility File Formats.

LOBS FROM lob-path
Specifies the path or paths that store LOB files. The names of the LOB data files are stored in the main data file (ASC, DEL, or IXF), in the column that will be loaded into the LOB column. This option is ignored if lobsinfile is not specified within the filetype-mod string.
MODIFIED BY filetype-mod
Specifies additional information unique to the ASC, DEL, WSF, or IXF file format (see ***).
METHOD
L
Specifies the start and end column numbers from which to import data.

Note:This method can only be used with ASC files, and is the only valid option for that file type.

N
Specifies the names of the columns to be imported.

Note:This method can only be used with IXF files.

P
Specifies the numbers of the columns to be imported.

Note:This method can only be used with IXF or DEL files, and is the only valid option for the DEL file type.

NULL INDICATORS n
Specifies a column (by number) to be used as a null indicator field. If this option is used, a null indicator column for each data column must also be specified. Zero (0) indicates that the data column is not nullable, and that there will always be data in that column.

While processing each row, a Y indicates that the column data is NULL, while an N indicates that the column data is not NULL, and that column data specified by the METHOD L option will be imported.

COMMITCOUNT n
Performs a commit every n records.
RESTARTCOUNT n
Specifies that an import is to be started at record n + 1. The first n records are skipped.
MESSAGES message-file
Specifies the destination for warning and error messages that occur during import. If the file already exists, IMPORT appends the information. If the complete path to the file is not specified, IMPORT uses the current directory and the default drive as the destination. If message-file is omitted, the messages are written to standard output.
INSERT
Adds the imported data to the table without changing the existing table data.
INSERT_UPDATE
Adds rows of imported data to the target table, or updates existing rows (of the target table) with matching primary keys.
REPLACE
Deletes all existing data in the table, and inserts the imported data. The table definition and the index definitions are not changed. This option can only be used if the table exists. It is not valid for tables with DATALINK columns. For typed tables, this option can only operate on the entire hierarchy.
REPLACE_CREATE
If the table exists, deletes all existing data in the table, and inserts the imported data without changing the table definition or the index definitions.

If the table does not exist, creates the table definition and row contents. If the data was exported from a database manager or a DB2 for OS/2 table, indexes are also created.

This option can only be used with IXF files. It is not valid for tables with DATALINK columns. For typed tables, this option can only operate on the entire hierarchy.

CREATE
Creates the table definition and row contents. If the data was exported from a database manager table, sub-table, or hierarchy, indexes are created. If this option operates on a hierarchy, and data was exported from a database manager, a type hierarchy will also be created. This option can only be used with IXF files.
Note:If the data was exported from an MVS host database, and it contains LONGVAR fields whose lengths, calculated on the page size, are less than 254, CREATE may fail because the rows are too long. In this case, the table should be created manually, and IMPORT with INSERT should be invoked, or, alternatively, the LOAD command should be used.
INTO table-name
Specifies the database table into which the data is to be imported. This table cannot be a system table or a summary table.

One can use an alias for INSERT, INSERT_UPDATE, or REPLACE, except in the case of a down-level server, when the fully qualified or the unqualified table name should be used. A qualified table name is in the form: schema.tablename. The schema is the user name under which the table was created.

insert-column
Specifies the name of a column within the table or view into which the data is to be inserted.
ALL TABLES
An implicit keyword for hierarchy only. When importing a hierarchy, the default is to import all tables specified in the traversal order.
sub-table-list
For typed tables with the INSERT or the INSERT_UPDATE option, a list of sub-table names is used to indicate the sub-tables into which data is to be imported.
HIERARCHY
Specifies that hierarchical data is to be imported.
STARTING sub-table-name
A keyword for hierarchy only, specifying to use the default order, starting from sub-table-name. For PC/IXF files, the default order is the order stored in the data file. The default order is the only valid order for the PC/IXF format.
traversal-order-list
For typed tables with the INSERT, INSERT_UPDATE, or the REPLACE option, a list of sub-table names is used to indicate the traversal order of the importing sub-tables in the hierarchy.
UNDER sub-table-name
Specifies a parent table for creating one or more sub-tables.
AS ROOT TABLE
Creates one or more sub-tables as a stand-alone table hierarchy.
IN tablespace-name
Identifies the table space in which the table will be created. The table space must exist, and must be a REGULAR table space. If no other table space is specified, all table parts are stored in this table space. If this clause is not specified, the table is created in a table space created by the authorization ID. If none is found, the table is placed into the default table space USERSPACE1. If USERSPACE1 has been dropped, the table creation fails.
INDEX IN tablespace-name
Identifies the table space in which any indexes on the table will be created. This option is allowed only when the primary table space specified in the IN clause is a DMS table space. The specified table space must exist, and must be a REGULAR DMS table space.
Note:Specifying which table space will contain a table's index can only be done when the table is created.
LONG IN tablespace-name
Identifies the table space in which the values of any long columns (LONG VARCHAR, LONG VARGRAPHIC, LOB data types, or distinct types with any of these as source types) will be stored. This option is allowed only when the primary table space specified in the IN clause is a DMS table space. The table space must exist, and must be a LONG DMS table space.
DL_LINKTYPE
If specified, it should match the LINKTYPE of the column definition. Thus, DL_LINKTYPE URL is acceptable if the column definition specifies LINKTYPE URL.
DL_URL_DEFAULT_PREFIX "prefix"
If specified, it should act as the default prefix for all DATALINK values within the same column. In this context, prefix refers to the "scheme host port" part of the URL specification.

Examples of prefix are:

   "http://server"
   "file://server"
   "file:"
   "http://server:80"

If no prefix is found in a column's data, and a default prefix is specified with DL_URL_DEFAULT_PREFIX, the default prefix is prefixed to the column value (if not NULL).

For example, if DL_URL_DEFAULT_PREFIX specifies the default prefix "http://toronto":

DL_URL_REPLACE_PREFIX "prefix"
This clause is useful while loading or importing data previously generated by the export utility, when the user wants to globally replace the host name in the data with another host name. If specified, it becomes the prefix for all non-NULL column values. If a column value has a prefix, this will replace it. If a column value has no prefix, the prefix specified by DL_URL_REPLACE_PREFIX is prefixed to the column value.

For example, if DL_URL_REPLACE_PREFIX specifies the prefix "http://toronto":

DL_URL_SUFFIX "suffix"
If specified, it is appended to every non-NULL column value for the column. It is, in fact, appended to the "path" component of the URL part of the DATALINK value.

Examples

The following example shows how to import information from myfile.ixf to the STAFF table:

   db2 import from myfile.ixf of ixf messages msg.txt insert into staff



SQL3150N  The H record in the PC/IXF file has product "DB2    01.00", date
"19970220", and time "140848".
 
SQL3153N  The T record in the PC/IXF file has name "ex", qualifier "        ",
and source "            ".
 
SQL3109N  The utility is beginning to load data from file "ex".
 
SQL3110N  The utility has completed processing.  "58" rows were read from the
input file.
 
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "58".
 
SQL3222W  ...COMMIT of any database changes was successful.
 
SQL3149N  "58" rows were processed from the input file.  "58" rows were
successfully inserted into the table.  "0" rows were rejected.

The following example shows how to import the table MOVIETABLE from the input file delfile1, which has data in the DEL format:

   db2 import from delfile1 of del
       modified by dldel|
       insert into movietable (actorname, description, url_making_of, url_movie)
       (dl_url_default_prefix "http://narang"),
       (dl_url_replace_prefix "http://bomdel" dl_url_suffix ".mpeg")

Notes:

  1. The table has four columns:
       actorname              VARCHAR(n)
       description            VARCHAR(m)
       url_making_of          DATALINK (with LINKTYPE URL)
       url_movie              DATALINK (with LINKTYPE URL)
    

  2. The DATALINK data in the input file has the vertical bar (|) character as the sub-field delimiter.

  3. If any column value for url_making_of does not have the prefix character sequence, "http://narang" is used.

  4. Each non-NULL column value for url_movie will get "http://bomdel" as its prefix. Existing values are replaced.

  5. Each non-NULL column value for url_movie will get ".mpeg" appended to the path. For example, if a column value of url_movie is "http://server1/x/y/z", it will be stored as "http://bomdel/x/y/z.mpeg"; if the value is "/x/y/z", it will be stored as "http://bomdel/x/y/z.mpeg".

Usage Notes

The database table or hierarchy must exist before data in the ASC, DEL, or WSF file formats can be imported; however, if the table does not already exist, IMPORT CREATE or IMPORT REPLACE_CREATE creates the table when it imports data from a PC/IXF file. For typed tables, IMPORT CREATE can create the type hierarchy and the table hierarchy as well. PC/IXF import should be used to move hierarchical data between databases.

The import utility will issue a COMMIT or a ROLLBACK statement. Ensure that all database activity in the current transaction has completed, and that all locks are released (by issuing a COMMIT or a ROLLBACK) before issuing the IMPORT command.

PC/IXF import should be used to move data between databases. If character data containing row separators is exported to a delimited ASCII (DEL) file and processed by a text transfer program (moving, for example between OS/2 and AIX systems), fields containing the row separators will shrink or expand.

PC/IXF file format specifications permit migration of data between OS/2 (IBM Extended Services for OS/2, OS/2 Extended Edition, and DB2 for OS/2) databases and DB2 for AIX databases via export, binary copying of files between OS/2 and AIX, and import. The file copying step is not necessary if the source and the target databases are both accessible from the same client.

The data in ASC and DEL files is assumed to be in the code page of the client application performing the import.

IXF files, which allow for different code pages, are recommended when importing data in different code pages. If the IXF file and the import utility are in the same code page, processing occurs as for a regular application. If the two differ, and the FORCEIN option is specified, IMPORT assumes that data in the IXF file has the same code page as the application performing the import. This occurs even if there is a conversion table for the two code pages. If the two differ, FORCEIN is not specified, and there is a conversion table, all data in the IXF file will be converted from the file code page to the application code page. If the two differ, FORCEIN is not specified, and there is no conversion table, the import will fail. This applies only to IXF files on DB2 for AIX clients.

For table objects on an 8KB page that are close to the limit of 1012 columns, import of IXF data files may cause DB2 to return an error, because the maximum size of an SQL statement was exceeded. This situation can occur only if the columns are of type CHAR, VARCHAR, or CLOB. The restriction does not apply to import of DEL or ASC files. If IXF files are being used to create a new table, an alternative is to dump the source table's DDL using db2look - DB2 Statistics Extraction Tool, and then to issue that statement through the CLP.

DB2 Connect can be used to import data to DRDA servers such as DB2 for OS/390, DB2 for VM and VSE, and DB2 for OS/400. Only PC/IXF import (INSERT option) is supported. The RESTARTCOUNT parameter, but not the COMMITCOUNT parameter, is also supported.

Importing a multiple-part PC/IXF file whose individual parts are copied from an OS/2 system to an AIX system is supported on DB2.

On the Windows NT operating system:

When using the CREATE option with typed tables, create every sub-table defined in the IXF file; sub-table definitions cannot be altered.

When using options other than CREATE with typed tables, the traversal order list enables one to specify the traverse order; therefore, the traversal order list must match the one used during the export operation. For the IXF format, one need only specify the target sub-table name and use the traverse order stored in the file.

DB2 File Manager Considerations

Before running the DB2 import utility, do the following:

  1. Copy the DATALINK files into the appropriate file servers.

  2. Define one or more prefix names to the DLFMs in the file servers. There may be other administrative tasks, such as registering the database, if required.

  3. Update the file server information in the URLs (of the DATALINK columns) from the exported data for the SQL table, if required. (If the original configuration's file servers are the same at the target location, the file server names need not be updated.)

  4. Define the file servers at the target configuration in the DB2 File Manager server_configuration file.

When the import utility is executed on the target system, data related to DATALINK columns is loaded into the underlying DB2 tables using SQL INSERT (as is the case for other columns).

The import utility uses APIs to generate values for the DATALINK column. During the insert operation, DATALINK column processing links the file in the appropriate file server according to the column specifications at the target database.

Representation of DATALINK Information in an Input File

For a description of how DATALINK information is represented in an input file, see ***.

File Type Modifications
Note:The import utility does not issue a warning if an attempt is made to use unsupported file types with the MODIFIED BY option. If this is attempted, the import fails, and an error code is returned.

Table 7. Valid File Type Modifications (IMPORT)
Modification Description
All File Formats
compound=x x is a number between 1 and 100 inclusive (7 on DOS/Windows). Uses nonatomic compound SQL to insert the data, and x statements will be attempted each time.
dldelx x is a single character DATALINK delimiter. The default value is a semicolon (;). The specified character is used in place of a semicolon as the inter-field separator for a DATALINK value. It is needed because a DATALINK value may have more than one sub-value. ab
Note:For DEL (delimited ASCII) files, x must not be the same character specified as the row, column, or character string delimiter.
lobsinfile lob-path specifies the path to the files containing LOB values.
no_type_id Valid only when importing into a single sub-table. Typical usage is to export data from a regular table, and then to invoke an import operation (using this modifier) to convert the data into a single sub-table.
nodefaults If a source column for a target table column is not explicitly specified, and the table column is not nullable, default values are not loaded. Without this option, if a source column for one of the target table columns is not explicitly specified, one of the following occurs:
  • If the column is defaultable, the default value is loaded
  • If the column is nullable and not defaultable, a NULL is loaded
  • If the column is not nullable and not defaultable, an error is returned, and the utility stops processing.
usedefaults If a source column for a target table column has been specified, but it contains no data for one or more row instances, default values are loaded. Examples of missing data are:
  • For DEL files: ",," is specified for the column
  • For DEL/ASC/WSF files: A row that does not have enough columns, or is not long enough for the original specification.
instance, one of the following occurs:
  • If the column is nullable, a NULL is loaded
  • If the column is not nullable, the utility rejects the row.
ASCII File Formats (ASC/DEL)
implieddecimal The location of an implied decimal point is determined by the column definition; it is no longer assumed to be at the end of the value. For example, the value 12345 is loaded into a DECIMAL(8,2) column as 123.45, not 12345.00.
noeofchar The optional end-of-file character x'1A' is not recognized as the end of file. Processing continues as if it were a normal character.
ASC (Non-delimited ASCII) File Format
nullindchar=x x is a single character. Changes the character denoting a null value to x. The default value of x is Y.b

This modifier is case sensitive for EBCDIC data files, except when the character is an English letter. For example, if the null indicator character is specified to be the letter N, then n is also recognized as a null indicator.

reclen=x x is an integer with a maximum value of 32 767. x characters are read for each row, and a new-line character is not used to indicate the end of the row.
striptblanks Truncates any trailing blank spaces when loading data into a variable-length field. If this option is not specified, blank spaces are kept.

In the following example, striptblanks causes the import utility to truncate trailing blank spaces:

   db2 import from myfile.asc of asc modified by striptblanks
      method l (1 10, 12 15) messages msgs.txt
      insert into staff

This option cannot be specified together with striptnulls. These are mutually exclusive options.
Note:This option replaces the obsolete t option, which is supported for back-level compatibility only.

striptnulls Truncates any trailing NULLs (0x00 characters) when loading data into a variable-length field. If this option is not specified, NULLs are kept.

This option cannot be specified together with striptblanks. These are mutually exclusive options.
Note:This option replaces the obsolete padwithzero option, which is supported for back-level compatibility only.

DEL (Delimited ASCII) File Format
chardelx x is a single character string delimiter. The default value is a double quotation mark ("). The specified character is used in place of double quotation marks to enclose a character string.ab

The single quotation mark (') can also be specified as a character string delimiter. In the following example, chardel'' causes the import utility to interpret any single quotation mark (') it encounters as a character string delimiter:

   db2 "import from myfile.del of del modified by chardel''
      method p (1, 4) insert into staff (id, years)"
coldelx x is a single character column delimiter. The default value is a comma (,). The specified character is used in place of a comma to signal the end of a column.ab

In the following example, coldel; causes the import utility to interpret any semicolon (;) it encounters as a column delimiter:

   db2 import from myfile.del of del modified by coldel;
      messages msgs.txt insert into staff
datesiso Date format. Causes all date data values to be imported in ISO format.
decplusblank Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign.
decptx x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.ab

In the following example, decpt; causes the import utility to interpret any semicolon (;) it encounters as a decimal point:

   db2 "import from myfile.del of del modified by chardel'
      decpt; messages msgs.txt insert into staff"
nodoubledel Suppresses recognition of double character delimiters.
IXF File Format
defer_import Valid only for the CREATE option. After the table or sub-tables are created, the import utility returns without importing any data, if this modifier was specified. Typical usage is to invoke the import utility to create the large hierarchy, and then to use the load utility to move the data into the database.
forcein Directs the utility to accept data despite code page mismatches, and to suppress translation between code pages.

Fixed length target fields are checked to verify that they are large enough for the data. If nochecklengths is specified, no checking is done, and an attempt is made to import each row.

indexixf Directs the utility to drop all indexes currently defined on the existing table, and to create new ones from the index definitions in the PC/IXF file. This option can only be used when the contents of a table are being replaced. It cannot be used with a view, or when a insert-column is specified.
indexschema=schema Uses the specified schema for the index name during index creation. If schema is not specified (but the keyword indexschema is specified), uses the connection user ID. If the keyword is not specified, uses the schema in the IXF file.
nochecklengths Used with the forcein modifier. If nochecklengths is specified, fixed length target fields are not checked to verify that they are large enough for the data, and an attempt is made to import each row.
Note:
  • a Table 6 lists the characters that can be used as delimiter overrides.
  • b The character must be specified in the code page of the source data.

    The character code point (instead of the character symbol), can be specified using the syntax xJJ or 0xJJ, where JJ is the hexidecimal representation of the code point. For example, to specify the # character as a column delimiter, use one of the following:

       ... modified by coldel# ...
       ... modified by coldel0x23 ...
       ... modified by coldelX23 ...
    

See Also

EXPORT


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]