Automic Workload Automation

Expand all | Collapse all

SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

  • 1.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 06-20-2016 07:35 PM
    We recently received this question in a request; What is a SQL query that will list all objects with no statistics that are marked as Inactive within job plans.  Currently there is no Out of the Box way to do this and Support usually only provides SQL scripts for troubleshooting due to the unforeseen consequences this can have on a Production system.

    I'm opening this post so that anyone with the custom SQL or any other methods can possibly assist with this.

    Also, all tables can be found in the Documentation at the following:

    http://docs.automic.com/documentation/AE/11.2/english/AE_DB_TABLES/tables/index.htm

    As always we suggest this is done in a Test Environment first to ensure there is no interruption to your Production Environment.

    SQL query that will list all objects with no statistics that are marked as Inactive within jobplans




  • 2.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 06-22-2016 07:57 AM
    What is a usage object?


  • 3.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 06-23-2016 12:41 PM
    Ive updated the wording to match what is needed, as this was a request for a SQL query that will list all objects with no statistics that are marked as Inactive within job plans.


  • 4.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 07-05-2016 08:19 PM
    This will include inactive tasks in a jobplan or schedule, and external tasks in a jobplan/schedule that has no statistics.

    select oh.oh_client ClientName, oh.oh_otype TaskType, oh.oh_name Task, oh.oh_idnr TaskID, jpp_otype TaskType2,jpp_oh_idnr  JobPlan_ID, wf.oh_name  JobPlan, wf.oh_otype JobPlanType
    from oh, jpp, oh wf  where 1=1
    and oh.oh_name = jpp_object
    and jpp_oh_idnr = wf.oh_idnr 
    and wf.oh_client = oh.oh_client 
    and jpp_active = 0 --- 'not active in workflow/jobplan' and oh.oh_idnr not in (select ah_oh_idnr from ah) --- 'no statistics'
    ---- and jpp_otype <> '<XTRNL>' --- 'exclude external dependency/tasks'
    ---- and wf.oh_otype = 'JOBP' --- 'search in jobplan only'

    Note: Query is for MSSQL. Syntax may need to be changed for Oracle/DB2.




  • 5.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 07-12-2016 07:43 AM

    The statement works for Oracle and DB2 as well, without modification.



  • 6.  SQL query that will list all objects with no statistics that are marked as Inactive within jobplans

    Posted 07-12-2016 01:26 PM
    Sorry to take so long to reply, I've been OOO on vacation for over a week.

    Thanks for your solution, I will try that and see how it works.

    Frank Goodwin