Had a little free time before the end of the day. Try this.
You weren't specific on what execution data you were looking for, so I used run count, minimum elapsed, maximum elapsed, and average elapsed. The elapsed numbers come out in days, typically a rather small number. Formatting in the query is messy, much easier to import into Excel and format as hh:mm:ss.
select unique level, a.* from
(SELECT jtc.so_module chain, jt.so_module job, jt.so_command_type, cd.so_task_name, jp.so_prompt, jp.so_prompt_descr, cd.so_predecessors,
jp.so_prompt_dflt, so_det_arg, oc.so_soc_order, oc.so_cond_timing, oc.so_condition_1, oc.so_qualifier, oc.so_condition_2,
oc.so_action, oc.so_act_arg, count(jh.so_module) run_count, min(jh.so_job_finished - jh.so_job_started) min_elapsed ,
max(jh.so_job_finished - jh.so_job_started) max_elapsed, avg(jh.so_job_finished - jh.so_job_started) avg_elapsed
FROM appworx.so_job_table jt
INNER JOIN appworx.so_chain_detail cd
ON cd.so_job_seq = jt.so_job_seq
INNER JOIN appworx.so_job_table jtc
ON jtc.so_job_seq = cd.so_chain_seq
LEFT OUTER JOIN appworx.so_job_prompts jp
ON (jp.so_job_seq = jt.so_job_seq)
LEFT OUTER JOIN appworx.so_object_cond oc
ON (oc.so_object_seq = cd.so_det_seq
and cd.so_chain_seq = jtc.so_job_seq)
LEFT OUTER JOIN appworx.so_chain_args ca
ON (ca.so_det_seq = cd.so_det_seq
AND jp.so_prompt = ca.so_det_arg_no)
LEFT OUTER JOIN APPWORX.SO_JOB_HISTORY jh
ON (jh.so_chain_seq = cd.so_chain_seq
AND jh.so_job_seq = jt.so_job_seq)
WHERE cd.so_act_flag = 'Y'
AND jh.so_status = 32
group by jtc.so_module, jt.so_module, jt.so_command_type, cd.so_task_name, jp.so_prompt, jp.so_prompt_descr, cd.so_predecessors,
jp.so_prompt_dflt, so_det_arg, oc.so_soc_order, oc.so_cond_timing, oc.so_condition_1, oc.so_qualifier, oc.so_condition_2,
oc.so_action, oc.so_act_arg) a
start with a.chain = 'Your-Chain-Name'
connect by CHAIN = prior JOB
order siblings by job, so_prompt;
Original Message:
Sent: 06-11-2020 02:46 PM
From: Shashank Singh
Subject: SQL Query for getting job details to the lowest granular level
Thanks for the help Pete.
@Pete Gross Can you help me in querying the output of this query to history table (so_job_history)?
The project team requires the execution data for performance testing. I have tried multiple variations but the data doesn't seem to be correct.
Original Message:
Sent: 06-03-2020 06:51 PM
From: Pete Gross
Subject: SQL Query for getting job details to the lowest granular level
See how this works for you. It looks correct in my environment. I added so_command_type to your query to identiy what was a chain and what was not. I also added the pseudocolumn 'level' to tell what recurse level the row is at. You'll want to adjust the sorting clause to suit your needs - I didn't try to use your original sorting.
select unique level, a.* from
(SELECT jtc.so_module chain, jt.so_module job, jt.so_command_type, cd.so_task_name, jp.so_prompt, jp.so_prompt_descr, cd.so_predecessors,
jp.so_prompt_dflt, so_det_arg, oc.so_soc_order, oc.so_cond_timing, oc.so_condition_1, oc.so_qualifier, oc.so_condition_2,
oc.so_action, oc.so_act_arg
FROM appworx.so_job_table jt
INNER JOIN appworx.so_chain_detail cd
ON cd.so_job_seq = jt.so_job_seq
INNER JOIN appworx.so_job_table jtc
ON jtc.so_job_seq = cd.so_chain_seq
LEFT OUTER JOIN appworx.so_job_prompts jp
ON (jp.so_job_seq = jt.so_job_seq)
LEFT OUTER JOIN appworx.so_object_cond oc
ON (oc.so_object_seq = cd.so_det_seq
and cd.so_chain_seq = jtc.so_job_seq)
LEFT OUTER JOIN appworx.so_chain_args ca
ON (ca.so_det_seq = cd.so_det_seq
AND jp.so_prompt = ca.so_det_arg_no)
WHERE cd.so_act_flag = 'Y') a
start with a.chain = 'Your-Chain-Name'
connect by CHAIN = prior JOB
order siblings by job, so_prompt;
Regards,
Pete
Original Message:
Sent: 06-01-2020 08:27 AM
From: Shashank Singh
Subject: SQL Query for getting job details to the lowest granular level
Hi All,
Can someone review the query and help me build a recursive query out of it. Need this urgently for an ongoing project.
Regards,
Shashank Singh
Original Message:
Sent: 05-28-2020 03:31 PM
From: Shashank Singh
Subject: SQL Query for getting job details to the lowest granular level
I was able to come up with the below query but it gives detail only to a single level. I want it to be recursive. Can someone take a look at this and help me convert it into recursive.
SELECT jtc.so_module chain, jt.so_module job, cd.so_task_name, jp.so_prompt, jp.so_prompt_descr, cd.so_predecessors,
jp.so_prompt_dflt, so_det_arg, oc.so_soc_order, oc.so_cond_timing, oc.so_condition_1, oc.so_qualifier, oc.so_condition_2,
oc.so_action, oc.so_act_arg
FROM so_job_table jt
INNER JOIN so_chain_detail cd
ON cd.so_job_seq = jt.so_job_seq
INNER JOIN so_job_table jtc
ON jtc.so_job_seq = cd.so_chain_seq
LEFT OUTER JOIN so_job_prompts jp
ON (jp.so_job_seq = jt.so_job_seq)
LEFT OUTER JOIN so_object_cond oc
ON (oc.so_object_seq = cd.so_det_seq
and cd.so_chain_seq = jtc.so_job_seq)
LEFT OUTER JOIN so_chain_args ca
ON (ca.so_det_seq = cd.so_det_seq
AND jp.so_prompt = ca.so_det_arg_no)
WHERE jtc.so_module = 'Your-Chain-Name'
AND cd.so_act_flag = 'Y'
ORDER BY jtc.so_module, jt.so_module, cd.so_chain_order, jp.so_prompt, oc.so_soc_order
Thanks,
Shashank
Original Message:
Sent: 05-28-2020 04:40 AM
From: Shashank Singh
Subject: SQL Query for getting job details to the lowest granular level
Hi All,
We have a requirement from project team to get all the details of a chain to the lowest granular level. Say if we pass the chain name in the query it should provide details of all the modules in it along with prompt, conditions and predecessors. Also if the chain has multiple sub-chains in it, we should be getting details of all modules for the sub-chains as well. Do we have a recursive query for getting such details?
The project team also needs to know the historic runs of the chain at the same granular level. The current history query via GUI provides the first level details only and not the granular level details.
Please let me know if anyone has such custom queries.
Regards,
Shashank Singh