DB2 Version 9.7 for Linux, UNIX, and Windows

Import sessions - CLP examples

Example 1
The following example shows how to import information frommyfile.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 "myfile", 
qualifier "        ", and source "            ".
 
SQL3109N  The utility is beginning to load data from file "myfile".
 
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.

Example 2
The following example shows how to import into a table that has identity columns:

TABLE1 has 4 columns:

C1 VARCHAR(30)
C2 INT GENERATED BY DEFAULT AS IDENTITY
C3 DECIMAL(7,2)
C4 CHAR(1)

TABLE2 is the same as TABLE1, except that C2 is a GENERATED ALWAYS identity column.

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.

Example 3
The following example shows how to import into a table that has null indicators:

TABLE1 has 5 columns:

COL1 VARCHAR 20 NOT NULL WITH DEFAULT
COL2 SMALLINT
COL3 CHAR 4
COL4 CHAR 2 NOT NULL WITH DEFAULT
COL5 CHAR 2 NOT NULL

ASCFILE1 has 6 elements:

ELE1 positions 01 to 20
ELE2 positions 21 to 22
ELE5 positions 23 to 23
ELE3 positions 24 to 27
ELE4 positions 28 to 31
ELE6 positions 32 to 32
ELE6 positions 33 to 40

Data Records:

   1...5....10...15...20...25...30...35...40
   Test data 1         XXN 123abcdN
   Test data 2 and 3   QQY    wxyzN
   Test data 4,5 and 6 WWN6789    Y

The following command imports records from ASCFILE1 into TABLE1:

   db2 import from ascfile1 of asc
   method L (1 20, 21 22, 24 27, 28 31)
   null indicators (0, 0, 23, 32)
   insert into table1 (col1, col5, col2, col3)

Note:

Since COL4 is not provided in the input file, it will be inserted into TABLE1 with its default value (it is defined NOT NULL WITH DEFAULT).
Positions 23 and 32 are used to indicate whether COL2 and COL3 of TABLE1 will be loaded NULL for a given row. If there is a Y in the column's null indicator position for a given record, the column will be NULL. If there is an N, the data values in the column's data positions of the input record (as defined in L(........)) are used as the source of column data for the row. In this example, neither column in row 1 is NULL; COL2 in row 2 is NULL; and COL3 in row 3 is NULL.
In this example, the NULL INDICATORS for COL1 and COL5 are specified as 0 (zero), indicating that the data is not nullable.
The NULL INDICATOR for a given column can be anywhere in the input record, but the position must be specified, and the Y or N values must be supplied.