Skip to content

Instantly share code, notes, and snippets.

@mk1tools
Created November 25, 2019 17:51
Show Gist options
  • Save mk1tools/575e3ac809736e01b724928fc2c48597 to your computer and use it in GitHub Desktop.
Save mk1tools/575e3ac809736e01b724928fc2c48597 to your computer and use it in GitHub Desktop.
Up and down (ti svelo l'IPL con SQL)
-- Autore: Marco Riva
-- www.markonetools.it
/* I suggerimenti di Markone n. 4
Quando si è spento e acceso l'ultima volta il nostro IBM i? E quanto tempo ha impiegato per spegnersi ed accendersi?
Alcuni metodi per saperlo tramite istruzioni SQL. */
-- le istruzioni seguenti sono testate su IBM i 7.3 TR6 e IBM i 7.3 TR7. In particolare:
-- la funzione di tabella SPOOLED_FILE_DATA richiede IBM i 7.3 TR6
-- la funzione di tabella HISTORY_LOG_INFO richiede IBM i 7.3 TR1 o 7.2 TR5
-- la funzione di tabella JOB_INFO richiede IBM i 7.2 TR3
-- le CTE (clausola with) richiedono V5R4
-- la vista SYSTEM_VALUE_INFO richiede IBM i 7.1
-- la vista SCHEDULED_JOB_INFO richiede IBM i 7.2
-- l'API QWCCRTEC richiede IBM i 6.1
-->> informazioni sui passi di esecuzione dell'ultimo IPL
-- N.B. da IBM i 7.1 non è più necessario specificare il secondo parametro con la lunghezza della stringa del comando da eseguire
call qcmdexc('CALL QWCCRTEC');
-->> Ultimo spegnimento
with
-- valore di sistema QABNORMSW
SV_QABNORMSW as
(select case when CURRENT_CHARACTER_VALUE = '1' then 'anomalo' else 'normale' end as SYSVAL
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QABNORMSW'),
-- estrazione orario inizio PWRDWNSYS dallo spool di QWCCRTEC
PWRDOWN_START as
(select timestamp_format(substr(SPOOLED_DATA, 103, 17), 'MM/DD/YY HH24:MI:SS', 0) as PDSTART
from table(SYSTOOLS/SPOOLED_FILE_DATA('*', 'QPSRVDMP'))
where ucase(substr(SPOOLED_DATA, 87, 11)) = '*XPF PWRDWN'),
-- estrazione orario fine PWRDWNSYS dallo spool di QWCCRTEC
PWRDOWN_END as
(select timestamp_format(substr(SPOOLED_DATA, 103, 17), 'MM/DD/YY HH24:MI:SS', 0) as PDEND
from table(SYSTOOLS/SPOOLED_FILE_DATA('*', 'QPSRVDMP'))
where ucase(substr(SPOOLED_DATA, 87, 11)) = '*END PWRDWN'),
-- estrazione messaggio CPI0995 (immissione PWRDWNSYS) da history log (DSPLOG)
HSTLOG_CPI0995 as
(select timestamp(MESSAGE_TIMESTAMP, 0) as M1TIME,
trim(substr(MESSAGE_SECOND_LEVEL_TEXT, locate_in_string(MESSAGE_SECOND_LEVEL_TEXT, '. . . :', 1)+8)) as M1TEXT
from table(HISTORY_LOG_INFO((select PDSTART - 1 hours from PWRDOWN_START),
(select PDSTART + 1 hours from PWRDOWN_START))) as HL
where MESSAGE_ID = 'CPI0995'
order by MESSAGE_TIMESTAMP desc
fetch first 1 rows only)
-- query finale
select
SYSVAL as "Stato spegnimento",
M1TIME as "Immissione PWRDWNSYS", PDSTART as "Inizio spegnimento", PDEND as "Fine spegnimento",
M1TEXT as "Dettagli PWRDWNSYS"
from SV_QABNORMSW left join PWRDOWN_START on 1=1 left join PWRDOWN_END on 1=1 left join HSTLOG_CPI0995 on 1=1;
-->> Ultima accensione:
with
-- valore di sistema QIPLSTS
SV_QIPLSTS as
(select case CURRENT_CHARACTER_VALUE
when '0' then 'pannello operatore'
when '1' then 'automatico dopo ripristino alimentazione'
when '2' then 'riavvio'
when '3' then 'ora prestabilita'
when '4' then 'remoto'
else 'N/A' end as IPLSTS
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLSTS'),
-- valore di sistema QIPLTYPE
SV_QIPLTYPE as
(select case CURRENT_CHARACTER_VALUE
when '0' then 'non presidiato'
when '1' then 'presidiato con DST'
when '2' then 'presidiato (debug)'
else 'N/A' end as IPLTYPE
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLTYPE'),
-- estrazione orario inizio IPL dallo spool di QWCCRTEC
IPL_START as
(select timestamp_format(substr(SPOOLED_DATA, 103, 17), 'MM/DD/YY HH24:MI:SS', 0) as ISTART
from table(SYSTOOLS/SPOOLED_FILE_DATA('*', 'QPSRVDMP'))
where ucase(substr(SPOOLED_DATA, 87, 8)) = '*XPF IPL'
fetch first 1 rows only -- solo il primo rek. In caso di IPL con applicazione PTF possono esserci due record consecutivi *XPF IPL
),
-- estrazione orario fine IPL dallo spool di QWCCRTEC
IPL_END as
(select timestamp_format(substr(SPOOLED_DATA, 103, 17), 'MM/DD/YY HH24:MI:SS', 0) as IEND
from table(SYSTOOLS/SPOOLED_FILE_DATA('*', 'QPSRVDMP'))
where ucase(substr(SPOOLED_DATA, 87, 11)) = '*END OF IPL'),
-- orario avvio job SCPF (Start Control Program Function)
SCPF_START as
(select JOB_ENTERED_SYSTEM_TIME as SCPFSTART
from table(JOB_INFO(JOB_STATUS_FILTER => '*ACTIVE', JOB_USER_FILTER => 'QSYS')) as Job
where JOB_NAME = '000000/QSYS/SCPF'),
-- messaggio operatore sistema con informazioni su area IPL
-- (il messaggio potrebbe essere stato rimosso quindi è meglio estrarre questa informazione dall'history log)
-- QSYSOPR_AREAIPL as
-- (select trim(MESSAGE_TEXT) as AREAIPL, timestamp(MESSAGE_TIMESTAMP, 0) as AREAIPL_TIME
-- from QSYS2/MESSAGE_QUEUE_INFO
-- where MESSAGE_QUEUE_NAME = 'QSYSOPR' and MESSAGE_ID = 'CPI0C04'),
-- estrazione messaggi da history log (DSPLOG)
HSTLOG as
(select MESSAGE_ID, timestamp(MESSAGE_TIMESTAMP, 0) as M1TIME,
MESSAGE_TEXT as M1TEXT1, MESSAGE_SECOND_LEVEL_TEXT as M1TEXT2
from table(HISTORY_LOG_INFO((select ISTART - 1 hours from IPL_START),
(select IEND + 1 hours from IPL_END))) as HL
where MESSAGE_ID in ('CPI0C04', 'CPF0998', 'CPI091D')),
-- estrazione messaggio CPI0C04 da history log: area IPL
HSTLOG_CPI0C04 as
(select trim(M1TEXT1) as M1TEXT1
from HSTLOG
where MESSAGE_ID = 'CPI0C04'
order by M1TIME desc
fetch first 1 rows only),
-- estrazione messaggio CPF0998 da history log: chiusura anomala
HSTLOG_CPF0998 as
(select M1TEXT1
from HSTLOG
where MESSAGE_ID = 'CPF0998'
order by M1TIME desc
fetch first 1 rows only),
-- estrazione messaggio CPI091D da history log: causa chiusura anomala
HSTLOG_CPI091D as
(select substr(M1TEXT1, locate_in_string(M1TEXT1, 'codice causa', 1)+13, 1) as CODICE,
substr(M1TEXT2,
locate_in_string(M1TEXT2, '&P ' concat substr(M1TEXT1, locate_in_string(M1TEXT1, 'codice causa', 1)+13, 1), 1)+7,
(locate_in_string(M1TEXT2, '&P ', locate_in_string(M1TEXT2, '&P ' concat substr(M1TEXT1, locate_in_string(M1TEXT1, 'codice causa', 1)+13, 1), 1)+1)-1) -
(locate_in_string(M1TEXT2, '&P ' concat substr(M1TEXT1, locate_in_string(M1TEXT1, 'codice causa', 1)+13, 1), 1)+7)
)
as DESCRIZIONE
from HSTLOG
where MESSAGE_ID = 'CPI091D'
order by M1TIME desc
fetch first 1 rows only)
-- query finale
select IPLSTS as "Tipo di IPL eseguito", IPLTYPE as "Tipo IPL da eseguire",
ISTART as "Inizio accensione",
SCPFSTART as "Data/Ora avvio Start Control Program Function",
IEND as "Fine accensione",
HSTLOG_CPI0C04.M1TEXT1 as "Area IPL",
case when HSTLOG_CPF0998.M1TEXT1 is null then 'No' else 'Sì' end as "IPL dopo chiusura anomala",
HSTLOG_CPI091D.CODICE concat ' - ' concat HSTLOG_CPI091D.DESCRIZIONE as "Causa chiusura anomala"
from SV_QIPLSTS, SV_QIPLTYPE, IPL_START, IPL_END, SCPF_START
left join HSTLOG_CPI0C04 on 1=1
left join HSTLOG_CPF0998 on 1=1
left join HSTLOG_CPI091D on 1=1;
-->> Prossima accensione pianificata
-- accensione programmata in menu POWER
select 'Menu POWER' as "Tipo",
timestamp_format(substr(trim(CURRENT_CHARACTER_VALUE), 2), 'YYMMDDHH24MISS', 0) as "Data/ora prossima accensione"
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLDATTIM'
and CURRENT_CHARACTER_VALUE <> '*NONE'
union all
-- accensione schedulata con comando PWRDWNSYS ... RESTART(*YES)
select 'Job schedulato' as "Tipo",
timestamp_format(NEXT_SUBMISSION_DATE concat ' ' concat SCHEDULED_TIME, 'YYYY-MM-DD HH24:MI:SS', 0) as "Data/ora prossima accensione"
from SCHEDULED_JOB_INFO as JS
where ucase(COMMAND_STRING) like '%PWRDWNSYS%RESTART(*YES)%'
and STATUS = 'SCHEDULED'
order by 2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment