NullToValue and other Null handling functions result error

[just4geeks]

I am selecting fields off of a Netezza Table, of which a few have null values. I use the Neteeza Database stage to read the table. I tried to handle the nulls in a subsequent transformer using [ If isNull(column) then -- else -- ] and NullToValue but the job would abort with the following error.

Code: Select all

Null in field "sample"; the result is non-nullable and there is no handle_null to specify a default value.

However, when I modified the select query to include nvl functions, the job ran successfully. The jobs is [Netezza Stage] --> Transformer --> Peek.

Any ideas on how can I handle the nulls without specifying a user-generated query? I would like to use the system generated query. Further, since I am not sure what columns may or may not have nulls, I have specified the nullability as unknown in column properties.

[kwwilliams]

Is the transformer stage the one throwing the error? If not, Does the metadata on the Neteeza stage have the field set as nullable?

[just4geeks]

Is the transformer stage the one throwing the error? If not, Does the metadata on the Neteeza stage have the field set as nullable?

The Netezza stage first throws up warnings, saying

Code: Select all

"When checking operator: When validating import schema: At field "sample": Importing potential null to not nullable field"

I have ensured that the nullability is either set to Yes or Unknown.

These are followed by warnings from Transformer saying,

Code: Select all

"When checking operator: When binding input interface field "sample" to field "sample": Converting a nullable source to a non-nullable result;a fatal runtime error could occur;use a modify operator to specify the value to which the null should be converted."

Then the fatal message reads

Code: Select all

APT_CombinedOperatorController,0: Null in field "sample"; the result is non-nullable and there is no handle_null to specify a default value. 

[kwwilliams]

Its the neteeza stage that is calling you grief, I don't have neteeza but you should be able to set a null handle in the field properties of the stage. DataStage is getting the metadata from the database and according to it the data should not be null. I must admit I am curious why you would get this error at all if a null is stored in the database. I bet if you put a trim around the field in sql it would work as well, because the function does not allow datastage to understand the nullability so it trusts what you have input on the metadata.

[just4geeks]

......you should be able to set a null handle in the field properties of the stage.

Following is the property window for a column in Netezza Stage. I dont see any place where I can specify the null - replacement value.



Do you have any idea on where can find that setting?

[kwwilliams]

I believe I misled you, you aren't going to be able to handle null one fo the properties. Had to get in front of a datastage client to see it.

Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.

[just4geeks]

...Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.

There do exist NULL in the fields. I can see them when I read the table in a SQL client.

[kwwilliams]

...Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.

There do exist NULL in the fields. I can see them when I read the table in a SQL client.

Can you look at the DDL? This is where DataStage is getting its metadata on the table.

[just4geeks]

Can you look at the DDL? This is where DataStage is getting its metadata on the table.

Well, I do know that the table contains NULLs since I can see them when I read the table in SQL client. And I have clearly specified null handling in the transformer. Do you mean to say that the DDL doesn't mention anything about NULLs which leads DataStage to falsely believe that there are no NULLs and so, it throws up an error when it encounters NULLs?

[kwwilliams]

When DataStage makes the connection it sends the sql statement to the database and run time metadata is returned, which is why you get the warning in the Neteeza said:

"When checking operator: When validating import schema: At field "sample": Importing potential null to not nullable field"

It is validating that the run time and design time metadata match and its complaining that they don't. You said that the design metadata is set to Null or Unknown.

When you used the NVL function in sql it can't obtain information from the database catalog about this field because it is a function call that is being returned so it assumes null and didn't complain any longer. To be honest when I read this warning from the Neteeza stage I think it is saying that your design time metadata specifies not null, but your run time metadata specifies null.

If we could see the dump score for your job and the DDL for the table (at least the offending column, we should be able to quickly track down your problem.

[just4geeks]

....If we could see the dump score for your job....

I have enabled APT_DUMP_SCORE and run the job. Do you know how does one see the score in the job log?

[Sreenivasulu]

Any field which is 'Nullable' needs to be checked for 'IsNull' then only you can do the derivations in the transformer. The logic being if the column is 'nullable' and if you try to do some derivation like 'substring' then basically you are trying do a 'substring' on 'null' values which will throw a null pointer exception.
Hope i am clear.

Regards
Sreeni

[kwwilliams]

You'll see it as a line item in the log.

[just4geeks]

Any field which is 'Nullable' needs to be checked for 'IsNull' then only you can do the derivations in the transformer.....

I do have the [ If isNull(column) then -- else -- ] in my transformer as I mentioned in my first post. Yet the job fails. The job only works if I specify nvl in the select query in the Netezza stage.

[just4geeks]

You'll see it as a line item in the log.

Thanks Keith. Here is the score dump.

Code: Select all

main_program: This step has 2 datasets:
ds0: {op0[1p] (sequential APT_DBImportOperator in NZ_extract_univ_161plus)
      ->eCollectAny
      op1[1p] (sequential APT_NZReadSubOperator in NZ_extract_univ_161plus)}
ds1: {op0[1p] (sequential APT_DBImportOperator in NZ_extract_univ_161plus)
      eAny<>eCollectAny
      op2[8p] (parallel APT_CombinedOperatorController:APT_TransformOperatorImplV0S15_od_universe_extract_split_v2_Extract2_null_handling in Extract2_null_handling)}
It has 3 operators:
op0[1p] {(sequential APT_DBImportOperator in NZ_extract_univ_161plus)
    on nodes (
      node1a[op0,p0]
    )}
op1[1p] {(sequential APT_NZReadSubOperator in NZ_extract_univ_161plus)
    on nodes (
      node1a[op1,p0]
    )}
op2[8p] {(parallel APT_CombinedOperatorController:
      (APT_TransformOperatorImplV0S15_od_universe_extract_split_v2_Extract2_null_handling in Extract2_null_handling)
      (Peek_20)
    ) on nodes (
      node1a[op2,p0]
      node2a[op2,p1]
      node3a[op2,p2]
      node4a[op2,p3]
      node1b[op2,p4]
      node2b[op2,p5]
      node3b[op2,p6]
      node4b[op2,p7]
    )}
It runs 10 processes on 8 nodes.