null export handling

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
lory
Participant
Posts: 21
Joined: Tue Apr 13, 2004 9:05 am

null export handling

Post by lory »

Hi,
I have a job:

Oracle extraction - file sequential.

Some fields are nullable, and I want them null. But I have some discard, the message is the following:

Rss_Consistenza_file.Estraz,0: Field "FIELD1" is null but no null export handling is defined
Rss_Consistenza_file.Estraz,0: Export unsuccessful at record 3 (continuing) :
..........

Could anyone help me?

Thanks
Eric
Participant
Posts: 254
Joined: Mon Sep 29, 2003 4:35 am

Post by Eric »

look at the columns list in the sequential file and double click on the row number (left hand side), this brings you up another screen that allows you to set the -> Nullable -> Null Field Value.

This allows you to set the character string you are using to mean NULL that is written to file.
lory
Participant
Posts: 21
Joined: Tue Apr 13, 2004 9:05 am

Post by lory »

[quote="Eric"]look at the columns list in the sequential file and double click on the row number (left hand side), this brings you up another screen that allows you to set the -> Nullable -> Null Field Value.

This allows you to set the character string you are using to mean NULL that is written to file.[/quote]

I'd like to know if it is possible to leave the NULL value in the file without without being substituted by another string :(
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Essentially the answer is NO.

NULL is a meaningless concept in a text file. If you want the internal database representation of null, you will find that this varies between databases, and would mean that your text file is probably a binary file (there's no guarantee that the internal representation of null doesn't contain 0x0D either, which would really foul you up).

If you want the null fields in your text file to contain zero-length strings (""), do nothing. This is the default for the Sequential File stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
lory
Participant
Posts: 21
Joined: Tue Apr 13, 2004 9:05 am

Post by lory »

The problem is that the record with null values are doscarded and not written in my file. Why?
:(
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Because you have not specified null handling.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Eric
Participant
Posts: 254
Joined: Mon Sep 29, 2003 4:35 am

Post by Eric »

Just to note:
When you use a 'special character sting' to represent a NULL in a text file.

When you read that text file back into DataStage you can tell DataStage that the 'special character string' means a null, so when you load data back into a database you again have a NULL value (rather that your special string).
gh_amitava
Participant
Posts: 75
Joined: Tue May 13, 2003 4:14 am
Location: California
Contact:

Post by gh_amitava »

lory,

Use NVL function of Oracle in the select clause. It is best to handle null there.

Regards
~Amitava
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Post by richdhan »

Hi Amitava,

Using the NVL function in oracle will not resolve the warning. The record will not be discarded but the warning will still persist. The best way to handle this situation is using the Null handling feauture available in Field properties of sequential file stage as described by Eric and Ray.

Rich
PilotBaha
Premium Member
Premium Member
Posts: 202
Joined: Mon Jan 12, 2004 8:05 pm

Post by PilotBaha »

Ok, as a newbee I have a question about this null handling. I was not doing anything on null handling and the job started to give a lot of null errors. So, after doing a search on the board here, I decided to try null field length = 0 approach.

This time I am getting

Code: Select all

Error when checking operator: At field "PRUSERNAME": "null_length" may only be used in conjunction with prefix length or link length
as an error.

Dealing with these nulls is becoming pretty annoying at this point..
Earthbound misfit I..
Post Reply