Automic Workload Automation

Expand all | Collapse all

Finding broken object references

  • 1.  Finding broken object references

    Posted 02-09-2017 11:29 AM
    We are interested in setting up a systematic process for identifying broken object references — that is references from one AE object to another object where the target object is missing.
    1. Does anyone have a good SQL query for identifying broken object references in an AE system?
    2. Is there a comprehensive list somewhere of the XPaths in the AE object schema that can refer to AE objects?


  • 2.  Finding broken object references

    Posted 02-09-2017 12:21 PM
    What a GREAT IDEA!  But extremely challenging too.  I wonder if PhilippElmer might have thoughts on this too?

    Just off the top of my head I can think of these different ways that objects can become broken;

    Schedule references an object that has been deleted.

    Workflow references an object that has been deleted.

    Job references a prompset that has been deleted.

    A script references an object that has been deleted.

    A precondition or postcondition references an object that has been deleted.

    A Runtime tab references a notify object that has been deleted.

    ...there are more...


  • 3.  Finding broken object references

    Posted 02-09-2017 12:32 PM
    A few other object types that are used in other objects:
    • Agents
    • Queues
    • Login objects
    • Connection objects
    • Code pages
    • Notification objects
    • On-failure-execute objects
    • If-MRT-exceeded-execute objects
    • VARA objects referenced from prompt sets etc.


  • 4.  Finding broken object references

    Posted 02-09-2017 03:10 PM
    Job includes and their related variables would be another source, potentially.

    As far as a method, there's definitely an API solution that honestly might be easier than the SQL, especially jumping between all the various fields and possible values. Come to think of it this is likely something I'll have to code soon enough to support our environments. I'd honestly be interested which method would be faster.

    I'm not going to have access to my PC for a bit but if people want the Java version of this I could hammer it out. If an SQL-based script gets made I'd be interested to see it. 


  • 5.  Finding broken object references

    Posted 02-09-2017 06:11 PM
     I have a sql query that returns this information. 
           SELECT
                SUBSTR(DSE_FOLDERS.COMPLETE_PATH, 6) AS FOLDER,
                OH.OH_NAME AS PROCESS_FLOW,
                JPP_OTYPE AS OBJECT_TYPE,
                JPP_OBJECT AS JOB_NAME,
                '(Row:'||JPP_ROW||' Column:'||JPP_COL||')' AS JOB_POSITION
            FROM
                OFS
            JOIN
                OH
            ON
                OH.OH_IDNR = OFS.OFS_OH_IDNR_O
            JOIN
                (
                    SELECT
                        OH_IDNR AS OBJECT_ID,
                        OH_NAME AS OBJECT_NAME,
                        SUBSTR(OH_NAME, INSTR(OH_NAME, 'k\')+2, LENGTH(OH_NAME) ) "TRIMMED_NAME",
                        CONNECT_BY_ROOT SUBSTR(OH_NAME, INSTR(OH_NAME, 'k\')+2, LENGTH(OH_NAME) ) "Up",
                        LEVEL-1 "Pathlen",
                        SYS_CONNECT_BY_PATH(SUBSTR(OH_NAME, INSTR(OH_NAME, 'k\')+2, LENGTH(OH_NAME) ),'/') "COMPLETE_PATH"
                    FROM
                        OH
                    START WITH OH_IDNR = 1072001
                    CONNECT BY PRIOR OH_IDNR = SUBSTR(OH_NAME, 0, INSTR(OH_NAME, 'k\')-1)
                ) DSE_FOLDERS
            ON
                OFS.OFS_OH_IDNR_F = DSE_FOLDERS.OBJECT_ID
            JOIN
                JPP
            ON
                JPP.JPP_OH_IDNR = OH.OH_IDNR
            LEFT JOIN
                OH JOB_NM
            ON
                JOB_NM.OH_NAME = JPP.JPP_OBJECT
            JOIN
                (SELECT DISTINCT AH_OH_IDNR FROM AH_V WHERE AH_ACTIVETIME > CURRENT_DATE - 30) ACTIVE
            ON
                ACTIVE.AH_OH_IDNR = OH.OH_IDNR
            WHERE
                OFS.OFS_LEVEL = 0
            AND
                OH.OH_OTYPE = 'JOBP'
            AND
                COALESCE(JPP.JPP_OTYPE,'<EMPTY>') NOT IN ('<START>','<END>','<FE>','<IF>')
            AND
                JOB_NM.OH_IDNR IS NULL
            AND
                (
                    OH.OH_NAME NOT LIKE '%DEPRECATED%'
                OR
                    OH.OH_NAME NOT LIKE 'XX%'
                OR
                    DSE_FOLDERS.COMPLETE_PATH NOT LIKE '%DEPRECATED%'
                OR
                    DSE_FOLDERS.COMPLETE_PATH NOT LIKE '%TO_DELETE%'
                )
            ORDER BY SUBSTR(DSE_FOLDERS.COMPLETE_PATH, 6), OH.OH_NAME


  • 6.  Finding broken object references

    Posted 02-10-2017 05:21 AM
    Thank you petwir for pointing me to this interesting thread!
    SamahSohrab607228 your SQL is not client save because you're joining OH and JPP solely via the object names, and these are not unique on a system but only on each client.
    Here's a sample SQL to find missing JPPs (= Workflow and Schedule tasks) on a client:
    select JPP_Otype as UsageType, a.OH_Name as ParentName, JPP_Lnr as TaskNr, JPP_Object as MissingObject from OH a inner join JPP on a.OH_Idnr = JPP_OH_Idnr --filter client --remove filter and include a.OH_Client in select list and order by for system wide results where a.OH_Client = 1000 --filter out START, END, IF, FE and (JPP_OType in (select OTYP_Type from UC_OTYP where OTYP_Executable = 1) or JPP_OType = '<XTRNL>') --semi anti join -check for existence and not exists (      select * from OH b      where b.OH_DeleteFlag = 0      and a.OH_Client = b.OH_Client      and JPP_Object = b.OH_Name ) order by ParentName, TaskNr;
    Finding ALL missing object references - now that's a different challenge!
    I honestly wouldn't use the Java API for that because it connects through the application causing quite some overhead.

    Take a look into the table UC_OREF - it lists all db columns where object references can be found (except for references in AE scripting), so this would be a good starting point.
    And one more thought: IF you use "Y" for OBJECT_ACCESS_CLIENT_ZERO in UC_SYSTEM_SETTINGS, it would be smart to include client 0 objects in your search to avoid false positives ;-)

     



  • 7.  Finding broken object references

    Posted 02-10-2017 01:59 PM
    this is great. thanks Philipp. I only have 1 client, that's why my query worked just fine for our use case.


  • 8.  Finding broken object references

    Posted 02-10-2017 03:41 PM
    Philipp Elmer wrote:
    Thank you petwir for pointing me to this interesting thread!

    Here's a sample SQL to find missing JPPs (= Workflow and Schedule tasks) on a client…

     

    Good query, Philipp. I admit I was surprised by the number of results when I ran it against our systems. :)
    Finding ALL missing object references - now that's a different challenge!
    I honestly wouldn't use the Java API for that because it connects through the application causing quite some overhead.

    Take a look into the table UC_OREF - it lists all db columns where object references can be found (except for references in AE scripting), so this would be a good starting point.

    Even if one ignores scripting fields and curly-brace-style VARA object references, there are still a lot of places where one object can reference another. It’s a real challenge. I stumbled across UC_OREF a couple of weeks ago. Good stuff.