Data Movement Utilities Guide and Reference

IMPORT Command

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--(-----null-indicator-list---+---)--' |
                |       .-,--------------.                                                                           |
                |       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 SPECIFICATION--| 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

ALL TABLES

An implicit keyword for hierarchy only. When importing a hierarchy, the default is to import all tables specified in the traversal order.

AS ROOT TABLE

Creates one or more sub-tables as a stand-alone table hierarchy.

COMMITCOUNT n

Performs a COMMIT after every n records are imported.

CREATE

Creates the table definition and row contents. If the data was exported from a DB2 table, sub-table, or hierarchy, indexes are created. If this option operates on a hierarchy, and data was exported from DB2, 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.

DATALINK SPECIFICATION

For each DATALINK column, there can be one column specification enclosed by parentheses. Each column specification consists of one or more DL_LINKTYPE, prefix, and a DL_URL_SUFFIX specification. The prefix specification can be either DL_URL_REPLACE_PREFIX or DL_URL_DEFAULT_PREFIX.

There can be as many DATALINK column specifications as the number of DATALINK columns defined in the table. The order of specifications follows the order of DATALINK columns found within the insert-column list, or within the table definition (if an insert-column list is not specified).

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. (For Distributed File System, or DFS, prefix refers to the "scheme cellname filespace-junction" part of the URL specification.)

Examples of prefix are:

   "http://server"
   "file://server"
   "file:"
   "http://server:80"
   "dfs://.../cellname/fs"

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":

The column input value "/x/y/z" is stored as "http://toronto/x/y/z".
The column input value "http://coyote/a/b/c" is stored as "http://coyote/a/b/c".
The column input value NULL is stored as NULL.

DL_URL_REPLACE_PREFIX "prefix"

This clause is useful for 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 Distributed File System (DFS), prefix refers to the "scheme cellname filespace-junction" part of the URL specification.

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

The column input value "/x/y/z" is stored as "http://toronto/x/y/z".
The column input value "http://coyote/a/b/c" is stored as "http://toronto/a/b/c". Note that "toronto" replaces "coyote".
The column input value NULL is stored as NULL.

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.

FROM filename

Specifies the file that contains the data to be imported. If the path is omitted, the current working directory is used.

HIERARCHY

Specifies that hierarchical data is to be imported.

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, 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 an index can only be done when the table is created.

insert-column

Specifies the name of a column in the table or the view into which data is to be inserted.

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.

INTO table-name

Specifies the database table into which the data is to be imported. This table cannot be a system table, a declared temporary 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.

LOBS FROM lob-path

Specifies one or more 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 the lobsinfile modifier is not specified.

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 if 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.

MESSAGES message-file

Specifies the destination for warning and error messages that occur during an import operation. If the file already exists, the import utility appends the information. If the complete path to the file is not specified, the utility uses the current directory and the default drive as the destination. If message-file is omitted, the messages are written to standard output.

METHOD

L

Specifies the start and end column numbers from which to import data. A column number is a byte offset from the beginning of a row of data. It is numbered starting from 1.

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 indexes (numbered from 1) of the input data fields 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.

MODIFIED BY filetype-mod

Specifies additional options (see Table 5).

NULL INDICATORS null-indicator-list

This option can only be used when the METHOD L parameter is specified; that is, the input file is an ASC file). The null indicator list is a comma-separated list of positive integers specifying the column number of each null indicator field. The column number is the byte offset of the null indicator field from the beginning of a row of data. There must be one entry in the null indicator list for each data field defined in the METHOD L parameter. A column number of zero indicates that the corresponding data field always contains data.

A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL, and that column data specified by the METHOD L option will be imported.

The NULL indicator character can be changed using the MODIFIED BY option (see the description of the nullindchar modifier in Table 5).

OF filetype

Specifies the format of the data in the input file:

ASC (non-delimited ASCII format)
DEL (delimited ASCII format), which is used by a variety of database manager and file manager programs
WSF (work sheet format), which is used by programs such as:
- Lotus 1-2-3
- Lotus Symphony
IXF (integrated exchange format, PC version), which means it was exported from the same or another DB2 table. An IXF file also contains the table definition and definitions of any existing indexes, except when columns are specified in the SELECT statement.

For more information about file formats, see Appendix C, Export/Import/Load Utility File Formats.

REPLACE

Deletes all existing data from the table by truncating the data object, 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. If this option is used when moving data between hierarchies, only the data for an entire hierarchy, not individual subtables, can be replaced.

REPLACE_CREATE

If the table exists, deletes all existing data from the table by truncating the data object, and inserts the imported data without changing the table definition or the index definitions.

If the table does not exist, creates the table and index definitions, as well as the row contents.

This option can only be used with IXF files. It is not valid for tables with DATALINK columns. If this option is used when moving data between hierarchies, only the data for an entire hierarchy, not individual subtables, can be replaced.

RESTARTCOUNT n

Specifies that an import operation is to be started at record n + 1. The first n records are skipped.

STARTING sub-table-name

A keyword for hierarchy only, requesting the default order, starting from sub-table-name. For PC/IXF files, the default order is the order stored in the input file. The default order is the only valid order for the PC/IXF file format.

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.

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.

[ Top of Page | Previous Page | Next Page ]