Aggregator output nullability

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Aggregator output nullability

Post by chulett »

Playing some aggregator games today and had some interesting issues, probably old news to many here but new to me (not having done much PX aggregation yet) and thought I would share. Needed to group by one field, sum three others and generate a count for each group.

Is the output from calculation fields always assumed to be nullable regardless of the setting in the stage? In spite of the fact that all fields had Nullable set to No all the way through the job, was still seeing messages like this:

Code: Select all

Aggregator_1: When checking operator: When binding output interface field "X" to field "X": 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.
OK... my source isn't nullable but I'll play the Modify game. Added one between the Aggregator and the Sequential File target of its handiwork and did the whole handle_null thing. It then complained about that:

Code: Select all

Modify_14: When checking operator: When binding output schema variable "outRec": When binding output interface field "X" to field "X": Null value handling is specified with a non-nullable source field and no conversion; ignoring.
Pfffttt... now suddenly my source is non-nullable so you don't need to handle nulls, you idiot. I had to switch the output columns of my Aggregator to actually show as 'Nullable' before this warning would go away.

Is this normal? Is calculation output always considered to be nullable even when I know it will never be and I mark it as such in the job? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Have you set any of the null-handling properties of the Aggregator stage itself?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Such as? All I've done is set the Nullable attribute on the Columns tab to 'No' at first for all fields (later changed to 'Yes' as noted) and I see the default stage property of 'Allow Null Output' is set to 'False'. I don't see what else I can do there.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

If you set Allow Null Output (I couldn't recall the property name) then it might eliminate the warning. No idea why, but it might be worth a try.

There's always message handlers!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dashpriya
Participant
Posts: 28
Joined: Thu Mar 09, 2006 12:57 am
Location: mumbai
Contact:

Re: Aggregator output nullability

Post by dashpriya »

We also faced same issue, but we are also not able to remove those warnings.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

As posted, I've already handled all of the warnings and the job runs completely clean right now. I just wanted to get a better idea of what was going on and why I needed to jump though hoops to make it happy.

I'll try changing Allow Null Output to True and see how that affects what I have to do downstream.
-craig

"You can never have too many knives" -- Logan Nine Fingers
krishna.m
Premium Member
Premium Member
Posts: 4
Joined: Tue Apr 07, 2009 11:46 pm

Aggregator output nullability

Post by krishna.m »

Hi,

I am also facing the same problem.I had tried using the "Allow Null Output = True" in the aggregator stage.This is not helping to eliminate the warnings. Could anyone suggest how to eliminate the warnings without hopping....(I mean changing the columns to "Null" and again handling them in Modify stage).

Thanks in advance..
Krishna.
PhilHibbs
Premium Member
Premium Member
Posts: 1044
Joined: Wed Sep 29, 2004 3:30 am
Location: Nottingham, UK
Contact:

Post by PhilHibbs »

Also getting this. I suspect it is because an aggregation function can return a NULL even if the input column is not nullable. What happens if you specify a "Missing Value" (e.g. 0) and all the values in an aggregation set are that value? Will you get a NULL output from a non-nullable input? I suspect so, therefore aggregation function results should always be considered nullable, hence this warning.
Phil Hibbs | Capgemini
Technical Consultant
Post Reply