Want to join avg runtime per job to job def in CME

Some examples running with Control-M and Control-EM database.
Post Reply
User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Want to join avg runtime per job to job def in CME

Post by DocGoo » 06 Nov 2012 9:38

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
Last edited by DocGoo on 07 Nov 2012 2:57, edited 2 times in total.

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 06 Nov 2012 10:28

Well it turns out I have some funky thing going on with caps. Once column names are in caps it works.

Can anyone tell me how to add avg runtime to this query?

User avatar
pmdeshayes
Nouveau
Nouveau
Posts: 23
Joined: 07 Jul 2008 12:00

Post by pmdeshayes » 11 Nov 2012 8:15

The 'funky CAPS' behavior is most likely due to your SQL Server database being created with a case sensitive collation (ain't that great?).

As for your avg runtime, you could get it from avg_run_info table.

Cheers

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

SQL Server Report of All Production Jobs

Post by DocGoo » 12 Nov 2012 9:08

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

User avatar
pmdeshayes
Nouveau
Nouveau
Posts: 23
Joined: 07 Jul 2008 12:00

Post by pmdeshayes » 12 Nov 2012 10:07

@DocGoo: just wondering how long does your query takes to complete and how many jobs you got in def_ver_jobs ?

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 12 Nov 2012 10:20

We only have 3082 rows in DEF_VER_JOB and really only about 700 jobs. The query runs in a couple of seconds under windows and SQL Server, we do not have very powerfull machines for servers.

User avatar
pmdeshayes
Nouveau
Nouveau
Posts: 23
Joined: 07 Jul 2008 12:00

Post by pmdeshayes » 12 Nov 2012 11:43

A small production server indeed :)

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 13 Nov 2012 12:23

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

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 13 Nov 2012 2:20

Because the output format of Reporting Facility is not modifiable. It puts out some pretty ugly reports.

Also I need to verify reports from raw data to cma.

If there is a way to format output so columns do not get cut off from being too wide let me know.

--Goo

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 13 Nov 2012 2:58

To output can be in .csv or .xls: are modifiables and dont cut any colums.
Only the pdf format can have problem with the cutting of columns (or the GUI interface).
For example if you export the report on xls file and open it with excel you can see all the colums you want....

Regards

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 13 Nov 2012 3:41

If you look at my SQL you will see that I use CASE and a Sub-Query along with a lot of formatting.

Thanks again!

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 13 Nov 2012 4:22

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 :D

Have a good day

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 13 Nov 2012 4:26

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

Post Reply