Ultimo aggiornamento: 19-Mag-2020

10 Novembre 2017MarkOne

SQL prêt-à-porter

SQL prêt-à-porter è una raccolta di istruzioni SQL pronte all’uso.
Il DB2 offre numerosi funzioni, viste, procedure raccolte nella grande famiglia “DB2 for i Service” che ad ogni release e technology refresh vengono ampliate ed arricchite.
Una fonte ricca di preziose informazioni per il database è il catalogo del DB2, che consiste in una collezione di viste che descrivono ogni oggetto presente nel DB2.
Oltre a tutto ciò ci si trova spesso nella situazione di aver bisogno di alcuni istruzioni “modello” da avere sempre a portata di mano.
Si potrebbe (e si dovrebbe) leggere attentamente i manuali e soprattutto il sito IBM developerworks per scoprire nei dettagli tutte le potenzialità dell’SQL per DB2 for i.
Ma se colti da pigrizia si vuole passare subito all’azione e invece di preoccuparsi di confezionare un vestito su misura, si opta per la soluzione più economica dei modelli standardizzati, ecco qua che viene in aiuto SQL prêt-à-porter.

Le istruzioni sono raggruppate per argomento:

Alcuni degli esempi seguenti sono basati sulle tabelle del database di esempio fornito da IBM. Per creare il database di esempio eseguire la procedura:

call CREATE_SQL_SAMPLE('DB2SAMPLE');

Quindi l’istruzione seguente per impostarlo come schema predefinito:

set current schema = 'DB2SAMPLE';

Per ottenere un elenco completo delle funzioni, procedure e viste presenti in DB2 for i Service si può interrogare la vista SERV_INFO. In IBM i 7.3 ne esistono ben 99! In 7.1 sono 79 (al 21/02/19). Questo per evidenziare che ad ogni release e ad ogni TR questi servizi vengono sempre incrementati.

Per ogni servizio viene indicato anche il prerequisito necessario.

-- DB2 for i SERVICE INFO
select SERVICE_CATEGORY "Categoria", SERVICE_NAME "Nome servizio", SERVICE_SCHEMA_NAME "Libreria", SYSTEM_OBJECT_NAME "Nome sistema", SQL_OBJECT_TYPE "Tipo", EARLIEST_POSSIBLE_RELEASE concat ' PTF DB2 liv. ' concat INITIAL_DB2_GROUP_LEVEL "Prerequisito",
  replace(substr(EXAMPLE, 1, position(x'0D', EXAMPLE) -1), '-- Description: ', '') "Descrizione breve"
  --, EXAMPLE "Descrizione completa"
  from SERV_INFO
  order by SERVICE_CATEGORY, SERVICE_NAME;

Scarica l’elenco completo per la versione 7.3.

Sequenze ▲

Tabella fittizia con una sequenza di numeri (da V5R4)

with NbrTab (NBR) as
 (select 1 NBR from sysibm/sysdummy1
 union all
 select NBR + 1
  from NbrTab
  where NBR + 1 <= 10) -- modificare per estendere il limite superiore della serie
 select NBR from NbrTab;

Scovare i buchi nelle sequenze.

Si supponga di avere una tabella degli impiegati il cui codice è un progressivo e si voglia individuare se esistono dei codici mancanti nella sequenza dei dati esistenti. Nell’esempio il campo codice impiegato della tabella EMPLOYEE è alfanumerico ma contiene di fatto solo stringhe numeriche. Quindi può essere convertito agevolmente con la funzione int per effettuare le operazioni matematiche. In una tabella dove il codice da controllare è un campo numerico questa conversione non è necessaria.

select T1.EMPNO as "Codice1",
      (select min(T2.EMPNO) from EMPLOYEE as T2 where T2.EMPNO > T1.EMPNO) as "Codice2",
      ((select min(T2.EMPNO) from EMPLOYEE as T2 where T2.EMPNO > T1.EMPNO) - int(T1.EMPNO) -1) as "Gap"
  from EMPLOYEE as T1
  where ((select min(T2.EMPNO)
             from EMPLOYEE as T2
             where T2.EMPNO > T1.EMPNO) - int(T1.EMPNO)) > 1
  order by T1.EMPNO;

Sempre nella nostra tabella impiegati abbiamo bisogno di trovare il primo codice libero da usare per creare un nuovo impiegato

select T1.EMPNO + 1 as "Codice libero"
  from EMPLOYEE as T1
    exception join EMPLOYEE as  T2
      on int(T1.EMPNO) + 1 = T2.EMPNO
  fetch first 1 rows only;

Generare un ID univoco

-- restituisce bit data character string 13 bytes long - CHAR(13) FOR BIT DATA

select generate_unique()
  from sysibm/sysdummy1;
-- restituisce l'ID univoco formattato come timestamp

select timestamp(generate_unique())
  from sysibm/sysdummy1;

Esecuzione comandi ▲

Eseguire un comando CL da uno script SQL

call qcmdexc('CHGCURLIB $$LIBFS2  ', 20); 
-- da 7.1 non è più necessario il parametro con la lunghezza del comando
call qcmdexc('DSPLIBL *PRINT');

Check-up DB2 ▲

Esistono alcune funzioni/procedure che visualizzano informazioni utili per analizzare lo stato di “salute” e le performance del DB2

-- Retrieve the activity information for all objects within the DB2SAMPLE schema, using a maximum of 10 objects per each activity
call Health_Activity(1, 0, 'DB2SAMPLE', '%', 10, null, null, null);

-- Retrieve the overview for the entire database.
call Health_Database_Overview(1, '%', null, null, null);
  • Restituisce un conteggio dei vari tipi di oggetti di database:p.es. schemi, tabelle, file fisici, file logici, indici binari, indici EVI…
-- Retrieve the size limit information for all object names which start with the letter E, within the DB2SAMPLE schema, using a maximum of 2 objects per each design limit
call Health_Size_Limits(1, 0, 'DB2SAMPLE', 'E%', 2, null, null, null);

  • Informazioni sui record allocati (da 7.1 TR11 / 7.2 TR3)
select table_schema, table_name, table_partition, relative_record_number, lock_state, lock_status, lock_scope, job_name,
  (select JOB_STATUS
     from TABLE(ACTIVE_JOB_INFO('NO', '', substr(job_name, locate('/', job_name, 8)+1), '')) AJ where AJ.JOB_NAME = job_name) 
   as JOB_STATUS,
  (select FUNCTION
     from TABLE(ACTIVE_JOB_INFO('NO', '', substr(job_name, locate('/', job_name, 8)+1), '')) AJ where AJ.JOB_NAME = job_name) 
   as FUNCTION
  from RECORD_LOCK_INFO 
  where table_schema = 'DB2SAMPLE' and table_name = 'EMPLOYEE';

  • informazioni statistiche oggetti (proprietario, data creazione, ultimo utilizzo, giorni di utilizzo, ultima modifica, ultimo salvataggio, registrato su giornale…)
 -- statistiche degli oggetti di tipo *FILE della libreria DB2SAMPLE
select *
  from table(OBJECT_STATISTICS('DB2SAMPLE', 'FILE')) as T;


Catalogo DB2 ▲

  • elenco database definiti (WRKRDBDIRE)
select *
  from SYSCATALOGS;

  • elenco tabelle (file fisici)
select TABLE_NAME "File", SYSTEM_TABLE_NAME "Nome sistema", TABLE_TYPE "Tipo", TABLE_TEXT "Descrizione"
  from SYSTABLES
  where TABLE_TYPE not in('L', 'V', 'A') and TABLE_SCHEMA = 'DB2SAMPLE'
  order by TABLE_NAME;
  • elenco viste e indici (file logici)
select TABLE_NAME "File", SYSTEM_TABLE_NAME "Nome sistema", TABLE_TYPE "Tipo", TABLE_TEXT "Descrizione"
  from SYSTABLES
  where TABLE_TYPE in('L', 'V') and TABLE_SCHEMA = 'DB2SAMPLE'
  order by TABLE_NAME;
  • elenco indici
select INDEX_NAME "Indice", SYSTEM_INDEX_NAME "Nome sistema", TABLE_SCHEMA "Lib.file dip.", TABLE_NAME "File dip.", SYSTEM_TABLE_NAME "File dip. (nome sistema)", INDEX_TEXT "Descrizione", IS_UNIQUE "Chiave un."
from SYSINDEXES
  where table_schema = 'DB2SAMPLE'
  order by INDEX_NAME;
  • elenco viste
select TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE
  from VIEWS
  where TABLE_SCHEMA = 'DB2SAMPLE'
  order by TABLE_NAME;
  • definizione file
select COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
  NUMERIC_PRECISION, NUMERIC_SCALE
  from COLUMNS
  where TABLE_SCHEMA = 'DB2SAMPLE' and TABLE_NAME = 'EMPLOYEE'
  order by ORDINAL_POSITION;

-- oppure

select ORDINAL_POSITION as "Pos", COLUMN_NAME "Nome", SYSTEM_COLUMN_NAME "Nome sis.", DDS_TYPE as "Tipo (DDS)", DATA_TYPE as "Tipo", DATA_TYPE_LENGTH as "Lungh.", NUMERIC_SCALE as "Dec.", IS_NULLABLE as "Cons.nulli", COLUMN_HEADING as "Descrizione", COLUMN_TEXT as "Descrizione (TEXT)", CCSID
  from table(qsys2/qsqsyscol2(char(ucase('DB2SAMPLE'), 10), char(ucase('EMPLOYEE'), 10))) as DefFile;
select ORDINAL_POSITION as "Ord.", SYSTEM_COLUMN_NAME as "Col.sist.", COLUMN_NAME as "Col.", DATA_type as "Tipo", sum(STORAGE) over(partition by 1 order by ORDINAL_POSITION) - STORAGE + 1 as "Pos.", LENGTH as "Lungh.", NUMERIC_SCALE as "Dec.", 
    COLUMN_TEXT as "Descrizione (testo)", COLUMN_HEADING as "Descrizione"
  from QSYS2/SYSCOLUMNS
  where TABLE_SCHEMA = 'DB2SAMPLE' and TABLE_NAME = 'EMPLOYEE'
  order by ORDINAL_POSITION;

  • ricerca campo campo per descrizione su tutti i file fisici di una libreria
select C.SYSTEM_TABLE_NAME, SYSTEM_COLUMN_NAME, trim(COLUMN_TEXT) as COLUMN_TEXT, COLUMN_HEADING, DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
  from SYSCOLUMNS c
    inner join SYSTABLES T on (C.SYSTEM_TABLE_SCHEMA, C.SYSTEM_TABLE_NAME) = (T.SYSTEM_TABLE_SCHEMA, T.SYSTEM_TABLE_NAME) and TABLE_TYPE = 'T'
  where C.SYSTEM_TABLE_SCHEMA = 'DB2SAMPLE' 
    and (ucase(COLUMN_HEADING) like '%JOB%')
  order by C.SYSTEM_TABLE_NAME, SYSTEM_COLUMN_NAME;

  • riepilogo regole di integrità referenziale di una libreria
select CST.TABLE_NAME, CST.CONSTRAINT_NAME, CST.CONSTRAINT_TYPE, CST.CONSTRAINT_KEYS as
  "Key number", CST.CONSTRAINT_STATE as "State", CST.ENABLED, CST.CHECK_PENDING,
  CST.CONSTRAINT_TEXT, COL.COLUMN_NAME, REF.UNIQUE_CONSTRAINT_NAME as "Parent", REF.UPDATE_RULE,
  REF.DELETE_RULE
  from SYSCST as CST left
  join SYSREFCST as REF on CST.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA and
    CST.CONSTRAINT_NAME = REF.CONSTRAINT_NAME left
  join SYSCSTCOL as COL on CST.CONSTRAINT_SCHEMA = COL.CONSTRAINT_SCHEMA and
    CST.CONSTRAINT_NAME = COL.CONSTRAINT_NAME left
  join SYSKEYCST as key on COL.CONSTRAINT_SCHEMA = KEY.CONSTRAINT_SCHEMA and
    COL.CONSTRAINT_NAME = KEY.CONSTRAINT_NAME and COL.COLUMN_NAME = KEY.COLUMN_NAME
  where CST.CONSTRAINT_SCHEMA = 'DB2SAMPLE'
  order by CST.TABLE_NAME, CST.CONSTRAINT_TYPE, CST.CONSTRAINT_NAME, KEY.ORDINAL_POSITION;

  • elenco trigger su tabelle
select TRIGGER_NAME as "Nome trigger", EVENT_OBJECT_SCHEMA "Libreria", EVENT_OBJECT_TABLE as "File", ACTION_ORDER as "Sequenza", EVENT_MANIPULATION as "Evento", ACTION_TIMING as "Time",
trim(TRIGGER_PROGRAM_LIBRARY) concat '/' concat trim(TRIGGER_PROGRAM_NAME) as "Programma", ENABLED as "Attivo", ALLOW_REPEATED_CHANGE as "Mod.ripetute"
  from SYSTRIGGER
  where EVENT_OBJECT_SCHEMA = 'DB2SAMPLE'
  order by EVENT_OBJECT_TABLE, ACTION_ORDER;
  • relazione file dipendenti
with VISTEDEP (SYSTEM_VIEW_SCHEMA, SYSTEM_VIEW_NAME, LEVEL, OBJECT)
  as (select T1.SYSTEM_VIEW_SCHEMA, T1.SYSTEM_VIEW_NAME, 1, T1.OBJECT_NAME
        from SYSVIEWDEP as T1
        where T1.SYSTEM_VIEW_SCHEMA = 'DB2SAMPLE' and T1.OBJECT_NAME = 'EMPLOYEE'
      union all
        select T2.SYSTEM_VIEW_SCHEMA, T2.SYSTEM_VIEW_NAME, O.LEVEL + 1, T2.OBJECT_NAME
          from SYSVIEWDEP as T2
          join VISTEDEP as O on O.SYSTEM_VIEW_NAME = T2.OBJECT_NAME and O.SYSTEM_VIEW_SCHEMA =
            T2.OBJECT_SCHEMA)
  select distinct SYSTEM_VIEW_SCHEMA "Libreria", SYSTEM_VIEW_NAME "Dipendenza", level "Liv.", OBJECT "Oggetto"
    from VISTEDEP
    order by level desc, SYSTEM_VIEW_NAME;


Rete ▲

  • reperimento informazioni configurazione TCP del client
-- TCP/IP info client (vista)
select * from TCPIP_INFO;
-- TCP/IP info client (procedura)
select * from table(TCPIP_INFO()) as IP;
  • interfacce di rete attive: NETSTAT OPTION(*IFC)
-- TCP/IP interfacce (NETSTAT OPTION(*IFC))
select CONNECTION_TYPE, INTERNET_ADDRESS, NETWORK_ADDRESS, SUBNET_MASK, LINE_DESCRIPTION, INTERFACE_STATUS, AUTOSTART
  from NETSTAT_INTERFACE_INFO
  where INTERFACE_STATUS = 'ACTIVE'
  order by INTERNET_ADDRESS;

  • porte TCP/IP in ascolto: NETSTAT OPTION(*CNN)
-- NetStat (porte in ascolto) (NETSTAT OPTION(*CNN))
select CONNECTION_TYPE, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, NETWORK_CONNECTION_TYPE, IDLE_TIME, BIND_USER, NUMBER_OF_ASSOCIATED_JOBS
  from NETSTAT_INFO
  where LOCAL_ADDRESS = '0.0.0.0'
  order by LOCAL_PORT;

  • quale è l’indirizzo IP con cui IBM i si presenta in rete?
select MYIP
 from (values(systools.httpgetclob('https://api.ipify.org',''))) WS(MYIP);
  • connessioni di rete aperte
-- NetStat (connessioni aperte) (NETSTAT OPTION(*CNN))
select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, IDLE_TIME, BIND_USER, BYTES_SENT_REMOTELY, BYTES_RECEIVED_LOCALLY, NUMBER_OF_ASSOCIATED_JOBS
  from NETSTAT_INFO
  where LOCAL_ADDRESS not in('0.0.0.0', '127.0.0.1')
  order by LOCAL_PORT, REMOTE_ADDRESS;
  • connessioni di rete aperte dal client corrente
-- NetStat (connessioni aperte) (NETSTAT OPTION(*CNN)) dal client corrente
select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, IDLE_TIME, BIND_USER, BYTES_SENT_REMOTELY, BYTES_RECEIVED_LOCALLY, NUMBER_OF_ASSOCIATED_JOBS
  from NETSTAT_INFO
  where REMOTE_ADDRESS = (select CLIENT_IP_ADDRESS from TCPIP_INFO)
  order by LOCAL_PORT, REMOTE_ADDRESS;

  • connessioni di rete aperte dal client corrente: job info
-- NetStat: job info delle connessioni aperte dal cliente corrente
select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, AUTHORIZATION_NAME as USER, JOB_NAME as JOB
  from NETSTAT_JOB_INFO
  where REMOTE_ADDRESS = (select CLIENT_IP_ADDRESS from TCPIP_INFO)
    and JOB_NAME is not null
  order by LOCAL_PORT, JOB_NAME;

  • instradamenti: NETSTAT OPTION(*RTE)
-- NetStat (instradamenti) (NETSTAT OPTION(*RTE))
select CONNECTION_TYPE, ROUTE_DESTINATION, SUBNET_MASK, NEXT_HOP, ROUTE_STATUS as ROUTE_AVAILABLE, LOCAL_BINDING_INTERFACE, LOCAL_BINDING_NETWORK_ADDRESS, LOCAL_BINDING_SUBNET_MASK, LOCAL_BINDING_LINE_DESCRIPTION, LOCAL_BINDING_VIRTUAL_LAN_ID
  from NETSTAT_ROUTE_INFO
  where ROUTE_STATUS = 'YES'
  order by ROUTE_DESTINATION;

Check-up sistema ▲

  • riepilogo informazioni sul sistema (alcune delle informazioni restituite richiedono IBM i 7.3 TR6)
select
   current server as "Nome partizione",
   (select NUMBER_OF_PARTITIONS
      from table(QSYS2/SYSTEM_STATUS('NO'))) as "Num.tot.partizioni",
   (select HOST_NAME
      from sysibmadm.env_sys_info) as "Nome host",
   (select CONFIGURED_CPUS
      from sysibmadm.env_sys_info) as "CPU config.",
   (select CURRENT_CPU_CAPACITY
      from table(QSYS2/SYSTEM_STATUS('NO'))) as "CPU attive",
   (select CONFIGURED_MEMORY
      from sysibmadm.env_sys_info) as "Memoria config.",   
   (select TOTAL_AUXILIARY_STORAGE
      from table(QSYS2/SYSTEM_STATUS('NO'))) as "ASP tot.",
   (select SYSTEM_ASP_STORAGE
      from table(QSYS2/SYSTEM_STATUS('NO'))) as "ASP sis.",
   (select cast(current_numeric_value as dec(5, 0))
      from QSYS2.SYSTEM_VALUE_INFO
      where SYSTEM_VALUE_NAME = 'QCCSID') as "CCSID sistema",
   (select FW_MACHINE_TYPE_MODEL
      from systools/FIRMWARE_CURRENCY) as "Tipo/Modello",
   (select OS_NAME concat ' ' concat OS_VERSION concat '.' concat OS_RELEASE as release
      from sysibmadm/env_sys_info) as "Release OS",
   (select count(*)
      from SYSTOOLS/GROUP_PTF_CURRENCY
      where PTF_GROUP_CURRENCY = 'UPDATE AVAILABLE') as "Gruppi PTF da aggiornare",
   (select FW_CURRENTFIXPACK
      from systools/FIRMWARE_CURRENCY) as "Rel.firmware",
   (select FW_RECOMMENDED_UPDATE
      from systools/FIRMWARE_CURRENCY) as "Rel.firmware da aggiornare",
   (select MAXIMUM_JOBS_IN_SYSTEM
      from table(QSYS2/SYSTEM_STATUS('NO'))) as "Num.max.lavori",
   (select SERVER_IP_ADDRESS from QSYS2/TCPIP_INFO) as "Indirizzo IP",
   (select count(*)
      from QSYS2/NETSTAT_INTERFACE_INFO
      where INTERFACE_STATUS = 'ACTIVE' and LINE_DESCRIPTION <> '*LOOPBACK') as "Interfacce attive"
  from SYSIBM.SYSDUMMY1;

  • informazioni generali sul sistema (versione sistema operativo, host name, numero processori, memoria RAM)
-- info ambiente
select * from SYSIBMADM/ENV_SYS_INFO;

  • Valori di sistema (WRKSYSVAL)
select SYSTEM_VALUE_NAME "Valore di sistema", coalesce(char(CURRENT_NUMERIC_VALUE), trim(CURRENT_CHARACTER_VALUE)) "Valore corrente"
  from SYSTEM_VALUE_INFO;
  • System status (WRKSYSSTS/WRKSYSACT)
select HOST_NAME "Nome sistema",
   TOTAL_JOBS_IN_SYSTEM "Num.lavori tot.",
   (MAXIMUM_JOBS_IN_SYSTEM - TOTAL_JOBS_IN_SYSTEM) "Lavori disponibili",
   ACTIVE_JOBS_IN_SYSTEM "Lav.attivi",
   INTERACTIVE_JOBS_IN_SYSTEM "Lav.interattivi",
   CONFIGURED_CPUS "Num.proc.",
   ELAPSED_CPU_USED "% CPU corrente",
   AVERAGE_CPU_UTILIZATION "% CPU media",
   MAXIMUM_CPU_UTILIZATION "% CPU max",
   TOTAL_AUXILIARY_STORAGE "Storage tot.",
   SYSTEM_ASP_USED "% storage occ.",
   CURRENT_TEMPORARY_STORAGE "Storage temp. corrente",
   MAXIMUM_TEMPORARY_STORAGE_USED "Storage temp. max",
   PERMANENT_ADDRESS_RATE "% ind.perm.",
   TEMPORARY_ADDRESS_RATE "% ind.temp.",
   RESTRICTED_STATE "Stato limitato"
  from SYSTEM_STATUS_INFO;
  • Lotti di memoria (WRKSHRPOOL/WRKSYSSTS)
select SYSTEM_POOL_ID "ID lotto", POOL_NAME "Lotto", DESCRIPTION "Descrizione",
    CURRENT_SIZE "Dim. (Mb)", RESERVED_SIZE "Dim.riserv. (Mb)",
    PAGING_OPTION "Opz.paging", 
    MAXIMUM_ACTIVE_THREADS "Lav.att.max",
    CURRENT_THREADS "Lav.att.corrente",
    CURRENT_INELIGIBLE_THREADS "Lav.inel.",
    ELAPSED_DATABASE_FAULTS "DB err", ELAPSED_DATABASE_PAGES "DB pag.",
    ELAPSED_NON_DATABASE_FAULTS "Non DB err", ELAPSED_NON_DATABASE_PAGES "Non DB pag.",
    ELAPSED_ACTIVE_TO_WAIT "Act.->Att.", ELAPSED_WAIT_TO_INELIGIBLE "Wait inel.",
    ELAPSED_ACTIVE_TO_INELIGIBLE "Act.inel."
  from MEMORY_POOL_INFO;
  • informazioni stato dischi (WRKDSKSTS)
select ASP_NUMBER, DISK_TYPE, DISK_MODEL, UNIT_NUMBER, dec(UNIT_STORAGE_CAPACITY/1073741824,0, 13, 2) as UNIT_STORAGE_CAPACITY_GB, dec(UNIT_SPACE_AVAILABLE/1073741824,0, 13, 2) as UNIT_SPACE_AVAILABLE_GB, PERCENT_USED, LOGICAL_MIRRORED_PAIR_STATUS, MIRRORED_UNIT_STATUS
  from SYSDISKSTAT;

  • programmi su licenza (WRKLICINF): visualizza le informazioni solo dei prodotti che richiedono una licenza
select *
  from LICENSE_INFO
  order by PRODUCT_ID, FEATURE_ID;
  • software installato (DSPSFWRSC):
call qsys2.qcmdexc('QSYS/DSPSFWRSC OUTPUT(*OUTFILE) OUTFILE(QTEMP/PROD_INFO) OUTMBR(*FIRST *REPLACE)');

select lcprdi "Prodotto", lcpfgi "Id caricamento", lcsfgi "Opzione",
  ltrim(lcvrsl, '0') concat (case when lcrlsl <> '00' then '.' else '' end) concat ltrim(lcrlsl, '0') concat (case when lcmodl <> '00' then '.' else '' end) concat ltrim(lcmodl, '0') "Versione",
  message_text "Descrizione"
  from qtemp.prod_info
    inner join qsys2.message_file_data
      on message_file_library = (case when lcdtml = '*LIBL' then 'QSYS' else lcdtml end)
       and message_file = lcdtmf
       and message_id = lcdtmi
  where LCPFGI <> '2932' -- senza dettaglio lingua installata
  order by LCPRDI;

  • informazioni su profili utenti
select AUTHORIZATION_NAME "Utente", TEXT_DESCRIPTION "Descrizione", PREVIOUS_SIGNON "Ult.collegamento", SIGN_ON_ATTEMPTS_NOT_VALID "Coll.non validi", STATUS "Stato", PASSWORD_CHANGE_DATE "Data/Ora mod.pwd", DAYS_UNTIL_PASSWORD_EXPIRES "gg a scadenza password", SET_PASSWORD_TO_EXPIRE "Pwd a scad.", USER_CLASS_NAME "Classe", SPECIAL_AUTHORITIES "Aut.spec.", LIMIT_CAPABILITIES "Capac.lim.", GROUP_PROFILE_NAME "Profilo di gruppo", OWNER "Propr.", trim(INITIAL_MENU_LIBRARY_NAME) concat '/' concat trim(INITIAL_MENU_NAME) "Menu", trim(INITIAL_PROGRAM_LIBRARY_NAME) concat '/' concat trim(INITIAL_PROGRAM_NAME) "Menu", trim(JOB_DESCRIPTION_LIBRARY_NAME) concat '/' concat trim(JOB_DESCRIPTION_NAME) "Desc.lavoro", trim(OUTPUT_QUEUE_LIBRARY_NAME) concat '/' concat trim(OUTPUT_QUEUE_NAME) "Coda emissione", PRINT_DEVICE "Stampante", HOME_DIRECTORY "Indirizzario corrente"
  from USER_INFO
  order by AUTHORIZATION_NAME;
  • informazioni lavoro corrente (stato, priorità, sottosistema, CPU e RAM utilizzata, nome del client, utente del client…)
select *
  from table(get_job_info('*')) as Job;
  • joblog lavoro corrente (DSPJOBLOG)
select MESSAGE_ID, MESSAGE_TYPE, SEVERITY, MESSAGE_TIMESTAMP, MESSAGE_FILE, trim(MESSAGE_TEXT) as MESSAGE_TEXT, trim(MESSAGE_SECOND_LEVEL_TEXT) as MESSAGE_SECOND_LEVEL_TEXT
  from table(JOBLOG_INFO('*')) as JOBLOG
  order by MESSAGE_TIMESTAMP desc;
  • estrazione dei soli messaggi di eccezione dalla stampa di un joblog (da 7.3 TR6). Sostituire numero/utente/nome con il numero, utente e nome del lavoro che contiene la stampa del joblog.
with
 CPF as (
  select ORDINAL_POSITION, SPOOLED_DATA
    from table(SYSTOOLS/SPOOLED_FILE_DATA('numero/utente/nome', 'QPJOBLOG'))
    where substr(SPOOLED_DATA, 12, 6) = 'Uscita'),
 CPF_MSG as (
  select ORDINAL_POSITION, SPOOLED_DATA
    from table(SYSTOOLS/SPOOLED_FILE_DATA('numero/utente/nome', 'QPJOBLOG'))
    where SPOOLED_DATA not like '%Visual. registrazione lavoro%'
      and SPOOLED_DATA not like '%Nome lavoro  . . . . . . . . :%'
      and SPOOLED_DATA not like '%Descrizione lavoro . . . . . :%'
      and SPOOLED_DATA not like '%IDMSG      TIPO%')
 select substr(CPF.SPOOLED_DATA, 41, 8) "Data", substr(CPF.SPOOLED_DATA, 51, 8) "Ora",
    substr(CPF.SPOOLED_DATA, 1, 7) "ID MSG", substr(CPF.SPOOLED_DATA, 36, 2) "Gravità", 
    trim(substr(CPF.SPOOLED_DATA, 114, 10)) concat '/' concat trim(substr(CPF.SPOOLED_DATA, 102, 10)) "Programma",
    substr(SP.SPOOLED_DATA, posstr(SP.SPOOLED_DATA, '. . . :') + 10) "Messaggio"
   from CPF
     inner join CPF_MSG as SP
       on CPF.ORDINAL_POSITION = SP.ORDINAL_POSITION - 1
   order by CPF.ORDINAL_POSITION;

  • lista librerie, current schema e current path del lavoro corrente
select 'LL' as "Gruppo", ORDINAL_POSITION as "Pos.", SYSTEM_SCHEMA_NAME "Lib.", TYPE as "Tipo", TEXT_DESCRIPTION as "Descrizione" from LIBRARY_LIST_INFO
 union all
select 'CS', 0, CURRENT SCHEMA as "CurLib", 'CURRENT', ' ' from SYSDUMMY1
 union all
select 'CP', 0, CURRENT PATH as "CurLib", 'PATH', ' ' from SYSDUMMY1
order by 1, 2;

  • elenco lavori in errore (stato MSGW)
select SUBSYSTEM "Sottosistema", JOB_NAME "Lavoro", AUTHORIZATION_NAME "Utente", JOB_TYPE "Tipo lavoro", FUNCTION_TYPE "Tipo funzione", FUNCTION "Funzione", CPU_TIME "Tempo ut.CPU", TOTAL_DISK_IO_COUNT "Tot. I/O"
  from TABLE(ACTIVE_JOB_INFO('NO', -- reset statistics
                             '',  -- subsystem filter
                             '',  -- job name filter (* current, *ALL tutti, *CURRENT tutti i lavori con nome uguale al corrente, *SBS all active subsystems, *SYS all active system jobs)
                             '' -- current user filter (comma delimited list)
                             )) ACT_JOBS
  where JOB_STATUS = 'MSGW'                           
  order by SUBSYSTEM, JOB_NAME;
  • info lavori schedulati (WRKJOBSCDE)
select SCHEDULED_JOB_NAME, STATUS, SCHEDULED_TIME, SCHEDULED_DATE, SCHEDULED_DAYS, FREQUENCY, RELATIVE_DAYS_OF_MONTH, DATES_OMITTED, NEXT_SUBMISSION_DATE, DESCRIPTION, COMMAND_STRING, USER_PROFILE_FOR_SUBMITTED_JOB, JOB_DESCRIPTION_LIBRARY_NAME concat '/' concat JOB_DESCRIPTION_NAME as JOB_DESCRIPTION, JOB_QUEUE_LIBRARY_NAME concat '/' concat JOB_QUEUE_NAME as JOB_QUEUE,
    LAST_SUCCESSFUL_SUBMISSION_JOB, LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP
from SCHEDULED_JOB_INFO
  order by SCHEDULED_JOB_NAME;
  • info lavori schedulati con ora inizio e fine dell’ultima immissione e calcolo della durata del tempo di esecuzione (N.B. questa query può richiedere anche parecchio tempo se si selezionano molti record. Circa 1 minuto per ogni record)
select SCHEDULED_JOB_NAME, STATUS, SCHEDULED_TIME, SCHEDULED_DATE, SCHEDULED_DAYS, FREQUENCY, RELATIVE_DAYS_OF_MONTH, DATES_OMITTED, NEXT_SUBMISSION_DATE, DESCRIPTION, COMMAND_STRING, USER_PROFILE_FOR_SUBMITTED_JOB, JOB_DESCRIPTION_LIBRARY_NAME concat '/' concat JOB_DESCRIPTION_NAME as JOB_DESCRIPTION, JOB_QUEUE_LIBRARY_NAME concat '/' concat JOB_QUEUE_NAME as JOB_QUEUE,
  LAST_SUCCESSFUL_SUBMISSION_JOB, LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP, 
  case when LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP is null then null else (select timestamp(MESSAGE_TIMESTAMP, 0)
     from table(HISTORY_LOG_INFO(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP,
                                 timestamp(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP) + 24 hours)) as HL
     where FROM_JOB = LAST_SUCCESSFUL_SUBMISSION_JOB and MESSAGE_ID = 'CPF1164') end as LAST_SUCCESSFUL_SUBMISSION_END,
  case when LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP is null then null else round(timestampdiff(4, char((select MESSAGE_TIMESTAMP 
     from table(HISTORY_LOG_INFO(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP,
                                 timestamp(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP) + 24 hours)) as HL
     where FROM_JOB = LAST_SUCCESSFUL_SUBMISSION_JOB and MESSAGE_ID = 'CPF1164') - LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP))/60,0, 2) end as DURATA_HH,
   case when LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP is null then null else timestampdiff(4, char((select MESSAGE_TIMESTAMP 
     from table(HISTORY_LOG_INFO(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP,
                                 timestamp(LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP) + 24 hours)) as HL
     where FROM_JOB = LAST_SUCCESSFUL_SUBMISSION_JOB and MESSAGE_ID = 'CPF1164') - LAST_SUCCESSFUL_SUBMISSION_TIMESTAMP)) end as DURATA_MM
  from SCHEDULED_JOB_INFO
  where STATUS = 'SCHEDULED' -- solo schedulati
  order by SCHEDULED_JOB_NAME;
  • elenco di risposte del sistema (WRKRPYLE)
select *
  from REPLY_LIST_INFO;
  • informazioni su allocazione di un oggetto
select MEMBER_LOCK_TYPE, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE, JOB_NAME
  from table(OBJECT_LOCK_INFO('DB2SAMPLE', 'EMPLOYEE', '*FILE', 0)) as OBJECT_LOCK;
  • informazioni su allocazione di un gruppo di oggetti (ATTENZIONE: può richiedere parecchio tempo, è preferibile utilizzare la function table su un singolo oggetto)
select SYSTEM_OBJECT_NAME "Oggetto", MEMBER_LOCK_TYPE, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE, JOB_NAME
  from OBJECT_LOCK_INFO
  where OBJECT_SCHEMA = 'DB2SAMPLE' and SQL_OBJECT_TYPE = 'TABLE';

  • lista spool file per utente (da 7.1)
select *
  from OUTPUT_QUEUE_ENTRIES
  where USER_NAME = 'QSYSOPR';

IPL

Informazioni sull’ultimo spegnimento e accensione del sistema:

PTF sistema operativo ▲

  • stato installazione PTF (WRKPTFGRP)
select PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_LEVEL, PTF_GROUP_STATUS
  from GROUP_PTF_INFO
  order by PTF_GROUP_NAME, PTF_GROUP_LEVEL desc;

  • cumulativo più recente installato
select max(PTF_GROUP_LEVEL) as "Most recently install CUM"
from GROUP_PTF_INFO
where PTF_GROUP_DESCRIPTION like 'CUMULATIVE PTF PACKAGE%' and PTF_GROUP_STATUS = 'INSTALLED';
  • stato installazione PTF e disponibilità aggiornamenti da installare da sito IBM
select *
  from SYSTOOLS/GROUP_PTF_CURRENCY
  order by PTF_GROUP_CURRENCY desc, PTF_GROUP_ID;

  • dettaglio PTF installate (DSPPTF)
select PTF_GROUP_NAME "Gruppo", trim(PTF_GROUP_DESCRIPTION) "Descrizione", PTF_PRODUCT_ID "Prodotto", trim(PTF_PRODUCT_DESCRIPTION) "Descrizione prodotto", PTF_IDENTIFIER "PTF ID", 
    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato", varchar_format(PTF_STATUS_TIMESTAMP, 'DD/MM/IYYY') "Data stato", 
    PTF_ACTION_PENDING "In attesa", PTF_ACTION_REQUIRED "Az.richiesta", PTF_IPL_ACTION "Az.IPL", PTF_IPL_REQUIRED "Rich.IPL", 
    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Incluso in gruppo", PTF_CUM_PACKAGE "Incluso in cumulativo", PTF_IS_RELEASED "Rilasciata", PTF_SUPERCEDED_BY_PTF "Superata da", 
    varchar_format(PTF_CREATION_TIMESTAMP, 'DD/MM/IYYY') "Data creaz."
  from SYSTOOLS/GROUP_PTF_DETAILS
  where PTF_STATUS <> 'PTF MISSING'
  order by PTF_GROUP_NAME, PTF_IDENTIFIER;
  • dettaglio PTF in attesa di un’azione
select PTF_GROUP_NAME "Gruppo", trim(PTF_GROUP_DESCRIPTION) "Descrizione", PTF_PRODUCT_ID "Prodotto", trim(PTF_PRODUCT_DESCRIPTION) "Descrizione prodotto", PTF_IDENTIFIER "PTF ID", 
    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato", varchar_format(PTF_STATUS_TIMESTAMP, 'DD/MM/IYYY') "Data stato", 
    PTF_ACTION_PENDING "In attesa", PTF_ACTION_REQUIRED "Az.richiesta", PTF_IPL_ACTION "Az.IPL", PTF_IPL_REQUIRED "Rich.IPL", 
    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Incluso in gruppo", PTF_CUM_PACKAGE "Incluso in cumulativo", PTF_IS_RELEASED "Rilasciata", PTF_SUPERCEDED_BY_PTF "Superata da", 
    varchar_format(PTF_CREATION_TIMESTAMP, 'DD/MM/IYYY') "Data creaz."
  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_GROUP_NAME, PTF_IDENTIFIER;

Altre istruzioni sono disponibili in https://gist.github.com/mk1tools/83a01cd4044909ca2c99f31ca304e80b.

Grafici ▲

Per quanto inusuale potrebbe a volte tornare comodo rappresentare un campo numerico in forma grafica.

  • grafico a lunghezza imprevedibile
select EMPNO, SALARY, repeat('*', int(SALARY/1000)) as Grafico
from EMPLOYEE
where SALARY <> 0
order by EMPNO;

  • grafico a lunghezza massima fissa (da V5R4)
with
 temp1 (EMPNO, SALARY) as
  (select EMPNO, SALARY
  from EMPLOYEE
  where SALARY <> 0),
 temp2 (MaxSalary) as
  (select int(max(SALARY))/25 -- 25 è la lunghezza massima del campo grafico
     from EMPLOYEE
     where SALARY <> 0)
 select EMPNO, SALARY,
  repeat('*', int(SALARY/MaxSalary)) as Grafico
     from temp1, temp2
     order by SALARY desc;

Bibliografia ▲