Sequential File Import - Doesnt like NULL Integer and Dates

[nmacolin]

Hi all,
General Question here, something basic or obvious I'm not doing.

Situation.
I Have server jobs which extract from source to Sequential files.
The data types vary - Varchars, Integers, Dates.

I then use the Sequential file in Px stage to load into Target schema.

Problem :
When I try and view the data file from the Sequential File stage in Px It fails as it cannot import nulls ???
So there would be some columns which do not contain any data in the source. These would apear as <DATA> ,,,, in the csv file. Would seem common place, no integer or date values. ( i,e End dates )

I changed the DATE dataypes to VARCHAR as that will be fine to load into the DB2 DATE field as long the format is correct. But still need to be able to handle integers ?

But when these columns do not have any values the Sequential file stage doesn't allow it to load.

Is there some general rules that I've missed here ?

Thanks
Nick

[hamzaqk]

do the null handling for these fields in the column metadata

[ray.wurlod]

There's no such thing as a null in a Sequential File, since a Sequential File does not have true data types.

However the parallel Sequential File stage treats the file as if it does have true data types, so you must provide null handling (for example Null Field Value property), because "" is neither a valid date nor a valid integer.

[nmacolin]

Hi guys,

Sorry Ray don't have access to Premium content yet. (There's a lot of red tape to get through to get a few dollars for something useful in this place )

I can understand that the Sequential file doesn't have any types.
Having empty strings in my file is fine, I have made the columns nullable as this is a migration project, so if empty field in , empty field out.

The comments above say use "Null Handling".
I'm not sure what the best approach here is and where to perform the null handling.

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

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 place 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

[hamzaqk]

i just imported the metadata of the file and viewed it. its working ok . you can try this and see what metadata type is assgined when you do it. If you have integer values you need to handle the nulls yourself and use the field max width property for such columns

[nmacolin]

Yep I've repeated this from the parallel post, just incase it doesn't get moved.

Anyway here is the answer

In the Server Job which generates the file set the
Format Tab :: Default Null String "

In the Paralle Job which read the file the have set
Format Tab : Field Defaults : Null Field Value "

Yep may be obvious now .....

Regards
Nick