We are running 10 night reports.
Anyone want the XML or documentation just send me an email.
Anyone have additional reports, please contribute them to the success of everyone.
Thanks to everyone
Be Well,
Phil Frankford
AH 24HR ABEND History JOBS.GROOVY.SQL.REPORT.AH_24HR_ABENDS.1
SELECT ah_alias as Job
,ah_idnr as runid
,ah_otype as type
,ah_title as title
,ah_hostdst as host
,ah_status as status
,ah_RetCode as RC
,m.msgtx_text as status_text
,ah_timestamp1 as activation_time
,ah_timestamp2 as start_time
,ah_timestamp4 as end_time
FROM ah a, msgtx m
WHERE ah_client = ?
and (ah_hostdst in (?) or ? IS NULL)
and (ah_status in (?) or ? IS NULL)
and (ah_otype IN (?) or ? IS NULL)
and m.msgtx_msgl_short = ?
and CASE
WHEN 'ACTIVATION' = ? THEN ah_timestamp1
WHEN 'START' = ? THEN ah_timestamp2
ELSE ah_timestamp4
END between ? and ?
and m.msgtx_msg_idnr = a.ah_status and ah_status between 1800 and 1899
order by ah_otype, ah_status, ah_timestamp1
AH 24HR Good History JOBS.GROOVY.SQL.REPORT.AH_24HR_GOOD.1
SELECT ah_alias as Job
,ah_idnr as runid
,ah_otype as type
,ah_title as title
,ah_hostdst as host
,ah_status as status
,ah_RetCode as RC
,m.msgtx_text as status_text
,ah_timestamp1 as activation_time
,ah_timestamp2 as start_time
,ah_timestamp4 as end_time
FROM ah a, msgtx m
WHERE ah_client = ?
and (ah_hostdst in (?) or ? IS NULL)
and (ah_status in (?) or ? IS NULL)
and (ah_otype IN (?) or ? IS NULL)
and m.msgtx_msgl_short = ?
and CASE
WHEN 'ACTIVATION' = ? THEN ah_timestamp1
WHEN 'START' = ? THEN ah_timestamp2
ELSE ah_timestamp4
END between ? and ?
and m.msgtx_msg_idnr = a.ah_status and ah_status between 1900 and 1920 and ah_otype <> 'REPORT' and ah_otype <> 'USER'
order by ah_otype, ah_status, ah_timestamp1
AH 24HR History Status JOBS.GROOVY.SQL.REPORT.AH_24HR_STATUS.1
SELECT ah_alias as Job
,ah_idnr as runid
,ah_otype as type
,ah_title as title
,ah_hostdst as host
,ah_status as status
,ah_RetCode as RC
,m.msgtx_text as status_text
,ah_timestamp1 as activation_time
,ah_timestamp2 as start_time
,ah_timestamp4 as end_time
FROM ah a, msgtx m
WHERE ah_client = ?
and (ah_hostdst in (?) or ? IS NULL)
and (ah_status in (?) or ? IS NULL)
and (ah_otype IN (?) or ? IS NULL)
and m.msgtx_msgl_short = ?
and CASE
WHEN 'ACTIVATION' = ? THEN ah_timestamp1
WHEN 'START' = ? THEN ah_timestamp2
ELSE ah_timestamp4
END between ? and ?
and m.msgtx_msg_idnr = a.ah_status
order by ah_otype, ah_status, ah_timestamp1
24 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.24HR_JOBJCL_HISTORY.1
with maxrc (oh_idnr, ov_vname, ov_value) as
(
select oh_idnr
,ov_vname
,ov_value
from ov, oh
where oh_idnr = ov_oh_idnr
and oh_client = ?
and oh_deleteflag = 0
and ov_vname = '&MAXRC#'
)
,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLFILE#'
)
,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLLIB#'
)
,hosts (oh_idnr, jba_hostdst, jba_logindst) as
(
select oh_idnr
,jba_hostdst
,jba_logindst
from oh, jba
where oh.oh_idnr = jba.jba_oh_idnr
and oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
)
select oh.oh_idnr as runit
,oh_name as jobname
,oh_archive1 as job
,jcl.opud_value as jcl
,jcllib.opud_value as jcllib
,oh_crdate as cre_date
,oh_moddate as mod_date
,maxrc.ov_value as maxrc
,oh_hostattrtypedst as host
,jba_hostdst as host_group
,jba_logindst as jba_login
from oh
LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
where oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
and (oh_moddate >= GetDate() - 1)
order by oh_name, oh_moddate
48 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.48HR_JOBJCL_HISTORY.1
with maxrc (oh_idnr, ov_vname, ov_value) as
(
select oh_idnr
,ov_vname
,ov_value
from ov, oh
where oh_idnr = ov_oh_idnr
and oh_client = ?
and oh_deleteflag = 0
and ov_vname = '&MAXRC#'
)
,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLFILE#'
)
,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLLIB#'
)
,hosts (oh_idnr, jba_hostdst, jba_logindst) as
(
select oh_idnr
,jba_hostdst
,jba_logindst
from oh, jba
where oh.oh_idnr = jba.jba_oh_idnr
and oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
)
select oh.oh_idnr as runit
,oh_name as jobname
,oh_archive1 as job
,jcl.opud_value as jcl
,jcllib.opud_value as jcllib
,oh_crdate as cre_date
,oh_moddate as mod_date
,maxrc.ov_value as maxrc
,oh_hostattrtypedst as host
,jba_hostdst as host_group
,jba_logindst as jba_login
from oh
LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
where oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
and (oh_moddate >= GetDate() - 4)
order by oh_name, oh_moddate
72 Hour Job/JCL Mod History JOBS.GROOVY.SQL.REPORT.72HR_JOBJCL_HISTORY.1
with maxrc (oh_idnr, ov_vname, ov_value) as
(
select oh_idnr
,ov_vname
,ov_value
from ov, oh
where oh_idnr = ov_oh_idnr
and oh_client = ?
and oh_deleteflag = 0
and ov_vname = '&MAXRC#'
)
,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLFILE#'
)
,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLLIB#'
)
,hosts (oh_idnr, jba_hostdst, jba_logindst) as
(
select oh_idnr
,jba_hostdst
,jba_logindst
from oh, jba
where oh.oh_idnr = jba.jba_oh_idnr
and oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
)
select oh.oh_idnr as runit
,oh_name as jobname
,oh_archive1 as job
,jcl.opud_value as jcl
,jcllib.opud_value as jcllib
,oh_crdate as cre_date
,oh_moddate as mod_date
,maxrc.ov_value as maxrc
,oh_hostattrtypedst as host
,jba_hostdst as host_group
,jba_logindst as jba_login
from oh
LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
where oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
and (oh_moddate >= GetDate() - 7)
order by oh_name, oh_moddate
Job/JCL History JOBS.GROOVY.SQL.REPORT.JOBJCL_HISTORY.1
with maxrc (oh_idnr, ov_vname, ov_value) as
(
select oh_idnr
,ov_vname
,ov_value
from ov, oh
where oh_idnr = ov_oh_idnr
and oh_client = ?
and oh_deleteflag = 0
and ov_vname = '&MAXRC#'
)
,jcl (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLFILE#'
)
,jcllib (oh_idnr, opu_name, opud_vname, opud_value) as
(
select oh_idnr
,opu_name
,opud_vname
,opud_value
from oh, opu, opud
where oh_deleteflag = 0
and oh_client = ?
and oh_idnr = opu_oh_idnr
and opu_oh_idnr = opud_oh_idnr
and opud_vname = 'JCLLIB#'
)
,hosts (oh_idnr, jba_hostdst, jba_logindst) as
(
select oh_idnr
,jba_hostdst
,jba_logindst
from oh, jba
where oh.oh_idnr = jba.jba_oh_idnr
and oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
)
select oh.oh_idnr as runit
,oh_name as jobname
,oh_archive1 as job
,jcl.opud_value as jcl
,jcllib.opud_value as jcllib
,oh_crdate as cre_date
,oh_moddate as mod_date
,maxrc.ov_value as maxrc
,oh_hostattrtypedst as host
,jba_hostdst as host_group
,jba_logindst as jba_login
from oh
LEFT OUTER JOIN jcl ON oh.oh_idnr = jcl.oh_idnr
LEFT OUTER JOIN jcllib ON oh.oh_idnr = jcllib.oh_idnr
LEFT OUTER JOIN maxrc ON oh.oh_idnr = maxrc.oh_idnr
LEFT OUTER JOIN hosts ON oh.oh_idnr = hosts.oh_idnr
where oh_otype = 'JOBS'
and oh_client = ?
and oh_deleteflag = 0
order by oh_name, oh_moddate
Prior 24 HR Long Running Jobs JOBS.GROOVY.SQL.REPORT.24HR_LONG_RUNNING_JOBS.1
select ah_idnr as runid
,ah_otype as type
,ah_alias as job
,ah_title as title
,ah_hostdst as host
,ah_status as status
,m.msgtx_text as status_text
,ah_runtime as runtime
,ah_timestamp1 as activation_time
,ah_timestamp2 as start_time
,ah_timestamp4 as end_time
FROM ah a, msgtx m
WHERE ah_client = ?
and (ah_status in (?) or ? IS NULL)
and (ah_otype IN (?) or ? IS NULL)
and ah_timestamp1 between ? and ?
and m.msgtx_msg_idnr = a.ah_status
and ah_runtime >= (? * 60 * 60) + (? * 60) + ?
and m.msgtx_msgl_short = ?
order by ah_alias, ah_timestamp2
External Schedule Jobs JOBS.GROOVY.SQL.REPORTS.EXTERNALS
with tasks (workflow, object, alias) as
(
select oh_name, jpp_object, jpp_alias
from oh, jpp
where oh_client = ?
and oh_deleteflag = 0
and oh_otype = 'JOBP'
and oh_idnr = jpp_oh_idnr
and jpp_otype <> '<XTRNL>'
)
select oh_name
,jpp_object as ext_object
,jpp_parentobject as ext_parent
,jpp_alias as ext_alias
,jpp_extwhen
,tasks.*
from oh, jpp
LEFT OUTER JOIN tasks ON jpp_parentobject = workflow and jpp_object = object
where oh_client = ?
and oh_deleteflag = 0
and oh_otype = 'JOBP'
and oh_idnr = jpp_oh_idnr
and jpp_otype = '<XTRNL>'
order by oh_name, jpp_object
Wait Job Status JOBS.GROOVY.SQL.REPORT.WAIT_JOB_STATUS.1
select a.eh_client as Client
,a.eh_Alias as WorkflowName
,a.eh_ah_idnr as Runid
,a.eh_status as RC
,case a.eh_status
when 1850 then 'Inactive'
when 1560 then 'Blocked'
when 1698 then 'WaitingForStartTime'
when 1709 then 'WaitingForParallel'
else
isnull(
(select distinct 'Active '
from eh b
where b.eh_ah_idnr = b.EH_ParentAct
and not b.eh_starttime is null and b.eh_endtime is null
)
,case a.EH_External
when 1 then 'WaitingForExternal'
else 'Processing'
end
)
end as WorkflowState
from eh a
where a.eh_otype = 'JOBP'
order by a.eh_status, a.eh_Alias