SQL Query for getting job details to the lowest granular level

[Shashank Singh]

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

[Shashank Singh]

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

[Shashank Singh]

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

[Pete Gross]

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

[Shashank Singh]

Thanks for the help Pete. The query worked fine.

[Shashank Singh]

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.

[Pete Gross]

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;

[Shashank Singh]

@Pete Gross Thank you Pete. You are a savior.

I​ modified the query and removed the join with prompts & conditions table since it was taking more than a minute to execute and giving enormous amount of data along with data of chain for all the months present in history. I included the so_request_date column and then filtered the output in excel based on the date the chain was requested. Let me know if my approach is correct. I tried adding the so_request_date column in where clause but I felt it wasn't giving correct output. Can you review the below query and suggest improvements.

select unique level, a.* from
(SELECT jtc.so_module chain, jt.so_module job, jt.so_command_type, cd.so_task_name, jh.so_parent_name parent, jh.so_operator,
ROUND(24*60*(so_job_finished-so_job_started),0) run_minutes, TO_CHAR(so_job_started,'YYYY-MM-DD HH24:MI:SS') job_started,
TO_CHAR(so_job_finished,'YYYY-MM-DD HH24:MI:SS') job_finished, jh.so_request_date
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_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'
) a
start with a.chain = 'FDC_DAILY_LOAD'
connect by CHAIN = prior JOB
order siblings by job

Regards,
Shashank Singh

[Shashank Singh]

@Pete Gross I was informed by project team that the module / job alias name is not populating correctly in the query output.
In my environment there are a lot of aliases being used and prompt values being input in job name e.g. Job xxx_yyy_zzzz_{#1} will take the first prompt value and the name would be xxx_yyy_zzzz_abcd. 
I reviewed and made a slight change in the query, replacing cd.so_task_name with jh.so_module. 
The earlier query with cd.so_task_name was not resolving the prompt and giving xxx_yyy_zzzz_{#1} in output but with jh.so_module it is happening, name is appearing as xxx_yyy_zzzz_abcd and the number of rows of the overall query has reduced.
The number of rows reduced drastically from approx 5k to 2.7k. Manually checking the output is tough but looking at high level I feel the below query may be correct. With your experience, can you let me know if the below query is correct or I am making a mistake?

select unique level, a.* from
(SELECT jtc.so_module chain, jt.so_module job, jt.so_command_type, jh.so_module alias , jh.so_parent_name parent, jh.so_operator,
ROUND(24*60*(so_job_finished-so_job_started),0) run_minutes, TO_CHAR(so_job_started,'YYYY-MM-DD HH24:MI:SS') job_started,
TO_CHAR(so_job_finished,'YYYY-MM-DD HH24:MI:SS') job_finished, jh.so_request_date
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_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'
) a
start with a.chain = 'FDC_DAILY_LOAD'
connect by CHAIN = prior JOB
order siblings by job ​


Regards,
Shashank Singh

[Joe Simon]

Y

Sent from Outlook Mobile

[Shashank Singh]

Duplicates are also appearing in the output. Should we apply Distinct before Job name?

[Pete Gross]

Yes, distinct/unique should be used in the inner select.  Also, the so_job_history join should be changed from left outer join to inner join to eliminate rows with no job history.  Depending on how much job history you keep, you might also want to limit the amount of history you're looking at.  The query can run a very long time if starting with a large workflow.  See updated query below.

select unique level, a.* from
(SELECT unique jtc.so_module chain, jt.so_module job, jt.so_command_type, jh.so_module alias , jh.so_parent_name parent, jh.so_operator,
ROUND(24*60*(so_job_finished-so_job_started),0) run_minutes, TO_CHAR(so_job_started,'YYYY-MM-DD HH24:MI:SS') job_started,
TO_CHAR(so_job_finished,'YYYY-MM-DD HH24:MI:SS') job_finished, jh.so_request_date
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
INNER 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 so_job_started > sysdate - 60
) a
start with a.chain = 'Your-Chain-Name'
connect by CHAIN = prior JOB
order siblings by job;

[Shashank Singh]

@Pete Gross I tried DISTINCT and UNIQUE but they do not work. We still get duplicate rows. On forums in stackoverflow they mention to use CTE something in below format -

WITH cte AS /* Declaring a new table named 'cte' to be a clone of your table */
(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
FROM MyTable /* Selecting only unique values based on the "id" field */
)
SELECT * /* Here you can specify several columns to retrieve */
FROM cte
WHERE rn = 1

But in our case since this being a recursive query, it is hard to use the above format or rather beyond my capabilities.
I added jh.so_jobid (RUNID) column to our query and removed duplicate rows via excel after spending multiple hours in hit & try :(

What I understand, somewhere the recursive query is returning duplicate rows in different levels. This also leads me to the question that our original query to get the job details before querying the history table might also be doing the same (returning duplicates). I verified the results for the same and observed duplicate values.

Not sure how to resolve this. I believe support should provide such queries but unfortunately they do not.

Regards,
Shashank Singh​

[Pete Gross]

@Shashank Singh I'm not getting duplicates in my environment even when using the largest workflow we have.  It seems to me that having the columns with timestamps would make it rather difficult to get any duplicates.  Are you getting a lot of duplicates or just a few?  Do you get duplicates even for a small workflow?

I'm not familiar with the syntax in the query snippet you posted, but from the comments it doesn't sound much different than what is being done in the inner select - the result of that inner select is essentially a new table.

Regards,
Pete

[Shashank Singh]

@Pete Gross Duplicates are appearing only with larger workflows. With Smaller workflows the data comes out to be correct.
Larger workflows gives 6k rows in my environment and it is hard to troubleshoot duplicates in them.

Duplicates are appearing in both the queries (getting job details & history) for large workflows.

Regards,
Shashank
 ​​