It's all about the answers!

Ask a question

DCC Request Management History job fails


Ryan Boyce (39) | asked Apr 04 '16, 5:25 p.m.

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?

One answer



permanent link
Francesco Chiossi (5.7k1719) | answered Apr 05 '16, 3:26 a.m.
Hello Ryan,

you can look for the error code returned by the database in the log (if there is one), or trying to execute the sql query directly against the data warehouse and see what happens.

Note: The history jobs are disabled by default as they are very expensive to compute and the data they populate is not used by any out of the box reports. Unless you know you have a specific need, you might want to consider disabling them.

Best Regards,

Francesco Chiossi

Comments
Ryan Boyce commented Apr 05 '16, 1:15 p.m.

Thanks Francesco. We cannot disable this job, but I'll look into your suggestions.

Ryan

Your answer


Register or to post your answer.