Sequential Files, Timestamps, Null values and Asterisks

[perfman]

Hi,

I am using DataStage 7.5.1

I am trying to load a test csv file which reads like this:
"16/11/2005 13:58:38","17/05/2007 09:59:29",0,"This one does not have a null date"
"16/11/2005 13:58:38",,1,"This one does have a null date in field 2"
"16/11/2005 13:58:38",,2,"This one does have a null date in field 2"
,"16/11/2005 13:58:38",3,"This one does have a null date in field 1"

My job is very simple, it has a sequential file stage and an Oracle Enterprise stage. The job is supposed to read the sequential file and load the data into the database. It is supposed to load the empty date fields in oracle as NULL values.

In my sequential file stage, I define the columns as;

Field 1 ****See image****
-- SQL Type = Timestamp
-- Format string = %dd/%mm/%yyyy %hh:%nn:%ss
-- Nullable = Yes

Field 2 ****See image****
-- SQL Type = Timestamp
-- Format string = %dd/%mm/%yyyy %hh:%nn:%ss
-- Nullable = Yes

Field 3
-- SQL Type = Integer
-- Nullable = No

Field 4
-- SQL Type = VarChar(255)
-- Nullable = No

When I run the job I get the message:
Sequential_File,0: Import complete; 1 records imported successfully, 3 rejected.

The only record that goes into the database is the first one.
("16/11/2005 13:58:38","17/05/2007 09:59:29",0,"This one does not have a null date")

I have used a workaround for this before by including a transformer in the job and bringing the timestamp in as a varchar and then converting it to a timestamp in the transformer, however it is not a very elegant solution.

I would like to define the data types of the csv file in the sequential file definition where it belongs.

And about the asterisks... It looks like DataStage automatically replaces the null dates with 19 asterisks (*******************) and I beleive this is what causes the rejection into the database.

[ray.wurlod]

Welcome aboard. Please advise what the settings are on the Format tab of your Sequential File stage.

[perfman]

Hi Ray,

Here is my format tab:

[ray.wurlod]

OK, try setting the Null Field Value property to "".

[perfman]

Thanks Ray, I tryied that and it did not work. This is still un resolved, however I have put this problem to the side for now, I hope to resolve it sometime, but as I have a workaround in place it is not as important.

[sanjay]

Check whether null value in csv is null or empty string ??

Thanks
Sanjay

Thanks Ray, I tryied that and it did not work. This is still un resolved, however I have put this problem to the side for now, I hope to resolve it sometime, but as I have a workaround in place it is not as important.

[perfman]

null values are empty strings, I think by setting the datatype to a timestamp it implies a fixed width. I came accross this somewhere:

APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL
Set this to true and it should allow a zero length ('') for the NULL value in the decimal fixed width field upon export.

The parallel Job Advance Developer Guide document states the following for the above environment variable:

"APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL
When set, allows zero length null_field value with fixed length fields. This
should be used with care as poorly formatted data will cause incorrect
results. By default a zero length null_field value will cause an error."

This seems to explain my problem and a way to fix it, however the env. var. APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL is not available to me.

[ray.wurlod]

You are permitted to create environment variables, using the Administrator client. Not every environment variable mentioned in the manual is part of the product as shipped, though the ability to react to them is.

[perfman]

I see, so simply adding this environment variable through administrator and setting it to true will produce the intended outcome when including it as a parameter inside a job. Is this correct?

[chulett]

Yes. Once set in the Administrator, it will automatically be included in the environment of every job in the project. Include it as a parameter to override that value.

[ray.wurlod]

I can not read your mind, so can not determine your particular intent. :D

However what you say is true if you use "documented effect". As you note, you don't even need to include it in the job as a parameter - simply having it set (and therefore active in the environment) in the project will suffice to allow it to "do its thing".

Load it as a job parameter if you may wish to change its value on a per-job basis. "Override" in your terminology is correct.

[perfman]

Thanks Ray,

You would probably be just as good at mind reading as you would a grammar teacher.

To be honest though, I don't care two hoots about that! After all this is a DataStage forum, not ProperEnglish forum

Thanks Ray and Chulett for your help, it is very much appreciated. I will give it a go and report back.

[perfman]

Just one question before I do that.

Do I just add it in as I would for any "user defined" environment variable?

[chulett]

Yes.

[ag_ram]

OK, try setting the Null Field Value property to "".

This will NOT work as was. the reason is as given below.

Here, the datatype of nullable field is "TimeStamp" which has indeed fixed width length of 19 characters by default. If this type of field does have null values, Null representation in the Sequential file should be done at length of 19.

Code: Select all

E.g: "16/11/2005 13:58:38","                   ",1,"This one does have a null date in field 2"

Field 2 has null representation as 19 blank space. This could be read from Sequential File Stage with setting property

Code: Select all

Null Field Value="                   ";

Instead of this, if Null field value is empty string, a message will come as

At field "<filedName>" "null_field" length (0) must match field's fixed width.