Sequential file format to load varchar empty field in DB2

[Salegueule]

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.

[Salegueule]

Bonjour Thomas,

Here is the output generated by OSH_PRINT_SCHEMAS:

main_program: Schemas:
Data set "users_rwd_test:Ln_Out_PADB_rwd.v":
record
( indiv_id: nullable decimal[10,0];
rwd_prod_clm_seq: nullable int16;
item_conf_no_bookg: nullable string[max=32];
)
Operator "rwd_file":
input 0 interface:
record
( indiv_id: nullable decimal[10,0];
rwd_prod_clm_seq: nullable int16;
item_conf_no_bookg: nullable string[max=32];
)
Operator "users_rwd_test":
output 0 interface:
record
( indiv_id: nullable decimal[10,0];
rwd_prod_clm_seq: nullable int16;
item_conf_no_bookg: nullable string[max=32];
)


In the source database all columns are set to NOT NULL. the generated output file is as follow:

" 0000000003.","4","Benoit"
" 0000000001.","2","Yves"
" 0000000007.","8",""
" 0000000005.","6"," "

The DS engine put quotes on all columns. We would resolved our problem if we did not have the quotes surrounding the numeric columns.

Thanks.

[chulett]

The DS engine put quotes on all columns.

Because you defined them as string (VARCHAR) fields in the Sequential File target stage.

[Salegueule]

Hi chulett,

Columns are not specified in the Sequential File target stage. We are using RCP.

[chulett]

Point still stands however, regardless of how they get there.

[Salegueule]

Bonjour Thomas,

You are right. What I am now looking at is to verify if I could add surrounding quotes to column using the Modify stage. If that is working then I am planning to do the following:
-first produced a DS dataset; then using a shell script, parse the output of the orchadmin describe command and grep for each column the occurrences of string in order to build a parameter command line that will feed the modify stage dynamically before going tomy Sequential file stage. That should do it I think.

[Salegueule]

Bonjour Thomas,

Yes you are right; cant do that with the Modify stage. I implement it as per your suggestion and it is working fine. I am getting the quotes only on the string columns but the column order is changed in the output sequential file. I will look at it.

Thanks.