Up and down (ti svelo l'IPL con SQL)

Up_and_down.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;