How to load context of text files into DB2

The task is fairly simply --- I need to load the content of some text files into DB2. There are many ways to do so, but since I only need to this only once (hopefully), I want to find an easy way without writing any external code.

So here is the solution:

(1) create a table containing a CLOB (VARCHAR should work as well, if we know the maxium size of the files in advance) column. For example:

create table test (docid INTEGER GENERATED BY DEFAULT AS IDENTITY,doc CLOB)

(2) put the files to be loaded into one directory and create a .del file based on their names. The content in the .del file should based on the table you have just created. For instance, a .del file for the example table created in (1) should look like the following:

1,"doc1.txt"

2, "doc2, txt"

.....

where the first value is the id, and the second value is the name of a file to be loaded.

(3) load the content

import from test.del of del lobs from /home/dirName/ modified by lobsinfile replace into test

Depends on the exact nature of your task, you may add additional modifiers to the import command. But the above three steps should be adequate in many cases.

Finally, just to make sure that the files are properly loaded into the database, we can export part of the table and view the content:

export to myfile.del of del lobs to outDirName lobfile lobs1, lobs2 modified by lobsinfile select * from test where docid < 10

Comments

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account associated with the e-mail address you provide, it will be used to display your avatar.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <div> <h1><h2><h3><sub><sup><b><i><u><font><img>
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • Lines and paragraphs break automatically.

More information about formatting options

To prevent automated spam submissions leave this field empty.
Nice place