CODEWITHSUNDEEP
sqluniximportdb2
Vicky
Vicky

Reputation: 17375

spaces in not null column in db2 table using import command

I have two tables in two different databases with same structure.

I am exporting one table into a .dat file and then importing it to other table using db2 import command:

import from $TEMP_DIR/TO_IMPORT.dat of del modified by dateformat=\"YYYY-MM-DD\" timeformat=\"HH:MM:SS\" timestampformat=\"YYYY-MM-DD HH:MM:SS.UUU\" chardel0x01 coldel|  COMMITCOUNT 1000 messages $LOG_DIR/to_import.log insert into MY_SCHEMA.MY_TABLE

A row in TO_IMPORT.dat is as below:

col1_val|col2_val|col3_val|col4_val|col5_val

The delimiter is '|'

col1, col2, col3 are NOT NULL columns in source and destination tables.

However, in source table, in some of the rows, col2 and col3 are having spaces in them instead of character values.

These are coming in .dat file as below:

abc| | |def|pqr

Now, during import of above row, these spaces are getting considered as NULL and thus everthing fails on trying to insert a null value in a not null column of destination table.

What is the solution/workaround for this problem ?

Thanks for reading!

Upvotes: 2

Views: 3729

Answers (1)

Mayur Manani
Mayur Manani

Reputation: 825

You can use option KEEPBLANKS and it will not treat the spaces as NULL.

From IBM DB2 Documentation:

KEEPBLANKS - Preserves the leading and trailing blanks in each field of type CHAR, VARCHAR, LONG VARCHAR, or CLOB. Without this option, all leading and tailing blanks that are not inside character delimiters are removed, and a NULL is inserted into the table for all blank fields.

Upvotes: 3

Related Questions