Want to join avg runtime per job to job def in CME
Want to join avg runtime per job to job def in CME
I am trying to make a report of all production jobs and predecessors. I also want to add avg runtime column to this query.
Anyideas???????
Basically want to join DEF_VER_JOB to avg runtime in Active Net JOB table.
Thank You ----- Goo
Here is the query in SQL Server
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,SUBSTRING(a.JOB_NAME,3,10) AS 'JOB'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'DAY'
,ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':' +
SUBSTRING(a.FROM_TIME,3,2),'') AS 'START'
,SUBSTRING(e.CONDITION,3,25) AS 'IN CONDITION'
,REPLACE(a.DESCRIPTION,',',' ') AS 'DESCRIPTION'
from DOC_CTM_EM64.dbo.DEF_VER_JOB a
JOIN DOC_CTM_EM64.dbo.DEF_VER_TABLES c
on (a.TABLE_ID=c.TABLE_ID)
LEFT OUTER JOIN DOC_CTM_EM64.dbo.DEF_VER_LNKI_P e
on ((a.table_id = e.table_id) AND (a.job_id = e.job_id))
where
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
and SUBSTRING(c.SCHED_TABLE,3,5) = 'BKF'
AND a.IS_CURRENT_VERSION = 'Y'
order by 1,2
Anyideas???????
Basically want to join DEF_VER_JOB to avg runtime in Active Net JOB table.
Thank You ----- Goo
Here is the query in SQL Server
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,SUBSTRING(a.JOB_NAME,3,10) AS 'JOB'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'DAY'
,ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':' +
SUBSTRING(a.FROM_TIME,3,2),'') AS 'START'
,SUBSTRING(e.CONDITION,3,25) AS 'IN CONDITION'
,REPLACE(a.DESCRIPTION,',',' ') AS 'DESCRIPTION'
from DOC_CTM_EM64.dbo.DEF_VER_JOB a
JOIN DOC_CTM_EM64.dbo.DEF_VER_TABLES c
on (a.TABLE_ID=c.TABLE_ID)
LEFT OUTER JOIN DOC_CTM_EM64.dbo.DEF_VER_LNKI_P e
on ((a.table_id = e.table_id) AND (a.job_id = e.job_id))
where
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
and SUBSTRING(c.SCHED_TABLE,3,5) = 'BKF'
AND a.IS_CURRENT_VERSION = 'Y'
order by 1,2
Last edited by DocGoo on 07 Nov 2012 2:57, edited 2 times in total.
- pmdeshayes
- Nouveau
- Posts: 23
- Joined: 07 Jul 2008 12:00
SQL Server Report of All Production Jobs
Here is the query I came up with, it shows the following
--Group Names
--Job Names
--Job Start Time
--Job Avg Run Time
--Job Description
--Precerssor Job Name
--Precerssor Job Description
--Other Nuggets
Feel free to use and send any comments regarding problems or additional enhancements. Once run I put in Excel for duplicate removal and job highlighting bands per job. Also adding titles and formatting for business review. I can find most answers to questions in meetings with this. But then we only run abouat 1000 jobs.
Thank for the help and enjoy ----- Goo
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,SUBSTRING(a.JOB_NAME,3,10) AS 'JOB'
,ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,1) 'MIN'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'DAY'
,ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':' +
SUBSTRING(a.FROM_TIME,3,2),'') AS 'START'
,REPLACE(a.DESCRIPTION,',',' ') AS ' JOB DESCRIPTION'
,ISNULL(SUBSTRING(e.CONDITION,3,25),'') AS 'IN CONDITION'
,
ISNULL((SELECT MAX(d.DESCRIPTION) FROM XX.DEF_VER_JOB d
where ISNULL(SUBSTRING(d.JOB_NAME, 3, LEN(d.JOB_NAME) ),'') =
ISNULL ( SUBSTRING(e.CONDITION, 3, NULLIF( CHARINDEX( '-', e.CONDITION ) -3,-3) ), '' )
),'')AS 'PRED_DESC'
from XX.DEF_VER_JOB a
JOIN XX.DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN XX.dbo.AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN XX.DEF_VER_LNKI_P e
ON ( A.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
where
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
order by 1,2
--Group Names
--Job Names
--Job Start Time
--Job Avg Run Time
--Job Description
--Precerssor Job Name
--Precerssor Job Description
--Other Nuggets
Feel free to use and send any comments regarding problems or additional enhancements. Once run I put in Excel for duplicate removal and job highlighting bands per job. Also adding titles and formatting for business review. I can find most answers to questions in meetings with this. But then we only run abouat 1000 jobs.
Thank for the help and enjoy ----- Goo
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,SUBSTRING(a.JOB_NAME,3,10) AS 'JOB'
,ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,1) 'MIN'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'DAY'
,ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':' +
SUBSTRING(a.FROM_TIME,3,2),'') AS 'START'
,REPLACE(a.DESCRIPTION,',',' ') AS ' JOB DESCRIPTION'
,ISNULL(SUBSTRING(e.CONDITION,3,25),'') AS 'IN CONDITION'
,
ISNULL((SELECT MAX(d.DESCRIPTION) FROM XX.DEF_VER_JOB d
where ISNULL(SUBSTRING(d.JOB_NAME, 3, LEN(d.JOB_NAME) ),'') =
ISNULL ( SUBSTRING(e.CONDITION, 3, NULLIF( CHARINDEX( '-', e.CONDITION ) -3,-3) ), '' )
),'')AS 'PRED_DESC'
from XX.DEF_VER_JOB a
JOIN XX.DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN XX.dbo.AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN XX.DEF_VER_LNKI_P e
ON ( A.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
where
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
order by 1,2
- pmdeshayes
- Nouveau
- Posts: 23
- Joined: 07 Jul 2008 12:00
Why you dont use the Reporting Facility ?
In the Temples "Active\Active jobs" there are the following fields that can be excracted and reported:
Average Runtime
Average start time
Elapsed
Elapsed runtime
EM stistic period
etc.
Remember also that, once defined as template, the report can be scheduled (reporting form).
Regards
In the Temples "Active\Active jobs" there are the following fields that can be excracted and reported:
Average Runtime
Average start time
Elapsed
Elapsed runtime
EM stistic period
etc.
Remember also that, once defined as template, the report can be scheduled (reporting form).
Regards
Of course! As you prefer.
But I want say that there is also another method to do this kind of things, using the Control-M Reporting Facility; may be more simple, or not. Only let know that exist.
Then I can also add another example: once you have extracted/exported data in a shared file system, all days without formatting (csv); You can open an excel with macro designed on it that do .... everything !
Formatting, graphics,..... coffee![Very Happy :D](./images/smilies/icon_biggrin.gif)
Have a good day
But I want say that there is also another method to do this kind of things, using the Control-M Reporting Facility; may be more simple, or not. Only let know that exist.
Then I can also add another example: once you have extracted/exported data in a shared file system, all days without formatting (csv); You can open an excel with macro designed on it that do .... everything !
Formatting, graphics,..... coffee
![Very Happy :D](./images/smilies/icon_biggrin.gif)
Have a good day
Try this in Excel for highlighting jobs based on job name change,
Sub color()
Dim r As Long
Dim c, x As Integer
Dim colorIt As Boolean
r = 1
c = 2
x = 0
colorIt = True
Do While Cells(r, c) <test_value> 0 Then
Cells(r, 1).Value = ""
Cells(r, 2).Value = ""
Cells(r, 3).Value = ""
Cells(r, 4).Value = ""
Cells(r, 5).Value = ""
Cells(r, 6).Value = ""
End If
Selection.Interior.ColorIndex = 15
Else
Cells(r, c).EntireRow.Select
Selection.Interior.ColorIndex = xlNone
If x > 0 Then
Cells(r, 1).Value = ""
Cells(r, 2).Value = ""
Cells(r, 3).Value = ""
Cells(r, 4).Value = ""
Cells(r, 5).Value = ""
Cells(r, 6).Value = ""
End If
End If
r = r + 1
x = x + 1
Loop
colorIt = Not (colorIt)
x = 0
Loop
End Sub
-- Ciao --Goo
Sub color()
Dim r As Long
Dim c, x As Integer
Dim colorIt As Boolean
r = 1
c = 2
x = 0
colorIt = True
Do While Cells(r, c) <test_value> 0 Then
Cells(r, 1).Value = ""
Cells(r, 2).Value = ""
Cells(r, 3).Value = ""
Cells(r, 4).Value = ""
Cells(r, 5).Value = ""
Cells(r, 6).Value = ""
End If
Selection.Interior.ColorIndex = 15
Else
Cells(r, c).EntireRow.Select
Selection.Interior.ColorIndex = xlNone
If x > 0 Then
Cells(r, 1).Value = ""
Cells(r, 2).Value = ""
Cells(r, 3).Value = ""
Cells(r, 4).Value = ""
Cells(r, 5).Value = ""
Cells(r, 6).Value = ""
End If
End If
r = r + 1
x = x + 1
Loop
colorIt = Not (colorIt)
x = 0
Loop
End Sub
-- Ciao --Goo