Administration Guide

Moving Data

DB2 provides the import and load utilities to help you move data into a table from existing sources. The information provided in this section is a brief overview of moving data. For more detailed information on moving data, you should refer to the Data Movement Utilities Guide and Reference manual.

The import utility takes data from an input file and inserts it into a table or view. In this case, the input file contains data that was extracted from an existing source of data, such as a Lotus 1-2-3 file or an ASCII file. You can also use the import utility to re-create a table or view that was saved by using the export utility. The following information tells you how to import data.

Once you have an input file available in a supported format, use the Import notebook to insert data from the file into an existing table. If this table already contains data, you can either replace or append to the existing data with the data in the file.

You can also use the Import notebook to create a new table that is populated by an input file, or delete existing rows in the selected table and repopulate it using data from the input file.

To import a file into an existing table:

1. Open the File page of the Import notebook.

2. Optional. Specify the Import notebook.

3. Optional. Retrieve Large objects.

4. Optional. Specify column import options.

5. Click OK

To open the File page of the Import notebook:

1. From the Control Center, expand the object tree until you find the Tables folder.

2. Click the Tables folder. Any existing tables are displayed in the contents pane.

3. Click mouse button 2 on a table in the contents pane and select Import from the pop-up menu. The Import notebook opens with the File page displayed.

To specify the file options:

1. In the Import file field of the File page, enter the name of the file that contains the data you want to import.

2. Specify the type of file to import by selecting one of the following

   • Non-delimited ASCII format (ASC)

     Non-delimited ASCII data is data that is aligned in columns.

   • Delimited ASCII format (DEL)

     Delimited ASCII data is a commonly used way of storing data that separates column values with a user-defined delimiting character, such as a comma.

   • Worksheet format (WSF)

   • Integrated exchange format (IXF)

     PC/IXF is a structured description of a database table or view. Data that was exported in PC/IXF format can be imported or loaded into another DB2 Universal Database product database.

   See the online help for the specific products and releases that are supported.

3. Optional: Specify file type modifiers by clicking the corresponding Options push button. The Options window for that format opens.

4. Select an Import mode. The available import modes vary depending on the file type you selected.

5. Optional: In the Commit records field, enter the number of records to import before the changes are committed.

6. Optional: In the Restart field, enter the number of records in the file to skip before beginning the import action.

7. Optional: In the Compound field, type a number to specify how many SQL statements will be executed (in an executable block).

8. Optional: Select the Insert an implied decimal point on decimal data (IMPLIEDDECIMALPOINT) check box.

9. In the Message file field, type the name of the file that will contain warning and error messages that occur during import.

To retrieve large objects from separate files, use the Large Objects page of the Import notebook to retrieve large objects (LOBs) from the path or paths that store the LOB files:

1. Click the Retrieve large objects (LOBs) in separate files (LOBSINFILE) check box to enable the options on the Large Objects page.

2. Specify the location of separate LOB files in the LOB paths list box by clicking the Add push button. These paths are searched (in the order in which they appear in the LOB paths list box) for the LOB files specified in the LOB column of the input file.

3. Click OK to accept the defaults on the other notebook pages and begin the import process.

Specify column import options. Use the Columns page of the Import notebook to specify column import options:

1. Click one of the radio buttons in the Include columns by box to specify the column method that will be used to import data file columns into the table. The available methods vary depending on the file type and mode you selected on the File page.

2. Optional: Specify or change the import file column attributes by clicking the Change push button.

   This option is not available if you selected the Default (method D) radio button.

[ DB2 List of Books | Search the DB2 Books ]