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.