Null handling in PX Sequential files

[vjreddy65]

How do I read a Delimited sequiential file (having Null values in a couple fields ) with out dropping any of the records in PX.

Sample input file format

12|32|43
32||23

Here FIeld2/Row2 is a null. When std PX Sequential file stg is used to read this, second record is getting dropped.

Any ideas are appreciated..

[rasi]

Hi

In the Sequential File Properties go to the format tab and Fill in the Null Field Value with empty string or with some default value. Also make sure that Nullable columns are handled properly in your transformation

Thanks

[Eric]

Remember there is no such thing as a NULL in a sequential file only an empty string

[ArndW]

Eric - there can actually be NULLs in a sequential file; the SQL null is CHAR(128) and that can be found in strings. But you are right that the concept of NULL as it is used in databases doesn't exist in flat files.

[vjreddy65]

Guys,
Thx for your ideas.

I have a Nothing fields like two delimiters coming side by side with nothing between them
12|32|43
32||23

Second row in the example has the scenario Iam talking about.

When I read such a row using PX sequential file, it gets dropped in reading itself. It doesn't come even till next stage. So How do I read this row without dropping it.

[ArndW]

Just follow the suggestions that Rasi gave and you should be OK

[vjreddy65]

As Rasi/Siva said, I even included the Null field value to diff characters but in vain. Any thoughts...

[ray.wurlod]

Siva's advice relates to server jobs. The way you handle zero-length strings in parallel jobs is different; but the representation of null property in the Sequential File stage is one of them. Once you can get "" into your job, you then have to monitor it right through the job design and either convert it back to a null, or supply a replacement default value, if either of those actions is appropriate.

[vjreddy65]

Ray,
But how do I get it in to the job. Those records with NULL values are getting dropped in the sequential file stage itself and Iam not able to get it to the next stage. Suggest me a way to get them in.

[elavenil]

Agree with Siva's suggesstion. We implemented the same method to handle null value, which is specify the default value in the null string column when you read the seq file and transform back to NULL in the transformer stage before writing the record into a dataset.

This would resolve the issue.

Regards
Saravanan

[dsdesigner]

Hi Vj

I may be reiterating what siva has already said, but this is what you can try for the example data that you posted that you need to read.
Go to the properties tab of the sequential file and add the property default null field value and put in ''(two single quotes) as the value.

HTH

Thanks

[dsxdev]

Hi VJ,
Above suggestions hold good if you have used the Null field property while writing to the file.

Otherwise you will not be able to read this record.
Actually Nulls do exist in Sequential files but these values are not interpreted correctly. To avoid any such prcarious situation, use Null field value property while writing to the file.

Then use same Null Filed value property to read the file. This will ensure that null vallues are interpretted correctly in DataStage jobs.

[lshort]

I just completed a PX seminar. I had this same problem in an exercise. If your reading a row like:

a,,b,c,d

this row will be dropped because of the null in F2.

To solve this problem you must set the Null String property. You must pad this property with characters matching the length defined for the field that is null.

ie. if F2 is defined as char(3) you must set the Null String property to '___ ' or 000 etc.

btw: if your source file was fixed length there is an enviornment variable for Null handling which would accomplish the same thing (FIXED ONLY)

This approach worked for me.
Good Luck

[ray.wurlod]

Clarification: That environment variable has the name APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL (whew!) and applies only to the Import and Export operators in the framework.

[chulett]

But what would qualify as a 'null' in a fixed width sequential file? Any inner column that might qualify would have to be all spaces to maintain the sizing of the record. Are we talking about that or trailing (i.e. missing) columns from the end of the record?