Sei aggiornato? Te lo dico con SQL

PTF_SQL.sql

-- Autore: Marco Riva
-- www.markonetools.it

/* I suggerimenti di Markone n. 5

Quali PTF sono installate? L'ultimo cumulativo installato? Ci sono disponibili nuovi aggiornamenti da installare?
E il firmware è aggiornato?
E' possibile rispondere a queste domande con poche istruzioni SQL
*/

/* LINK DOCUMENTAZIONE
 GROUP_PTF_INFO view:     https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewgrpptfinfo.htm
 GROUP_PTF_CURRENCY view: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewgrpptfcurr.htm 
 GROUP_PTF_DETAILS view:  https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewgrpptfdetails.htm
 PTF_INFO view:           https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewptfinfo.htm
 FIRMWARE_CURRENCY view:  https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewfirmwarecurrency.htm
*/

-->> RIEPILOGO DB2 for i SERVICE: CATEGORIA PTF
select SERVICE_NAME "Nome servizio", SERVICE_SCHEMA_NAME "Libreria", SYSTEM_OBJECT_NAME "Nome sistema", SQL_OBJECT_TYPE "Tipo", 
  replace(substr(EXAMPLE, 1, position(x'0D', EXAMPLE) -1), '-- Description: ', '') "Descrizione breve"
  from QSYS2/SERVICES_INFO
  where SERVICE_CATEGORY = 'PTF'
  order by SERVICE_NAME;

-->> STATO AGGIORNAMENTO
  -- WRKPTFGRP
select PTF_GROUP_NAME "ID gruppo", PTF_GROUP_DESCRIPTION "Descrizione", PTF_GROUP_LEVEL "Liv.", PTF_GROUP_STATUS "Stato", PTF_GROUP_TARGET_RELEASE "Rel."
  from GROUP_PTF_INFO
  order by PTF_GROUP_NAME, PTF_GROUP_LEVEL desc;

  -- *** WRKPTFGRP PTFGRPLVL(*INSTALLED) ***
with
 LASTLEVEL as
 (select PTF_GROUP_NAME, max(PTF_GROUP_LEVEL) PTF_GROUP_LEVEL
    from GROUP_PTF_INFO
    group by PTF_GROUP_NAME
 ),
 DESCPTF as
 (select distinct PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_STATUS, PTF_GROUP_TARGET_RELEASE
    from GROUP_PTF_INFO
    where PTF_GROUP_DESCRIPTION not like '%CUMULATIVE%'
  ),
  DESCCUM as
  (select PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_STATUS, PTF_GROUP_TARGET_RELEASE
     from GROUP_PTF_INFO
     where PTF_GROUP_DESCRIPTION like '%CUMULATIVE%'
     order by PTF_GROUP_LEVEL desc
     fetch first 1 rows only
  ),
  DESCTOT as
  (select *
     from DESCPTF
   union all
   select *
     from DESCCUM
  )
 select L.PTF_GROUP_NAME "ID gruppo", PTF_GROUP_DESCRIPTION "Descrizione", L.PTF_GROUP_LEVEL "Liv.", PTF_GROUP_STATUS "Stato", PTF_GROUP_TARGET_RELEASE "Rel."
   from LASTLEVEL as L
     inner join DESCTOT
       using(PTF_GROUP_NAME)
   order by L.PTF_GROUP_NAME;
   
  -- ultimo livello di cumulativo installato (DSPPTF LICPGM(5770SS1): prima riga con codice che inizia con TC)
select max(PTF_GROUP_LEVEL) as "Livello cumulativo",
       date('20' concat max(PTF_GROUP_LEVEL)) "Data"
  from GROUP_PTF_INFO
  where PTF_GROUP_DESCRIPTION like 'CUMULATIVE PTF PACKAGE%' and PTF_GROUP_STATUS = 'INSTALLED';

  -- dettaglio singole PTF (DSPPTF)
select PTF_IDENTIFIER "ID PTF", PTF_RELEASE_LEVEL "Rel.PTF", PTF_PRODUCT_ID "Prodotto", PTF_PRODUCT_OPTION "Opz.", PTF_PRODUCT_DESCRIPTION "Descr.prodotto", PTF_PRODUCT_RELEASE_LEVEL "Rel.prod.",
       PTF_LOADED_STATUS "Stato", PTF_IPL_ACTION "Azione IPL", PTF_ACTION_PENDING "Azione in sospeso", PTF_ACTION_REQUIRED "Azione richiesta", PTF_IPL_REQUIRED "Richiesto IPL", timestamp(PTF_TEMPORARY_APPLY_TIMESTAMP, 0) "Data/ora applicazione temp.",
       PTF_SUPERCEDED_BY_PTF "PTF superata da"
  from PTF_INFO
  where PTF_IDENTIFIER like 'MF%'
    --and PTF_LOADED_STATUS <> 'SUPERCEDED' -- escludi PTF superate
  order by PTF_IDENTIFIER desc;

-->> CONTROLLO AGGIORNAMENTI DISPONIBILI
  -- *** elenco PTF installate con confronto PTF disponibili sul sito IBM Fix Central ***
select PTF_GROUP_ID "ID Gruppo", PTF_GROUP_TITLE "Descrizione", PTF_GROUP_CURRENCY "Stato aggiornamento", PTF_GROUP_STATUS_ON_SYSTEM "Stato installazione", PTF_GROUP_LEVEL_INSTALLED "Liv.installato", PTF_GROUP_LEVEL_AVAILABLE "Liv.disponibile", PTF_GROUP_LAST_UPDATED_BY_IBM "Data agg."
  from SYSTOOLS/GROUP_PTF_CURRENCY
  --where PTF_GROUP_CURRENCY = 'UPDATE AVAILABLE' -- solo gruppi da aggiornare
  order by PTF_GROUP_CURRENCY desc, PTF_GROUP_ID;

  -- dettaglio PTF mancanti sul sistema rispetto a quelle disponibili in Fix Central  non ancora presenti in un cumulativo
select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "ID Gruppo", PTF_GROUP_DESCRIPTION "Descrizione", PTF_PRODUCT_ID "Prodotto", 
    PTF_STATUS "Stato inst.", 
    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in un gruppo da (MM/DD/YY)", PTF_CUM_PACKAGE "Incluso in cumulativo"
  from SYSTOOLS/GROUP_PTF_DETAILS
  where PTF_STATUS = 'PTF MISSING' and PTF_CUM_PACKAGE = 'NONE'
  order by PTF_GROUP_NAME, PTF_IDENTIFIER;

  -- dettaglio PTF sul sistema per uno specifico gruppo
select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "ID Gruppo", PTF_GROUP_DESCRIPTION "Descrizione", PTF_PRODUCT_ID "Prodotto", 
    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato corrente",
    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in un gruppo da (MM/DD/YY)", PTF_CUM_PACKAGE "Incluso in cumulativo", 
    PTF_IPL_ACTION "Azione IPL", PTF_ACTION_PENDING "Azione in sospeso", PTF_ACTION_REQUIRED "Azione richiesta", PTF_IPL_REQUIRED "Richiesto IPL",
    PTF_SUPERCEDED_BY_PTF "Superata da", timestamp(PTF_CREATION_TIMESTAMP, 0) "Data/ora creaz. PTF"
  from SYSTOOLS/GROUP_PTF_DETAILS
  where PTF_STATUS <> 'PTF MISSING'
    and PTF_GROUP_NAME = 'SF99703' -- gruppo DB2
  order by PTF_IDENTIFIER;

  -- dettaglio PTF in attesa di azione (in una situazione "normale" questa query non dovrebbe restituire nessun record)
    -- DSPPTF SELECT(*ACTRQD)   
select PTF_IDENTIFIER "PTF ID", PTF_GROUP_NAME "ID Gruppo", PTF_GROUP_DESCRIPTION "Descrizione", PTF_PRODUCT_ID "Prodotto", 
    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato corrente",
    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Disp. in un gruppo da (MM/DD/YY)", PTF_CUM_PACKAGE "Incluso in cumulativo", 
    PTF_IPL_ACTION "Azione IPL", PTF_ACTION_PENDING "Azione in sospeso", PTF_ACTION_REQUIRED "Azione richiesta", PTF_IPL_REQUIRED "Richiesto IPL",
    PTF_SUPERCEDED_BY_PTF "Superata da", timestamp(PTF_CREATION_TIMESTAMP, 0) "Data/ora creaz. PTF"
  from SYSTOOLS/GROUP_PTF_DETAILS
  where PTF_STATUS <> 'PTF MISSING' and (PTF_ACTION_PENDING <> 'NO' or PTF_ACTION_REQUIRED not in('NONE', 'IPL') or PTF_IPL_ACTION <> 'NONE')
  order by PTF_IDENTIFIER;

  -- verifica aggiornamento firmware (da 7.3 TR6): DSPFMWSTS 
    -- N.B. bisogna essere autorizzati ai comandi DSPHDWRSC e DSPFMWSTS
    -- N.B. per un problema di modifica del formato dei dati restituiti dal web service questa vista non restituisce dati. (cfr. APAR SE72579 del 3/12/2019 https://www-01.ibm.com/support/docview.wss?uid=nas2SE72579)
select *
  from SYSTOOLS/FIRMWARE_CURRENCY;