Automic Workload Automation

Expand all | Collapse all

XREQ objects

  • 1.  XREQ objects

    Posted 02-20-2017 12:54 PM

    Update: This discussion has been summarized and condensed into a single document:

    XREQ objects.

     


     

    If you have worked with the Automation Engine for a while, you will no doubt have seen references to “XREQ” objects in error messages, logs, or traces. The UC_OTYP table refers to these objects as “Xrequests”. Although the XREQ table documentation page says the table is for storing “Memory distribution”, the table actually contains information about these objects. XREQs appear to be used internally by the Automation Engine, mostly for functions that require user interaction.
     hun8vitjac9j.gifhttps://us.v-cdn.net/5019921/uploads/editor/d1/hun8vitjac9j.gif" width="32">
    The XREQ icon, from the ucdj.jar file in the Java User Interface.

     

    Although there is no XREQ_OH_IDNR column, bytes 6 through 15 of XREQ_Name correspond to OH_IDNR entries in OH, and all of these have the OH_OType XREQ. With a join, it is possible to list the objects by name. Here’s an Oracle SQL query that will do the trick.

    WITH XREQ_OBJECTS
    AS ( SELECT OH_IDNR,OH_NAME,OH_OTYPE,XREQ_NAME,
    SUBSTR (XREQ_NAME, 1, 4) AS XREQ_SUBTYPE,
    CASE SUBSTR (XREQ_NAME, 1, 4)
    WHEN 'ODOC' THEN SUBSTR (XREQ_NAME, 17, 4)
    ELSE ' '
    END AS ODOC_SUBTYPE, XREQ_CONTENTLEN,XREQ_CONTENT
    FROM UC4.XREQ LEFT JOIN UC4.OH ON SUBSTR (XREQ_NAME, 6, 10) = OH_IDNR
    ORDER BY OH_Name, SUBSTR (XREQ_NAME, 19, 4))
    SELECT OH_IDNR, OH_OTYPE,OH_NAME,XREQ_NAME,XREQ_SUBTYPE,ODOC_SUBTYPE,
    XREQ_CONTENTLEN,XREQ_CONTENT,
    FROM XREQ_OBJECTS
    WHERE ROWNUM < 200
    ORDER BY OH_NAME, XREQ_NAME

    The first four bytes of XREQ_Name are some sort of sub-type. The subtype is always either XREQ or ODOC. XREQ_CONTENT contains binary data — what, I’m not sure.

     

    As with many other object types, a lot of the content of XREQ objects is stored in other tables, including OT and ODOC. Here’s my best guess of what these two sub-types mean:

    XREQ sub-typeDescription

    XREQ

    Scripting tab content stored in OT table

    ODOC

    Documentation tab content stored in ODOC table

    The ODOC tables also includes binary data in ODOC_CONTENT. (ODOC contains the contents of Documentation tabs of AE objects.)

     

    The BLOB fields in the DB are not directly readable, and I did not have much success in decoding their contents. However, the scripting content in OT is readable, and it seems like ordinary AE scripting, with a few additional Automic-internal commands. Also, XREQ objects are included in the initial data, e.g., in db/general/8.0/uc_ini.txt and db/general/12.0/uc_ini.txt. If you browse these files, you will find a bunch of AE scripting and XML related to Xrequests.

     

    It is very interesting to learn a bit more about how the Automation Engine works. The next time I encounter an error message related to an XREQ, I will know how to do a bit more investigation to identify the root of the problem. And more generally, I find it fascinating to see how many of the higher-level functions of the Automation Engine and its various user interfaces are built using AE scripting and XML under the hood.



  • 2.  XREQ objects

    Posted 02-21-2017 07:36 PM
    I've used this SQLServer syntax to look at and search the contents of our documentation tabs;

    cast(ODOC_Content as varchar(4096))

    ODOC_Name tells you which tab it is associated with. Use ODOC_OH_IDnr to get the object name from the OH table.


  • 3.  XREQ objects

    Posted 02-22-2017 04:18 AM
    petwir: Thanks. The corresponding cast in Oracle SQL is:
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(ODOC_Content,2000,1))


  • 4.  XREQ objects

    Posted 02-22-2017 07:17 AM
    Does anyone have some SQL to extract scripting tab content from the OT table? I'm not sure of the best way to concatenate multiple results into a single long string.


  • 5.  XREQ objects

    Posted 02-22-2017 01:22 PM
    Not trivial!   Here is a discussion of different ways to approach the problem in SQL;
    https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/


  • 6.  XREQ objects

    Posted 02-23-2017 09:32 AM
    for SQL there seems to be an easier way, here is just an example:
    select OT_Content + ';' AS 'data()'
    from OT
    where OT_OH_Idnr = 1016002
    FOR XML PATH('')


  • 7.  XREQ objects

    Posted 02-23-2017 12:37 PM
    I found a way to do it in Oracle, but it’s almost certainly not the most efficient way!
    SELECT XMLQUERY (
              'for $line in /ROWSET/ROW/OT_CONTENT/text()
               return concat($line, "&#10;")'
              PASSING XMLType.createXML (
                         SYS.DBMS_XMLGEN.getXML (
                            'select OT_Content from OH,OT
                             where OH_IDNR = OT_OH_IDNR
                             and OH_NAME = ''object_name'''))
              RETURNING CONTENT)
              OT_CONTENT_MERGED
      FROM DUAL;
    Going via XML is almost certainly not the best way to do this, and it has problems. Complexity & inefficiency aside, the output is not 100% correct: several characters are entitized (replaced with HTML character entities). Also, I have no idea how to combine this into a larger query that lists multiple objects.


  • 8.  XREQ objects

    Posted 02-24-2017 03:57 AM
    Hi,
    in Oracle there is an easy way using listagg - but it uses VARCHAR2, so it's limited to scripts of 4000 characters.

    Example concatenating the process tab of object 75 (EVENT.WINCMD)
    select OT_Type, listagg(OT_Content, CHR(10)) within group (order by OT_Lnr) as scriptcontent from OT where OT_OH_IDNR = 75 group by OT_Type order by OT_Type;

    For larger scripts, using XML for aggregation (with the xmlagg function) is a valid approach in Oracle.
    You can use XMLCAST to onvert the result to a CLOB
    Example concatenating the process tabs of object 2244 (FILE.BACKUP.WINDOWS)
    select OT_Type, rtrim (   XMLCAST(     xmlagg (       xmlelement (e, OT_Content || CHR(10))       order by OT_Lnr     ) AS CLOB   ) ) as scriptcontent from OT where OT_OH_IDNR = 2244 group by OT_Type order by OT_Type;

    I hardcoded the OH_IDNR In above examples for simplicity.
    You can of course add a join with OH and include the client and the object name.
    Beware: Never use the obect name without client - it's ambiguous because there could be an object with the same name on a different client!

    Cheers, Philipp


  • 9.  XREQ objects

    Posted 02-24-2017 11:35 AM
    Ok, I think I figured it out. The relationships between the various elements are shown below.
    e1leyboorbpt.png
    The XREQ_Name field contains three parts separated by underscores (_). The second part is the OH_Idnr of the XREQ object. XREQ objects can contains several tabs, similar to DOCU objects. For a given OH_Idnr, one row appears in the XREQ table for each tab that XREQ object contains.

    The first part of XREQ_Name indicates in which table the content of each tab is stored:

    First 4 characters
    of XREQ_NAME
    DescriptionLast 4 characters of
    XREQ_NAME corresponds to

    ODOC

    Documentation tab content stored in ODOC tableODOC.ODOC_Name
    XREQ

    Scripting tab content stored in OT table

    OT.OT_Type
    The last part identifies the row uniquely in either the ODOC or OT tabe. Tab content can be found in ODOC_Content and OT_Content, respectively.

    Here’s an Oracle query that will list the complete contents of both ODOC and OT tabs of XREQ objects.
    CREATE OR REPLACE FUNCTION blob2clob (p_blob BLOB)
       RETURN CLOB
    IS
       l_clob           CLOB;
       l_dest_offsset   INTEGER := 1;
       l_src_offsset    INTEGER := 1;
       l_lang_context   INTEGER := DBMS_LOB.default_lang_ctx;
       l_warning        INTEGER;
    BEGIN
       IF p_blob IS NULL
       THEN
          RETURN NULL;
       END IF;

       DBMS_LOB.createTemporary (lob_loc => l_clob, cache => FALSE);

       DBMS_LOB.converttoclob (dest_lob       => l_clob,
                               src_blob       => p_blob,
                               amount         => DBMS_LOB.lobmaxsize,
                               dest_offset    => l_dest_offsset,
                               src_offset     => l_src_offsset,
                               blob_csid      => DBMS_LOB.default_csid,
                               lang_context   => l_lang_context,
                               warning        => l_warning);
       RETURN l_clob;
    END;

    WITH BIND_PARMS                      -- Set client number and object name here
         AS (SELECT 0 AS CLIENT_NUMBER, '%' AS OBJECT_NAME FROM DUAL),
         XREQ_OBJECTS
         AS (  SELECT OH_Idnr,
                      OH_Client,
                      OH_Name,
                      OH_OType,
                      XREQ_Name,
                      CASE SUBSTR (XREQ_Name, 1, 4)
                         WHEN 'XREQ' THEN 'OT'
                         WHEN 'ODOC' THEN 'ODOC'
                      END
                         AS XREQ_SubType,
                      CASE SUBSTR (XREQ_Name, 1, 4)
                         WHEN 'ODOC' THEN SUBSTR (XREQ_Name, 17, 4)
                         ELSE '    '
                      END
                         AS XREQ_ODOC_SubType,
                      XREQ_CONTENTLEN,
                      CASE SUBSTR (XREQ_Name, 1, 4)
                         WHEN 'XREQ' THEN TO_NUMBER (SUBSTR (XREQ_Name, 19, 4))
                         ELSE NULL
                      END
                         AS XREQ_OT_SubType,
                      XREQ_CONTENT
                 FROM XREQ LEFT JOIN OH ON SUBSTR (XREQ_Name, 6, 10) = OH_IDNR
                WHERE OH_Client = (SELECT CLIENT_NUMBER FROM BIND_PARMS)
             ORDER BY OH_Client,
                      OH_Name,
                      SUBSTR (XREQ_Name, 1, 4),
                      SUBSTR (XREQ_Name, LENGTH (XREQ_Name) - 3, 4)),
         ODOC_TABS
         AS (  SELECT OH_Client,
                      OH_Idnr,
                      ODOC_Name,
                      ODOC_Type,
                      RTRIM (
                         XMLCAST (
                            XMLAGG (
                               XMLELEMENT (e, blob2clob (ODOC_Content) || CHR (10))
                               ORDER BY ODOC_Lnr) AS CLOB))
                         AS ODOC_CONTENT_CLOB
                 FROM ODOC LEFT OUTER JOIN OH ON ODOC_OH_Idnr = OH_Idnr
                WHERE OH_Client = (SELECT CLIENT_NUMBER FROM BIND_PARMS)
             GROUP BY OH_Client,
                      OH_Idnr,
                      ODOC_Name,
                      ODOC_Type
             ORDER BY OH_Client,
                      OH_Idnr,
                      ODOC_Name,
                      ODOC_Type),
         OT_TABS
         AS (  SELECT OH_Client,
                      OH_Idnr,
                      OT_TYPE,
                      RTRIM (
                         XMLCAST (
                            XMLAGG (XMLELEMENT (e, OT_Content || CHR (10))
                                    ORDER BY OT_Lnr) AS CLOB))
                         AS OT_CONTENT_CLOB
                 FROM OT LEFT OUTER JOIN OH ON OT_OH_Idnr = OH_Idnr
                WHERE OH_Client = (SELECT CLIENT_NUMBER FROM BIND_PARMS)
             GROUP BY OH_Client, OH_Idnr, OT_Type
             ORDER BY OH_Client, OH_Idnr, OT_Type),
         MAIN_QUERY
         AS (  SELECT OH_Client,
                      OH_IDnr,
                      OH_OType,
                      OH_Name,
                      XREQ_Name,
                      XREQ_SubType,
                      XREQ_ODOC_SubType,
                      XREQ_ContentLen,
                      XREQ_OT_SubType,
                      XREQ_Content,
                      CASE XREQ_SubType
                         WHEN 'ODOC'
                         THEN
                            (SELECT ODOC_CONTENT_CLOB
                               FROM ODOC_TABS
                              WHERE     OH_Client = ODOC_TABS.OH_Client
                                    AND OH_Idnr = ODOC_TABS.OH_Idnr
                                    AND XREQ_ODOC_SubType = ODOC_TABS.ODOC_Name
                                    AND ROWNUM = 1)
                         WHEN 'OT'
                         THEN
                            (SELECT OT_CONTENT_CLOB
                               FROM OT_TABS
                              WHERE     OH_Client = OT_TABS.OH_Client
                                    AND OH_Idnr = OT_TABS.OH_Idnr
                                    AND XREQ_OT_SubType = OT_TABS.OT_Type
                                    AND ROWNUM = 1)
                      END
                         AS TAB_CONTENT
                 FROM XREQ_OBJECTS
             ORDER BY OH_Name, XREQ_Name)
    SELECT *
      FROM MAIN_QUERY
    WHERE     OH_Client = (SELECT CLIENT_NUMBER FROM BIND_PARMS)
           AND OH_NAME like (SELECT OBJECT_NAME FROM BIND_PARMS)
           AND ROWNUM < 101
    I found the blob2clob function on StackOverflow. It was necessitated by my wish to display tab content in one column regardless of whether it came from ODOC or OT. There might be a more straightforward way to convert BLOBs to CLOBs, but this worked well enough. The subqueries defining TAB_CONTENT were a bit of a hassle. I had to add the predicate AND ROWNUM = 1 even though as far a I could tell the other predicates should have been specific enough to guarantee uniqueness. I realize the query is not optimized for efficiency.

    This was certainly an instructive exercise. Thanks for the input, PhilippElmer, Christian_Boeck_57, and petwir!


  • 10.  XREQ objects

    Posted 04-07-2017 09:45 AM
    There are also many other hidden objects related to XREQ objects. These include JOBI, PRPT, and VARA objects.


  • 11.  XREQ objects

    Posted 10-31-2017 06:21 AM
    I discovered this morning that the XRequest API class is newly documented in v12.1. The class description confirms my understanding of the role XREQ objects play:
    XREQ Object : Each X-request has an according X-request object XREQ available in the the AE database that specifies how a request is processed. The processing instructions are written in a specific script (Automic script and reserved internal functions of DSFUN). These objects are of type XREQ and exclusively stored in client 0. They are not visible for the customer an hidden in folder 0000\XREQ\. XREQ objects are part of the AE initial data (OH_Idnr lower than 100.000) and can be imported/exported via DB.LOAD/UNLOAD (uc_ini.txt).
    This class is not usable by mere mortals, because the com.uc4.api.objects.XRequest package is not included in the Java Application Interface made available to customers.

    Update 2017.11.02:
    I was mistaken about this. I had been using the v12.0.3 JAR file. Once I switched to the v12.1 JAR file, I was able to use the class. This class is one of many added recently.
    ...
    log.writeLine(String.format("Opening %s in read-only mode.", objectName));
    XMLRequest xmlReq = new OpenObject(new UC4ObjectName(objectName), true, true);
    localUC4Object.sendRequestAndWait(xmlReq);
    Common.getMsgBox(xmlReq);
    OpenObject openObj = (OpenObject) xmlReq;
    UC4Object uc4Object = openObj.getUC4Object();
    log.writeLine(String.format("Reading basic information about object %s.", objectName));
    String objName = uc4Object.getName();
    String objType = uc4Object.getType();
    String objIdnr = uc4Object.getIdnr();
    boolean objIsExec = uc4Object.isExecutable();
    log.writeLine(String.format("Object name   :  %s", objName));
    log.writeLine(String.format("Object type   :  %s", objType));
    log.writeLine(String.format("Object ID     :  %s", objIdnr));
    if ("XREQ".equals(objType)) {
    // Cast object to XRequest
        XRequest xRequest = (XRequest) uc4Object;
    }
    ...
    Even though the class is usable, it’s not really useful, because there’s an authorization mechanism that prevents reading some objects, including XREQ objects. If I try to open one of the XREQ objects, the AE returns an error:
    U000000009 'XDESKTOP': Access denied
    The documentation does not provide much information about message U00000009.

    At first I thought this might have to do with just the object ID — i.e., that the mechanism prevents opening any object with an OH_Idnr less than 100,000. However, I can open UC_CLIENT_SETTINGS and other initial data objects in client 0, and all of these objects have OH_Idnr values less than 100,000. Perhaps the mechanism blocks access based OH_OType.

    I gather there is some sort of switch in the AE that Automic developers set to allow them to work with these objects. For now, the SQL query above is still the most straightforward way for mere mortals to examine these objects.