AppWorx, Dollar Universe and Sysload Community

 View Only
  • 1.  AM v8: Extracting jobs and their specific predecessors

    Posted Apr 09, 2014 07:17 AM

     

    I want to analyse a specific chain (process flow) of jobs that ran on a particular day/night to find critical paths, etc. I do not have the Gannt view addon, so I want to extract the job data from the database to import into Microsoft Project.

    My question is, when extracting so_job_history records, I want to also bring data relating to the specific job_id that was the predecessor of this job. This table does have a so_predecessors column, but this contains a job (module) name, rather than the actual job_id. Given that the same job/module name might run many times during the course of the process flow, the name is not good enough - I need to the job_id of the predecessor(s).

    Does anybody know where (i.e. what table/view & column) I might find this info relating to job history?



  • 2.  AM v8: Extracting jobs and their specific predecessors

    Posted Apr 09, 2014 03:10 PM

    I'm not aware of that info being in any permanent table, but I suspect the application figures the predecessor logic in the context of the individual parent chain. 

    You could query so_job_history for the other children of the same chain, and then match the names in so_predecessors to the results.

    Here's an example... not the greatest performance, though; if you can limit the so_job_finished values for each use of so_job_history, it should be much faster (at least, I think so_job_finished is indexed by default? It is in our instance but that could be custom).

    SELECT other_jobs_hist.so_module,

      other_jobs_hist.so_jobid

    FROM so_job_history job_hist

    INNER JOIN so_job_history chain_hist

    ON job_hist.so_parents_jobid = chain_hist.so_jobid

    INNER JOIN so_job_history other_jobs_hist

    ON other_jobs_hist.so_parents_jobid = chain_hist.so_jobid

    where job_hist.so_jobid             = [your so_jobid]



  • 3.  AM v8: Extracting jobs and their specific predecessors

    Posted May 01, 2014 04:27 AM

     

    Thanks, Jessica

    Not exactly what I was hoping for, but good nonetheless



  • 4.  AM v8: Extracting jobs and their specific predecessors

    Posted May 15, 2014 07:51 AM

    you'll find this information in AJPP and AJPPA. For active tasks it's EJPP and EJPPA.



  • 5.  AM v8: Extracting jobs and their specific predecessors

    Posted Dec 23, 2015 08:01 AM
    Alan,

    Is this what you are looking for?

    Alter session set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
    SELECT
      b.SO_PARENT_NAME,
      b.so_module,
      b.SO_CHAIN_ORDER,
      b.SO_START_DATE,
      b.SO_JOB_STARTED,
      b.SO_JOB_FINISHED,
      b.SO_STATUS_NAME,
      a.so_predecessors
    FROM so_chain_detail a, so_job_history b
    WHERE  b.SO_PARENT_NAME = 'TEST_PRED'
      AND a.so_job_seq = b.so_job_seq
    ORDER BY 3,2;





  • 6.  RE: AM v8: Extracting jobs and their specific predecessors

    Posted Jun 11, 2020 03:28 PM
    @Steven_Nguyen_130 I wrote a similar query -

    select jh.SO_PARENT_NAME, jh.so_module, jh.SO_CHAIN_ORDER, jh.SO_START_DATE, jh.SO_JOB_STARTED, jh.SO_JOB_FINISHED, jh.SO_STATUS_NAME, cd.so_predecessors
    from appworx.so_job_history jh
    INNER JOIN appworx.so_chain_detail cd
    ON cd.so_job_seq = jh.so_job_seq
    INNER JOIN appworx.so_job_table jt
    ON jt.so_job_seq = cd.so_chain_seq
    WHERE jh.so_request_date = to_timestamp('Job Req Date')
    and SO_PARENT_NAME = 'Your-Chain-Name' ​

    But the query is not giving the actual job names. It just gives the job names from the chain detail table (components of chain) but does not give actual job names that may be part of those chain components / sub-chains. It is just repeating the sub-chain name multiple times. Can someone help here.

    Regards,
    Shashank Singh