Useful SQL request for Control-M

 

Some useful SQL request :

Example:

select * from CMR_IOALOG where MSGID='5134' and LOGDATE='20120722'

Here we  put a SQL request  to get the job KO (5134) for the date of 22 July 2012 Jobs OK ==> 5133  

See those who created the jobs and those who have changed ==>

select JOBNAME,CREATIONUSERID,CHANGEUSERID from CMS_JOBDEF

ordered by date descending ==>

select JOBNAME,CREATIONUSERID,CREATIONDATETIME,CHANGEUSERID,CHANGEDATETIME from CMS_JOBDEF ORDER BY CREATIONDATETIME DESC

See the Job Status :

select ORDERNO,STATE,JOBNAME from CMR_AJF where STATE='4'

Here we looking for “executings” jobs.

more complete version :
select ODATE,ORDERNO,STATE,JOBNAME,STARTRUN,ENDRUN,NEXTTIME,OSCOMPMSG from CMR_AJF where STATE='4' order by STARTRUN desc

The status :

wait scheduling -> 0
wait confirm -> 1
submitted -> 2
not submitted -> 3
executing -> 4
ended -> 5
analyzed -> 6
disappered -> 7
post processed -> 8
Not found -> 9
wait rerun -> A
wait time -> B
wait cond -> C
wait ressource -> D
wait submission -> E
not known -> F
retry submit -> G
wait group sch -> H
fail submit -> I
unknown -> Z

With these status you can build your own SQL report ;)