Skip to content

Instantly share code, notes, and snippets.

@mk1tools

mk1tools/PTF_SQL.sql

Created Dec 10, 2019
Embed
What would you like to do?
Sei aggiornato? Te lo dico con 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.