Sequential File - NULL Handling (Best Approach Please)
Posted: Tue Nov 04, 2008 1:08 am
Hi all,
I'm just trying to figure our how to best handle null in the Sequential File stage.
I have the following Jobs in this scenario.
Server Job
Unidata --> Transformer (Oconv(dates)) --> Sequential file
Parallel Job
Sequential File --> Transformer (various) --> Db2 table.
In the serverjob I have set the delimiter to Comma and enclosed "", I do not have anything for the null string option.
Ihave defined the meta data for the Sequential file for the last four columns as
Integer
Date
Integer
Date
In the Parallel job.
I initially started with the sequential job having the same meta data as the server jon sequential file.
But since my file looks like this.
"REFERENCE","DESCRIPTION",AMOUNT,"BUDGET_REFERENCE",TRANSACTION_DATE_RW,"TRANSACTION_DATE",POSTING_DATE_RW,"POSTING_DATE","REPORT_NUMBER_NO_LONGER_USED","USER_BATCH_NUM_BATCH_INDEX_ID","BUDGET_ALLOCATION_EG_MT",JOB_COSTING_DATE_RW,"JOB_COSTING_DATE","CLAIM_NO",POSTING_DAT_PRCSSD_INTO_CLM_RW,"POSTING_DAT_PRCSSD_INTO_CLM",JOB_CSTNG_DT_OF_CST_PRGRSS_CLM_RW,"JOB_CSTNG_DT_OF_CST_PRGRSS_CLM"
"50-01","OBAL",2000000,"50-01",14124,"01-09-2006",14197,"13-11-2006","","","MT",14124,"01-09-2006","",,"",,""
The last four columns are empty. So I get the following error when trying to use the Parallel Sequential File.
##E IIS-DSEE-TFIG-00187 05:51:49(000) <SEFBudgetJournals,0> Field "POSTING_DAT_PRCSSD_INTO_CLM_RW" has import error and no default value; data: <empty>, at offset: 102
I've tried using the null string in the Format Tab, but that looks like it need to be applied to all non VARCHAR values, which is not ideal.
Where should I put the null handling ?
In the output to the ServerJob Sequential file ?
In the Transformer of the ServerJob ?
In the input Sequential file in the Parallel Job?
There seems be a lot places to intercept a null.
In short I want to load an empty field if that is what is in the source.
Thanks in advance for your help guys. Can you give an example in your reply.
Regards
Nick
I'm just trying to figure our how to best handle null in the Sequential File stage.
I have the following Jobs in this scenario.
Server Job
Unidata --> Transformer (Oconv(dates)) --> Sequential file
Parallel Job
Sequential File --> Transformer (various) --> Db2 table.
In the serverjob I have set the delimiter to Comma and enclosed "", I do not have anything for the null string option.
Ihave defined the meta data for the Sequential file for the last four columns as
Integer
Date
Integer
Date
In the Parallel job.
I initially started with the sequential job having the same meta data as the server jon sequential file.
But since my file looks like this.
"REFERENCE","DESCRIPTION",AMOUNT,"BUDGET_REFERENCE",TRANSACTION_DATE_RW,"TRANSACTION_DATE",POSTING_DATE_RW,"POSTING_DATE","REPORT_NUMBER_NO_LONGER_USED","USER_BATCH_NUM_BATCH_INDEX_ID","BUDGET_ALLOCATION_EG_MT",JOB_COSTING_DATE_RW,"JOB_COSTING_DATE","CLAIM_NO",POSTING_DAT_PRCSSD_INTO_CLM_RW,"POSTING_DAT_PRCSSD_INTO_CLM",JOB_CSTNG_DT_OF_CST_PRGRSS_CLM_RW,"JOB_CSTNG_DT_OF_CST_PRGRSS_CLM"
"50-01","OBAL",2000000,"50-01",14124,"01-09-2006",14197,"13-11-2006","","","MT",14124,"01-09-2006","",,"",,""
The last four columns are empty. So I get the following error when trying to use the Parallel Sequential File.
##E IIS-DSEE-TFIG-00187 05:51:49(000) <SEFBudgetJournals,0> Field "POSTING_DAT_PRCSSD_INTO_CLM_RW" has import error and no default value; data: <empty>, at offset: 102
I've tried using the null string in the Format Tab, but that looks like it need to be applied to all non VARCHAR values, which is not ideal.
Where should I put the null handling ?
In the output to the ServerJob Sequential file ?
In the Transformer of the ServerJob ?
In the input Sequential file in the Parallel Job?
There seems be a lot places to intercept a null.
In short I want to load an empty field if that is what is in the source.
Thanks in advance for your help guys. Can you give an example in your reply.
Regards
Nick