Automic Workload Automation

Expand all | Collapse all

Database query

Nitin_Gaonkar_5618

Nitin_Gaonkar_5618Oct 20, 2015 06:28 PM

Nitin_Gaonkar_5618

Nitin_Gaonkar_5618Oct 21, 2015 04:50 PM

  • 1.  Database query

    Posted Oct 13, 2015 01:37 PM
    Hi, Can any one provide me with the sql query or point me to the table where I can find the scheduling details of the jobs. I want to query the table and get the below information: job schedule time schedule object


  • 2.  Database query

    Posted Oct 14, 2015 07:04 PM
    Hi Nitin,

    You could something such as: 
    SELECT OH_Name,JPP_Object,JPP_ErlstStTime FROM OH,JPP WHERE OH_OType = 'JSCH' and OH_Idnr=JPP_OH_Idnr

    It will give you the Job name, the schedule object it belong too and the time its to start.



  • 3.  Database query

    Posted Oct 15, 2015 02:29 PM
    This looked pretty useful to me, so I added another column to also display the associated calendar keyword if one exists;

    select oh_name           as SCHEDULE
          , jpp_object       as OBJECT
          , JPP_ErlstStTime  as STARTTIME
          , jppc_calekeyname as CALENDAR
       from uc4.dbo.oh
          inner join uc4.dbo.jpp 
             on oh_idnr = jpp_oh_idnr
          left outer join uc4.dbo.jppc 
             on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
      where oh_otype = 'JSCH' 
      order by 1,2,3,4;


  • 4.  RE: Database query

    Posted Apr 24, 2020 03:37 PM
    Hello Pete.
    I have a requirement to produce a SQL report showing a distinct listing of Job Plans, and a column for Schedules.  If the Job Plan exists in a Schedule then the Schedule Name should appear in the Schedules column.  If the Job Plan does NOT exist in a Schedule, then a blank or null should appear in the Schedules column.

    Bases on your info, I started with the following query.  It works fine, but it lists ONLY the JobPlans that exist in Schedules.  How would I expand the query to also list the Job Plans that do NOT exist in a Schedule?

          Select jpp_object as " OBJECT",
                oh_name as " SCHEDULE "
          from UC4.oh, UC4.jpp
          where oh_idnr = jpp_oh_idnr
                and oh_otype = 'JSCH'
          order by 1,2;

    Thanks,
    Gerry

    ------------------------------
    Gerry Jandeska
    Lead Scheduling Systems Engineer
    AEA. Core Technology Services
    CME Group
    20 South Wacker Drive
    Chicago, Illinois 60606
    ------------------------------



  • 5.  RE: Database query

    Posted Apr 24, 2020 07:03 PM
    Edited by Pete Wirfs Apr 24, 2020 07:04 PM
    This sure is an old thread!  You're lucky I'm still alive!  LOL!

    Give this a try (tested in SQLServer);

    select oh_name     
    , (select top 1 jsch.oh_name from oh jsch, jpp      
    where jpp_oh_idnr = jsch.oh_idnr and jpp_object = jobp.oh_name) as schedule
    from oh jobp
    where oh_otype = 'JOBP' and oh_deleteflag = 0 and oh_client = 100
    order by 1,2;

    This will only return one row per workflow, even if it is scheduled in more than one JSCH object.  I used the "top 1" trick to randomly choose one.  You could also return a count(*) value if you needed to know how many schedules it is in.

    ------------------------------
    Pete
    ------------------------------



  • 6.  RE: Database query

    Posted Apr 30, 2020 05:16 PM
    Hello Pete,
    I'm glad you were available to help!  After tweaking your query for SQLPlus and adding some conditions, your solution worked fine.  Thanks.  

    Now I'd like to ask you for assistance with another SQLPlus Query......  
    This SQLPlus query pulls the Schedule and Calendar/Keyword conditions for all Objects:

      select jpp_object as OBJECT
             , oh_name as SCHEDULE
             , JPPC_CaleName as CalendarName
             , JPPC_CaleKeyName as CalendarKey
        from UC4.oh
        inner join UC4.jpp
            on oh_idnr = jpp_oh_idnr
             left outer join UC4.jppc
                  on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
             where oh_otype = 'JSCH'
                 and oh_name NOT LIKE '%old%'
                 and oh_name NOT LIKE '%OLD%'
       order by 1,3,2;

    So, how would I modify it to pull only those objects that have NEW or MODIFIED Calendar/Keyword conditions?

    ------------------------------
    Gerry Jandeska
    Lead Scheduling Systems Engineer
    AEA. Core Technology Services
    CME Group
    20 South Wacker Drive
    Chicago, Illinois 60606
    ------------------------------



  • 7.  Database query

    Posted Oct 20, 2015 06:28 PM
    Thank you Luu and Pete, this really helps.


  • 8.  Database query

    Posted Oct 21, 2015 02:27 PM

    Hi Guys,

    I am still getting use to these tables. just wanted to know if you are guys are aware where i can find the below coloumns

    I want the start time, end time of the job and also trying to get the command which we are executing in the process tab of the job.

    like startime,endtime, command.



  • 9.  Database query

    Posted Oct 21, 2015 02:46 PM
    For reporting historical statistics, we use the UC4.DB Reporting Tool.  You use their GUI to configure xml report rules files, and then execute batch utility UCYBDBRT and pass it the xml report rule files so it knows what you want reported.  The results are either in HTML format or CSV format.

    I think asking for the command is too complex of a request.  Any process tab can cause more than one command to be executed.

    If you want to write it yourself, I believe you would start with the AH table.
    There is some great online table documentation too;
    http://docs.automic.com/documentation/AE/10.0/english/AE_DB_TABLES/tables/index.htm





  • 10.  Database query

    Posted Oct 21, 2015 04:50 PM
    Thanks for your feedback Pete


  • 11.  Database query

    Posted Oct 28, 2015 02:41 PM

    All,

    Below is the query which you can use to get the content of the process tab

    select * from ot where ot_oh_idnr in ( select oh_idnr from oh where oh_name='job_name')

     



  • 12.  Database query

    Posted Oct 29, 2015 12:24 PM
    To join Pete's and Nitin's answers and include the Process tab content:
    SELECT soh.oh_name       as SCHEDULE
          , jpp_object       as OBJECT
          , jpp_erlststtime  as STARTTIME
          , jppc_calekeyname as CALENDAR
          , DECODE (ot_type,0,'Process',1,'PreProcess',2,'PostProcess',ot_type) as TEXT_CONTEXT
          , ot_content as TEXT
    FROM oh soh
         INNER JOIN jpp 
         ON soh.oh_idnr = jpp_oh_idnr
         INNER JOIN oh joh ON jpp_object = joh.oh_name and joh.oh_client = soh.oh_client
         LEFT OUTER JOIN ot ON ot_oh_idnr = joh.oh_idnr
         LEFT OUTER JOIN jppc ON jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = soh.oh_idnr
    WHERE soh.oh_otype = 'JSCH' 
         AND joh.oh_client = 1000

    However, I don't see how you'd get an end time for jobs that are planned but not yet run.


  • 13.  Database query

    Posted Nov 02, 2015 02:49 PM

    Thanks Jessica, is there a way we can get the end time of the jobs which have already run?

    for example, I need the startime and end time of a particular process