Scheduler Usage

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

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?

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

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

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

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.

A small production server indeed

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

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

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

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!

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

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