aggregator stage hangs and job aborts

[knowledge]

I have a job which uses aggregator stage , It runs fine for small data but when it reaches 3.9 mil it aborts , without specific warning just 'Abnormal Termination'

I am not sure what is going wrong .....

I tried same logic in sql query which runs fine in toad but giving problem in OCI stage :


SELECT M.ARD_CNTY_CODE, M.ARD_MNCPLTY_CODE, M.ARD_ACDNT_YR_NUM,
M.ARD_ACDNT_CASE_NUM, M.ARD_VHCL_ID, M.ARD_VIN_ID,
MAX(M.TEST_DATE)
FROM (SELECT B.VHCL_IDNTFCTN_NUM,
TO_CHAR (B.TEST_START_DATE, 'YYYY-MM-DD HH24:MI:SS') TEST_DATE,
A.ARD_CNTY_CODE, A.ARD_MNCPLTY_CODE, A.ARD_ACDNT_YR_NUM,
A.ARD_ACDNT_CASE_NUM, A.ARD_VHCL_ID, A.ARD_VIN_ID,
TO_CHAR (A.ARD_ACC_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM CRASH_PROD.ARD_VEHICLE_INSPECTION A,
CRASH_PROD.CRASH_INSPECTION B
WHERE TRIM (A.ARD_VIN_ID) = TRIM (B.VHCL_IDNTFCTN_NUM)) M
GROUP BY M.ARD_CNTY_CODE,
M.ARD_MNCPLTY_CODE,
M.ARD_ACDNT_YR_NUM,
M.ARD_ACDNT_CASE_NUM,
M.ARD_VHCL_ID,
M.ARD_VIN_ID


........................

Please suggest either one , why aggregator stage is not working or how to retrieve data from OCI stage with the above query, i have included all columns in the inner query , do we have to give column derivation too , it does not return any data but it works very fine in toad....

[ray.wurlod]

Moderator: please move to Server forum

Server Aggregator stage does have limitations with the amount of unsorted data it can handle. Search for more information.

[keshav0307]

did you tried writing the query in oracle stage as user defined query.
what error you get?

[chulett]

What you'll find when you search is the need to sort your data properly and then assert that sorted order in the Aggregator. Then you'll find it can support pretty much any volume of data.