How to extract Job Statistics data from Control-M database

[Walty]

Hi,

In the table <CMR_RUNINF> you find statistics for the 20 last scheduled jobs with status ENDED-OK

I use this query to extract some informations:

#!/bin/csh
SQL << EOF | tee /tmp/runinf.log
SET TERMOUT OFF
SET UNDERLINE =
SET SPACE 2
SET PAGESIZE 80
SET LINESIZE 132
SET NEWPAGE 0
SET WRAP OFF
SET HEADING ON
COLUMN JOBNAME FORMAT A15 HEADING " JobName"
COLUMN NODEID FORMAT A10 HEADING " Nodeid"
COLUMN ELAPTIME FORMAT A12 HEADING " Start_Time"
COLUMN TIMESTMP FORMAT A12 HEADING " End_Time"
COLUMN ELAPTIME FORMAT A12 HEADING " Duration"
select JOBNAME
, nodeid
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(to_date(trim(timestmp), 'YYYYMMDDHH24MISS')
, 'YYYY-MM-DD HH24:MI:SS') as end_time
, substr(to_char(numtodsinterval(elaptime / 100, 'SECOND')
, 'HH24:MI'), 12, as duration
from CMR_RUNINF where CMR_RUNINF.JOBNAME like 'your_jobs'
order by jobname, start_time
EOF

Regards
Walty

[Walty]

Update because bad paste, sorry


#!/bin/csh
SQL << EOF | tee /tmp/runinf.log
SET TERMOUT OFF
SET UNDERLINE =
SET SPACE 2
SET PAGESIZE 80
SET LINESIZE 132
SET NEWPAGE 0
SET WRAP OFF
SET HEADING ON
COLUMN JOBNAME FORMAT A15 HEADING " JobName"
COLUMN NODEID FORMAT A10 HEADING " Nodeid"
COLUMN ELAPTIME FORMAT A12 HEADING " Start_Time"
COLUMN TIMESTMP FORMAT A12 HEADING " End_Time"
COLUMN ELAPTIME FORMAT A12 HEADING " Duration"
select JOBNAME
, nodeid
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(to_date(trim(timestmp), 'YYYYMMDDHH24MISS')
, 'YYYY-MM-DD HH24:MI:SS') as end_time
, substr(to_char(numtodsinterval(elaptime / 100, 'SECOND')
, 'HH24:MI'), 12, 8) as duration
from CMR_RUNINF where CMR_RUNINF.JOBNAME like 'your_jobs'
order by jobname, start_time;
EOF
exit

Regards
Walty

[mered720]

Hi,

I'm really new to this, so how do you run this query? It looks like a simple shell script, is it executed in the shell, or within the control-m environment? If it must be run within the control-m environment, can it be scheduled as a job?

Thanks in advance!
Meredith

[Walty]

This script can be executed in command line invoked by <controlm> user (control-m environment)

You can also schedule this script as a job using <controlm> owner.
This job must be submitted on the control-m/server nodeid

Regards
Walty

[philmalmaison]

look at this script working on all controlm server on unix with ctrlm account , resultat format csv :

#!/bin/ksh -x
# extraction des traitement dans l'AJF
datej=`date '+%y%m%d'`
datejj=`date '+%d/%m/%y'`
heurej=`date "+%H:%M:%S"`
homedir="/your home directory"
cd ${homedir}

SQL -w999 <<EOF> ${homedir}/datacenter.out
select DATA_CNTR_NAME from CMS_CMNPRM
go
EOF
datacenter=`cat ${homedir}/datacenter.out | sed 's/ //g'`
echo "Datacenter : ${datacenter}"

FIC_TEMP="${homedir}/tmp_${datacenter}_${datej}.out"
FIC_CSV="${homedir}/traitements_${datacenter}_${datej}.csv"
rm ${homedir}/datacenter.out
# extraction SQL (jointure entre l'AJF et la JOBINF sur l'ORDERNO)

SQL -w999 <<EOF> $FIC_TEMP
select a.APPLIC,";",a.APPLGROUP,";",a.JOBNAME,";",a.MEMNAME,";",b.STARTRUN,";",b.ENDRUN,";",b.OSCOMPSTAT
from CMR_AJF a,CMR_JOBINF b
where a.ORDERNO=b.ORDERNO
order by APPLIC
go
EOF


# Lecture et mise en forme

echo "APPLI;GROUPE;JOBNAME;SCRIPT;START_DATE;START_HEURE;END_DATE;END_HEURE;DUREE_seconde;CODE" > $FIC_CSV
cat $FIC_TEMP | sed 's/ //g' | while read line
do
ApP=`echo ${line} | awk -F";" '{print $1}'`
ApG=`echo ${line} | awk -F";" '{print $2}'`
JbN=`echo ${line} | awk -F";" '{print $3}'`
MeM=`echo ${line} | awk -F";" '{print $4}'`
StA=`echo ${line} | awk -F";" '{print $5}'`
EnD=`echo ${line} | awk -F";" '{print $6}'`
JrC=`echo ${line} | awk -F";" '{print $7}'`

if [ "${StA}" != "" -a "${EnD}" != "" ]; then
StHeures=`echo ${StA} | awk '{
StH=substr($0,9,2)
printf (StH)
}'`
EnHeures=`echo ${EnD} | awk '{
EnH=substr($0,9,2)
print (EnH)
}'`
StMin=`echo ${StA} | awk '{
StM=substr($0,11,2)
print (StM)
}'`
EnMin=`echo ${EnD} | awk '{
EnM=substr($0,11,2)
print (EnM)
}'`
StSec=`echo ${StA} | awk '{
StS=substr($0,13,2)
print (StS)
}'`
EnSec=`echo ${EnD} | awk '{
EnS=substr($0,13,2)
print (EnS)
}'`



# conversion de l'heure en secondes
# start
Sec_heure_start=`expr $StHeures \* 3600`
Sec_min_start=`expr $StMin \* 60`
Sec_deb=`expr $Sec_heure_start + $Sec_min_start + $StSec`
# end
Sec_heure_end=`expr $EnHeures \* 3600`
Sec_min_end=`expr $EnMin \* 60`
Sec_fin=`expr $Sec_heure_end + $Sec_min_end + $EnSec`
# duree en sec
Sec_duree=`expr $Sec_fin - $Sec_deb`
# reconvertion en hh:mm:ss
Duree=`echo $Sec_duree | awk '{
H = int ( $0 / 3600)
R = $0 - H * 3600
M = int ( R / 60 )
S = R - M * 60
printf ("%02d:%02d:%02d\n",H,M,S)}'`
# concatenation de l'enregistrement CSV
Debut=`echo $StA | nawk '{print (substr($0,7,2))"/"(substr($0,5,2))"/"(substr($0,1,4))}'`
Debut="${Debut};${StHeures}:${StMin}:${StSec}"
Fin=`echo $EnD | nawk '{print (substr($0,7,2))"/"(substr($0,5,2))"/"(substr($0,1,4))}'`
Fin="${Fin};${EnHeures}:${EnMin}:${EnSec}"
echo "${ApP} ; ${ApG} ; ${JbN} ; ${MeM} ; ${Debut} ; ${Fin} ; ${Sec_duree} ; ${JrC}" >> $FIC_CSV
#exit
fi
done
rm $FIC_TEMP
# purge a 7 jours
find ${homedir}/traitements* -mtime +7 -exec rm {} \;


regards
Philmalmaison