IBM Support

50 DB2 Nuggets #25 : Tech Tip - Upon load/import of data you may see additional data in the first row inserted.

Technical Blog Post


Abstract

50 DB2 Nuggets #25 : Tech Tip - Upon load/import of data you may see additional data in the first row inserted.

Body

Using a simple test case. This can be easily observed. Document was exported using a utility that applies a byte order mark to text documents. 
 
db2 "create table numbers (col1 char(10),col2 char(10),col3 char(10))"
DB20000I  The SQL command completed successfully.
 
[db2v97@ts-carose2 ~]$ db2 import from numbers.csv of del insert into numbers
SQL3109N  The utility is beginning to load data from file "numbers.csv".
 
SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.
 
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "2".
 
SQL3222W  ...COMMIT of any database changes was successful.
 
SQL3149N  "2" rows were processed from the input file.  "2" rows were
successfully inserted into the table.  "0" rows were rejected.
 
 
Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2
 
[db2v97@ts-carose2 ~]$ db2 "select * from numbers"
 
COL1       COL2       COL3
---------- ---------- ----------
"one"     two        three <-----------------First row has extra data in it. 
four       five       six
 
  2 record(s) selected.

 

This caused by a byte order mark being put into the file. This is a mark to indicate the endianness of a text file or stream. Most text files will not show it. But Db2 will read this as the first part of the data to be inserted. 

Some 3rd party utilities will add this byte order mark when a table is exported using the utility. It is recommended if you see this issue to check if the utility does this. 

You can also open the file with a text editor that will show this byte order mark. 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141588