Inserts data from an external file with a supported file format into a table 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--+--)--+ |
| |
| |
+-CREATE--INTO--table-name---+---------------------------+--| tblspace-specs |--+
| +-,--------------+ |
| V | |
+-(----insert-column--+--)--+
tblspace-specs
|--+-------------------------------------------------------------------------------------+->
+-IN--tablespace-name--+----------------------------+--+---------------------------+--+
+-INDEX IN--tablespace-name--+ +-LONG IN--tablespace-name--+
>--------------------------------------------------------------|
|
Command Parameters
For more information about file formats, see Appendix C. "IMPORT/EXPORT/LOAD Utility File Formats".
| Note: | This method can only be used with ASC files, and is the only valid option for that file type. |
| Note: | This method can only be used with IXF files. |
| Note: | This method can only be used with IXF or DEL files, and is the only valid option for the DEL file type. |
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.
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.
| 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. |
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.
| Note: | Specifying which table space will contain a table's index can only be done when the table is created. |
Example
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. |
Usage Notes
The database table 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.
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.
DB2 Connect can be used to import data to DRDA servers such as DB2 for MVS, SQL/DS, and 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.
| 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 8. 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. | ||
| lobsinfile | lob-path specifies the path to the files containing LOB values. | ||
| 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 | |||
| 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.
| ||
| 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.
| ||
| 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
| ||
| 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"
| ||
| IXF File Format | |||
| 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 load 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. | ||
| |||
See Also