aggregator stage hangs and job aborts
Posted: Tue Apr 13, 2010 1:31 pm
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....
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....