Automic Workload Automation

  • 1.  Object with only OFS_Link=1

    Posted 01-06-2020 11:39 AM
    Edited by Michael A. Lowry 01-07-2020 04:40 AM
    Today I was looking for an object in one of my AE systems. I found it using the quick search function, right-clicked, and chose Explorer to display the object's home folder. A window opened with <No Folder> selected. The object was not listed.

    I decided to take a look at the database to see what was going on. The locations of AE objects in the folder hierarchy are stored in the OFS table. In another thread I documented a handy SQL query to list the OFS table entries for an object. If I list the OFS entries for this object, I see that it has a single entry deep in the hierarchy, with OFS_Link=1. If I navigate to that location in the folder hierarchy, sure enough, I see a link. The object has no OFS entry with OFS_Link=0. This means that the object has no home folder, even though it has a single link. Normally this should not happen.

    What can cause this problem? Is there a quick fix?


  • 2.  RE: Object with only OFS_Link=1

    Posted 01-07-2020 09:37 AM
    Edited by Michael A. Lowry 01-08-2020 03:27 AM
    Well, I figured out how the problem originated. I came up with a query to find out the scope of the problem. This query lists all of the objects under a specified path, and all of the folder locations (including of links) of those objects, even if those locations are outside of the specified path.

    with
    Bind_Parms -- Set client number and folder path here
    as (select 100 as Client_Number,
    '0100\APPS' AS Folder_Path
    from dual),

    AllFolderPaths (FOLD_Idnr, FolderPath) AS (
    select OH_IDnr,OH_Name
    from OH
    where OH_OType = 'CLNT'
    and OH_Name = '0100'
    --between '0000' and '9999'
    UNION ALL
    select OFS_OH_Idnr_O,
    FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
    from AllFolderPaths
    inner join OFS on FOLD_Idnr = OFS_OH_Idnr_F
    inner join OH on OFS_OH_Idnr_O = OH_Idnr
    where OH_OType = 'FOLD'
    ),

    child_objects as
    (
    select OFS_OH_Idnr_O,OFS_Link,OFS_OH_Idnr_F
    from OFS
    start with OFS_OH_Idnr_F =
    (
    select OH_IDNR from OH where OH_OType='FOLD'
    and OH_Idnr = (
    select FOLD_Idnr
    from AllFolderPaths
    where FolderPath = (Select Folder_Path from Bind_Parms)
    )
    and OH_CLIENT=(Select Client_Number from Bind_Parms)
    )
    connect by prior OFS_OH_Idnr_O = OFS_OH_Idnr_F
    ),

    child_objects_and_their_links as (
    select OH_Idnr, OH_OType, OH_Name, FOLD_Idnr,
    replace(substr(FolderPath,5),'\', '/') as Path,
    OFS_Link
    from AllFolderPaths
    join OFS on FOLD_Idnr=OFS_OH_Idnr_F
    join OH on OFS_OH_Idnr_O = OH_Idnr
    where OH_OType <> 'FOLD'
    and OH_Idnr in (select OFS_OH_Idnr_O from child_objects)
    order by OH_Name
    ),

    MainQuery as(
    select *
    from child_objects_and_their_links
    inner join (
    select OH_Name OH_Name_P, count(distinct(FOLD_Idnr)) as num_unique_paths
    from child_objects_and_their_links
    group by OH_Name
    ) subq
    on child_objects_and_their_links.OH_Name = subq.OH_Name_P
    )

    select
    OH_Idnr, OH_OType, OH_Name, FOLD_Idnr, Path, OFS_Link, Num_Unique_Paths
    from MainQuery
    where Num_Unique_Paths = 1
    and OFS_Link=1
    order by OH_Name,OFS_Link,Path

    This query returned just 7 results - 7 links in a single folder. I realized that the problem likely occurred during XML import, and I quickly confirmed this hunch. The XML file in question is version 3 of my general-purpose EXEC VARAs. The first part of the XML file follows.
    <?xml version="1.0" encoding="ISO-8859-15"?>
    <uc-export clientvers="12.0.5+hf.2.build.1638">
    <FolderStruct>
    <includeExternalObjects>1</includeExternalObjects>
    <FOLD name="GENERAL_PURPOSE_EXEC_VARAS" title="">
    <JOBS_UNIX id="5812018" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.JOBS"/>
    <SCRI id="5808048" link="0" name="UC4.LOOK_UP_FREE_SPACE.SCRI"/>
    <SCRI id="5808049" link="0" name="UC4.LOOK_UP_PWP.SCRI"/>
    <SCRI id="5796069" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.SCRI"/>
    <VARA id="5790060" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.VARA_EXEC"/>
    <JOBS_SQL id="5775096" link="0" name="UC4.GP.JOBS_SQL##TEST##"/>
    <JOBS_SQL id="5775097" link="0" name="UC4.GP_SQL.JOBS"/>
    <VARA id="5775098" link="0" name="UC4.GP_SQL.VARA_EXEC"/>
    <VARA id="5775099" link="0" name="UC4.GP_SQL.VARA_EXEC##TEST##"/>
    <JOBS_UNIX id="5775100" link="0" name="UC4.GP_UNIX.JOBS"/>
    <VARA id="5775101" link="0" name="UC4.GP_UNIX.VARA_EXEC"/>
    <VARA id="5775102" link="0" name="UC4.GP_UNIX.VARA_EXEC##TEST##"/>
    <FOLD name="COMMON" title="">
    <VARA id="5771045" link="1" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
    <VARA id="5790030" link="1" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
    <JOBI id="5805033" link="1" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
    <JOBI id="5774078" link="1" name="UC4.READ_JOB_OUTPUT.JOBI"/>
    <JOBI id="3141355" link="1" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
    <JOBI id="5804007" link="1" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
    <VARA id="5806033" link="1" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
    </FOLD>
    </FOLD>
    <FOLD name="##INCLUDED_EXTERNALS##">
    <VARA link="0" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
    <VARA link="0" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
    <JOBI link="0" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
    <JOBI link="0" name="UC4.READ_JOB_OUTPUT.JOBI"/>
    <JOBI link="0" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
    <JOBI link="0" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
    <VARA link="0" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
    </FOLD>
    </FolderStruct>

    Note that exactly seven links (to objects outside of the folder) are included. It appears that when this XML file was imported, the objects were created as links, but with no home folder. This seems like a bug.


  • 3.  RE: Object with only OFS_Link=1
    Best Answer

    Posted 01-07-2020 10:53 AM
    Edited by Diane Craddock 01-17-2020 09:41 AM
    I was able to reproduce the problem:
    1. Remove the existing objects from the target system.
    2. Remove the ##INCLUDED_EXTERNALS## element and its children from the XML file.
    3. Import the XML file, with Replace existing objects enabled and Keep existing folder links disabled.

    So, the problem happens in the following set of circumstances:
    • The XML file contains a FolderStruct element; and
    • An object entry appears under FolderStruct with link=1, but without another corresponding entry with link=0.

    The Automation Engine ought to be clever enough to recognize this problematic situation, and should either reject the XML file, or should create OFS entries for these objects with OFS_Link=0, not 1. It should not create links objects without a home folder location.