1 Luglio 2024MarkOne

Query di tutti i giorni

Nella vita lavorativa uno sviluppatore IBM i ogni giorno si trova ad eseguire molteplici operazioni ripetitive.

Abitualmente utilizziamo comandi di sistema operativo eseguiti dall’interfaccia green-screen tramite l’emulatore 5250. E’ sicuramente il metodo più immediato e al quale siamo abituati da decenni. Non è detto che sia il metodo più efficiente o il più comodo in ogni situazione.

L’output prodotto dal comando è visualizzato in uno schermo di emulatore a caratteri con dimensioni limitate (27 righe per 132 colonne al massimo) e può essere scomodo riuscire ad avere una visione più ampia del risultato e focalizzare l’attenzione sulle informazioni che si stanno cercando.

Anche la selezione dell’oggetto o degli oggetti di cui si desidera estrarre le informazioni è vincolata alle possibilità previste dai parametri del comando. A volte ci occorre estrarre informazioni per un gruppo di oggetti il cui nome contiene una specifica stringa, ma alcuni comandi potrebbero prevedere solo di poter specificare un singolo oggetto oppure un nome generico nella classica forma ABC* (stringa che inizia con…). Oppure potremmo voler filtrare l’elenco in base al tipo e attributo dell’oggetto ed il comando potrebbe prevedere solo il filtro per tipo. E gli esempi potrebbero essere molti altri.

Se si desidera esportare l’output in un file da conservare su PC o da condividere con altri utenti si ha a disposizione nell’immediato un copia/incolla testuale o ancora peggio uno screenshot. Nel caso si desiderasse produrre un file excel dall’output di un comando che espone una griglia di informazioni bisogna ricorrere alla creazione di un *OUTFILE e il suo download su PC tramite altri strumenti (trasferimento dati ACS, CPYTOIMPF, ODBC/JDBC, …). Inoltre non tutti i comandi prevedono la possibilità di specificare come output un file di database; capita a volte di avere a disposizione come output solo un file di spool.

Quindi anche una banale interrogazione può richiedere diversi passaggi e diverso tempo da spendere per ottenere il risultato nella forma desiderata e più facilmente fruibile e condivisibile. Se riflettessimo sul consumo di energie e tempo (anche se di pochi minuti) per il numero di ripetizioni con cui certe operazioni vengono eseguite, ci accorgeremmo di come potremmo lavorare con un metodo più efficiente e risparmiare molto tempo.

Ormai esistono da parecchi anni numerosi servizi SQL disponibili nel sistema operativo IBM i che consentono di ottenere in forma tabellare molte informazioni che nativamente non sono tabelle di DB2: informazioni su oggetti, librerie, proprietà di configurazione del sistema, aree dati, user spaces, stream files, ecc.

Vi propongo una raccolta di query di uso comune per le attività quotidiane di uno sviluppatore IBM i. Uno script da conservare sempre a portata di mano nel proprio client SQL, da arricchire e personalizzare secondo le proprie esigenze.

Suggerisco di eseguire le query in un client SQL su PC: esegui script SQL di ACS è lo strumento sicuramente più a portata di mano, ma vi invito a provare altri client SQL con funzionalità più evolute; per esempio DBeaver o Squirrel SQL client.

Le query proposte negli esempi prevedono l’utilizzo di variabili (stringhe precedute da : ) che consentono di eseguire facilmente la medesima istruzione sostituendo i valori delle variabili al momento dell’esecuzione. Caratteristica molto comoda nei vari client SQL disponibili nel mondo PC e dalle ultime versioni disponibile anche in ACS.

Lo script è organizzato in 6 sezioni:

  • [A] JOB/LAVORO
  • [B] RICERCHE OGGETTI E STREAM FILES
  • [C] INFO SU OGGETTI PROGRAMMA
  • [D] AREE DATI/USER SPACES
  • [E] JOB IN ESECUZIONE E RISORSE IN USO
  • [F] DATABASE

All’inizio dello script l’indice aiuta a trovare facilmente l’istruzione desiderata. Le istruzioni sono catalogate con un identificativo racchiuso tra parentesi quadre (p.es. [F02] definizione tabella con evidenziazione campi chiave primaria o univoca)

Vediamo insieme qualche esempio.

  • ricerca di un oggetto in lista librerie
-- [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;
  • ricerca membro sorgente
-- [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;
  • ricerca procedura in un elenco di programmi di una libreria. Ipotizziamo di analizzare un membro sorgente RPG che richiama una procedura o funzione. Dalla lettura del sorgente conosciamo la binding directory scritta nelle specifiche di controllo (H) e il nome della procedura. In quale service program si trova?
-- [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;
  • definizione tabella con evidenziazione campi chiave primaria o univoca. Spesso abbiamo la necessità di conoscere la definizione dei campi di una tabella. I metodi a disposizione sono molteplici. Con questa query non sono visualizziamo la definizione dei campi della tabella ma evidenziamo anche i campi che compongono la chiave primaria. Anche quando non è definita in modo esplicito la chiave primaria cerca di desumerla dal primo indice con chiave univoca.
-- [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;

Ma lascio a voi esplorare lo script completo.

Scarica lo script completo:

Prendilo, provalo e personalizzalo. Buon lavoro!