My DCC ETL job is failing when trying to load the request management history. I've seen similar errors online, but none quite like mine. You see the following exception and stack trace in the log:
java.util.concurrent.ExecutionException: com.ibm.rational.datacollection.etl.fetcher.FetcherException: CRRCD4504E The ; WITH T AS
(
SELECT 4112 AS DATE_ID, H."REQUEST_ID", H."REQUEST_STATE_ID", H."DAYS_IN_STATE",
R."COMPONENT_ID", R."OWNER_ID", R."CREATOR_ID", R."REQUEST_SEVERITY_ID", R."REQUEST_PRIORITY_ID",
R."REQUEST_TYPE_ID", R."PROJECT_ID", R."REQUEST_CLASS_ID"
FROM
(
SELECT DISTINCT H."REQUEST_ID", H."PREV_REQUEST_STATE_ID" AS REQUEST_STATE_ID, H."DAYS_IN_STATE"
FROM "RIODS"."REQUEST_STATE_HISTORY" H
WHERE H."ISSOFTDELETED"=0
UNION
SELECT R."REQUEST_ID", R."REQUEST_STATE_ID",
{fn timestampdiff(SQL_TSI_DAY, CAST(MIN(R."CHANGE_DATE") AS DATE), CAST('2016-04-04 01:46:15.347' AS DATE))} AS DAYS_IN_STATE
FROM
(
SELECT R."REQUEST_ID", R."REQUEST_STATE_ID", RH1.CHANGE_DATE
FROM "RIODS"."REQUEST" R
JOIN "RIODS"."REQUEST_STATE_HISTORY" RH1 ON R."REQUEST_ID"=RH1."REQUEST_ID" AND R."REQUEST_STATE_ID"=RH1."REQUEST_STATE_ID"
LEFT JOIN "RIODS"."REQUEST_STATE_HISTORY" RH2 ON RH1.REQUEST_STATE_ID=RH2.PREV_REQUEST_STATE_ID AND DATEDIFF(DAY, RH2.CHANGE_DATE, RH1.CHANGE_DATE)<=0
WHERE RH2.REQUEST_STATE_HISTORY_ID IS NULL AND R."ISSOFTDELETED"=0
)R
GROUP BY R."REQUEST_ID", R."REQUEST_STATE_ID"
) H
JOIN "RIODS"."REQUEST" R ON R."REQUEST_ID"=H."REQUEST_ID"
)
SELECT DISTINCT RM."AGING_METRIC_ID",R."REQUEST_ID"
FROM "RIDW"."F_REQUEST_TPLN_AGING" RM
JOIN (
SELECT 4112 AS DATE_ID, T."REQUEST_ID", T."REQUEST_STATE_ID", T."DAYS_IN_STATE",
T."COMPONENT_ID", T."OWNER_ID", T."CREATOR_ID", T."REQUEST_SEVERITY_ID", T."REQUEST_PRIORITY_ID",
T."REQUEST_TYPE_ID", T."PROJECT_ID", T."REQUEST_CLASS_ID",
CASE WHEN ER."ITERATION_ID">-1 THEN ER."ITERATION_ID" ELSE COALESCE(I."ITERATION_ID",-1) END AS "ITERATION_ID",
CASE WHEN RTL."TESTPLAN_ID">-1 THEN RTL."TESTPLAN_ID"
ELSE COALESCE(EWI."TESTPLAN_ID",-1) END AS "TESTPLAN_ID"
FROM T
LEFT JOIN "RIODS"."EXECRES_REQUEST_LOOKUP" L ON L."REQUEST_ID"=T."REQUEST_ID"
LEFT JOIN "RIODS"."EXECUTION_RESULT" ER ON ER."EXECUTION_RESULT_ID"=L."EXECUTION_RESULT_ID"
LEFT JOIN "RIODS"."EXECRES_EXECWKITEM_LOOKUP" EWIL ON EWIL."EXECUTION_RESULT_ID"=ER."EXECUTION_RESULT_ID"
LEFT JOIN "RIODS"."EXECWORKITEM_ITERATION_LOOKUP" I ON I."EXECWORKITEM_ID"=EWIL."EXECWORKITEM_ID"
LEFT JOIN "RIODS"."EXECUTION_WORKITEM" EWI ON EWI."EXECWORKITEM_ID"=EWIL."EXECWORKITEM_ID"
LEFT JOIN "RIODS"."REQUEST_TESTPLAN_LOOKUP" RTL ON RTL.REQUEST_ID=T.REQUEST_ID
)
R ON
RM."REQUEST_CLASS_ID"=R."REQUEST_CLASS_ID" AND
RM."PROJECT_ID"=R."PROJECT_ID" AND
RM."COMPONENT_ID"=R."COMPONENT_ID" AND
RM."RESOURCE_ID"=R."OWNER_ID" AND
RM."CREATOR_ID"=R."CREATOR_ID" AND
RM."ITERATION_ID"=R."ITERATION_ID" AND
RM."TESTPLAN_ID"=R."TESTPLAN_ID" AND
RM."DATE_ID"=R."DATE_ID" AND
RM."STATE_ID"=R."REQUEST_STATE_ID" AND
RM."PRIORITY_ID"=R."REQUEST_PRIORITY_ID" AND
RM."SEVERITY_ID"=R."REQUEST_SEVERITY_ID" AND
RM."REQUEST_TYPE_ID"=R."REQUEST_TYPE_ID"
WHERE RM."DATE_ID"=4112 prepared statement could not be executed.
at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:233)
at java.util.concurrent.FutureTask.get(FutureTask.java:94)
at com.ibm.rational.datacollection.etl.tasks.ETLSubTask.call(ETLSubTask.java:164)
at com.ibm.rational.datacollection.etl.tasks.ETLSubTask.call(ETLSubTask.java:1)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
at java.util.concurrent.FutureTask.run(FutureTask.java:149)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:908)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:931)
at java.lang.Thread.run(Thread.java:773)
This repeats itself a few times, before the log says the job has failed.
Has anyone encountered this, or can you point me in the right direction?
