Automic Workload Automation

In another discussion about the limitations of ucybdbld,I proposed a work-around based on Java API calls that query and change object folder location(s) in an Automation Engine system. I would like to investigate the feasibility of such a work-around.

Given the name of a UC4 object, I would like to know the best way to use the Java Application Interface to find out:

• in which folder the object resides, and
• in which folder(s) any links to the object reside

Thanks in advance.

It occurred to me that there might be a straightforward way to do this with a query of the UC4 database. Does anyone know how to query an object’s folder location from the UC4 DB?

I figured out how to get the folder ID of the folder containing an object via a DB query.

1. Get the folder ID & OH ID of the folder where an object resides

SELECT * from OFS
where OFS_LINK = 0
and OFS_OH_IDNR_O in
 (select OH_IDNR from OH
 where OH_NAME = 'object_name');

2. Get the folder ID & OH ID of the folders where any links to an object reside

SELECT * from OFS
where OFS_LINK = 1
and OFS_OH_IDNR_O in
 (select OH_IDNR from OH
 where OH_NAME = 'object_name');

Turning these IDs into usable paths usually requires a recursive query. The OH_NAME returned by the following query contains the name of the directory, prefixed by the OH_IDNR of the parent directory, a lowercase letter k, and a backslash.

3. Get name of folder and OH ID of parent folder

select OH_OTYPE,OH_NAME from OH
where OH_IDNR in
(select OFS_OH_IDNR_F from OFS
where OFS_LINK = 0
and OFS_OH_IDNR_O in
  (select OH_IDNR from OH
  where OH_NAME = 'object_name'));

E.g., if results returned by the above query are

OH_TYPEOH_NAME
FOLD       2139047k\TEST

this means that the folder in which the object resides is called TEST, and that the parent folder of TEST has the OH_IDNR 2139047. One can get the information on the parent folder using this query:

select OH_OTYPE,OH_NAME from OH
where OH_IDNR = '2139047';

One must keep going up the folder hierarchy like this until one reaches an OH record whose OH_TYPE is CLNT instead of FOLD. At that point, the previous OH record found corresponds to a top-level folder in that client.

If there is no Java method for getting the folder, then the simplest solution might be to get the folder ID using query 1 or 2, above, and then to use the IFolder.fullPath method to get the folder’s full path. I hope there is a way to do this just using the Java APIs though.

Thanks to the kind folks with Automic Support, I have an answer to the question. Here are two code samples.

Get object location

/**
 * Returns the location of the object or null it it does not exist.
 *
 * @param con API Connection
 * @param objectName Name of the object
 * @return Folder location or null if not found
 * @throws IOException If the API connection was interrupted
 */
private static String getObjectFolderLocation(Connection con, String objectName) throws IOException {
  SearchObject search = new SearchObject();
  search.selectAllObjectTypes();
  search.setName(objectName);
  uc4.sendRequestAndWait(search);
  Iterator<SearchResultItem> it = search.resultIterator();
  if (it.hasNext()) {
    return it.next().getFolder();
  }
  return null;
}

Get the location(s) of any links to an object

/**
 * Returns an iterator over all links for the specified object.
 *
 * @param objectName Name of the object
 * @return Iterator of links or null if there are no links or the object does not exist
 * @throws IOException If the API connection was interrupted
 */
private static Iterator<SearchResultItem> getLinks(String objectName) throws IOException {
  SearchObject search = new SearchObject();
  search.selectAllObjectTypes();
  search.setName(objectName);
  search.setIncludeLinks(true);
  uc4.sendRequestAndWait(search);
  List<SearchResultItem> linksOnly = new LinkedList<SearchResultItem>();
  Iterator<SearchResultItem> it = search.resultIterator();
  while (it.hasNext()) {
    SearchResultItem item = it.next();
    if (item.isLink()) {
      linksOnly.add(item);
    }
  }
  if (linksOnly.isEmpty()) return null;
  return linksOnly.iterator();
}

The second code sample relies on the SearchObject.setIncludeLinks()  method, which is currently available only in the Automation Engine Application Interface V10 and later.

Automic Support provided an Oracle SQL query that will print the paths of any links to an object. This can be used on systems that do not provide the SearchObject.setIncludeLins() method.

select path from (select level ,ofs_oh_idnr_f as parent ,ofs_oh_idnr_o as child ,oh_name ,substr(oh_name,10,99) ,SYS_CONNECT_BY_PATH( substr(oh_name,10,99) , '\' ) as path ,ofs.ofs_link from ofs, oh where oh.oh_idnr = ofs_oh_idnr_f and ofs_oh_idnr_o = (select oh_idnr from oh where oh_name = 'UC0.2.DOCU') start with ofs_oh_idnr_f = (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 100) connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f) where ofs_link = 1;

It works fine, and we have used it to build a fix for the ucybdbld bug. In the query, simply replace UC0.2.DOCU with the name of the object.

Here's a variation of the query above that will return a list of all the linked objects for a given client, and their paths:

select (select OH_Client from oh where oh_idnr = child)  Client
     , (select OH_OType  from oh where oh_idnr = child)  Type
     , (select OH_Name   from oh where oh_idnr = child)  Object_Name
     , PATH
  from (select level
             , ofs_oh_idnr_f                                      as parent
             , ofs_oh_idnr_o                                      as child
             , oh_name
             , substr(oh_name,10,99)
             , SYS_CONNECT_BY_PATH( substr(oh_name,10,99) , '\' ) as path
             , ofs_link
          from ofs
             , oh
         where oh_idnr            = ofs_oh_idnr_f
           and ofs_link           = 1
         start with ofs_oh_idnr_f = (select oh_idnr
                                       from oh
                                      where oh_otype  = 'CLNT'
                                        and oh_client = &CLIENT)
        connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f)
 order by PATH, Type, Object_Name
/

I ran into this problem again today, and was surprised anew that there is no script statement to get the folder path of an object. One can move an object to a particular folder using MOVE_OBJECT, but there’s no equivalent GET_OBJECT_LOCATION or GET_OBJECT_FOLDER_PATH script statement to find out where it is. :smile:

Not deterred by this, I decided to adapt the above SQL statement into something roughly equivalent an AE script statement. Simply create an SQLI VARA object called UC4.GET_FOLDER_PATH_OF_OBJECT.VARA_SQLI, with the following SQL statement:

select substr(path,2) as "Folder path" from
(select level
,ofs_oh_idnr_f as parent
,ofs_oh_idnr_o as child
,oh_name
,substr(oh_name,10,99)
,SYS_CONNECT_BY_PATH( substr(oh_name,10,99) , '/' ) as path
,ofs.ofs_link
from ofs, oh
where oh.oh_idnr = ofs_oh_idnr_f
and ofs_oh_idnr_o = (select oh_idnr from oh where oh_name = ?)
start with ofs_oh_idnr_f = (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = ?)
connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f)
where ofs_link = 0

Set the following bind parameters:

1. &$ARCHIVE_KEY#
2. &$CLIENT#

Then, you can use this SQLI in your scripts by calling it with GET_VAR:

:SET &OBJECT_NAME# = "UC0.LOGIN"
:PUT_ATT ARCHIVE_KEY1 = &OBJECT_NAME#
:SET &FOLDER_PATH# = GET_VAR("UC4.GET_FOLDER_PATH_OF_OBJECT.VARA_SQLI")
:PRINT "Folder path of &OBJECT_NAME#: &FOLDER_PATH#"

The report of this script shows the result:

2016-02-12 16:58:09 - U0020408 Folder path of UC0.LOGIN: /SYSTEM/LOGINS

Note that I’ve written the SQLI VARA to pass the bind parameter for the object name via the predefined variable &$ARCHIVE_KEY# because Automation Engine v9 does not properly support using ordinary object variables in SQLI/SQL VARA bind parameters. AE v10 and later do not suffer from this limitation, so if you’re running v10 or later, you can just put &OBJECT_NAME# directly in bind parameter 1, and omit the :PUT_ATT statement.

Here is an updated version. It incorporates the following changes:

• eliminates the use ofthe &$ARCHIVE_KEY#predefined variable (requires v10 or later);
• uses an improved SQL query based onthis query; and
• switches the order of the two bind parameters.
  

UC4.GET_FOLDER_PATH_OF_OBJECT.VARA_SQLI

with AllFolderPaths (Idnr, FolderPath) AS (
  select OH_IDnr,
  OH_Name
  from OH
  where OH_OTYPE = 'CLNT'
  and OH_Name = ?
  UNION ALL
  select OFS_OH_IDNR_O,
  FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
  from AllFolderPaths
  inner join OFS on Idnr = OFS_OH_Idnr_F
  inner join OH on OFS_OH_Idnr_O = OH_IDNR
  where OH_OTYPE = 'FOLD'
)

select replace(substr(FolderPath,5),'\', '/') from AllFolderPaths
where Idnr in
( select OFS_OH_IDNR_F
  from OFS
  where OFS_OH_Idnr_O =
  ( select OH_IDNR
    from oh
    where oh_name = ?
  )
  and OFS_Link = 0
)

Set the following bind parameters in the SQLI VARA:


1. &$CLIENT#
2. &OBJECT_NAME#

The value of OFS_Link could be passed as s third bind parameter too, to look for the path of object links.

UC4.GET_FOLDER_PATH_OF_OBJECT.SCRI

:SET &OBJECT_NAME# = "UC0.LOGIN"
:SET &FOLDER_PATH# = GET_VAR("UC4.GET_FOLDER_PATH_OF_OBJECT.VARA_SQLI")
:PRINT "Folder path of &OBJECT_NAME#: &FOLDER_PATH#"

An XML export of these two objects is attached.