Ingest utility doesn't insert NULL value in a column of integer type

131 Views Asked by At

I am reading a CSV file through a named pipe. In the CSV file the field2 column is blank which need to be inserted into a table column as NULL. The table column is of type integer, but When I try to run the ingest

I am getting an error that says 'field2 cannot be converted to the value type: integer'.

Here is my below code

mkfifo mypipe
tail -n +2 myfile.csv > mypipe &

db2 "INGEST FROM FILE mypipe 
   FORMAT DELIMITED 
   (
      $field1 CHAR(9),
      $field2 INTEGER EXTERNAL,
      $field3 CHAR(32)
   )
   INSERT INTO my_table 
      VALUES($field1, $field2, $field3)"

In the above code, $field2 will be blank. In the my_table, $field2 value doesn't get inserted as NULL when the field is blank in csv.

Sample input csv data as shown below

Subject_Name,Student_ID,STATUS
Maths,,COMPLETED
Physics,,PENDING
Computers,,PENDING

I want the data to be ingested in the table like below

Subject_Name|Student_id|STATUS   |
------------|----------|---------|
Maths       |NULL      |COMPLETED|
------------|----------|---------|
Physics     |NULL      |PENDING  |
------------|----------|---------|
Computers   |NULL      |PENDING  |
------------|----------|---------|

Can anyone suggest a way to resolve this issue?

0

There are 0 best solutions below