Column Import - Using VARCHAR fields

[shankar_ramanath]

Hello All,

(Disclaimer: I have searched the forums but could not find a similar issue.)

I am trying to import a record using the Column Import stage. The record has an empty value for a VARCHAR field for a given column. The column is defined as VARCHAR with the NULLABLE property set to NO. When importing the data, I am expecting the column import stage to reject the record because the value is empty, but the record is not getting rejected.

I tried to change the column properties by editing the column meta-data but I could find anything relevant. I also tried to change the column data type to CHAR but it did not help. The length of the column is set to "unbounded", but I tried to change it to a specific length as well.

I could not find any environment variable that would allow treating empty fields as NULLs.

Please advise.

Thanks,

[ray.wurlod]

Take a look at the Null Field Value property (there may not be one) and the Default Value property (which I'm guessing is set to "").

[shankar_ramanath]

Take a look at the Null Field Value property (there may not be one) and the Default Value property (which I'm guessing is set to "").

Thanks Ray. There is no Null Field Value property in "Edit Column Metadata" dialog box. I set the value in the "Format--Field Defaults". I tried "",'',\0. None of these helped. The description of "Null Field Value" states the following: "On import, the value given to a field containing a null." I am not sure if it implies that the input value provided is set only if the incoming value in the file is NULL. In this case, the input value is an empty string, which I would like to treat as NULL so that the record can be rejected.

There is "Default value" property available in "Edit Column Metadata" dialog box. I tried setting it to \0, '' and "" but of no avail.

Essentially, the requirement is to treat empty strings as NULL values so that the Column Import stage can recognize the record as a faulty one. I tried looking into the environment variables and could find none. Is there an unpublished enviorment variable that satisfies this requirement?

Thanks again,

[shankar_ramanath]

I found that the "Edit Column Metadata" dialog box contains the option to set "Null Field Value" when the column is defined as NULLABLE. I set the NULLable property to Yes and set a value of '' for the column in question. In the subsequent stage, I set the NULLable property to No.

Now I see that the record is dropped when it passes from the Column Import stage to the subsequent stage. The problem is that the record drops silently. I am unable to reject the record to an output file. While the Column Import stage has a reject link, I am unable to use the link because the NULLable property for the column is (and needs to be) set to Yes.

One of the IBM Redbooks had some good information about handling nulls. Below is an excerpt that I used for this book.

Source Field: Nullable
Destination Field: not Nullable
Result: If the source value is not null, the source value propagates.

So, I am still stuck with my original problem of not being able to reject a record when one of the VARCHAR column contains an empty string.

Any input is appreciated.

Thanks,

[ray.wurlod]

You could always intersperse a filtering stage (Filter or Transformer) to capture these rows before they are re-parsed by the Column Import stage.