Aggregator Error %s and |-100|

ch_on14 · Post by **ch_on14** » Mon Feb 26, 2007 3:27 pm

I am reading millions of rows from a source table and after about 12 to 17 million rows my job aborts at the Aggregator Stage. The errors in the log are:

JobName.Agg_StageName: %s (Warning)
JobName.Agg_StageName: |-100| (Abort)

Has anyone else encountered these errors. I have put an nvl function on the dollar amounts that have to be summed in the source SQL. So the Aggregator shoud not be receiving Null values.

chulett · Post by **chulett** » Mon Feb 26, 2007 4:03 pm

It's probably just volume. Have you sorted your incoming data in a manner which supports the aggregation? Have you 'asserted' your Sort Order in the Aggregator stage?

If not, then that is more than likely your problem. If you have, then something else is going on.

ch_on14 · Post by **ch_on14** » Mon Feb 26, 2007 4:13 pm

chulett wrote:It's probably just volume. Have you sorted your incoming data in a manner which supports the aggregation? Have you 'asserted' your Sort Order in the Aggregator stage?

If not, then that is more than likely your problem. If you have, then something else is going on.

Thanks for the reply.... I have not put in the sort for Input data.... I was suspecting the same. Will be trying it with the Sort... although is it faster to put the sort order it the Aggregator or have a Sort Stage? I found by adding a Sort Stage it slows down my number of rows read from source and in turn reduces performance. I have the ArraySize on source to 15000 and have an interprocessor along with Link Partitioner and Collector before the Aggregator. The InterProcessor has a buffer size of 8192. Any feedback would be appreciated.

Problem is testing with different scenarios since the source is already aggregating almost 2billion rows and takes 1-2 hrs just to start feeding the job data. Then I am performing some lookups and replacing the IDs from source with Numbers... thats why I have an aggregator in the job.

chulett · Post by **chulett** » Mon Feb 26, 2007 4:20 pm

There's no sorting in the Aggregator. You have to do it outside the Aggregator and then tell the stage that the incoming data is sorted. Lie about the sort order to it and it will blow up. Sort the data 'incorrectly' and you'll basically waste your time.

A Sort stage will be the slowest way to accomplish this. Do you have any kind of an external 'high speed sort' utility you can leverage? Sometimes it's faster to bulk load the intermediate results back into your database of choice and reselect things out with a new 'order by'.