Sequential file format to load varchar empty field in DB2
Posted: Mon Nov 16, 2015 12:42 am
Hi,
I am trying to generate a sequential file that will be used by a load command targeting DB2. The job is using RCP.
The structure of my test table is decimal not null, smallint not null, varchar(32) not null with no default. Our sequential file format is :
Record:
final delimiter=end
record delimiter=Unix new line
Field:
delimiter=comma
null field value= ''
quotes=double
Our test data:
insert into tests.r_test
values
(1,2,'Yves'),
(3,4,'Benoit'),
(5,6,' '),
(7,8,'');
Using the above does not seems to work with the load utility as everything is double quoted, it complains numeric conversion. If we set quotes=none, then the load complains that the value is missing for the third columns, as the fourth record has the varchar value set to empty.
Using the DB2 EXPORT utility generates the following format:
+0000000001.,2,"Yves"
+0000000003.,4,"Benoit"
+0000000005.,6," "
+0000000007.,8,""
Using the DB2 IMPORT utility to load the data seems to work fine with the above format. I am probably missing something simple here. Is it possible to generate the above format with Datastage?
Thanks for your help.
I am trying to generate a sequential file that will be used by a load command targeting DB2. The job is using RCP.
The structure of my test table is decimal not null, smallint not null, varchar(32) not null with no default. Our sequential file format is :
Record:
final delimiter=end
record delimiter=Unix new line
Field:
delimiter=comma
null field value= ''
quotes=double
Our test data:
insert into tests.r_test
values
(1,2,'Yves'),
(3,4,'Benoit'),
(5,6,' '),
(7,8,'');
Using the above does not seems to work with the load utility as everything is double quoted, it complains numeric conversion. If we set quotes=none, then the load complains that the value is missing for the third columns, as the fourth record has the varchar value set to empty.
Using the DB2 EXPORT utility generates the following format:
+0000000001.,2,"Yves"
+0000000003.,4,"Benoit"
+0000000005.,6," "
+0000000007.,8,""
Using the DB2 IMPORT utility to load the data seems to work fine with the above format. I am probably missing something simple here. Is it possible to generate the above format with Datastage?
Thanks for your help.