asked on

How to import tab delimited file into IBM DB2 UDB table

I need to import tab delimited data into a DB2 UDB table. Character strings are not delimited.
The import command fails

IMPORT FROM "mytable.del" OF DEL MODIFIED BY COLDEL0X09 MESSAGES "mytable.log" INSERT INTO mytable;

Fails with a message for each row

SQL3116W The field value in row "1" and column "2" is missing, but the target
column is not nullable.

I have checked the input file and it does have tab characters delimiting fields so it would appear that the lack of character string delimiters is causing a problem.

I have tried CHARDEL"" along with COLDEL0X09 with same result (as expected, I guess).

The import file was produced by another IBM product, iSeries Access for Windows data transfer facility.

Question: How import a tab delimited file with no character string delimiters into a DB2 UDB table.

Lowfatspread

can the iSeries Access for Windows data transfer facility.
product produce pcIXF format? if so consider trying that..

and it will help if you specify the Operating systems and Versions of DB2/UDB involved

pdegregorio

ASKER

To answer your question, DB2 UDB is version 8.1.2 on Win2K. On the iSeries side it is OS/400 V5R2.

I tried comma separated value output from iSeries Access for Windows data transfer. I had failures regarding it putting putting quotes around all date columns, so I switched to tab delimited format.

Tab delimited (without unneeded string delimiters) is a standard simple format. DB2 UDB does not support it?

ghp7000

have you made sure that column 2 of line 1 is not blank, the message you are getting is primary key violation or column cannot accept null values
I think tab markers are supported, I think the hex code you are using is incorrect, but try method L of the load command instead, it will be faster than import command.

Shalom Carmel

Quite possibly the date columns are saved as strings (some programmers ought to be shot...)
You can try the native iSeries database export tool, CPYTOIMPF .

CPYTOIMPF FROMFILE(your_library/your_file)
TOSTMF('/your_folder/your_file.csv')
MBROPT(*REPLACE)
STMFCODPAG(*PCASCII)
RCDDLM(*CRLF)
STRDLM(*NONE)

Get your friendly iSeries support person to help you with this setup.

Then, ftp into the iseries and do:

cd /your_folder
ascii
get your_file.csv

ShalomC

pdegregorio

ASKER

Summary: Is it possible to import tab delimited data without character string delimiters into DB2 UDB?

To clarify this because some of the comments are causing some drift: Is it possible to import tab delimited data without character string delimiters into DB2 UDB? Although the data originated on the AS/400 iSeries it is being imported into DB2 UDB on Windows (Win2K). The preferred method of export is tab delimited not comma delimited and the tab delimited format does not delimit character strings with quotes, as this is unecessary. Yet it does not appear that DB2 UDB offers an option to not have character string columns not be delimited. Consequently, the import loads the entire input record into the first field (a character field), tabs and all, and then complains the column 2 does not have any data furnished. I have checked the import file and it definitely contains tab characters (hex 09).

ASKER CERTIFIED SOLUTION

ghp7000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ghp7000

oop sorry, you can only use "OF ASC" file format, try it with OF ASC

Shalom Carmel

Hey,
Column 2 must allow null values, or the import will faill.

ShalomC

pdegregorio

ASKER

GHP7000: OF ASC did not work, however your verifying that tab delimited works for you was very helpful
SHALOMC: Yes, column 2 null issue was a problem ... solved by using USEDEFAULTS modifier

Here is what worked:

DB2 -tv IMPORT FROM mytable.del OF DEL MODIFIED BY COLDEL0X09 USEDEFAULTS MESSAGES mytable.log INSERT INTO mytable;

Thank you for the help