Automic Workload Automation

 View Only
  • 1.  SAP job attributes & SAP job sub-types

    Posted May 10, 2023 10:11 AM
    Edited by Michael A. Lowry May 11, 2023 09:59 AM

    This SQL query of the UC_SVALM table lists the sub-fields of JBA_Rest for SAP jobs, and the corresponding SAP job attributes.

    -- Show JBA_Rest sub-field IDs and corresponding attribute names for SAP R3 jobs
    select SVALM_RestID,SVALM_Attname
    from UC_SVALM
    where SVALM_Otype = 'JOBS'
    AND SVALM_ATTDBFNAME = 'jba_rest'
    and SVALM_HostAttrType = 'R3'
    order by SVALM_RestID;

    SVALM_RestID SVALM_Attname
    1 SAP_JOB_TYPE
    9 JOB_NAME
    9 JN
    A SAP_LANG
    B SAP_DST_SYSTEM
    C JC
    C JOB_CLASS
    E SAP_JOB_DELETE
    I SAP_STARTMODE
    L SAP_OVERRIDE_LOGIN
    X SAP_NOPRINT
    Y SAP_DELIVER
    Z SAP_STATUSBYMAIL
    e SAP_JOB_DEL_NOT_ON_ERROR
    u SAP_RECIPIENT
    v SAP_ADDRESSTYPE
    w SAP_EXPRESS
    x SAP_COPY
    y SAP_BLINDCOPY
    z SAP_NOFORWARD


    Using the data returned by the above query, one can decipher the different values of JBA_Rest of SAP R3 jobs. (JBA_Rest stores job attributes in the JBA table.) The query below displays this information for SAP R3 jobs.

    -- List attributes of SAP R3 jobs
    SELECT OH_Name, OH_Title, OH_Idnr, JBA_HostDst, JBA_LoginDst, JBA_Rest
    FROM OH JOIN JBA ON JBA_OH_Idnr = OH_Idnr 
    WHERE OH_DeleteFlag = 0
    AND OH_HostAttrTypeDst = 'R3'
    AND JBA_HostAttrType = 'R3'
    ORDER BY OH_Idnr


    Building on the above understanding of the meanings of the JBA_Rest sub-fields, one can use REGEXP_SUBSTR to extract the values of these sub-fields into dedicated columns.

    -- List attributes of SAP R3 jobs including sub-fields of JBA_Rest
    SELECT OH_Name, OH_Title, OH_Idnr, JBA_HostDst, JBA_LoginDst,
    REGEXP_SUBSTR(JBA_Rest, '(1)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_TYPE",
    REGEXP_SUBSTR(JBA_Rest, '(9)=['']([^'']*)['']', 1, 1, 'c', 2) AS "JOB_NAME",
    REGEXP_SUBSTR(JBA_Rest, '(A)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_LANG",
    REGEXP_SUBSTR(JBA_Rest, '(B)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_DST_SYSTEM",
    REGEXP_SUBSTR(JBA_Rest, '(C)=['']([^'']*)['']', 1, 1, 'c', 2) AS "JOB_CLASS",
    REGEXP_SUBSTR(JBA_Rest, '(E)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_DELETE",
    REGEXP_SUBSTR(JBA_Rest, '(I)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_STARTMODE",
    REGEXP_SUBSTR(JBA_Rest, '(L)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_OVERRIDE_LOGIN",
    REGEXP_SUBSTR(JBA_Rest, '(X)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_NOPRINT",
    REGEXP_SUBSTR(JBA_Rest, '(Y)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_DELIVER",
    REGEXP_SUBSTR(JBA_Rest, '(Z)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_STATUSBYMAIL",
    REGEXP_SUBSTR(JBA_Rest, '(e)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_JOB_DEL_NOT_ON_ERROR",
    REGEXP_SUBSTR(JBA_Rest, '(u)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_RECIPIENT",
    REGEXP_SUBSTR(JBA_Rest, '(v)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_ADDRESSTYPE",
    REGEXP_SUBSTR(JBA_Rest, '(w)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_EXPRESS",
    REGEXP_SUBSTR(JBA_Rest, '(x)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_COPY",
    REGEXP_SUBSTR(JBA_Rest, '(y)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_BLINDCOPY",
    REGEXP_SUBSTR(JBA_Rest, '(z)=['']([^'']*)['']', 1, 1, 'c', 2) AS "SAP_NOFORWARD"
    FROM OH JOIN JBA ON JBA_OH_Idnr = OH_Idnr 
    WHERE OH_DeleteFlag = 0
    AND OH_HostAttrTypeDst = 'R3'
    AND JBA_HostAttrType = 'R3'
    ORDER BY OH_Idnr


    This approach should also work with other job types that store attributes in JBA_Rest.



    Next, I have a question. Hopefully, someone at @Broadcom Community can answer.

    I'm looking for a way to distinguish between the different sub-types of SAP job: SAP_ABAP, SAP_JAVA, and SAP_PI.

    The UC_SVALM entry with SVALM_RestID 1 has the SVALM_AttName SAP_JOB_TYPE, but the value of sub-field 1 in JBA_Rest looks the same ('0') for all our SAP jobs, regardless of the SAP job sub-type.

    How does the AE distinguish jobs of type SAP_ABAP, SAP_JAVA, and SAP_PI? Where is this information stored in the database?



  • 2.  RE: SAP job attributes & SAP job sub-types

    Posted May 12, 2023 06:53 AM
    Edited by Michael A. Lowry May 12, 2023 08:00 AM

    This SQL query will list the distinct SAP JCL commands that are used in SAP jobs in the AE system.

    -- List distinct SAP JCL commands of SAP R3 jobs (from the OT table)
    SELECT DISTINCT REGEXP_SUBSTR(CAST(OT_Content AS VARCHAR2(100)), '^(\w*) ?.*$', 1, 1, 'i', 1) AS "SAP_JCL_COMMAND"
    FROM OH
    JOIN JBA ON JBA_OH_IDNR = OH_Idnr 
    LEFT OUTER JOIN OT ON OT_OH_IDNR = OH_Idnr 
    WHERE 1=1
    AND OH_DeleteFlag = 0
    AND OH_HOSTATTRTYPEDST = 'R3'
    AND JBA_HOSTATTRTYPE = 'R3'
    AND OT_Content NOT LIKE '!%'
    AND OT_Content NOT LIKE ':%'
    AND REGEXP_SUBSTR(CAST(OT_Content AS VARCHAR2(100)), '^(\w*) ?.*$', 1, 1, 'i', 1) IS NOT NULL
    ORDER BY REGEXP_SUBSTR(CAST(OT_Content AS VARCHAR2(100)), '^(\w*) ?.*$', 1, 1, 'i', 1)


    Sample output:

    SAP_JCL_COMMAND
    R3_ACTIVATE_JOBS
    R3_ACTIVATE_REPORT
    R3_CALL_TRANSACTION
    R3_GET_JOB_SPOOL
    R3_GET_SESSIONS
    R3_GET_SPOOLREQUESTS
    R3_GET_VARIANT_CONTENTS
    R3_MODIFY_VARIANT
    R3_SEND_SPOOL_REQUEST
    R3_SET_BDCDATA
    XI_GET_CHANNEL
    XI_SET_CHANNEL


    Note: This statement will not catch any SAP JCL statements that happen to be specified inside a :DATA statement.



  • 3.  RE: SAP job attributes & SAP job sub-types

    Posted May 23, 2023 06:09 AM
    Edited by Michael A. Lowry May 23, 2023 06:09 AM

    Does anyone have an answer to this question?

    How does the AE distinguish jobs of type SAP_ABAP, SAP_JAVA, and SAP_PI? Where is this information stored in the database?

    Thanks in advance.



  • 4.  RE: SAP job attributes & SAP job sub-types

    Posted Jun 02, 2023 02:58 PM
    Edited by Michael A. Lowry Jun 02, 2023 03:03 PM

    On 11 May 2023, I wrote:

    I'm looking for a way to distinguish between the different sub-types of SAP job: SAP_ABAP, SAP_JAVA, and SAP_PI.

    The UC_SVALM entry with SVALM_RestID 1 has the SVALM_AttName SAP_JOB_TYPE, but the value of sub-field 1 in JBA_Rest looks the same ('0') for all our SAP jobs, regardless of the SAP job sub-type.

    How does the AE distinguish jobs of type SAP_ABAP, SAP_JAVA, and SAP_PI? Where is this information stored in the database?

    Broadcom Support confirmed for me that subfield 1 of JBA_Rest does indeed store this information, using the following mapping:

    • 0: ABAP
    • 1: PI
    • 2: Java

    I'm not sure how I overlooked this before. Perhaps I was looking at a subset of SAP jobs that were all ABAP jobs.