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

Discussion created by Michael_Lowry on Aug 25, 2017
Latest reply on Aug 26, 2017 by Michael_Lowry

Like • Show 0 Likes0
Comment • 3

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:

Update UC0.MAL.TOOLS.LJDFLF.UC4_SYSTEMS.VARA_STATIC so that it contains a list of your AE systems.
Update the pre-process of both SQL jobs so that they set the connection object name appropriately for your environment.
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.

Attachments

LOOKUP_JOB_DETAILS_FROM_LOG_FILENAME 2017.08.26 (better query) uc4_export.xml32.9 KB

Outcomes

Michael_Lowry Aug 26, 2017 1:27 PM

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.

Attachments

LOOKUP_JOB_DETAILS_FROM_LOG_FILENAME (simplified and better query) 2017.08.26 uc4_export.xml24.7 KB

Actions

Michael_Lowry Jul 4, 2018 9:07 AM

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.

Actions

Michael_Lowry Aug 26, 2017 1:36 PM

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.

Actions

3 Replies

Michael_Lowry Aug 26, 2017 1:27 PM
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.
Attachments
- LOOKUP_JOB_DETAILS_FROM_LOG_FILENAME (simplified and better query) 2017.08.26 uc4_export.xml24.7 KB
Like • Show 0 Likes0
Actions
Michael_Lowry Jul 4, 2018 9:07 AM
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.
Like • Show 0 Likes0
Actions
Michael_Lowry Aug 26, 2017 1:36 PM
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.
Like • Show 0 Likes0
Actions

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

Attachments

Outcomes

Attachments

Related Content

Recommended Content

Incoming Links