Automic Workload Automation

  • 1.  Look up the details of the job that wrote a particular log file (v2)

    Posted Aug 25, 2017 06:18 AM
      |   view attached
    Here is an updated version of the workflow for looking up the details of the job that wrote a log file that I first published in March 2016. Here is a list of changes:
    • No longer limited to jobs in the particular AE system in which this workflow is run. If you set up connection objects for each of the AE systems’ databases, you can run this workflow in any AE system, and it can pull the necessary info from another system if necessary.
    • No longer dependent on SQL VARAs. Instead, the workflow now uses the approach I described earlier today for creating anEXEC VARA wrapper around an SQL job.
    • A greatly simplified SQL query eliminates the costly AH subquery and, and simply looks up job details based on the run ID returned by converting the base-26 alphanumeric job ID to the decimal equivalent usingALPHA2RUNNR.
    You’ll have to make a few preliminary configuration changes to adapt the batch to your environment:
    1. Update UC0.MAL.TOOLS.LJDFLF.UC4_SYSTEMS.VARA_STATIC so that it contains a list of your AE systems.
    2. Update the pre-process of both SQL jobs so that they set the connection object name appropriately for your environment.
    3. Make sure that the referenced connection object(s) exist and work correctly. These connection objects should be able to read (SELECT) from your respective AE system(s)’ DB(s). There should be one connection object for each AE system you defined in the static VARA.
    Enjoy!

    Update 2017.08.26: I updated the workflow to use a greatly simplified SQL query.



  • 2.  Look up the details of the job that wrote a particular log file (v2)

    Posted Aug 25, 2017 09:10 AM
      |   view attached
    I’m also attaching a simpler version of this workflow that completely eliminates the use of VARA objects. This simplified workflow has fewer moving parts, and is easier to troubleshoot; however, it’s also less useful as a demonstration of the approach of using an EXEC VARA as a wrapper for an SQL job.

    In some cases, it’s actually beneficial or necessary to use a VARA object for this sort of thing. This use case is not one of these cases though! :smile:

    Update 2017.08.26: I updated the workflow to use a greatly simplified SQL query.


  • 3.  Look up the details of the job that wrote a particular log file (v2)

    Posted Aug 25, 2017 12:17 PM

    The query that looks up job details based on the report file name can take a long time on systems with lots of statistical records (in the AH table). I was looking for a way to speed up the query when I realized that the query is much more complicated than it needs to me. This is because the alphanumeric file name of the report encodes the run ID of the job that created it. Converting it is easy and in fact the script was already doing this:

    :PSET &JobAlphaName#=STR_CUT(&JOB_LOGFILE#,2)
    :IF STR_FIND(&JobAlphaName#,".TXT") = 8
    : PSET &JobAlphaName#=STR_SUB(&JobAlphaName#,".TXT","")
    :ENDIF
    :PSET &RunID#=ALPHA2RUNNR(&JobAlphaName#)
    :PRINT "Job run ID: &RunID#"

     

    The variable &RunID# will contain the run ID of the job that created the report file. Knowing this, I greatly simplified the SQL query:

    with orig_job as
    (
    select AH_Idnr as Job_Run_ID, AH_Alias as Job_Alias, OH_Name as Job_Name,
    OH_ModUserIDNr
    from AH,OH
    where AH_OH_Idnr = OH_Idnr
    and AH_Idnr = &RunID#
    ),

    user_details as
    (
    select OH_Idnr, OH_Name as User_ID, USR_FirstName, USR_LastName,
    USR_EMail1,USR_DstngshdName,USR_LastSession
    from USR,OH
    where USR_OH_IDNr = OH_Idnr
    )

    select Job_Run_ID, Job_Alias, Job_Name, User_ID, USR_FirstName, USR_LastName,
    USR_EMail1,USR_DstngshdName,USR_LastSession
    from orig_job,user_details
    where orig_job.OH_ModUserIDNr = user_details.OH_Idnr

     

    The AH_IDnr uniquely identifies the job, so the task type, client number, and agent name are no long used in additional predicates. This query runs very quickly, even on large AE DBs.



  • 4.  Look up the details of the job that wrote a particular log file (v2)

    Posted Aug 26, 2017 09:36 AM
    I updated both versions of the workflow (the one that uses VARAs, and the one that does not) so that they both use the improved query above.