It is currently 30 May 2017 3:27


Post new topic  Reply to topic  [ 13 posts ] 
Author Message
PostPosted: 07 Nov 2012 6:38 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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 11:57, edited 2 times in total.

Top
   
 Post subject:
PostPosted: 07 Nov 2012 7:28 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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?


Top
   
 Post subject:
PostPosted: 11 Nov 2012 5:15 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Jul 2008 8:00
Posts: 23
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


Top
   
PostPosted: 13 Nov 2012 6:08 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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


Top
   
 Post subject:
PostPosted: 13 Nov 2012 7:07 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Jul 2008 8:00
Posts: 23
@DocGoo: just wondering how long does your query takes to complete and how many jobs you got in def_ver_jobs ?


Top
   
 Post subject:
PostPosted: 13 Nov 2012 7:20 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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.


Top
   
 Post subject:
PostPosted: 13 Nov 2012 8:43 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Jul 2008 8:00
Posts: 23
A small production server indeed :)


Top
   
 Post subject:
PostPosted: 13 Nov 2012 9:23 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Jun 2007 8:00
Posts: 807
Location: Varese - Italy
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


Top
   
 Post subject:
PostPosted: 13 Nov 2012 11:20 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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


Top
   
 Post subject:
PostPosted: 13 Nov 2012 11:58 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Jun 2007 8:00
Posts: 807
Location: Varese - Italy
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


Top
   
 Post subject:
PostPosted: 14 Nov 2012 12:41 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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!


Top
   
 Post subject:
PostPosted: 14 Nov 2012 1:22 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Jun 2007 8:00
Posts: 807
Location: Varese - Italy
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


Top
   
 Post subject:
PostPosted: 14 Nov 2012 1:26 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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


Top
   
Display posts from previous:  Sort by  
Post new topic  Reply to topic  [ 13 posts ] 

All times are UTC+10:00


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Limited
The site created by Franck YOT. Images, logo, content and trademarks about Control-M products are property of BMC Software.
All the comments are property of their posters. Images, logo, content and design are © copyright by Scheduler-Usage.com. All Rights Reserved