Esempi DB2 SQL per IBMi – parte 2

Sono passati oltre 10 anni da quando ho pubblicato alcuni esempi di SQL per IBMi (AS/400) che, ad oggi, è ancora l'articolo più visto di questo blog. Così, visto che ancora utilizzo quotidianamente SQL per la mia attività lavorativa (e non solo), ho deciso di scriverne un altro con nuovi trick, considerando anche che, nel frattempo, il DB2 è molto cresciuto e si è  arricchito di nuove funzionalità.

Giocare con date e tempi

Convertire data numerica in date

Le funzioni descritte in seguito prevedono di lavorare su colonne di tipo date o timestamp, ma tradizionalmente molte colonne "data" delle tabelle su IBMi sono definite come numeriche e non date, quindi, la prima cosa da capire è come convertirle nei formati sopra indicati.
Perchè dovrei farlo? I motivi possono essere diversi, ad esempio per interfacciarmi con altri DB o strutture esterne che richiedono veri formati date, oppure per poter utilizzare funzioni presenti nel DB2 che agiscono solo su campi date come quelle per reperire la settimana di una data.
Lo si può fare in vari modi ma quello che preferisco è il seguente:

date(to_date(char(mia_colonna_numerica), 'YYYYMMDD'))

Esempio:

WITH
test AS (
SELECT 
20211229 AS data_num
FROM sysibm.sysdummy1
)

SELECT 
data_num,
date(to_date(char(data_num), 'YYYYMMDD')) AS data_date
FROM test 

il risultato è il seguente:

DATA_NUM	DATA_DATE
20211229	2021-12-29

Convertire colonna date in formato numerico

Può capitare di dover fare anche il contrario di quanto descritto precedentemente, ovvero convertire il valore di una colonna di tipo date in un dato numerico intero.
L'esempio seguente mostra come farlo con la data corrente:

select 
int(replace(char(current date, ISO), '-', ''))
from sysibm.sysdummy1

Convertire una data in formato testo con separatori

SELECT 
VARCHAR_FORMAT(timestamp_iso(CURRENT_DATE), 'DD/MM/YYYY') 
FROM sysibm.sysdummy1

se la data attuale è il '2021-12-28' il risultato sarà "28/12/2021".

ATTENZIONE: VARCHAR_FORMAT funziona con i timestamp e non i date, motivo per cui, nell'esempio sopra, uso la funzione timestamp_iso per trasformare la data in timestamp. Mentre nell'esempio sotto non serve perchè la funzione to_date trasforma già in timestamp:

WITH
test AS (
SELECT 
20211229 AS data_num
FROM SYSIBM.sysdummy1
)

SELECT 
data_num,
VARCHAR_FORMAT(to_date(char(data_num), 'YYYYMMDD'), 'DD/MM/YYYY')  as data_char
FROM test

il risultato è il seguente:

DATA_NUM	DATA_CHAR
20211229	29/12/2021

Convertire due campi numerici data e ora in un campo timestamp

WITH
test AS (
SELECT 
20211229 AS data_num,
84532 AS ora_num
FROM SYSIBM.sysdummy1
)

SELECT 
data_num,
ora_num,
timestamp(lpad(data_num, 8, '0')||lpad(ora_num, 6, '0')) as mio_timestamp
FROM test 

il risultato è il seguente:

DATA_NUM	ORA_NUM	MIO_TIMESTAMP
20211229	84532	2021-12-29 08:45:32.000

Anno, mese, giorno

Per estrarre queste informazioni si usano rispettivamente le funzioni YEAR, MONTH e DAY. Inoltre esiste la funzione DAYNAME che restituisce il nome del giorno (lunedì, martedì, ecc.).

Esempio:

WITH
test (data_num)  
as 
( 
values 
(20211229), 
(20220101), 
(20220105), 
(20220106) 
) 

SELECT 
a.data_num,
year(to_date(char(data_num), 'YYYYMMDD')) AS anno,
month(to_date(char(data_num), 'YYYYMMDD')) AS mese,
day(to_date(char(data_num), 'YYYYMMDD')) AS giorno,
dayname(to_date(char(data_num), 'YYYYMMDD')) AS nome_giorno
FROM test a

il risultato è il seguente:

DATA_NUM	ANNO	MESE	GIORNO	NOME_GIORNO
20211229	2021	12	29	Mercoledi
20220101	2022	1	1	Sabato
20220105	2022	1	5	Mercoledi
20220106	2022	1	6	Giovedì

Funzioni relative alle Settimane

Ci sono varie funzioni che forniscono informazioni sulle settimane. Tra queste ne ho scelte alcune:

• WEEK: restituisce il numero della settimana della data fornita. Considera la Domenica il primo giorno della settimana e la prima settimana dell'anno è quella che contiene l'1 Gennaio
• WEEK_ISO: come WEEK restituisce il numero della settimana della data fornita. La differenza con WEEK è che il primo giorno della settimana è il Lunedì e la prima settimana dell'anno è quella che contiene il primo Giovedì dell'anno; quindi, ad esempio, se il 31 Dicembre dell'anno è giovedì, i primi 3 giorni dell'anno successivo appartengono all'ultima settimana dell'anno precedente (venerdì 1, sabato 2, domenica 3), poi lunedì 4 inizia una nuova settimana che contiene il giovedì e quindi diventa la settimana numero 1 dell'anno
• VARCHAR_FORMAT: può fornire molte informazioni, ma, nell'esempio riportato sotto, restituisce la combinazione Anno-Settimana ISO (esempio 202205) grazie al parametro 'IYYYIW'
• DAYOFWEEK e DAYOFWEEK_ISO: restituiscono entrambi il numero del giorno all'interno della settimana (da 1 a 7) della data fornita . La differenza consiste in quale giorno viene considerato il primo della settimana, ovvero rispettivamente la Domenica e il Lunedì.
• DAYNAME: restituisce il nome descrittivo del giorno della data fornita: lunedì, martedì, mercoledì, ecc.

WITH
test (data_num)  
as 
( 
values 
(20211229), 
(20220101), 
(20220105), 
(20220106) 
) 

SELECT 
a.data_num,
week(to_date(char(data_num), 'YYYYMMDD')) AS Sett,
week_iso(to_date(char(data_num), 'YYYYMMDD')) AS Sett_ISO,
varchar_format(TIMESTAMP_FORMAT(char(data_num), 'YYYYMMDD'), 'IYYYIW') AS Anno_Sett,
dayofweek(to_date(char(data_num), 'YYYYMMDD')) AS GG_Sett,
dayofweek_iso(to_date(char(data_num), 'YYYYMMDD')) AS GG_Sett_ISO,
dayname(to_date(char(data_num), 'YYYYMMDD')) AS Nome_GG
FROM test a

il risultato è il seguente:

DATA_NUM	Sett	Sett_ISO	Anno_Sett	GG_Sett	GG_Sett_ISO	Nome_GG
20211229	53	52	202152	4	3	Mercoledi
20220101	1	52	202152	7	6	Sabato
20220105	2	1	202201	4	3	Mercoledi
20220106	2	1	202201	5	4	Giovedì

Sottrarre/Sommare periodi ad una data numerica

Di seguito un esempio per sottrarre o sommare periodo (giorni, mesi o anni) ad una data numerica:

WITH
test AS (
SELECT 
20211229 AS data_num
FROM SYSIBM.sysdummy1
)

SELECT 
data_num,
date(to_date(char(data_num), 'YYYYMMDD')) - 41 days as data_date,
int(replace(char(date(to_date(char(data_num), 'YYYYMMDD')) - 41 days, ISO), '-', '')) as data_numerica,
date(to_date(char(data_num), 'YYYYMMDD')) + 3 months as add_mesi,
date(to_date(char(data_num), 'YYYYMMDD')) + 3 years as add_anni
FROM test 

il risultato è il seguente:

DATA_NUM	Data_Date	Data_Numerica	Add_MESI	Add_Anni
20211229	2021-11-18	20211118	2022-03-29	2024-12-29

Calcolare la differenza in giorni tra due date

WITH
test AS (
SELECT 
20211225 AS data_num1,
20220529 AS data_num2
FROM SYSIBM.sysdummy1
)

SELECT 
data_num1,
data_num2,
days(date(to_date(char(data_num2), 'YYYYMMDD'))) - days(date(to_date(char(data_num1), 'YYYYMMDD'))) as gg_diff
FROM test 

il risultato è il seguente:

DATA_NUM1	DATA_NUM2	GG_DIFF
20211225	20220529	155

Calcolare differenza in minuti (o altro) tra due timestamp

Supponendo di avere degli intervalli di tempo dati da data e ora iniziali e data e ora finali (espressi in formato numerico), per calcolare l'intervallo di tempo in minuti (o secondi, o ore, ecc.) si può usare la funzione TIMESTAMPDIFF come da esempio seguente:

WITH
test (dt_ini, h_ini, dt_fin, h_fin)  
as 
( 
values 
(20211229, 060000, 20211229, 194500), 
(20220101, 075000, 20220103, 090000)
) 

select
a.*,
TIMESTAMPDIFF(4, char(timestamp(lpad(dt_fin, 8, '0')||lpad(h_fin, 6, '0')) - timestamp(lpad(dt_ini, 8, '0')||lpad(h_ini, 6, '0')))) AS diff_min
FROM test a 

il risultato è il seguente:

DT_INI	H_INI	DT_FIN	H_FIN	DIFF_MIN
20211229	60000	20211229	194500	825
20220101	75000	20220103	90000	2950

Nella funzione TIMESTAMPDIFF il primo argomento (il valore 4 nell'esempio precedente), espresso con un intero, indica in quale unità di misura si desidera l'intervallo:

Codice	Significato
1	Microseconds
2	Seconds
4	Minutes
8	Hours
16	Days
32	Weeks
64	Months
128	Quarters
256	Years

Calcolare data di inizio e fine settimana di una certa data

Nell'esempio si considera lo standard ISO quindi la data di inizio settimana si riferisce ad un Lunedì mentre quella di fine settimana alla Domenica.
Inoltre calcolo anche le date di inizio e fine della stessa settimana dell'anno precedente.

WITH
test AS (
SELECT 
20211229 AS data_num
FROM SYSIBM.sysdummy1
)

SELECT 
data_num,
date(to_date(char(data_num), 'YYYYMMDD')) AS dt_date,
date((to_date(char(data_num), 'YYYYMMDD')) - dayofweek_iso((to_date(char(data_num), 'YYYYMMDD'))) days + 1 days ) as inizio_sett,
date((to_date(char(data_num), 'YYYYMMDD')) - dayofweek_iso((to_date(char(data_num), 'YYYYMMDD'))) DAYS + 7 days ) as fine_sett,
date(next_day(lpad(year(to_date(char(data_num), 'YYYYMMDD'))-2, 4) concat '-12-31', 'THU') + ((week_iso(to_date(char(data_num), 'YYYYMMDD'))-1)*7) days) - 3 days as inizio_sett_aa_prec,
date(next_day(lpad(year(to_date(char(data_num), 'YYYYMMDD'))-2, 4) concat '-12-31', 'THU') + ((week_iso(to_date(char(data_num), 'YYYYMMDD'))-1)*7) days) + 3 days as fine_sett_aa_prec
FROM test 

il risultato è il seguente:

DATA_NUM	DATA_DATE	Inizio_Sett	Fine_Sett	Inizio_Sett_AA_Prec	Fine_Sett_AA_Prec
20211229	2021-12-29	2021-12-27	2022-01-02	2020-12-21	2020-12-27

Per il calcolo delle date relative alla settimana dell'anno precedente uso anche la funzione NEXT_DAY che restituisce la data del prossimo giorno della settimana relativo alla data fornita. In pratica lo uso per determinare il giovedì (parametro "THU") successivo al fine anno di due anni prima.

Calcolare data di inizio e fine mese di una data

WITH
test AS (
SELECT 
20240219 AS data_num
FROM SYSIBM.sysdummy1
)

SELECT 
data_num,
date(to_date(char(data_num), 'YYYYMMDD') - (day(to_date(char(data_num), 'YYYYMMDD'))-1) DAYS) as inizio_mese,
int(replace(char(date(to_date(char(data_num), 'YYYYMMDD') - (day(to_date(char(data_num), 'YYYYMMDD'))-1) DAYS), ISO), '-', '')) as inizio_mese_int,
last_day(date(to_date(char(data_num), 'YYYYMMDD'))) as fine_mese,
int(replace(char(date(last_day((to_date(char(data_num), 'YYYYMMDD')))), ISO), '-', '')) as fine_mese_int
FROM test 

il risultato è il seguente:

DATA_NUM	Inizio_Mese	Inizio_Mese_Int	Fine_Mese	Fine_Mese_Int
20240219	2024-02-01	20240201	2024-02-29	20240229

Tra le funzioni utilizzato per questo scopo c'è LAST_DAY per restituisce l'ultimo giorno del mese della data di riferimento.

Generare una sequenza di date in formato intero partendo da un range di date

Se per qualche motivo servisse generare una sequenza di date entro un certo range, si può utilizzare la seguente SQL ricorsiva:

with 
Periodo as (
select
'2022-01-01' as dt_ini,     -- data iniziale periodo
'2022-05-29' as dt_fin      -- data fine periodo
from sysibm.sysdummy1
),

calendario (dt_cal) as (
select 
date(dt_ini)
from periodo

union all   
-- ...poi leggo i successivi livelli ricorsivamente
select
dt_cal + 1 days 
from calendario
where dt_cal < (select date(dt_fin) from Periodo)
)

-- infine converto la data in formato numerico
select
int(replace(char(dt_cal, ISO), '-', '')) as dt_calen
from calendario

il risultato è il seguente:

DT_CALEN
20220101
20220102
20220103
....
20220529

---

Giocare con le stringhe

Dividere una stringa in più sottostringhe

Se si ha una stringa molto lunga e la si vuole dividere/splittare in più sotto stringhe, ad esempio da 20 caratteri, si può usare il seguente metodo:

WITH
testo (stringa_lunga) AS (
VALUES
('La donzelletta vien dalla campagna, in sul calar del sole, col suo fascio dell''erba; e reca in mano un mazzolin di rose e di viole, onde, siccome suole, ornare ella si appresta dimani, al dì di festa, il petto e il crine')
),

splittiamo_tutto (str, ref, stringa) as (
select substr(stringa_lunga, 1, 20), 1, stringa_lunga
from testo

union all

select substr(stringa, ref + 20, 20), ref + 20, stringa
from splittiamo_tutto
where substr(stringa, ref + 20, 20)<>''
)

select str from splittiamo_tutto

Il risultato sarà il seguente:

STR
La donzelletta vien
dalla campagna, in s
ul calar del sole, c
ol suo fascio dell'e
rba; e reca in mano
un mazzolin di rose
e di viole, onde, si
ccome suole, ornare
ella si appresta dim
ani, al dì di festa,
il petto e il crine

Convertire un numero in CHAR senza perdere zeri

Capita spesso di dover convertire un numero in carattere (CHAR) senza perdere gli zeri NON signifiativi perchè, ad esempio, dev'essere scritto su file TXT o CSV utilizzato per l'interscambio di dati con altri applicativi.
Per questo scopo esitono varie funzioni. Di seguito qualche esempio:

WITH
dati (numero) AS (
values
cast(125.7106 AS decimal(11, 5))
)

SELECT
numero,
digits(numero) AS digits,
right(REPEAT('0', 15) concat trim(char(numero)), 15) AS RIGHT,
lpad(numero, 15, '0') AS lpad,
replace(lpad(numero, 15, '0'), '.', ',') AS lpad2
FROM dati

Il risultato sarà il seguente:

NUMERO	DIGITS	RIGHT	LPAD	LPAD2
125,71060	00012571060	000000125.71060	000000125.71060	000000125,71060

Con la funzione DIGITS (colonna 2) il numero viene convertito ma perde l'eventuale separatore dei decimali. Inoltre la lunghezza finale della stringa è fissa, ovvero è la dimensione della colonna convertita se è una colonna DECIMAL, oppure ha dimensioni fisse 5, 10 o 19 nel caso sia uno SMALLINT, INTEGER o BIGINT.

Per convertire il numero senza perdere il separatore dei decimali e indicando una lunghezza fissa (ad esempio perchè richiesta nelle specifiche) si usa la funzione LPAD o la funzione RIGHT (colonne 3 e 4). LPAD è decisamente più comoda ma è stata introdotta dalla V5R4.

In entrambi i casi, il separatore dei decimali è il punto ("."), se serve che sia la virgola (",") è sufficiente utilizzare la funzione REPLACE (colonna 5).

Determinare se una colonna "carattere" contiene solo numeri

Per questo obbiettivo, nel SQL di altri database esiste la funzione ISNUMERIC. Nel DB2 ci si deve arrangiare con la fantasia.

In sostanza lo si può determinare sostituendo tutti i caratteri numerici della colonna con lo "spazio", quindi trimmare il risultato ed infine determinarne la lunghezza. Se è ZERO significa che conteneva solo caratteri numerici, viceversa significa che contiene almeno un carattere NON numerico.

Ipotizzando che la colonna di tipo "CHAR" (o VARCHAR) da testare si chiami "my_str", l'istruzione da inserire all'interno della nostra SQL è la seguente:

CASE
  WHEN LENGTH(RTRIM(TRANSLATE(my_str, ' ', '0123456789'))) = 0 
  THEN 'All digits'
  ELSE 'No'
END

se si vuole fare qualche test si può usare la seguente SQL modicando a piacimento il valore della colonna "str":

WITH
param (str) AS (
values
('189A45   '),
('84586    '),
('187/7584 '),
('5262846  ')
)

SELECT
str,
TRANSLATE(str, ' ', '0123456789') AS str_trimmata,
CASE
  WHEN LENGTH(RTRIM(TRANSLATE(str, ' ', '0123456789'))) = 0 
  THEN 'Yes'
  ELSE 'No'
END AS numerico
FROM param

Nell'esempio precedente, il risultatà sarà:

STR	STR_TRIMMATA	NUMERICO
189A45	A	No
84586		Yes
187/7584	/	No
5262846		Yes

Estrarre cifre da una colonna alfanumerica

Per raggiungere lo scopo si usa la funzione per le espressioni regolari (regexp_replace)

Esempio:

WITH
dati (str) AS (
values
('AB189A45'),
('845CDEF86'),
('18-95-67-85')
)

SELECT
str,
regexp_replace(str, '([^0-9])', '') AS cifre
FROM dati

Il risultato sarà il seguente:

STR	CIFRE
AB189A45	18945
845CDEF86	84586
18-95-67-85	18956785

ATTENZIONE: se l'esecuzione vi da l'errore SQL0204 QQQSVREG in QSYS di tipo SRVPGM non trovato significa che non avete installato l'opzione 39 del sistema operativo (International Components for Unicode)

Escape di un carattere jolly

In SQL i caratteri jolly vengono utilizzati con l'operatore SQL LIKE e sono usati per sostituire qualsiasi altro carattere in una ricerca all'interno di una stringa. I più classici sono:

• %: sostituisce zero o più caratteri
• _: sostituisce un singolo carattere

Capita a volte che la stringa contenga uno dei caratteri jolly e si debba filtrare proprio quello, ad esempio il carattere underscore ("_").

In questo caso bisogna usare un carattere di escape per far capire al SQL che il carattere successivo è un carattere da ricercare nella stringa e NON un carattere jolly.

Per farlo si usa l'istruzione escape insieme all'operatore LIKE per indicare qual'è il carattere di escape che viene utilizzato. Ad esempio:

SELECT 
*
FROM mia_tabella
WHERE TRIM(mia_colonna_stringa) LIKE '%@_V' ESCAPE '@'

dove viene usato il carattere di escape "@" per cercare all'interno della colonna mia_colonna_stringa tutto quello che termina con "_V".

Concatenare stringhe da più righe in un'unica colonnna

Per aggregare più stringhe di righe diverse all'interno di un'unica colonna si usa la funzione LISTAGG.

Esempio: all'interno di un'ipotetica tabella di note dei DDT, in cui per ogni DDT di possono avere più righe di note, si vuole ottenere, per ogni DDT, la nota aggregata data dalla concatenazione di tutte le sue note separate da uno spazio. Il tutto si ottiene con

WITH
note (oggetto, tipo_nota, riga, nota) AS (
VALUES
('DDT00565', 'NOT', 1, 'Prima riga della nota'),
('DDT00565', 'NOT', 3, 'Terza ed ultima riga della nota'),
('DDT00565', 'NOT', 2, 'Seconda riga della nota'),
('DDT00568', 'NOT', 1, 'Decisamente'),
('DDT00568', 'NOT', 2, 'molto'),
('DDT00568', 'NOT', 3, 'semplice')
)

SELECT
oggetto, tipo_nota,
LISTAGG(TRIM(nota), ' ') WITHIN GROUP(ORDER BY riga) AS NOTA_AGGREGATA
FROM note 
GROUP BY oggetto, tipo_nota

Il risultato ottenuto sarà il seguente:

OGGETTO	TIPO_NOTA	NOTA_AGGREGATA
DDT00565	NOT	Prima riga della nota Seconda riga della nota Terza ed ultima riga della nota
DDT00568	NOT	Decisamente molto semplice

NOTE:

• all'interno della funzione LISTAGG viene indicato l'eventuale stringa separatrice (nel nostro caso lo spazio)
• WITHIN GROUP è facoltativo, ma se non specificato, l'ordine con cui vengono concatenate le righe all'interno del gruppo è quello sequenziale con il quale le righe si presentano, quindi, nell'esempio precedente, per il DDT 00565 la nota aggregata sarebbe diventata "Prima riga della nota Terza ed ultima riga della nota Seconda riga della nota"

Leggere un file di testo dall'IFS

Ebbene sì, con SQL è possibile leggere un file di testo direttamente dall'IFS.
Per farlo si usa la funzione IFS_READ o IFS_READ_UTF8.

Di seguito un esempio:

SELECT
* 
FROM TABLE(QSYS2.IFS_READ_UTF8(PATH_NAME => '/SMEDOC/EDI/01/test_paolo.edi'))

Il risultato mostrerà due colonne:

• LINE_NUMBER: numero di riga
• LINE: contenuto di ogni riga del file di testo

Contare il numero di occorrenze di una stringa all'interno di un altra stringa

Per contare quante volte è presente una stringa all'interno di un altra stringa posso usare la seguente instruzione SQL:

select REGEXP_COUNT (trim('codice articolo;descrizione;qt ordinata;UM;prezzo;'),trim(';'))
from SYSIBM.SYSDUMMY1
with NC 

con l'istruzione precedente conto quante volte è presente il carattere punto e virgola all'interno della stringa. Il risultato è 5.

---

Giocare con JSON

Dalla versione V7R3 del sistema operativo sono state introdotte nel DB2 dell'IBMi comode funzioni per gestire i JSON

generare una colonna con un array JSON

Esempio:

WITH
citta (nome_citta, provincia, abitanti) AS (
VALUES
('Bergamo', 'BG', 135000),
('Treviglio', 'BG', 30500),
('Brescia', 'BS', 197000),
('Caravaggio', 'BG', 16500)
)

SELECT 
json_object ('citta' value json_arrayagg ( json_object ('nome' value trim(nome_citta) , 'abitanti' value abitanti))) 
FROM citta 
WHERE provincia='BG'

il risultato sarà:

{"citta":[{"nome":"Bergamo","abitanti":135000},{"nome":"Treviglio","abitanti":30500},{"nome":"Caravaggio","abitanti":16500}]}

Parsing di un JSON

Nell'esempio seguente si farà il parsing di una stringa JSON che contiene anche un array:

WITH 
testjson AS (
SELECT  
CAST('{"team": "Atalanta", "citta": "Bergamo", "giocatori":[{"nome": "Musso", "ruolo": "P", "numero": 1}, {"nome": "Okoli", "ruolo": "D", "numero": 5},{"nome": "Pasalic", "ruolo": "C", "numero": 88},{"nome": "Zapata", "ruolo": "A", "numero": 91}] }' 
     AS varchar(32700) ) AS json_info
FROM sysibm.sysdummy1
),

righe (team, citta, sequenza, nome_giocatore, ruolo, numero_maglia) AS (
SELECT * FROM JSON_TABLE((SELECT json_info FROM testjson),
              '$'
                 COLUMNS(
                       team VARCHAR(30) PATH 'lax $.team' , 
                       citta VARCHAR(30) PATH 'lax $.citta' , 
                       NESTED PATH '$.giocatori[*]' COLUMNS ( 
                            ord FOR ORDINALITY,
                            "nome" CHAR(35), 
                            "ruolo" CHAR(1),
                            "numero" int
                       )
                 ) 
             ) as t
)

SELECT 
*
FROM righe

il risultato sarà il seguente:

TEAM	CITTA	SEQUENZA	NOME_GIOCATORE	RUOLO	NUMERO_MAGLIA
Atalanta	Bergamo	1	Musso	P	1
Atalanta	Bergamo	2	Okoli	D	5
Atalanta	Bergamo	3	Pasalic	C	88
Atalanta	Bergamo	4	Zapata	A	91

---

SQL da programmi CL

Di seguito un esempio per eseguire istruzioni SQL all'interno di un programma CL (CLP o CLLE):

PGM

QSH CMD('DB2 "update my_table set my_col01=15 +
              where my_col02=20181225"')

ENDPGM

---

SQL all'interno di programmi ILE RPG

Select Into

Evitare l'istruzione FETCH di lettura con SELECT ... INTO ... se la SELECT restituisce una sola riga.
Esempio:

Exec Sql Select replace(:$_STRIN, '/', '-')
         into :$_STROUT
         from SYSIBM.SYSDUMMY1;

in questo esempio viene valorrizzata la variabile $_STROUT con il contenuto della variabile $_STRIN sostituendo eventuali caratteri "/" con "-".

Ovviamente possono essere restituite anche più colonne contemporaneamente, ad esempio:

Exec Sql Select * into :MIA_DS
         from MIA_TABELLA
         where ID=:$_ID;

dove ovviamente MIA_DS è una data structure con la struttura di tutte le colonne restituite.

---

FULL OUTER JOIN

La FULL OUTER JOIN serve a mettere in JOIN due o più tabelle avendo come risultato anche le righe che NON hanno corrispondenze.
Esempio:

WITH
tab1 (id1, citta, id_pr) AS (
VALUES
(1, 'Caravaggio', 1),
(2, 'Gorgonzola', 15),
(3, 'Crema', 2)
),

tab2 (id2, provincia) AS (
VALUES
(1, 'Bergamo'),
(2, 'Cremona'),
(3, 'Mantova')
)

select
a.citta,
b.provincia
from tab1 a
full outer join tab2 b ON a.id_pr=b.id2

Il risultato sarà il seguente:

CITTA	PROVINCIA
Caravaggio	Bergamo
Gorgonzola
Crema	Cremona
	Mantova

Nomi lunghi e brevi per tabelle e colonne

A volte può essere comodo gestire tabelle con nomi lunghi e chiari sia per il nome tabella che per i nomi delle colonne. Questo però causa qualche problema se si devono usare all'interno di un programma RPG perchè i nomi più lunghi di 10 caratteri vengono tagliati e resi univoci con dei progressivi incomprensibili.

In questo caso conviene creare la tabella come nell'esempio seguente usando FOR COLUMN per indicare un nome breve (alias) della colonna in modo da poterlo utilizzare nei programmi RPG:

create table my_lib.mia_tabella_lunga (
  codice_articolo FOR COLUMN cdpar char(15),
  codice_cliente FOR COLUMN cdcli char(10),
  PRIMARY KEY (codice_articolo)
) RCDFMT MyTabR

in questo modo la mia tabella si chiamerà mia_tabella_lunga in ambito SQL o linguaggi di programmazione quali Java, PHP, ecc. Ma per il sistema e per l'RPG avrà il brutto nome MIA_T00001.

Però abbiamo già ottenuto due risultati:

• colonne con nomi lunghi e corti: il nome colonna è quello lungo ma nei programmi RPG vedo il nome corto indicato tramite "FOR COLUMN", quindi "codice_articolo" è il nome colonna che vedo in ambito SQL e numerosi linguaggi di programmazione mentre nel programma RPG (o comunque nel sistema con un semplice DSPFFD) lo vedo come "CDPAR"
• il nome del formato record è "MYTABR" grazie all'istruzione RECFMT senza la quale il formato record assumerebbe lo stesso nome della tabella e darebbe problemi con l'RPG (in quel caso è sufficiente l'istruzione "RENAME" nelle spedifiche "F" per aggirare il problema)

Per risolvere il "problema" del nome tabella corto che è decisamente poco intuitivo, si usa il comando SQL rename table:

rename table my_lib.mia_tabella_lunga   
to system name mytab00f

in questo modo in ambito SQL la tabella resta mia_tabella_lunga mentre per il sistema e quindi nei programmi RPG viene vista come MYTAB00F.

Trovare la prima riga di un gruppo

Può capitare di avere la necessità di identificare la prima riga di un gruppo, ovvero a rottura di una o più colonne. Esempio la prima riga di ogni DDT o di ogni fattura di un certo periodo, la prima riga di un gruppo di note, ecc.

Nell'esempio seguente di identifica il primo componente in sequenza di una ipotetica distinta base:

WITH
distinta (assieme, componente, sequenza, coefficiente_impiego) AS (
VALUES
('A', 'B', '040', 1),
('A', 'C', '020', 2),
('A', 'D', '030', 1),
('C', 'E', '015', 2),
('C', 'F', '020', 1),
('F', 'G', '005', 3.5),
('F', 'H', '010', 1),
('F', 'I', '020', 1),
('F', 'J', '040', 6),
('H', 'K', '010', 3),
('H', 'L', '020', 2),
('H', 'M', '030', 1)
),

righe_numerate as (
      select assieme, componente,
      row_number() over (partition by assieme order by assieme, sequenza) as Riga
      from distinta
      )
select
      a.assieme, a.componente, a.coefficiente_impiego
      from distinta a
      join righe_numerate b on a.assieme=b.assieme 
                           and a.componente=b.componente 
                           and b.riga=1

Il risultato sarebbe il seguente:

ASSIEME	COMPONENTE	COEFFICIENTE_IMPIEGO
A	C	2,0
C	E	2,0
F	G	3,5
H	K	3,0

PARTITION BY: lavorare su gruppi di dati

La funzione PARTITION BY divide l'intero set di dati in più gruppi basati sui valori delle colonne desiderate permettendo di eseguire funzioni su questi gruppi invece che sull'intero set di dati.

Come sempre, gli esempi aiutano meglio a capire il significato e l'utilità di quanto descritto sopra.

Esempio:

WITH
citta (nome_citta, provincia, abitanti) AS (
VALUES
('Bergamo', 'BG', 135000),
('Treviglio', 'BG', 30500),
('Brescia', 'BS', 220000),
('Caravaggio', 'BG', 16500),
('Bedizzole', 'BS', 12300)
)

SELECT 
row_number() over (partition by provincia ORDER BY provincia, nome_citta) AS riga,
a.*,
sum(abitanti) over (partition by provincia ORDER BY provincia, nome_citta) AS progressivo
FROM citta a

Il risultato è il seguente:

RIGA	NOME_CITTA	PROVINCIA	ABITANTI	PROGRESSIVO
1	Bergamo	BG	135.000	135.000
2	Caravaggio	BG	16.500	151.500
3	Treviglio	BG	30.500	182.000
1	Bedizzole	BS	12.300	12.300
2	Brescia	BS	197.000	209.300

Nell'esempio sopra, l'intero set di dati viene diviso per provincia e ordinato per provincia e nome_citta. Quindi, all'interno di questi gruppi vengono utilizzate le seguenti funzioni:

• ROW_NUMBER per ottenere la numerazione delle righe all'interno del gruppo
• SUM per ottenere la sommatoria progressiva all'interno del gruppo

---

SQL per controllare il server

A volte abbiamo la necessità di fare verifiche riguardandi lo stato dell'occupazione disco del server IBMi (oggetti, librerie, IFS) o relativamente all'allocazione di alcuni oggetti. Anche in questo caso, SQL ci viene in aiuto.
Di seguito alcuni esempi.

Dimensioni librerie ed oggetti

Un modo molto semplice per verificare la dimensione degli oggetti (*FILE, *PGM, *DTAARA, ecc.) e di conseguenza delle librerie tramite SQL è utilizzare le statistiche di sistema come fossero una tabella di database.

Esempio:

SELECT 
SUM(OBJSIZE) 
FROM TABLE (QSYS2.OBJECT_STATISTICS('mia_libreria', 'ALL')) AS A 

in questo modo ottengo l'occupazione totale della libreria "mia_libreria".

Ovviamente posso anche vedere la dimensione (e non solo) dei singoli oggetti:

SELECT 
objname,
objsize
FROM TABLE (QSYS2.OBJECT_STATISTICS('mia_libreria', 'ALL')) AS A 
ORDER BY objsize DESC 

Dimensioni IFS

Con SQL possiamo rilevare persino del dimensioni degli oggetti presenti nell'IFS (Integrated File System).
Per questo però, prima di eseguire l'SQL, abbiamo bisogno di un comando di sistema che recupera le informazioni dell'IFS e le registra in una tabella di database:

RTVDIRINF DIR(/) INFFILEPFX(IFS002) INFLIB(QGPL) OMIT('/qsys.lib' '/qntc' '/qfilesvr.400' '/qopt')

Con questo comando vengono esclusi dall'analisi alcuni percorsi (ad esempio "/qsys.lib") e vengono generate due tabelle:

• IFS002D: riferimeni ai percorsi delle directory
• IFS002O: dettaglio informazioni (tra cui la dimensione) per ogni oggetto. Contiene anche l'ID della directory che si lega all'ID della tabella precedente per sapere dove si trova l'oggetto

A questo punto posso usare SQL, ad esempio, per avere l'elenco degli oggetti in ordine discendente per occupazione:

Select
   Case D.QEZDIRNAM1
      When '/' Then D.QEZDIRNAM1 || O.QEZOBJNAM
      Else D.QEZDIRNAM1 || '/' || O.QEZOBJNAM
   End As "Object",
   QEZOBJTYPE As "Object Type",
   QEZALCSIZE As "Allocated Size"
 From
   QGPL.IFS002O O,
   QGPL.IFS002D D
 Where
   O.QEZDIRIDX = D.QEZDIRIDX
 Order By
   QEZALCSIZE DESC

oppure l'elenco delle directory sempre in ordine discendente per occupazione

Select
(Sum(O.QEZALCSIZE)/1048576) As Directory_Size_In_MB,
D.QEZDIRNAM1 As Directory_Path
FROM QGPL.IFS002D D 
Join QGPL.IFS002O O On D.QEZDIRIDX = O.QEZDIRIDX
Group BY D.QEZDIRIDX, QEZDIRNAM1
Order By   1 Desc, 2

ATTENZIONE: alcune colonne delle tabelle create dal comando RTVDIRINF hanno un CCSID diverso dai soliti che si usano sui server IBMi Power System, quindi se si eseguono le SQL precedenti da ambiente 5250 (ad esempio da STRSQL) si potrebbe ricevere l'errore SQL0332 - Conversione carattere tra CCSID 1200 e CCSID 65535 non valida.
In questi casti è sufficiente "castizzare" queste colonne con la funzione CAST. In quel caso, la prima istruzione SQL diventa la seguente:

Select
Case cast(D.QEZDIRNAM1 as varchar(1024) ccsid 280 )
  When '/' Then cast(D.QEZDIRNAM1 as varchar(1024) ccsid 280 ) !!
                cast(QEZOBJNAM as varchar(512) ccsid 280  )
  Else cast(D.QEZDIRNAM1 as varchar(1024) ccsid 280 ) !! 
                '/' !!
                cast(QEZOBJNAM as varchar(512) ccsid 280  )
End As "Object",
cast(QEZOBJTYPE as varchar(10) ccsid 280) As "Object Type",
QEZALCSIZE As "Allocated Size"
From QGPL.IFS002O O, QGPL.IFS002D D
Where O.QEZDIRIDX = D.QEZDIRIDX
Order By QEZALCSIZE DESC

Elenco lavori che allocano un oggetto

Con SQL si può persino avere l'elenco dei lavori che allocano un oggetto. Quello che normalmente si ottiene con il comando WRKOBJLCK. Farlo con SQL può essere utile per utilizzarlo in qualche programma particolare o per fare un controllo su più oggetti contemporaneamente.

Esempio:

SELECT 
OBJECT_NAME,
JOB_NAME
FROM qsys2.OBJECT_LOCK_INFO
WHERE OBJECT_SCHEMA='mia_libreria'
ORDER BY OBJECT_NAME 

In questo caso ottengo tutti i job che allocano gli oggetti della libreria "mia_libreria".

Interrogazione Index Advisor

Il database del nostro server IBMi, in base all'utilizzo quotidiano delle tabelle, produce continuamente statistiche con suggerimenti su indici che bisognerebbe creare per ottimizzare le performance (access plan).
Con SQL possiamo interrogare questi suggerimenti e decidere cosa farne:

select
table_name,
key_columns_advised,
index_type,
last_advised,
times_advised,
estimated_creation_time,
reason_advised
from qsys2.SYSIXADV
WHERE TABLE_SCHEMA NOT IN ('QSYS','QUSRSYS')
  and LAST_ADVISED > '2022-07-01 00:01:00.000000'
  and table_schema = 'mia_libreria'
order by times_advised desc  

con questa SQL otteniamo i suggerimenti successivi al 1 Luglio 2022 per le tabelle presenti nella libreria "mia_libreria" ordinati in modo discendente per il numero di volte che è stato generato il suggerimento.

La colonna key_columns_advised contiene l'elenco delle colonne che dovrebbero diventare la chiave dell'indice da creare.

Elenco utenti di sistema (USRPRF)

Anche per questa esigenza esiste una tabella di database con tutte le informazioni.
Esempio:

SELECT 
AUTHORIZATION_NAME AS utente,
STATUS AS stato,
PREVIOUS_SIGNON AS ultimo_login,
SIGN_ON_ATTEMPTS_NOT_VALID AS nr_login_non_validi,
TEXT_DESCRIPTION AS descrizione
FROM qsys2.user_info a
WHERE JOB_DESCRIPTION_NAME='mia_jobd'
ORDER BY AUTHORIZATION_NAME

Nell'esempio precedente vengono elencati tutti gli utenti associati ad una determinata job description indicando per ognuno lo stato, il timestamp dell'ultimo login valido, il numero dei login NON validi e la sua descrizione.

Elenco tabelle di una libreria

Per elencare le tabelle e relative informazioni di una specifica libreria usare la seguente istruzione SQL:

SELECT
TABLE_SCHEMA ,
TABLE_NAME ,
NUMBER_ROWS ,
DATA_SIZE 
from sysibm.SYSTABLESTAT 
WHERE TABLE_SCHEMA ='mia_libreria'
  AND NUMBER_ROWS >0
ORDER BY TABLE_SCHEMA , TABLE_NAME

Elenco Stack dei programmi di un lavoro

Con SQL è persino possibile leggere lo stack dei programmi di un job.

Di seguito un esempio in cui vengono filtrati solo i programmi ILE la cui libreria inizia con "SME", evitanto quindi tutti i programmi di sistema:

SELECT 
a.ORDINAL_POSITION,
a.PROGRAM_NAME,
a.statement_identifiers,
a.*
FROM TABLE(QSYS2.STACK_INFO('246408/SMEUPADM/A_MRP2','ALL')) a
WHERE ENTRY_TYPE='ILE'
  AND program_library_name LIKE 'SME%'
ORDER BY ordinal_position

Variabili di sistema

Le normali variabili di sistema che si possono gestire con il comando WRKENVVAR sono consultabili anche tramite SQL.

Ad esempio, se voglio reperire il valore della variabile "LANG" a livello del mio lavoro, posso farlo con la seguente istruzione SQL:

SELECT
ENVIRONMENT_VARIABLE_VALUE
FROM QSYS2.ENVIRONMENT_VARIABLE_INFO a
WHERE ENVIRONMENT_VARIABLE_TYPE ='JOB'
  AND ENVIRONMENT_VARIABLE_NAME ='LANG'

Il risultato sarà simile al seguente:

ENVIRONMENT_VARIABLE_VALUE
\/QSYS.LIB\/IT_IT.LOCALE

Questo è comodo anche quando nella nostra applicazione si utilizzano variabili di sistema dedicate aggiunte tramite il comando: ADDANVVAR.

Elenco comandi con default modificati (CHGCMDDFT)

Tramite la seguente SQL è possibile visualizzare l'elenco dei comandi di cui sono stati modificati i default di uno o più parametri:

SELECT OBJNAME,APAR_ID,USER_CHANGED
FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','CMD')) 
WHERE APAR_ID = 'CHGDFT'

by