Queries do sistema BMC Control-M
- Verificar qual job está forçando a execução de outro
Select b.jobname,b.schedtab,a.jobname as "Job ordenado"
from CMS_FORCEJ a
inner join cms_jobdef b on
a.jobno = b.jobno
--where b.jobname= :job; - Procurar jobs AFT/MFT pelo caminho especificado na configuração
SELECT a.JOBNO, b.jobname, a.VAR, a.VAREXPR
FROM CMS_SETVAR a
INNER JOIN cms_jobdef b on
a.JOBNO = b.JOBNO
WHERE (VAR LIKE '%%FTP-%PATH%')
and varexpr like '%/%';- Hosts sem job
Select nodeid, permhosts from ctmuser.cmr_nodes
where (nodeid not in (Select nodeid from ctmuser.cms_jobdef))
and (nodeid not in (Select nodeid from ctmuser.cms_nodgrp))
--and cmr_nodes.agstat in ('U','D') ;- Application / Grupo de acesso
Select a.fieldvalue as Application, b.ldapgroup
from activejobsauthorizations a
inner join ldap_groups b on
a.username = b.username;- Jobs que não foram executados nos ultimos 3 meses
Select a.jobname, a.applic, a.APPLGROUP, a.SCHEDTAB
from CMS_JOBDEF a
where
not exists (Select distinct(b.job_mem_name)
from RUNINFO_HISTORY b where a.JOBNAME = b.JOB_MEM_NAME);- Jobs em Hold
Select jobname, nodeid, overlib, applic,
to_date(order_time,'YYYYMMDDHH24MISS') as order_time ,
appltype, applver, tasktype, runcount, memname,
schedtab, state, orderno, holdflag
from CMR_AJF
where holdflag = 'Y' and nodeid != ' '
ORDER BY order_time;- Jobs em Confirm
Select jobname, nodeid, overlib, applic,
to_date(order_time,'YYYYMMDDHH24MISS') as order_time ,
appltype, applver, tasktype, runcount, memname, schedtab, state, orderno,
holdflag
from CMR_AJF where state = '1'
ORDER BY order_time;- Jobs em execução classificado por Status
Select jobname, to_number((to_date(odate,'YYYYMMDD') -
to_date(:dataYYYMMDD,'YYYYMMDD'))) as dias ,
APPLIC, applgroup, SCHEDTAB, overlib,
to_date(odate,'YYYYMMDDHH24MISS') as odate,
MAXWAIT, HOLDFLAG,
case state when '1' then 'Em Confirm'
when '8' then 'Finalizado'
when '0' then 'Em Hold'
when '4' then 'Executando'
when 'B' then 'Aguardando horario'
when 'C' then 'Aguardando condição'
when 'L' then 'Aguardando servidor'
when 'A' then 'Aguardando rerun'
when 'D' then 'Aguardando recurso'
else state end as state
from CMR_AJF
where tasktype not in ('S','G');- Job com SetOK
Select c.jobname, a.code, b.ACTION from CMS_ONSTMT a
inner join cms_do b on
b.JOBNO = a.JOBNO
and b.IF_NO = a.IF_NO
inner join CMR_AJF c on
b.JOBNO = c.JOBNO
where --a.code in('COMPSTAT=2','COMPSTAT=1')
b.action = 'OK' and code = 'NOTOK'
order by c.jobname;- Jobs em Executing a mais de 30 dias
Select jobname, nodeid, overlib, applic,
to_date(order_time,'YYYYMMDDHH24MISS') as order_time,
appltype, applver, tasktype, runcount, memname, schedtab, state, orderno, holdflag
from CMR_AJF where state = '4'
and to_date(order_time,'YYYYMMDDHH24MISS') < (sysdate-2)
ORDER BY order_time;- Jobs sem Application
Select jobname, overlib, applic,
to_date(order_time,'YYYYMMDDHH24MISS') as order_time
from CMR_AJF
where applic = ' '
order by jobname, order_time;- Erro Job único por dia
Select distinct(a.job_mem_name), a.APPLICATION, a.ended_status,
to_char(a.start_time, 'YYYY/MM/DD') as Data
from emuser.RUNINFO_HISTORY a
where ENDED_STATUS = 32
and start_time between to_date('dd/mm/aa 00:00:00','DD/MM/YY HH24:MI:SS')
and to_date('dd/mm/aa 23:59:59','DD/MM/YY HH24:MI:SS')
group by a.job_mem_name, a.application, a.ended_status,
to_char(a.start_time,'YYYY/MM/DD')
order by data asc; - Query de Ok e NotOk D-1
Select OVERLIB AS "PCP", JOBNAME AS "JOBNAME", COUNT (JOBNAME) AS "TOTAL",
count( case when ENDED_STATUS = 16 then 1 end) AS "OK",
count( case when ENDED_STATUS = 32 then 1 end ) AS "NOT OK"
FROM (
SELECT run.job_id, (run.job_mem_name) as jobname, run.ended_status,
(run.application) as overlib from
runinfo_history run
WHERE run.start_time >= TRUNC(SYSDATE - 1)
AND run.start_time < TRUNC(SYSDATE)
GROUP BY OVERLIB, JOBNAME
ORDER BY 1 ASC, 5 DESC; - Jobs com malha sem schedule
Select jobname, def_ver_tables.SCHED_TABLE AS Malha, applic, applgroup, cyclic,
cyclicint, interval_sequence, nodeid, overlib, fromtime, until, daystr,
def_ver_tables.USER_DAILY from ctmuser.CMS_JOBDEF
inner join def_ver_tables on
def_ver_tables.SCHED_TABLE = ctmuser.CMS_JOBDEF.SCHEDTAB
where def_ver_tables.USER_DAILY IS null and daystr != ' '; - Jobs sem schedule com malha com schedule
Select jobname, def_ver_tables.SCHED_TABLE AS Malha, applic, applgroup,
cyclic, cyclicint, interval_sequence, nodeid, overlib,
fromtime, until, daystr, def_ver_tables.USER_DAILY
from ctmuser.CMS_JOBDEF
inner join def_ver_tables on
def_ver_tables.SCHED_TABLE = CTM800.CMS_JOBDEF.SCHEDTAB
where daystr = ' ' and def_ver_tables.USER_DAILY IS not null; - Charset Oracle - Bônus
SELECT * FROM NLS_DATABASE_PARAMETERS; - Maior número de jobs carregado no mês
Select jobs, NET_DATE
from net_report
where jobs =(
Select max(jobs)
from net_report
where CREATE_TIME between trunc(to_date('dd/mm/aa', 'DD/MM/YY'))
and trunc(to_date('dd/mm/aa','DD/MM/YY'))); - Usuários que não logam a mais de 3 meses
Select distinct(username) as Login, USERFULLNAME as Nome,
DESCRIPTION as Descricao
from generalauthorizations
where isgroup = 0 and username not in (
Select audit_login.USERNAME
from audit_login
where LOGINTIME between to_date(sysdate -90) and to_date(sysdate))
order by USERFULLNAME; - Jobs sem alerta
Select a.jobno, a.jobname, a.applic
from cms_jobdef a
where not exists
(Select distinct(jobno) from cms_shout b where a.jobno = b.jobno)
and a.tasktype not in('G','S'); - Agentes indisponíveis
Select * from cmr_nodes
where agstat = 'U'
and nodetype != ' '; - Jobs com Remedy configurado
Select a.jobno, b.jobname, b.APPLIC, b.APPLGROUP, b.SCHEDTAB, b.OVERLIB, a.action
from cms_do a
inner join cms_jobdef b on
a.jobno = b.jobno
where a.action = 'REMEDY'- Job sem Remedy configurado
Select a.jobname, A.applic, a.APPLGROUP, a.SCHEDTAB
from CMS_JOBDEF a
where not exists (Select distinct(b.jobno) from cms_do b
where a.JOBNO = b.JOBNO and b.action = 'REMEDY')
AND a.TASKTYPE not in ('G','S')
order by a.jobname; - Jobs da base e com Remedy
Select t1.final as base, t2.jobs as automatizados,t1.applic as applicbase, t2.applic as applicauto from
(Select sum(PADRAO) as final, applic from (
Select count(jobname) as PADRAO, applic from (
Select jobname, applic from cms_jobdef
group by jobname, applic)
group by jobname, applic)
group by PADRAO, applic)t1,
(Select sum(jobs) as jobs, applic from (
Select count(jobname) as jobs, applic from (
Select distinct(b.jobname), b.APPLIC, b.APPLGROUP, b.SCHEDTAB, b.OVERLIB,
a.action
from cms_do a
inner join cms_jobdef b on
a.jobno = b.jobno
where a.action = 'REMEDY')
group by jobname, applic)
group by applic
order by jobs desc) t2
where t1.applic = t2.applic
group by t1.final, t2.jobs,t1.applic, t2.applic
order by t1.final desc; - Relatório SAP
Select t1.jobname, t1.varexpr as programa, t2.varexpr as parametro from
((Select distinct (b.JOBNAME), a.VAREXPR from cms_setvar a
inner join CMS_JOBDEF b on
b.JOBNO = a.jobno
where
a.var like '%%SAPR3-STEP-S%-PROGRAM'
and b.APPLIC = 'SAP'
and b.APPLFORM = 'SAP R3'
order by b.JOBNAME
)) t1,
(Select distinct (b.JOBNAME), a.VAREXPR from cms_setvar a
inner join CMS_JOBDEF b on
b.JOBNO = a.jobno
where a.var like '%%SAPR3-STEP-S%-VAR-NAME'
and b.APPLIC = 'SAP'
and b.APPLFORM = 'SAP R3'
order by b.JOBNAME
)t2
where t1.jobname = t2.jobname; - Jobs por servidor
Select count(a.JOBNAME) qnt, b.NODEid from CMS_JOBDEF a
full join cms_nodid b on
a.NODEID = b.NODEID
group by b.nodeid
order by qnt asc; - Tempo do job na malha produtiva
Select * from (
Select jobname,
to_number((to_date(odate,'YYYYMMDD') - to_date(:AAAAMMDD,'YYYYMMDD'))) as dias ,
APPLIC, applgroup, SCHEDTAB, to_date(odate,'YYYYMMDDHH24MISS') as odate, MAXWAIT,
HOLDFLAG
from CMR_AJF
where tasktype not in ('S','G')
order by 2 asc)
where dias <= -30; - Quantidade de entradas no DBLOG
Select count(*) from CMR_DBLOg; - Jobs Cíclicos de 0 em 0
select JOBNAME, APPLIC, RERUN_NO, RUNCOUNT
from CMR_AJF
where RUNCOUNT > 500
and CYCLICINT in ('00000M') and CYCLIC = 'Y'
order by RUNCOUNT desc;
select JOBNAME, APPLIC, RERUN_NO, RUNCOUNT
from CMR_AJF
where CYCLICINT in ('00000M') and CYCLIC = 'Y'
order by RUNCOUNT desc;
select JOBNAME, APPLIC, RERUN_NO, RUNCOUNT
from CMR_AJF
where RUNCOUNT > 500 and CYCLIC = 'Y'
order by RUNCOUNT desc;