Data Movement Utilities Guide and Reference

Example Import Sessions

CLP Examples

Example 1

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 "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 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)
       datalink specification (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".

Example 3 (Importing into a Table with an Identity Column)

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.

API Examples

See API Examples.