IBM Maximo: SQL Queries

= Performance =

(Oracle DB) Maiores tabelas e atualização de estatísticas
Verificando as maiores tabelas e a atualização de estatísticas das tabelas.

select table_name, round((num_rows*avg_row_len)/1048576) tbsize, num_rows, last_analyzed from all_tables where owner='MAX' and num_rows>0 order by num_rows desc

resultado

table_name  tbsize   num_rows    last_analyzed WOSTATUS    27442    306121780   24-SET-21 WORKORDER   65996    95582941    24-SET-21 ...

Mesmo que seu ambiente tenha pouca movimentação, atualizar as estatísticas é fundamental pra um funcionamento do ambiente com qualidade.

(Oracle DB) Atualização de estatísticas
Verificando a atualização de estatísticas das tabelas.

select TO_CHAR(last_analyzed) as last_a, count(1) as cnt from all_tables where owner='MAX' and num_rows>0 group by TO_CHAR(last_analyzed) order by TO_CHAR(last_analyzed)

resultado

last_a        cnt 17-NOV-21	148 18-NOV-21	569 26-NOV-20	217 ...

= Geral=

Registros por Ano
select yList.year y, (select count(*) from workorder where extract(year from reportdate)=yList.year) c_workorder, (select count(*) from wfinstance where extract(year from starttime)=yList.year) c_wfinstance, (select count(*) from ticket where extract(year from reportdate)=yList.year) c_ticket, (select count(*) from po where extract(year from orderdate)=yList.year) c_po, (select count(*) from matrectrans where extract(year from transdate)=yList.year) c_matrectrans, (select count(*) from servrectrans where extract(year from transdate)=yList.year) c_servrectrans from (select column_value as year FROM table(sys.odcivarchar2list('2020','2021'))) yList group by yList.year order by yList.year;

= Relatórios=

Listar os relatórios executados com maior tempo de duracao nos últimos 30 dias
select reportname, description, appname, lastrunduration, lastrundate from report where lastrunduration is not null and lastrundate > sysdate - 30 order by lastrunduration desc

Listar os relatórios mais executados nos últimos 30 dias
select reportname, appname, sum(runtime)/1000 tot_exec_time, count(*) num_exec, round(avg(runtime)/1000,3) avg_exec_time from reportusagelog where startdate > sysdate - 30 group by reportname, appname order by tot_exec_time desc

Listar os relatórios por hora
select extract(hour from (cast(startdate as timestamp))) exechour, round(sum(enddate-startdate)*24*60*60,3) exec_time from reportusagelog where startdate > sysdate - 30 group by extract(hour from (cast(startdate as timestamp))) order by extract(hour from (cast(startdate as timestamp)))

Outros exemplos

 * IBM Maximo: Exportando um relatório (BIRT) do banco de dados do Maximo

= Crontasks=

Listar as crontasks executados com maior tempo de duracao nos últimos 30 dias
select crontaskname, instancename, servername, (endtime-starttime)*24*60*60 exec_time, starttime from crontaskhistory where activity='ACTION' and starttime > sysdate - 30 order by exec_time desc

Listar as crontasks executados por hora
select extract(hour from (cast(starttime as timestamp))) exec_hour, round(sum(endtime-starttime)*24*60*60,3) exec_time from crontaskhistory where activity='ACTION' and starttime > sysdate - 30 group by extract(hour from (cast(starttime as timestamp))) order by extract(hour from (cast(starttime as timestamp)))

= Workflow =

Migrado para IBM Maximo: SQL Queries - WORKFLOW

= Escalacoes=

Arquivamento
Queries uteis para arquivamento/exclusao de registros em tabelas

select to_char(statusdate, 'YYYY') as YEAR1, count(1) as CONT from escstatus group by to_char(statusdate, 'YYYY') order by to_char(statusdate, 'YYYY') 2019	719.178 2020	773.078 2021	799.968

= Login=

Logins nos últimos 2 dias
select * from logintracking where attemptresult='LOGIN' and attemptdate > sysdate - 2

Listar os Logins por hora
select extract(hour from (cast(attemptdate as timestamp))) login_hour, count(*) num_login from logintracking where attemptresult='LOGIN' and attemptdate > sysdate - 10 group by extract(hour from (cast(attemptdate as timestamp))) order by extract(hour from (cast(attemptdate as timestamp)))

Arquivamento
Queries uteis para arquivamento/exclusao de registros em tabelas

select to_char(attemptdate, 'YYYY') as YEAR1, count(1) as CONT from logintracking group by to_char(attemptdate, 'YYYY') order by to_char(attemptdate, 'YYYY')

2019	296.852 2020	205.320 2021	221.299

Dica de Delete Workflow History for Inactive Records

= Ver também =


 * Artigos sobre IBM Maximo
 * Mais Artigos sobre Cloud / WebDev / Tecnologias