I am trying to create a simple report that shows the total execution time for a particular FOCEXEC using the tracking information in the Resource Analyzer tables. This particular FOCEXEC creates and reports from a number of HOLD files. I noticed that in the SMQUERY table there is a seperate entry for each part of the FOCEXEC for each HOLD file. This is great since it allows you to see measures for each part of the FOCEXEC. However, I do not see a common field that allows me to aggregate the values to get a total number for the FOCEXEC. Am I missing something? Is there a common field in each record that facilitates relating all the pieces to the initial FOCEXEC that was executed. There is a SMRPCNAME field that has the FOCEXEC name but there is no field that I can see that allows you to identify the parts of a particular execution of that FOCEXEC.

Does anyone have any suggestions?

Hi Mickey,

The total time stats for a given execution are found in the SMRPCS table. There will be multple entries for each execution, but the total will be on either line 0 or 1 depending on your configuration or whether it was executed from RC or WF. Hope this helps,

Kevin

Kevin,

Thanks for the info. I got the same info from Tech Support.

I can see the information in the SMRPCS file seems to use the SMWFRPCNAME field for the actual FOCEXEC name. Is there a way to link this record to the records in SMQUERY?

Mickey,

try this;

 
SELECT PC.SMRPCKEY,
PC.SMRPCNUM,
PC.SMDATE,
PC.SMTIME,
PC.SMRPCNAME,
PC.SMCPUTIME,
PC.SMELAPTIME,
RL.SMLITERAL
FROM RAG.SMRPCS PC
JOIN RAG.SMQUERY QY
ON PC.SMRPCKEY = QY.SMRPCKEY
JOIN RAG.SMRELATIONS RL
ON QY.SMKEY = RL.SMKEY