I'm loading a DB2 table from a file in a script using the format LOAD FROM ${CSVFILE} OF DEL REPLACE INTO T_TABLE. When the column value is "" , SQL Developer will display value as (null) but this is not an actual null. The behavior I want is to get this value set as NULL so the column default value gets set. How can I make it so in case of empty string it will set NULL value in DB?
4
-
What's the db2 platform and version?Mark Barinstein– Mark Barinstein2022-03-28 06:43:19 +00:00Commented Mar 28, 2022 at 6:43
-
V11.5.6.0 special_13320rmaitipe– rmaitipe2022-03-28 07:03:37 +00:00Commented Mar 28, 2022 at 7:03
-
What's the platform (OS)?Mark Barinstein– Mark Barinstein2022-03-28 07:33:20 +00:00Commented Mar 28, 2022 at 7:33
-
Unix using a csh scriptrmaitipe– rmaitipe2022-03-28 07:39:20 +00:00Commented Mar 28, 2022 at 7:39
Add a comment
|
1 Answer
You may use EXTERNAL TABLE functionality for that, because it's not so easy to make LOAD convert empty values into NULLs with user exit application.
db2 "declare c1 cursor for select nullif (a, '') as a, b from external '${CSVFILE}' (a varchar (10), b int) using (delimiter ',' quotedvalue double)"
db2 "load from c1 of cursor replace into T_TABLE"
Notes:
You have to describe all the file fields appropriately in the DECLARE CURSOR command.
You may use another appropriate options (ENCODING, CRINSTRING, LFINSTRING, etc.) in the USING clause - refer to the 1-st link for more details.
