ignoring end of line character in a DAT file in DB

Question

I have a dat file containing data. A line from the file is as below:

HHHH-III|AAAA0000536101|2011/11/16|0100002MM000

This data is being loaded in a DB2 table using db2 import command.

Issue: The last field in the data gets loaded along with the "new line" character at the end of the line in the dat file i.e. 0100002MM000 is getting loaded with some invisible character at the end.

Same field is being loaded in other table from other file. But in the other dat file it is at the centre somewhere in the line as below:

2011/11/16|0100002MM000|HHHH-III|AAAA|BBBBB|KDKDKDKDDKD MANAGED|C

So later on comparing 0100002MM000 from first table with 0100002MM000 in second table is failing.

How to resolve this issue?

So in this case,

Answer 1

Is it possible that the file has Windows "\r\n" (CRLF) line terminators, and the import is expecting Unix "\n" terminators, so it's importing the "\r" at the end? If so, the fix is just to convert the file to the Unix format - there are lots of tools around to do that, or you can do so programmatically easily enough.

Check whether this is the case by opening up the file in binary file editor and looking at the line terminator - if it's using 0x0D 0x0A (or a UTF-16 equivalent) then that's the Windows line terminator. Even if it's not that, looking in a binary file editor will show you what is at the end of the line - maybe there's a null character (0x00) which has got in there somehow. If that's the case, again it should be easy to strip but you should see whether you can avoid the problem in the first place by fixing the code which generates the file to start with. (This may be out of your control, but it may not be.)

Answer 2

Probably your data has not the same structure as DB2 default structure is. You must override this behaviour by specifying more in detail. Take a look at http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008304.html

Also, there is a link that talks about all those problems when importing DEL files with different delimiters: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0011047.html