Set maintenance window

gabriel_cst · Post by **gabriel_cst** » 10 May 2013 5:15

Hi everyone
I'm using Control-M 6.3. I need to set a maintenance window in one node. Is there any way to query the database or by using CTM Desktop/EM to filter jobs that will run in a specific day and node to check when a node is not with workload ?
Thanks for your help

th_alejandro · Post by **th_alejandro** » 23 May 2013 12:55

Check for table em_user.def_job, there is a node_id, from_time, to_time and the other fields from runtime definition for each job.

gabriel_cst · Post by **gabriel_cst** » 24 May 2013 4:47

Hi th_alejandro,
I could check that but what about jobs that start too early but runs for too long? Or jobs that doesn't have from_time and to_time because they wait for an IN condition?
I would need to run a query that provide How much jobs were running in a specific range of time and node?
Thanks

th_alejandro · Post by **th_alejandro** » 24 May 2013 5:50

Hi Gabriel, in this case, i recommend that implement a download statistical information from Control-M and create your own statistical system. For example, download app_name, grp_name, job_name, start_date, end_date, etc into a text file daily, then, load into a database and create programs that allow to you to sum, average, rating and make your own calculation based on the exported information.

In this way, you can have the traceability of time for each job and make you own graph about that.

In not easy. And Ctm not have a tool that allow to you achieve that. (The report facility is very pour).

gabriel_cst · Post by **gabriel_cst** » 24 May 2013 5:55

Ok Alejandro,
I'll check into that, it doesn't look like an easy task but it has to be done.
If I come up with some tool, I will post it here so it can help everyone
Thanks for your help, any other suggestion will be helpful

th_alejandro · Post by **th_alejandro** » 24 May 2013 6:52

Ok Gabriel, i'm very happy today.

Check this Oracle SQL as your first step :

# Change param &1 as odate format YYYYMMDD

spool /tmp/execution_log.spool

set termout off ;
set trimspool ON;
set numf 9999999999999 ;
set linesize 256;
set verify off ;
set echo off ;
set feedback off ;
set pagesize 0 ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;

SELECT a.nodeid||'|'||
b.applic||'|'||
b.applgroup||'|'||
a.jobname||'|'||
trim(b.descript)||'|'||
a.runcount||'|'||
to_date(a.startrun, 'YYYYMMDDHH24MISS')||'|'||
to_date(a.endrun, 'YYYYMMDDHH24MISS')||'|'||
trunc((to_date(a.endrun, 'YYYYMMDDHH24MISS')-to_date(a.startrun, 'YYYYMMDDHH24MISS'))*24*60*60)||'|'
FROM ctmuser.cmr_jobinf a,
ctmuser.cmr_ajf b
WHERE a.orderno = b.orderno
AND b.odate = '&1'
AND a.oscompstat = 0
ORDER BY a.jobname, a.runcount ;

spool off

rulfete · Post by **rulfete** » 27 May 2013 2:04

You can use ctmrpln utiliy in your Datacenter.

"The ctmrpln utility creates a report that lists selected jobs in selected Scheduling tables, and indicates when the jobs are scheduled to run. It enables you to test the effect of different calendars on job scheduling."

You can see how to launch in page 237 of "CONTROL-M/Server for UNIX and Microsoft Windows Administrator Guide"

gabriel_cst · Post by **gabriel_cst** » 30 May 2013 5:24

got it with this :
ctmlog list with date params of the day you want to check
That outputs ctmlog-date.txt
then i worked that file with java to parse it and upload information to an sql database
and after that I used google annotated timeline in asp to display the sql info in a nice way.
The final graph is very good for analyzing the load in the node.
Sadly i can't upload pictures to this thread but if you google annotated timeline you'll get an idea.
If anyone's interested on the implementation I can help
Thanks everyone and good luck

Set maintenance window

Set maintenance window

ctmpsm

Tips

First Step : SQL Extraction