Nullable warning

somu_june · Post by **somu_june** » Tue Apr 11, 2006 9:16 pm

Hi,

I have a job with XML stage as input to the job and it contains XML input stage . I have two columms which contains both null and non null values for the two columms . I gave in Output page for those two columms nullable property to Yes and I am loading these two columm data to a sequential file through transformer and I gave nullable property in sequential file for those two columms to Yes . Iam facing this warning how to over come this . I gave nullable yes for both input and output then why Iam getting this error and plz tell me how to solve this warning.

Warning

Sequential_Reject: When checking operator: When validating export schema: At field "PaymentTerms": Exporting nullable field without null handling properties

Thanks
Somaraju

richdhan · Post by **richdhan** » Wed Apr 12, 2006 3:10 am

Hi Somu,

What is the data type of the 2 columns. If it is varchar then you have to specify the null field value as ' '.

HTH
--Rich

kumar_s · Post by **kumar_s** » Wed Apr 12, 2006 7:51 am

Since the column is specified as Nullable, Datastage expectes null for the column should be handled prior to load. Either you can use transformer/modify stage to handle explicitly or as mention Nullable field value to specify implict null handling.

somu_june · Post by **somu_june** » Wed Apr 12, 2006 9:55 am

Hi Kumar,

The columm is decimal (7,4) . When Iam using a modify stage . Iam getting this error . I tried with string also I got the same error

PaymentTerms:Decimal = NullToValue (PaymentTerms,"Null")
ContractType:Decimal = NullToValue (ContractType,"Null")

main_program: Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string= NullToValue (ContractType,"Null")
;
Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string = NullToValue (ContractType,"Null")

Thanks,
Somaraju

somu_june · Post by **somu_june** » Sun Apr 16, 2006 8:46 pm

Hi,

I want to load a null value in to sequential file . Some rows contains null value and columm length for that which contains null value is Decimal(7,4). I am getting warning and fatal errors as I mentioned above . Plz help me how to load null values in to a sequential file with out warnings and fatal errors.

Thanks,
Somaraju.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 17, 2006 12:27 am

There is an error in the Parallel Job Developer's Guide. The NullToValue() function pertains only to the Transformer stage. What you need in a Modify stage is the handle_null() function. You can learn this from the Orchestrate Operators manual - the chapter on the Modify operator.

Ultramundane · Post by **Ultramundane** » Mon Apr 17, 2006 7:48 am

I assume you do not need a fixed length file.

I thought the original requirement was to load a null value into the file? If so, changing the value to something which is not null is not loading a null value into the file. A null is the absence of a value. Thus, for a sequential file it would be the field delimeter followed by another field delimeter.

I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""

"" (Empty string).

This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)

2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field

However, the data loaded into the file correctly and the file could be read correctly without any problems.

somu_june · Post by **somu_june** » Mon Apr 17, 2006 3:27 pm

Hi Ultramundane,

If I do it like this -> format -> Field Defaults -> Null Field Value="". When I load from Sequential file to a DB2 table, will this load a null value in the table . I think no because it is not a null value since '' '' is not a null value . Can I use NULLTOEmpty null handling function to load a null value .

Thanks,
Somaraju.

kool78 · Post by **kool78** » Tue Apr 18, 2006 12:01 pm

Try to do Edit metadata for both of the nullable columns. When you do a edit metadata you will have a new property window which has options to provide a default value for the nullable column.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 18, 2006 2:46 pm

To get to that, right-click on a row in the Columns grid and choose Edit Row from the menu. Note that you can move to the next or previous column without leaving the Edit Metadata dialog. Note also that you must Apply or abandon any change before moving on: the dialog reminds you to do so.

somu_june · Post by **somu_june** » Mon Apr 24, 2006 8:06 pm

Hi ,Ultramundane,

I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""

"" (Empty string).

This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)

2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field

I tried by giving padchar = null and space in general in Format tab of sequential file but Iam getting the same warning please can any one tell me how to get rid of this warning .

Thanks,
Somaraju

ray.wurlod · Post by **ray.wurlod** » Tue Apr 25, 2006 1:04 am

Try specifying any nine-character string as the null value (in the Sequential File stage), for example "*********".

Ultramundane · Post by **Ultramundane** » Tue Apr 25, 2006 2:04 pm

I am making an assumption that the datatype you are using is a char?

Most DBMS's don't care if the character datatype is varchar or char. In fact, for many, a char null is actually defined internally as a varchar null. Which I think would be technically correct. A char null is not fixed width. It either contains no characters or some characeters equal to its length and padded if not that long.

Anyways, I have to use varchar null instead of a char null when using this method. It seems that datastage cannot work with char null when using sequential files. Using a varchar has not caused me any issues to date because of the underlying DBMS's ability to work with chars and varchars interchangeably. It would be nice if datastage could function as elegantly.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 25, 2006 3:41 pm

DataStage can handle a NULL in a Char(N) column, but the value that is used to represent NULL must have exactly N characters. Such is the definition of Char(N), surely?

Ultramundane · Post by **Ultramundane** » Tue Apr 25, 2006 3:57 pm

ray.wurlod wrote:DataStage can handle a NULL in a Char(N) column, but the value that is used to represent NULL must have exactly N characters. Such is the definition of Char(N), surely? ...

No. The definition of null is lack of any value.