DB2: IMPORTing and EXPORTing

Here are some quick and dirty instructions on how to import data that you have in a file into a DB2 table. Likewise, you can export the data from a DB2 table into a file. The DB2 commands, surprise, are named import and export, respectively.

Yes, Virginia, there is a DB2 load command. It offers a way to bulk-load schemas and their data into DB2. However, load requires DBA privileges and so is not for us. The import command is available to all, and is exactly what we want for purposes of the project.

IMPORT

First, the table to which you want to add data should exist. So create it first, if you do not have it already. Prepare a file with the "tuples" that you want to import. For a file in "DEL" format, each line in the file is a tuple, and the attribute values are separated (delimited) by commas (","'s). The file is just an ASCII file.

import from file.del of del

messages import.msg

insert into table;

This imports the "tuples" from the file named file.del into the table named table. The import utility will attempt to insert each line from file.del into table just as if you had issued an insert command yourself (formatted correctly, of course). Note that the order of your attribute values on each line of file.del must be the same as the "order" of the attributes in the table. (The describe command will show you the "order" of your table's attributes. This is the same order as you specified them in your create command when you made the table.)

Of course, some of the inserts may succeed, others may fail. (Why?) Whatever the import utility accomplishes is recorded in the message file import.msg for you perusal.

You can replace "insert" in the command above with insert_update. Then any new tuple that clashes with a tuple already in the table will replace ("update") the older tuple. (How can two tuples clash? They have the same primary key values.) To use this option, your table must exist and have a primary key declared. And, of course, you must have update privilege on the table. Likewise, you can replace "insert" with replace. This first wipes all tuples from the table, and then adds the "tuples" from the file.

Format

Need to know what the format of you data must be in within file.del? A couple of pointers:

Put strings (for instance, char, varchar) in the file within double-quotes. For example, "Parke Godfrey".
Numbers (for instance, integer and real) are not quoted. So, 1234.
Dates (date) on our system are in the format year-month-day in numerals. For instance, 1999-10-30. Put them in single quotes in your file. For instance, '1999-10-30'. The alternate format of month/day/year is also accepted. For instance, '10/30/1999'.

Well, I am not about to recreate all the documentation here! Need more? Read the documentation. More easily, do the following. Populate your table with a couple of tuples by hand, using the insert command. Then export your table to a file. (See the export command below.) This will show you precisely the format you need for an import file for that table.

IMPORT via ASC

An alternate import method is as follows. It uses an "ASC" file instead of a "DEL" file like above. Again, the file is an ASCII file. But instead of the attribute values being separated by a delimiter character (the comma, in this case), each attribute entry begins at a specified character column in the file.

import from file.asc of asc

method L(1 20, 21 26, 29 30, 33 51, 54 63, 66 73)

messages import.msg

insert into table;

The method line above is an example. It is for a table with six attributes. It specifies that the value for attribute one is between character columns 1 and 20, the second attribute's value between columns 21 and 26, and so forth.

EXPORT

Conversely, you can export tuples from a table into a file.

export to file.del of del

messages export.msg

select * from table;

As above, instead of a "DEL" file, you could create an "ASC" file instead by putting the phrase "to asc". Note that you use your favorite (favourite?) select SQL query as the last part of the command. So you could add a where clause if you wanted to select just some of the tuples from table. (Likewise, you could save the results of a join query into a data file, if you ever had any need to do so.)

IXF Format

Probably you will not need this, but just to be informative, there is another file format supported (besides "DEL" and "ASC") called IXF. It is special in that it also encodes the table's schema into the file. So it is a useful utility for moving tables from one database to another or from one system to another.

But is can be useful sometimes for database gurus too, for other purposes. Database folks, like professional programmers, have quite a bag of tricks. Here is one that Don Chamberlin suggests.

export to temp.ixf of ixf

select * from table where 1 < 0;

import from temp.ixf of ixf

create into copy;

The option create is available to import for IXF files, because the IXF file contains the schema specification. Note that the "where 1 < 0" condition seems odd: it fails always. So no tupes are selected by the export command. However, the schema specification of the table named table is written to temp.ixf. Then the import command creates a new table named copy with an identical schema to table. The table copy is empty with no tuples. Hence, a quick and dirty way to replicate a table's schema!