Automic Workload Automation

Hi All,

hope you can help me, I am searching for a SQL Script (AE: V11.2, ORA) that identifies ALL JOBP Parents of a Job in a workflow in Object definition (OH).

Structure:

JOBP.TOP
JOBP.A
JOBP.B
JOBP.C
JOBS

If I have the name  (or a Part of it) the SQL should return all JOBP- Parents.

Unfortunately I do not habe the SQL knowledge for loops :-(

what I do have is a select for the first parent of the JOBS:select oh_name, OH_IDNR from OH

select oh_name, OH_IDNR from OH
where oh_idnr in (
select JPP_OH_IDNR from OH,JPP
where oh_client =1
and oh_deleteflag = 0
and JPP_OBJECT like 'JOBS.WIN.WORKFLOWCONSTRUCT.WIN%'
and OH_NAME = JPP_OBJECT
)

would be very thankful for any help.....



cheers, Wolfgang

Hi Wolfgang, i know it is not 100% the thing you want, but i have created once a sql statement which lists all earliest starttimes within workflow dependencies of its tasks. with a bit of reverse engineering it might be possible to list what you need.

https://community.automic.com/discussion/11168/how-to-determine-all-tasks-within-workflows-where-a-earliest-starttime-is-defined/p1?new=1

Hi Harald my old friend :-)

THX for your input - unfortunately it does not solve my issue - creating a loop in sql and iterating through all possible parents of my JOBS.

But thanks a lot anyway!

cheers, Wolfgang

Hi Wolfgang, yes, reading your post a third time it came up to me, well my attempt is a nice try, but will not help you, sorry for that.

let me please recap your inquiry:

you do have a job, and it is part of several jobp objects (as a task) and you would like to have a list with all JOBP objects where this job is part of?

Hi Harald,

simplified Yes exactly - all Parents of my JOBS -

THX, Wolfgang!

whew, a tough one. I can share with you a statement to determine how to find out what Workflow a specific job (or other object) is task of, but to list all jobs parents is not very easy, but maybe to be archived using a SQLI Vara Object which is feed out of a VARA Object with a list of the JOBS you want to check might do the job, even if it might take a lot of programming work:

same to me :-)

THX Harald!

We should definetly talk about this over a glass of beer or punsch ;)

Definitely!

and attend Philipp Elmer's DB training....

This should do the job

select
  level, OH.OH_NAME, OH.OH_IDNR, JPP.JPP_OBJECT
FROM
  JPP JOIN OH ON JPP.JPP_OH_IDNR = OH.OH_IDNR
START WITH
  JPP.JPP_OBJECT = '<jobname>'
CONNECT BY
   JPP.JPP_OBJECT = PRIOR OH.OH_NAME
ORDER BY
  level desc;

Regards, Matthias

Many thanks, Matthias!

You're welcome

Just tested it on my System (with ORA Db) , it works great.

Super, thank you very much, saved me some days if analysis!!

manymanymanymany thanks!

Wolfgang

Uhh, that brings me to a point. Forgot to mention this is Oracle specific :-)