DSXchange

I am getting the following warnings. In the job I am trying to extract data from DB2 stage to flat file. The field "AMT" is non nullable in source table as well as in target flat file

1)When checking operator: When binding output interface field "AMT" to field "AMT": Implicit conversion from source type "decimal[31,2]" to result type "decimal[9,2]": Possible range limitation.

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

If you check carefully in your source definition for the column "AMT" you will find that it is declared as nullable, hence that warning message. Whether the actual column is declared as nullable or not in the database is a different matter, but DataStage is reporting the potential problem based on the metadata it has been given.

The numerical format warning is similar, you are converting the size and precision of the variable implicitly and therefore are getting the warning.

What is your actual question?

I checked the metadata. The column is non nullable in it.

Where did you check the metadata? Using the DB's describe or the DS Manager isn't going to help. You need to start at the source stage in the columns tab and for that column right-mouse-click-edit-row to see what is set. Then do that for each stage to make sure you haven't changed those attributes inadvertantly on the way.

I have done as described by you, the field is non nullable

If you click "nullable" on some stage in your job then it might cause the warning message you are seeing. PX thinks it is nullable somewhere. Also, contrary to my previous mail, could you check your DDL in the database to see if the AMT column is declared as non-nullable there as well?

Thanks for all your replies. The last one has premium content, I am unable to read it I am not a premium member One other piece of information - Actually I am carrying forward SUM(AMT) to the flat file. AMT is decimal(9,2).

Check each and every stage in your job to see if the "nullable - yes" has not mistakenly been clicked on.

I have checked, rechecked and cross checked. Still the warning is intact

Is AMT nullable in the source DB2 table?

AMT is non nullable in DB2 source table. I did a describe for it

Would it be possible to write a new job that just reads the source and writes to a sequential file with non-nullable set on that column? does the error go away?

Ya. I tried that. Created a new job as told by you. When I tried giving SUM(AMT)..Same warnings...When I removed the SUM and gave just AMT, the warnings disappeared.. But in the original job I need the SUM..

I have faced similar problem- but when using the Aggregator Stage to do a summing. For the new aggregated column (which contains the aggregated value), the same warning was thrown, eventhough it was defined as Nullable NO.When I changed this to nullable YES, this warning disappeared.

I then included a modify stage to convert this field to a Nullable NO field.

Regards

The Bird

Where are you using "SUM(AMT)"? It sounds like a new column in your SQL query - and the metadata of that new column is most likely creating it as a nullable column. If you change your metadata for this column to be not nullable but add a null default handler in your row definition the error will go away. Or, better yet, create your explicit null handler in a modify stage or transform stage.