DB2 Import

"Exporting your worries is the passport to IMPORT happiness"

PS Note : First read DB2 Export .

Inserts data from an external file with a supported file format into a table, hierarchy, or view. A faster alternative is LOAD; however, the load utility does not support loading data at the hierarchy level.

Moreover the LOAD utility does not take care of indexes and realtions while loading the data and hence the integrity of the tale may be compromised. The import utility uses the INSERT queries , so even though import is slower than the LOAD the integrity of the tables are preserved.

db2 Import from myfile.ixf of ixf
Messages msg.txt
Insert into staff

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

Some of the parameters for Import Utility

• COMMITCOUNT n
  Performs a COMMIT after every n records are imported. It is not applicable when importing through DB2 Connect.

• CREATE
  Creates the table definition and row contents. Applicable only during import of IXF file.

• IN tablespace-name
  Identifies the table space in which the table will be created if REPLACE_CREATE, CREATE parameter is used.

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

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

• LOBS FROM lob-path
  Specifies one or more paths that store LOB files.

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

• MESSAGES message-file
  Specifies the destination for warning and error messages that occur during an import operation.

• METHOD
  L : (ASC File only)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.
  N :(IXF Files only) Specifies the names of the columns to be imported.
  P : (IXF , DEL Files)Specifies the indexes (numbered from 1) of the input data fields to be imported.
  

• 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).

• OF filetype
  ASC, DEL, WSF, IXF files to which data is already exported.

• REPLACE
  Deletes all existing data from the table by truncating the data object, and inserts the imported data.

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

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

Consider the following Scenario and how import works

Data records in DATAFILE1 (DEL format):

"Liszt"
"Hummel",,187.43, H
"Grieg",100, 66.34, G
"Satie",101, 818.23, I

Data records in DATAFILE2 (DEL format):

"Liszt", 74.49, A
"Hummel", 0.01, H
"Grieg", 66.34, G
"Satie", 818.23, I

The following command generates identity values for rows 1 and 2, since no identity values are supplied in DATAFILE1 for those rows. Rows 3 and 4, however, are assigned the user-supplied identity values of 100 and 101, respectively.

db2 Import from datafile1.del of del
Replace into table1

To import DATAFILE1 into TABLE1 so that identity values are generated for all rows, issue one of the following commands:

db2 Import from datafile1.del of del
Method P(1, 3, 4)
Replace into table1 (c1, c3, c4)

db2 Import from datafile1.del of del
Modified by identityignore
Replace into table1

To import DATAFILE2 into TABLE1 so that identity values are generated for each row, issue one of the following commands:

db2 Import from datafile2.del of del
Replace into table1 (c1, c3, c4)

db2 Import from datafile2.del of del
Modified by identitymissing
Replace into table1

If DATAFILE1 is imported into TABLE2 without using any of the identity-related file type modifiers, rows 1 and 2 will be inserted, but rows 3 and 4 will be rejected, because they supply their own non-NULL values, and the identity column is GENERATED ALWAYS.

Notes on Import

• REPLACE, REPLACE_CREATE : Commits are automatically performed when log is full
• INSERT, INSERT_UPDATE : Roll back is done when log is full.
• Referential constraints and foreign key definitions are not preserved when creating tables from PC/IXF files. (Primary key definitions are preserved if the data was previously exported using SELECT *.)
• Data cannot be imported to a system table, a declared temporary table, or a summary table.
• Views cannot be created through the import utility.

Some Modifiers that can be used in Import with all File formats:

• compound=x : Uses nonatomic compound SQL to insert the data, and x statements will be attempted each time.
• generatedignore :This modifier informs the import utility that data for all generated columns is present in the data file but should be ignored.
• generatedmissing : If this modifier is specified, the utility assumes that the input data file contains no data for the generated columns (not even NULLs), and will therefore generate a value for each row.
• identityignore : This modifier informs the import utility that data for the identity column is present in the data file but should be ignored.
• identitymissing : If this modifier is specified, the utility assumes that the input data file contains no data for the identity column (not even NULLs), and will therefore generate a value for each row.
• lobsinfile : lob-path specifies the path to the files containing LOB values.
• 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.
• 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.
• dateformat="x" : x is the format of the date in the source file. "D-M-YYYY" , "MM.DD.YYYY" , "YYYYDDD"
• 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.
• timeformat="x" : x is the format of the time in the source file. "HH:MM:SS" , "HH.MM TT" , "SSSSS"
• timestampformat="x" : "YYYY/MM/DD HH:MM:SS.UUUUUU" ... In all the above date/time formats TT indicates AM/PM and UUUUUU represents microseconds since midnight.

Modifiers that can be used in Import with ASC (Non Delimited ASCII file) file format alone

• nochecklengths : If nochecklengths is specified, an attempt is made to import each row, even if the source data has a column definition that exceeds the size of the target table column.
• nullindchar=x :x is a single character. Changes the character denoting a null value to x. The default value of x is Y.
• 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 / striptnulls : Truncates any trailing NULLs (0x00 characters) / blank spaces when loading data into a variable-length field. If this option is not specified, NULLs are kept.

Modifiers that can be used in Import with DEL (Delimited ASCII file) file format alone

• chardelx : x is a single character string delimiter.
• coldelx : x is a single character column delimiter.
• 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 (+).
• decptx : x is a single character substitute for the period as a decimal point character. The default value is a period (.).

Modifiers that can be used in Import with IXF file format alone

• forcein : Directs the utility to accept data despite code page mismatches, and to suppress translation between code pages.
• 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.
• indexschema=schema : Uses the specified schema for the index name during index creation.

Reference : http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp