Query di tutti i giorni

Query_di_tutti_i_giorni.sql

-- Autore: Marco Riva 
-- (c) MK1 www.markonetools.it
-- ultimo aggiornamento: 27-giu-2024

/* QUERY DI TUTTI I GIORNI */

-- indice
-->>> [A] JOB/LAVORO <<<
  -- [A01] reperimento nome job corrente
  -- [A02] reperimento lista librerie job corrente
  -- [A03] modifica lista librerie
  -- [A04] modifica current schema e libreria corrente
-->>> [B] RICERCHE OGGETTI E STREAM FILES <<<
  -- [B01] ricerca oggetto in librerie
  -- [B02] ricerca sorgente
-->>> [C] INFO SU OGGETTI PROGRAMMA <<<
  -- [C01] informazioni oggetto programma (DSPPGM, DSPSRVPGM)
  -- [C02] informazioni oggetto modulo (DSPPGM, DSPSRVPGM)
  -- [C03] programmi embedded SQL statici: dettaglio file e campi utilizzati (da 7.2)
  -- [C04] informazioni bind di un programma (DSPPGM, DSPSRVPGM)
  -- [C05] informazioni bind di un programma e service programs correlati presenti in lista librerie (DSPPGM, DSPSRVPGM)
  -- [C06] binding directories (DSPBNDDIR)
  -- [C07] elenco procedure di un service program
  -- [C08] ricerca procedura in un elenco di programmi di una libreria
  -- [C09] oggetti referenziati da un programma (DSPPGMREF)
-->>> [D] AREE DATI/USER SPACES <<<
  -- [D01] visualizza area dati (DSPDTAARA)
  -- [D02] visualizzazione user space
-->>> [E] JOB IN ESECUZIONE E RISORSE IN USO <<<
  -- [E01] informazioni su allocazione oggetto (WRKOBJLCK)
  -- [E02] file db aperti da un job
  -- [E03] proprietà job
-->>> [F] DATABASE <<<
  -- [F01] definizione tabella (DSPFFD)
  -- [F02] definizione tabella con evidenziazione campi chiave primaria o univoca
  -- [F03] ricerca campi in un gruppo di tabelle
  -- [F04] definizione indici dipendenti da una tabella (DBPDBR+DSPFD)
  -- [F05] definizione indici dipendenti da una tabella con dettaglio definizione campi chiave (DSPDBR+DSPFD)
  -- [F06] oggetti dipendenti da una tabella (DSPDBR)
  -- [F07] viste dipendenti da una tabella (ricerca ricorsiva) (DSPDBR plus)
  -- [F08] elenco file fisici/tabelle
  -- [F09] elenco file logici/viste/indici
  -- [F10] file fisici registrati su giornale
  -- [F11] file fisici con trigger
  -- [F12] tabella con campo identity: verifica limite
  -- [F13] confronto file fisici/logici tra due librerie
  -- [F14] confronto lista oggetti database mancanti tra due librerie
--


-->>> [A] JOB/LAVORO <<<

-- [A01] reperimento nome job corrente
values JOB_NAME;

-- [A02] reperimento lista librerie job corrente
select 'LL' as "Gruppo", ORDINAL_POSITION as "Pos.", SYSTEM_SCHEMA_NAME "Lib.", TYPE as "Tipo", TEXT_DESCRIPTION as "Descrizione"
  from QSYS2.LIBRARY_LIST_INFO
 union all
values ('CS', 0, CURRENT SCHEMA, 'CURRENT_SCHEMA', ' ')
 union all
values ('CP', 0, CURRENT PATH, 'PATH', ' ')
order by 1, 2;

-- [A03] modifica lista librerie
call qsys2.qcmdexc('ADDLIBLE ' concat :Libreria concat ' *FIRST');
call qsys2.qcmdexc('RMVLIBLE ' concat :Libreria);
call qsys2.qcmdexc('CHGLIBL LIBL(QTEMP QGPL)');

-- [A04] modifica current schema e libreria corrente
set current schema = :Libreria;
call qsys2.qcmdexc('CHGCURLIB ' concat :Libreria);


-->>> [B] RICERCHE OGGETTI E STREAM FILES <<<

-- [B01] ricerca oggetto in librerie
select OBJLIB "Libreria", OBJNAME "Oggetto", OBJTYPE "Tipo", OBJATTRIBUTE "Attributo", 
       timestamp(OBJCREATED, 0) as "Data/ora creazione", 
       timestamp(CHANGE_TIMESTAMP, 0) as "Data/ora modifica", 
       dec(OBJSIZE/1048576, 10, 2)"Dimensione (Mb)",
       OBJTEXT "Descrizione", timestamp(LAST_USED_TIMESTAMP, 0) as "Data/ora ult.utilizzo", DAYS_USED_COUNT "GG utilizzo",
       SOURCE_LIBRARY concat '/' concat SOURCE_FILE concat '(' concat SOURCE_MEMBER concat ')' "Sorgente" 
  from table(QSYS2.OBJECT_STATISTICS(--OBJECT_SCHEMA => :Libreria, 
                                     OBJECT_SCHEMA => '*LIBL', -- lista librerie
                                     --OBJECT_SCHEMA => '*ALLUSR', -- tutte le librerie utente
                                     --OBJTYPELIST => :Tipo, -- filtro per tipo oggetto
                                     OBJTYPELIST => '*ALL', -- tutti i tipi oggetto
                                     OBJECT_NAME => :Oggetto)) as T
  --where OBJATTRIBUTE = :Attributo
  order by OBJLIB, OBJNAME;

-- [B02] ricerca sorgente
select SYSTEM_TABLE_SCHEMA "Libreria", SYSTEM_TABLE_NAME "File", SYSTEM_TABLE_MEMBER "Membro", SOURCE_TYPE "Tipo sorg.", 
       PARTITION_TEXT "Descr.sorg.", NUMBER_ROWS "Num.righe",
       timestamp(CREATE_TIMESTAMP, 0) "Data/ora creaz.sorg.", timestamp(LAST_SOURCE_UPDATE_TIMESTAMP, 0) "Data/ora mod.sorg." 
  from QSYS2.SYSPARTITIONSTAT as S
  where SYSTEM_TABLE_MEMBER = :Membro 
    -- singola libreria
    -- and SYSTEM_TABLE_SCHEMA = :Libreria
    -- lista librerie
    and SYSTEM_TABLE_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)   
    and SYSTEM_TABLE_NAME like 'Q%'
    -- esclusionme file sorgenti non rilevanti
    and SYSTEM_TABLE_NAME not in('EFEVENTF', 'QSQLTEMP1') and SOURCE_TYPE not like '%+_%' escape '+'
  order by SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER;


-->>> [C] INFO SU OGGETTI PROGRAMMA <<<

-- [C01] informazioni oggetto programma (DSPPGM, DSPSRVPGM)
select PROGRAM_LIBRARY "Libreria", PROGRAM_NAME "Nome", PROGRAM_TYPE "Tipo pgm", OBJECT_TYPE "Tipo obj", PROGRAM_ATTRIBUTE "Attr.",
       TEXT_DESCRIPTION "Descrizione", timestamp(CREATE_TIMESTAMP, 0) "Data/ora creaz.",
       PROGRAM_OWNER "Propr.", USER_PROFILE "Profilo", EARLIEST_POSSIBLE_RELEASE "Rel.min.", 
       MINIMUM_NUMBER_PARMS concat '-' concat MAXIMUM_NUMBER_PARMS "Param.min-max",
       ALLOW_RTVCLSRC "Rep.src.CL",
       trim(PROGRAM_ENTRY_PROCEDURE_MODULE_LIBRARY) concat '/' concat trim(PROGRAM_ENTRY_PROCEDURE_MODULE) "PEP", 
       ACTIVATION_GROUP "Gruppo attiv.",
       PROCEDURE_EXPORTS "Num.proc.esportate", SIGNATURES "Num.firme", cast(EXPORT_SIGNATURES as varchar(16) for bit data) "Firme", 
       SERVICE_PROGRAMS "Num.pgm servizio", 
       trim(SOURCE_FILE_LIBRARY) concat '/' concat trim(SOURCE_FILE) concat '(' concat trim(SOURCE_FILE_MEMBER) concat ')' "Sorgente", 
       timestamp(SOURCE_FILE_CHANGE_TIMESTAMP, 0) "Data/ora src",
       FIX_DECIMAL_DATA "Ignora err.dati decimali",
       SQL_STATEMENT_COUNT "Num.istruzioni SQL", SQL_COMMITMENT_CONTROL "SQL contr.sinc.", SQL_NAMING "SQL naming", 
       SQL_DATE_FORMAT "SQL formato data"
  from QSYS2.PROGRAM_INFO
  where PROGRAM_NAME = :Programma
    -- singola libreria 
    --and PROGRAM_LIBRARY = :Libreria
    -- lista librerie
    and PROGRAM_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
  order by PROGRAM_LIBRARY, PROGRAM_NAME;

-- [C02] informazioni oggetto modulo (DSPPGM, DSPSRVPGM)
select PROGRAM_LIBRARY "Libreria", PROGRAM_NAME "Nome", OBJECT_TYPE "Tipo", 
       trim(BOUND_MODULE_LIBRARY) concat '/' concat trim(BOUND_MODULE) "Modulo",
       MODULE_ATTRIBUTE "Tipo mod.", timestamp(MODULE_CREATE_TIMESTAMP, 0) "Data/ora creaz.mod.", 
       trim(SOURCE_FILE_LIBRARY) concat '/' concat trim(SOURCE_FILE) concat '(' concat trim(SOURCE_FILE_MEMBER) concat ')' "Sorgente", 
       timestamp(SOURCE_CHANGE_TIMESTAMP, 0) "Data/ora src",
       DEBUG_DATA "Dati debug", OPTIMIZATION_LEVEL "Liv.ottimizzazione", TARGET_RELEASE "Rel.min.",
       NUMBER_PROCEDURES "Num.procedure", 
       ALLOW_RTVCLSRC "Reperimento src CL", 
       SQL_STATEMENT_COUNT "Num.istruzioni SQL", SQL_COMMITMENT_CONTROL "Contr.sincronia", SQL_NAMING "SQL Naming", 
       SQL_DATE_FORMAT "SQL formato data"
  from QSYS2.BOUND_MODULE_INFO
  where PROGRAM_NAME = :Programma
    -- singola libreria 
    --and PROGRAM_LIBRARY = :Libreria
    -- lista librerie
    and PROGRAM_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
  order by PROGRAM_LIBRARY, PROGRAM_NAME;    

-- [C03] programmi embedded SQL statici: dettaglio file e campi utilizzati (da 7.2)
with
 PGM_STAT as
  (select A.NAMING, A.DECIMAL_POINT, A.SQL_STRING_DELIMITER, B.STATEMENT_NUMBER, B.STATEMENT_TEXT, 
          A.PROGRAM_SCHEMA, A.SYSTEM_PROGRAM_NAME, A.PROGRAM_TYPE
     from QSYS2.SYSPROGRAMSTAT as A
       inner join QSYS2.SYSPROGRAMSTMTSTAT as B
         on (A.PROGRAM_SCHEMA, A.PROGRAM_NAME, A.MODULE_NAME) = (B.PROGRAM_SCHEMA, B.PROGRAM_NAME, B.MODULE_NAME)
     where -- singola libreria
           A.PROGRAM_SCHEMA = :Libreria
           -- lista librerie
           --A.PROGRAM_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
           -- filtro oggetto programma
     --and A.PROGRAM_NAME = :Oggetto
  ),
 TAB_STAT as
 (select PROGRAM_SCHEMA, SYSTEM_PROGRAM_NAME, SQL_STATEMENT_TYPE, USAGE_TYPE, STATEMENT_NUMBER, 
        listagg(ltrim((case when C.SCHEMA is null then ' ' else C.SCHEMA concat '/' end) concat C.NAME), ', ')
                within group(order by C.NAME) as TABLES
    from PGM_STAT, 
      table(QSYS2.PARSE_STATEMENT(SQL_STATEMENT => STATEMENT_TEXT,
                                  NAMING => NAMING, 
                                  DECIMAL_POINT => DECIMAL_POINT, 
                                  SQL_STRING_DELIMITER => SQL_STRING_DELIMITER)) as C
    where C.NAME_TYPE = 'TABLE'
    group by PROGRAM_SCHEMA, SYSTEM_PROGRAM_NAME, SQL_STATEMENT_TYPE, USAGE_TYPE, STATEMENT_NUMBER),
 COL_STAT as
 (select PROGRAM_SCHEMA, SYSTEM_PROGRAM_NAME, SQL_STATEMENT_TYPE, USAGE_TYPE, STATEMENT_NUMBER, 
         listagg(distinct COLUMN_NAME, ', ') within group(order by COLUMN_NAME) as COLUMNS
    from PGM_STAT, 
      table(QSYS2.PARSE_STATEMENT(SQL_STATEMENT => STATEMENT_TEXT,
                                  NAMING => NAMING, 
                                  DECIMAL_POINT => DECIMAL_POINT, 
                                  SQL_STRING_DELIMITER => SQL_STRING_DELIMITER)) as C
  where C.NAME_TYPE = 'COLUMN'
  group by PROGRAM_SCHEMA, SYSTEM_PROGRAM_NAME, SQL_STATEMENT_TYPE, USAGE_TYPE, STATEMENT_NUMBER)
  select P.PROGRAM_SCHEMA "Libreria", P.SYSTEM_PROGRAM_NAME "Programma", P.PROGRAM_TYPE "Tipo",
         T.TABLES "File", C.COLUMNS "Campi", C.USAGE_TYPE "Uso",
         T.SQL_STATEMENT_TYPE "Tipo istruz.", P.STATEMENT_TEXT "Istruz."
    from PGM_STAT as P
      inner join TAB_STAT as T
        on (P.PROGRAM_SCHEMA, P.SYSTEM_PROGRAM_NAME, P.STATEMENT_NUMBER) = 
           (T.PROGRAM_SCHEMA, T.SYSTEM_PROGRAM_NAME, T.STATEMENT_NUMBER)
      left join COL_STAT as C 
        on (P.PROGRAM_SCHEMA, P.SYSTEM_PROGRAM_NAME, P.STATEMENT_NUMBER) = 
           (C.PROGRAM_SCHEMA, C.SYSTEM_PROGRAM_NAME, C.STATEMENT_NUMBER)
        -- filtro sull'utilizzo di uno specifica tabella
     where T.TABLES like :_Tabella_
        -- filtro sull'utilizzo di uno specifico campo
    -- where C.COLUMNS like :_Campo_  
    order by P.PROGRAM_SCHEMA, P.SYSTEM_PROGRAM_NAME, T.TABLES, T.SQL_STATEMENT_TYPE;

-- [C04] informazioni bind di un programma (DSPPGM, DSPSRVPGM)
select PROGRAM_LIBRARY "Libreria", PROGRAM_NAME "Nome", OBJECT_TYPE "Tipo", 
       trim(BOUND_SERVICE_PROGRAM_LIBRARY) concat '/' concat trim(BOUND_SERVICE_PROGRAM) "Pgm servizio",
       BOUND_SERVICE_PROGRAM_ACTIVATION "Attivaz.", cast(BOUND_SERVICE_PROGRAM_SIGNATURE as varchar(16) for bit data) "Firma"
  from QSYS2.BOUND_SRVPGM_INFO as B
  where PROGRAM_NAME = :Programma
    -- singola libreria 
    --and PROGRAM_LIBRARY = :Libreria
    -- lista librerie
    and PROGRAM_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
    and BOUND_SERVICE_PROGRAM_LIBRARY <> 'QSYS' -- esclusione service program sistema operativo
  order by PROGRAM_LIBRARY, PROGRAM_NAME, BOUND_SERVICE_PROGRAM_LIBRARY, BOUND_SERVICE_PROGRAM;

-- [C05] informazioni bind di un programma e service programs correlati presenti in lista librerie (DSPPGM, DSPSRVPGM)
with
 SP as 
 (select PROGRAM_LIBRARY, PROGRAM_NAME,
       P.TEXT_DESCRIPTION, timestamp(CREATE_TIMESTAMP, 0) CREATE_TIMESTAMP,
       SIGNATURES, cast(EXPORT_SIGNATURES as varchar(16) for bit data) EXPORT_SIGNATURES,
       ORDINAL_POSITION 
    from QSYS2.PROGRAM_INFO as P
      inner join QSYS2.LIBRARY_LIST_INFO as L
        on PROGRAM_LIBRARY = SYSTEM_SCHEMA_NAME
    where OBJECT_TYPE = '*SRVPGM'
 )
select B.PROGRAM_LIBRARY "Libreria", B.PROGRAM_NAME "Nome", B.OBJECT_TYPE "Tipo", 
       trim(B.BOUND_SERVICE_PROGRAM_LIBRARY) concat '/' concat trim(B.BOUND_SERVICE_PROGRAM) "Pgm servizio collegato",
       B.BOUND_SERVICE_PROGRAM_ACTIVATION "Attivaz.", cast(B.BOUND_SERVICE_PROGRAM_SIGNATURE as varchar(16) for bit data) "Firma",
       trim(SP.PROGRAM_LIBRARY) concat '/' concat trim(SP.PROGRAM_NAME) "Pgm servizio in linea",
       SP.TEXT_DESCRIPTION "Descrizione", SP.CREATE_TIMESTAMP "Data/ora creaz.",
       SP.SIGNATURES "Num.firme", SP.EXPORT_SIGNATURES "Firme"
  from QSYS2.BOUND_SRVPGM_INFO as B
    left join SP
      on B.BOUND_SERVICE_PROGRAM = SP.PROGRAM_NAME
  where B.PROGRAM_NAME = :Programma
    -- singola libreria 
    --and B.PROGRAM_LIBRARY = :Libreria
    -- lista librerie
    and B.PROGRAM_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
    and B.BOUND_SERVICE_PROGRAM_LIBRARY <> 'QSYS' -- esclusione service program sistema operativo
    and SP.PROGRAM_LIBRARY like (case when B.BOUND_SERVICE_PROGRAM_LIBRARY = '*LIBL' then '%' else B.BOUND_SERVICE_PROGRAM_LIBRARY end)
  order by B.PROGRAM_LIBRARY, B.PROGRAM_NAME, B.BOUND_SERVICE_PROGRAM_LIBRARY, B.BOUND_SERVICE_PROGRAM, SP.ORDINAL_POSITION;

-- [C06] binding directories (DSPBNDDIR)
select BINDING_DIRECTORY "Nome", trim(ENTRY_LIBRARY) concat '/' concat trim(ENTRY) "Voce", ENTRY_TYPE "Tipo", 
       ENTRY_ACTIVATION "Attiv.", timestamp(ENTRY_CREATE_TIMESTAMP, 0) "Data/ora origine"
  from QSYS2.BINDING_DIRECTORY_INFO
  where -- singola libreria
        BINDING_DIRECTORY_LIBRARY = :Libreria
        -- lista librerie
        --BINDING_DIRECTORY_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
    -- binding directory specifica    
    --and BINDING_DIRECTORY = :BindingDirectory
  order by BINDING_DIRECTORY_LIBRARY, BINDING_DIRECTORY, ENTRY;

-- [C07] elenco procedure di un service program
select PROGRAM_LIBRARY "Libreria", PROGRAM_NAME "Nome", OBJECT_TYPE "Tipo", SYMBOL_NAME "Nome simbolo", SYMBOL_USAGE "Tipo simbolo"
  from QSYS2.PROGRAM_EXPORT_IMPORT_INFO
  where PROGRAM_NAME = :ProgrammaServizio
    -- singola libreria 
    --and PROGRAM_LIBRARY = :Libreria
    -- lista librerie
    and PROGRAM_LIBRARY in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
  order by PROGRAM_LIBRARY, PROGRAM_NAME, SYMBOL_NAME;

-- [C08] ricerca procedura in un elenco di programmi di una libreria
select BINDING_DIRECTORY "Binding directory", PROGRAM_NAME "Programma", OBJECT_TYPE "Tipo" 
       --, cast(SYMBOL_NAME as varchar(8192)) as "Procedura"
  from QSYS2.PROGRAM_EXPORT_IMPORT_INFO
    -- abbino la binding directory tramite il nome programma (ignoro la libreria)
    left join QSYS2.BINDING_DIRECTORY_INFO
      on PROGRAM_NAME = ENTRY
        and BINDING_DIRECTORY_LIBRARY = :Libreria
  where PROGRAM_LIBRARY = :Libreria         
   and upper(SYMBOL_NAME) = upper(:NomeProcedura)
   and SYMBOL_USAGE = '*PROCEXP'
  order by PROGRAM_NAME;

-- [C09] oggetti referenziati da un programma (DSPPGMREF)
  -- richiede la creazione della funzione di tabella DSPPGMREF
  --  script DSPPGMREF SQL Table Function for IBM i.sql from Bob Cozzi (https://www.linkedin.com/pulse/dsppgmref-sql-table-function-ibmi-bob-cozzi)
select OBJNAME "Programma", OBJLIB "Libreria", OBJTYPE "Tipo", OBJTEXT "Descrizione",
       OBJREFCOUNT "Num.oggetti ref.", REFOBJNAME "Oggetto", REFOBJLIB "Libreria", REFOBJTYPE "Tipo",
       REFSRCNAME "Nome in sorgente", REFRCDFMT "Formato record", RCDFMTCOUNT "Num-formati record",
       FILEUSAGE "Uso", LVLCHKID "ID livello", FIELDSCOUNT "Num.campi"
  from table(DSPPGMREF(OBJECT_NAME => :Programma, 
                       --LIBRARY_NAME => :Libreria    -- filtro per singola libreria
                       LIBRARY_NAME => '*LIBL'         -- filtro per lista librerie
                      ));


-->>> [D] AREE DATI/USER SPACES <<<

-- [D01] visualizza area dati (DSPDTAARA)
select DATA_AREA_TYPE "Tipo", "LENGTH" as "Lunghezza", DECIMAL_POSITIONS "Decimali", DATA_AREA_VALUE "Valore"
  from table(QSYS2.DATA_AREA_INFO(DATA_AREA_NAME => :AreaDati, DATA_AREA_LIBRARY => :Libreria));

-- [D02] visualizzazione user space
select DATA
  from table(QSYS2.USER_SPACE(USER_SPACE_LIBRARY => :Libreria, USER_SPACE => :UserSpace));


-->>> [E] JOB IN ESECUZIONE E RISORSE IN USO <<<

-- [E01] informazioni su allocazione oggetto (WRKOBJLCK)
select MEMBER_LOCK_TYPE, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE, JOB_NAME
  from table(QSYS2.OBJECT_LOCK_INFO( :Libreria, :Oggetto, :Tipo, 0)) as OBJECT_LOCK
  --where LOCK_STATE = '*EXCL' and LOCK_STATUS = 'HELD'
  ;

-- [E02] file db aperti da 0un job
select JOB_NAME "Lavoro", SUBSYSTEM "Sottosistema", JOB_TYPE "Tipo job", FUNCTION_TYPE "Tipo funzione", "FUNCTION" as "Nome funzione",
       JOB_STATUS "Stato", JOB_ENTERED_SYSTEM_TIME "Data/ora avvio", OPEN_FILES "File aperti",
       LIBRARY_NAME "Libreria", FILE_NAME "File", FILE_TYPE "Tipo file", MEMBER_NAME "Membro", RECORD_FORMAT "Formato", 
       ACTIVATION_GROUP_NAME "Gruppo attivazione", 
       OPEN_OPTION "Opz.apertura", WRITE_COUNT "Num.scritt.", READ_COUNT "Num.lett.", 
       WRITE_READ_COUNT "Num.lett./scritt.", OTHER_IO_COUNT "Num. altro I/O", RELATIVE_RECORD_NUMBER "RRN"
  from table(QSYS2.ACTIVE_JOB_INFO(--SUBSYSTEM_LIST_FILTER => 'QINTER', -- filtro per sottosistema
                                   CURRENT_USER_LIST_FILTER => :Utente, -- filtro per utente corrente 
                                   JOB_NAME_FILTER => :Job,             -- filtro per nome lavoro   
                                   DETAILED_INFO => 'ALL')) as J
    cross join lateral(select *
                         from table(QSYS2.OPEN_FILES(J.JOB_NAME))) as F
  where FILE_TYPE not in('DSPF', 'PRTF')
  order by JOB_NAME, LIBRARY_NAME, FILE_NAME;

-- [E03] proprietà job
select JOB_NAME "Nome lavoro", SUBSYSTEM "Sottosistema", JOB_TYPE_ENHANCED "Tipo dettagliato",
       CLIENT_APPLNAME "Nome applicazione client",
       POOL_NAME "Nome pool", RUN_PRIORITY "Priorità esecuzione", TIME_SLICE "Time slice",
       trim(JOB_DESCRIPTION_LIBRARY) concat '/' concat trim(JOB_DESCRIPTION) "Descrizione lavoro",
       trim(JOB_QUEUE_LIBRARY) concat '/' concat trim(JOB_QUEUE) "Coda lavori",
       CCSID, 
       DATE_FORMAT "Formato data", DATE_SEPARATOR "Separatore data", TIME_SEPARATOR "Separatore ora",
       DECIMAL_FORMAT "Separatore decimali",
       TIMEZONE_ABBREVIATED_NAME "Fuso orario", TIMEZONE_CURRENT_OFFSET/60 "Offset fuso orario da UTC"
  from table(QSYS2.ACTIVE_JOB_INFO(--JOB_NAME_FILTER => '*',                -- filtro per job corrente 
                                   --JOB_NAME_FILTER => 'ADMIN*',             -- filtro per job con nome generico
                                   --SUBSYSTEM_LIST_FILTER => :Sottosistema,  -- filtro per sottosistema
                                   CURRENT_USER_LIST_FILTER => :Utente,     -- filtro per utente corrente
                                   DETAILED_INFO => 'ALL'));
                             
                           
-->>> [F] DATABASE <<<
                           
-- [F01] definizione tabella (DSPFFD)
select ORDINAL_POSITION as "Ord.", SYSTEM_COLUMN_NAME as "Col.sist.",
       COLUMN_NAME as "Col.", 
       DATA_type as "Tipo", DDS_TYPE as "Tipo DDS",
       -- old way
       --sum(STORAGE) over(partition by 1 order by ORDINAL_POSITION) - STORAGE + 1 as "Pos.",
       BUFFER_POSITION as "Pos.",
       LENGTH as "Lungh.", NUMERIC_SCALE as "Dec.", 
       COLUMN_TEXT as "Descrizione (testo)", COLUMN_HEADING as "Descrizione",
       REFERENCE_LIBRARY concat '/' concat REFERENCE_FILE concat '(' concat REFERENCE_FIELD concat ')' as "Riferimento",
       CCSID, IS_NULLABLE "Capacità nulla", COLUMN_DEFAULT "Default", IS_IDENTITY "Identità", HIDDEN "Nascosta"
  from QSYS2.SYSCOLUMNS2 as C
  where -- singola libreria
        --TABLE_SCHEMA = ucase( :Libreria) 
        -- la prima tabella trovata in lista librerie
        TABLE_SCHEMA = (select TABLE_SCHEMA
                          from QSYS2.SYSFILES
                            inner join QSYS2.LIBRARY_LIST_INFO
                              on TABLE_SCHEMA = SYSTEM_SCHEMA_NAME
                            and (TABLE_NAME = ucase( :Tabella) or SYSTEM_TABLE_NAME = ucase( :Tabella))
                          order by ORDINAL_POSITION
                          limit 1)
    and (TABLE_NAME = ucase( :Tabella) or SYSTEM_TABLE_NAME = ucase( :Tabella))
        -- filtro per campo
    --and COLUMN_NAME like :Campo
        -- filtro per descrizione
    --and (ucase(COLUMN_TEXT) like ucase( :_Descrizione_) or ucase(COLUMN_HEADING) like ucase( :_Descrizione_))
  order by ORDINAL_POSITION;

-- [F02] definizione tabella con evidenziazione campi chiave primaria o univoca
with
  -- individuazione chiave primaria o primo indice univoco
  PK as
  (select COLUMN_NAMES 
     from table(QSYS2.INDEX_PARTITION_STATISTICS(char(:Libreria, 10), char(:Tabella, 10), '*NORESET')) as IPS
     where UNIQUE = '0'
     -- ordine discendente per reperire prioritariamente la primary key se esiste
     order by INDEX_TYPE desc
     fetch first 1 rows only
  ),
  -- lista campi chiave della chiave primaria o indice univoco
  PK_D as
  (select trim(ELEMENT) as KEY
     from PK, table(SYSTOOLS.SPLIT(COLUMN_NAMES, ',')) 
  )
  -- definizione tabella (cfr. query F01)
 select ORDINAL_POSITION as "Ord.", case when KEY is not null then 'PK' else ' ' end "Primary key", 
        SYSTEM_COLUMN_NAME as "Col.sist.", 
        COLUMN_NAME as "Col.", 
        DATA_type as "Tipo", DDS_TYPE as "Tipo DDS",
        -- old way
        --sum(STORAGE) over(partition by 1 order by ORDINAL_POSITION) - STORAGE + 1 as "Pos.",
        BUFFER_POSITION as "Pos.",
        LENGTH as "Lungh.", NUMERIC_SCALE as "Dec.", 
        COLUMN_TEXT as "Descrizione (testo)", COLUMN_HEADING as "Descrizione",
        REFERENCE_LIBRARY concat '/' concat REFERENCE_FILE concat '(' concat REFERENCE_FIELD concat ')' as "Riferimento",
        CCSID, IS_NULLABLE "Capacità nulla", COLUMN_DEFAULT "Default", IS_IDENTITY "Identità", HIDDEN "Nascosta" 
   from QSYS2.SYSCOLUMNS2 as C
     left join PK_D
       on COLUMN_NAME = KEY
   where TABLE_SCHEMA = ucase( :Libreria) 
     and (TABLE_NAME = ucase( :Tabella) or SYSTEM_TABLE_NAME = ucase( :Tabella))
         -- filtro per campo
     --and COLUMN_NAME like :Campo
         -- filtro per descrizione
     --and (ucase(COLUMN_TEXT) like ucase( :_Descrizione_) or ucase(COLUMN_HEADING) like ucase( :_Descrizione_))
   order by ORDINAL_POSITION; 

-- [F03] ricerca campi in un gruppo di tabelle
select TABLE_SCHEMA "Libreria", TABLE_NAME "Tabella", 
       ORDINAL_POSITION as "Ord.", SYSTEM_COLUMN_NAME as "Col.sist.", COLUMN_NAME as "Col.", 
       DATA_TYPE as "Tipo", DDS_TYPE as "Tipo DDS", 
       BUFFER_POSITION as "Pos.", 
       LENGTH as "Lungh.", NUMERIC_SCALE as "Dec.", 
       COLUMN_TEXT as "Descrizione (testo)", COLUMN_HEADING as "Descrizione", 
       REFERENCE_LIBRARY concat '/' concat REFERENCE_FILE concat '(' concat REFERENCE_FIELD concat ')' as "Riferimento",
       CCSID, IS_NULLABLE "Capacità nulla", COLUMN_DEFAULT "Default", IS_IDENTITY "Identità", HIDDEN "Nascosta"
  from QSYS2.SYSCOLUMNS2 as C
  where -- lista librerie
        TABLE_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
        -- singola tabella
        --TABLE_SCHEMA = ucase( :Libreria) AND (TABLE_NAME = ucase( :Tabella) or SYSTEM_TABLE_NAME = ucase( :Tabella))
        -- nome campo specifico
    --and COLUMN_NAME = :Campo
        -- ricerca per descrizione
    and (ucase(COLUMN_TEXT) like ucase( :_Descrizione_) or ucase(COLUMN_HEADING) like ucase( :_Descrizione_))
        -- ricerca per tipo dati
    --and DATA_TYPE = :Tipo_Dati and LENGTH = :Lunghezza
        -- ricerca per riferimento
    --and REFERENCE_FIELD = :Campo_Riferimento
  order by TABLE_NAME, COLUMN_NAME;

-- [F04] definizione indici dipendenti da una tabella (DBPDBR+DSPFD)
select TABLE_PARTITION "Tabella", INDEX_SCHEMA "Libreria", INDEX_NAME "Indice", SYSTEM_INDEX_NAME "Indice sist.", TEXT "Descrizione", 
       case when UNIQUE = '0' then 'Y' else 'N' end "Univoco", INDEX_TYPE "Tipo", NUMBER_KEY_COLUMNS "Num.chiavi", COLUMN_NAMES "Chiavi", 
       SEARCH_CONDITION "Filtro"
  from table(QSYS2.INDEX_PARTITION_STATISTICS(char(:Libreria, 10), char(:Tabella, 10), '*NORESET')) as IPS
       -- filtro solo indici univoci
  --where UNIQUE = '0'
  order by INDEX_NAME;

-- [F05] definizione indici dipendenti da una tabella con dettaglio definizione campi chiave (DSPDBR+DSPFD)
select trim(DBFLDP) "Libreria", trim(DBFFDP) "Indice", trim(DBXTXT) as "Descrizione",
       DBKPOS "Ord.",  
       trim(DBKFLD) "Chiave", DBITXT "Descrizione", 
       DBITYP "Tipo", coalesce(DBICLN, DBINLN) "Lungh.", DBINSC "Dec.",
       (case DBXUNQ when 'U' then 'Y' else 'N' end) "Univoco"
  from QSYS.QADBLDEP
    left join QSYS.QADBXLFI
         on (DBFFDP = DBXFIL and DBFLDP = DBXLIB)
    left join QSYS.QADBKATR
         on (DBFFDP = DBKFIL and
             DBFLDP = DBKLIB and
             DBFRDP = DBKFMT)
    left join QSYS.QADBIATR
         on (DBFFDP = DBIFIL and DBFLDP = DBILIB and DBFRDP = DBIFMT and DBKFLD = DBIFLD)
   where DBFLIB = :Libreria and DBFFIL like :_Tabella_ 
     and DBXATR in ('LF','IX') and DBXREL = 'Y'
   order by DBFLDP, DBFFDP, DBKPOS;

-- [F06] oggetti dipendenti da una tabella (DSPDBR)
select SCHEMA_NAME "Libreria", SQL_NAME "Oggetto", SYSTEM_NAME "Oggetto sist.", SQL_OBJECT_TYPE "Tipo", 
       OBJECT_TEXT "Descrizione"
  from table(SYSTOOLS.RELATED_OBJECTS(LIBRARY_NAME => :Libreria, FILE_NAME => :Tabella))
  order by SQL_OBJECT_TYPE, SQL_NAME;

-- [F07] viste dipendenti da una tabella (ricerca ricorsiva) (DSPDBR plus)
with
 VISTEDEP (LIBRERIA, NOME, LIVELLO, LIBRERIA_DIP, NOME_DIP) as
  (select T1.VIEW_SCHEMA, T1.VIEW_NAME, 1, T1.OBJECT_SCHEMA, T1.OBJECT_NAME
     from QSYS2.SYSVIEWDEP as T1
     where T1.OBJECT_SCHEMA = :Libreria and T1.OBJECT_NAME = :Tabella
   union all
   select T2.VIEW_SCHEMA, T2.VIEW_NAME, O.LIVELLO + 1, T2.OBJECT_SCHEMA, T2.OBJECT_NAME
     from QSYS2.SYSVIEWDEP as T2
       join VISTEDEP as O
         on O.LIBRERIA = T2.VIEW_SCHEMA and O.NOME = T2.OBJECT_NAME)
 select distinct LIVELLO "Liv.", LIBRERIA "Libreria", NOME "Oggetto",  
        case when LIVELLO > 1 then LIBRERIA_DIP else null end "Libreria dipendente",
        case when LIVELLO > 1 then NOME_DIP else null end "Oggetto dipendente"
   from VISTEDEP
   order by LIVELLO, LIBRERIA, NOME;

-- [F08] elenco file fisici/tabelle
select TABLE_SCHEMA "Libreria", TABLE_NAME "Tabella", SYSTEM_TABLE_NAME "Tabella sist.", TEXT_DESCRIPTION "Descrizione",
       NUMBER_FIELDS "Num.campi", RECORD_LENGTH "Lungh.rec.", NUMBER_MEMBERS "Num.membri", TRIGGER_COUNT "Num.trigger", CONSTRAINT_COUNT "Num.vincoli",
       SOURCE_FILE_LIBRARY concat '/' concat SOURCE_FILE concat '(' concat SOURCE_FILE_MEMBER concat ')' "Sorgente",
       LEVEL_CHECK "Controllo liv.", FORMAT_LEVEL_ID "Livello", FORMAT_NAME "Nome formato", 
       REUSE_DELETED_RECORDS "Riutilizzo rec.canc.", INITIAL_RECORDS + (INCREMENT_RECORDS*MAXIMUM_INCREMENTS) "Num.max rec."
  from QSYS2.SYSFILES
  where NATIVE_TYPE = 'PHYSICAL' and FILE_TYPE = 'DATA'
        -- singola libreria
    and TABLE_SCHEMA = :Libreria
        -- lista librerie
    --and TABLE_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)    
    --and TABLE_NAME like ':_Tabella_'
        -- filtro per formato record
    --and FORMAT_NAME = :FormatoRecord
  order by TABLE_SCHEMA, TABLE_NAME;

-- [F09] elenco file logici/viste/indici
select TABLE_SCHEMA "Libreria", TABLE_NAME "Tabella", SYSTEM_TABLE_NAME "Tabella sist.", SQL_OBJECT_TYPE "Tipo", TEXT_DESCRIPTION "Descrizione",
       NUMBER_KEY_FIELDS "Num.chiavi", NUMBER_MEMBERS "Num.membri", NUMBER_BASED_ON_FILES "Num.file dipendenti",
       SOURCE_FILE_LIBRARY concat '/' concat SOURCE_FILE concat '(' concat SOURCE_FILE_MEMBER concat ')' "Sorgente",
       LEVEL_CHECK "Controllo liv.", FORMAT_LEVEL_ID "Livello", FORMAT_NAME "Nome formato", 
       ALLOW_UPDATE "Aggiornabile",
       PRIMARY_KEY "Chiave primaria", UNIQUE_CONSTRAINT "Univoco", SELECT_OMIT "Filtro", TOTAL_SELECT_OMIT "Num.filtri",
       IS_JOIN_LOGICAL "File logico join", INDEX_HAS_SEARCH_CONDITION "Search condition"
  from QSYS2.SYSFILES
  where NATIVE_TYPE = 'LOGICAL' and FILE_TYPE = 'DATA'
        -- singola libreria
    and TABLE_SCHEMA = :Libreria
        -- lista librerie
    --and TABLE_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
        -- filtro per nome file    
    --and TABLE_NAME like :_File_
  order by TABLE_SCHEMA, TABLE_NAME;

-- [F10] file fisici registrati su giornale
select OBJLONGSCHEMA "Libreria", OBJNAME "Oggetto", OBJTEXT "Descrizione",
       JOURNAL_LIBRARY || '/' || JOURNAL_NAME "Giornale", JOURNAL_IMAGES "Immagini",
       JOURNALED "Registrato", timestamp(JOURNAL_START_TIMESTAMP, 0) "Registrato da"
  -- filtro file su libreria
  --from table(OBJECT_STATISTICS(QSYS2.OBJECT_SCHEMA => :Libreria, OBJTYPELIST => '*FILE', OBJECT_NAME => :Tabelle))
  -- intera libreria
  from table(QSYS2.OBJECT_STATISTICS(OBJECT_SCHEMA => :Libreria, OBJTYPELIST => '*FILE'))
  where OBJATTRIBUTE = 'PF'
        -- solo file registrati su giornale
    and JOURNALED = 'YES'
  order by OBJLONGSCHEMA, OBJNAME;

-- [F11] file fisici con trigger
select TRIGGER_NAME as "Nome trigger", TRIGGER_TEXT "Descrizione", 
       EVENT_OBJECT_SCHEMA "Libreria", EVENT_OBJECT_TABLE as "Tabella", 
       ACTION_ORDER as "Sequenza", 
       EVENT_MANIPULATION as "Evento", ACTION_TIMING as "Time",
       trim(TRIGGER_PROGRAM_LIBRARY) concat '/' concat trim(TRIGGER_PROGRAM_NAME) as "Programma", 
       ALLOW_REPEATED_CHANGE as "Mod.ripetute", TRIGGER_UPDATE_CONDITION "Condiz.agg.",
       ENABLED as "Attivo", OPERATIVE "Operativo"
  from QSYS2.SYSTRIGGER
  where -- singola libreria
        EVENT_OBJECT_SCHEMA = :Libreria
        -- lista librerie
        --EVENT_OBJECT_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
        -- filtro su tabella
    --and (EVENT_OBJECT_TABLE like :_Tabella_ or SYSTEM_EVENT_OBJECT_TABLE like :_Tabella_)
  order by EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, ACTION_ORDER;

-- [F12] tabella con campo identity: verifica limite
select P.SYSTEM_TABLE_SCHEMA "Libreria", P.TABLE_NAME "Tabella", P.SYSTEM_TABLE_NAME "Tabella sist.",
       P.NEXT_IDENTITY_VALUE "Valore identity max", P.NUMBER_ROWS "Num.rec.", P.NUMBER_DELETED_ROWS "Num.rec.canc",
       (select C.COLUMN_NAME
          from QSYS2.SYSCOLUMNS2 as C
          where C.SYSTEM_TABLE_SCHEMA = P.SYSTEM_TABLE_SCHEMA
            and C.SYSTEM_TABLE_NAME = P.SYSTEM_TABLE_NAME
            and C.IS_IDENTITY = 'YES' 
       ) as "Colonna identity"
  from QSYS2.SYSPARTITIONSTAT as P
  where NEXT_IDENTITY_VALUE is not null
    and SYSTEM_TABLE_SCHEMA = :Libreria 
    --and (SYSTEM_TABLE_NAME like :_Tabella_ or TABLE_NAME like :_Tabella_)
  order by SYSTEM_TABLE_SCHEMA, TABLE_NAME;
  
-- [F13] confronto conteggio oggetti database tra due librerie
with
 LIB1 as
 (select DESCRIPTION, count(*) as NUM
    from SYSTABLES
      inner join (values ('A', 'ALIAS'), ('L', 'LOGICAL FILE'), ('M', 'MQT'), ('P', 'TABLE'), ('T', 'TABLE'), ('V', 'VIEW'))
                        as TYPE(CODE, DESCRIPTION)
        on TABLE_TYPE = TYPE.CODE
    where FILE_TYPE = 'D'
      and SYSTEM_TABLE_SCHEMA = :Libreria1
    group by DESCRIPTION with rollup),
 LIB2 as
 (select DESCRIPTION, count(*) as NUM
    from SYSTABLES
      inner join (values ('A', 'ALIAS'), ('L', 'LOGICAL FILE'), ('M', 'MQT'), ('P', 'TABLE'), ('T', 'TABLE'), ('V', 'VIEW'))
                        as TYPE(CODE, DESCRIPTION)
        on TABLE_TYPE = TYPE.CODE
    where FILE_TYPE = 'D'
      and SYSTEM_TABLE_SCHEMA = :Libreria2
    group by DESCRIPTION with rollup)    
 select coalesce(L1.DESCRIPTION, L2.DESCRIPTION) as "Tipo oggetto", 
        coalesce(L1.NUM, 0) "Conteggio per lib 1",
        coalesce(L2.NUM, 0) "Conteggio per lib 2"
   from LIB1 as L1
     full outer join LIB2 as L2
       on coalesce(L1.DESCRIPTION, 'TOTALE') = coalesce(L2.DESCRIPTION, 'TOTALE')
   order by 1;
   
-- [F14] confronto lista oggetti database mancanti tra due librerie
with
 LIB1 as
 (select SYSTEM_TABLE_NAME, TABLE_NAME, TABLE_TYPE, TABLE_TEXT, LAST_ALTERED_TIMESTAMP
    from SYSTABLES
    where FILE_TYPE = 'D'
      and SYSTEM_TABLE_SCHEMA = :Libreria1
 ),
 LIB2 as
 (select SYSTEM_TABLE_NAME, TABLE_NAME, TABLE_TYPE, TABLE_TEXT, LAST_ALTERED_TIMESTAMP
    from SYSTABLES
    where FILE_TYPE = 'D'
      and SYSTEM_TABLE_SCHEMA = :Libreria2
 )
 select coalesce(LIB1.SYSTEM_TABLE_NAME, LIB2.SYSTEM_TABLE_NAME), coalesce(LIB1.TABLE_NAME, LIB2.TABLE_NAME) 
   from LIB1
     -- oggetti mancanti in Libreria1 rispetto a Libreria2
     --right join LIB2
     -- oggetti mancanti in Libreria2 rispetto a Libreria1
     exception join LIB2
       on LIB1.SYSTEM_TABLE_NAME = LIB2.SYSTEM_TABLE_NAME
   order by 1;