Ottimizzare le query SQL su Db2 for i

Una query che ieri usciva in due secondi e oggi ne impiega quaranta è una delle telefonate più frequenti. La tentazione è aggiungere indici a caso finché qualcosa migliora. Su Db2 for i non serve: il sistema è in grado di dirti esattamente cosa non gli piace, basta chiederglielo.

Prima cosa: guardare cosa sta facendo davvero

Il punto di partenza è Visual Explain, dentro Run SQL Scripts di IBM i Access Client Solutions. Si scrive la query, si preme Ctrl+Shift+E invece di eseguirla, e compare il piano di accesso disegnato: quali file vengono letti, in che ordine, e soprattutto come.

C'è un dettaglio da cercare subito: un rettangolo etichettato Table Scan. Significa che il database sta leggendo il file dall'inizio alla fine. Su una tabella da diecimila record non è un problema. Su una da dieci milioni, è tutta la spiegazione che serve.

Il consulente indici tiene già l'elenco

Db2 for i registra da solo, in continuo, quali indici gli sarebbero serviti. Le informazioni stanno in una vista di sistema che si può interrogare come qualsiasi tabella:

SELECT TABLE_NAME,
       KEY_COLUMNS_ADVISED,
       TIMES_ADVISED,
       LAST_ADVISED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'MIALIB'
 ORDER BY TIMES_ADVISED DESC
 FETCH FIRST 20 ROWS ONLY;

TIMES_ADVISED è la colonna da guardare: dice quante volte il sistema ha desiderato quell'indice. Un indice consigliato una volta sola può essere stato una query estemporanea; uno consigliato quattromila volte sta rallentando qualcosa che gira ogni giorno.

Attenzione però a non creare tutto ciò che compare in quella lista. Ogni indice va mantenuto ad ogni INSERT, UPDATE e DELETE: venti indici su un file molto movimentato possono far danni maggiori del problema che risolvono. Conviene partire dai primi tre per numero di richieste e rimisurare.

Le condizioni che impediscono l'uso dell'indice

Anche con l'indice giusto, il database può essere costretto a ignorarlo. Succede quando la colonna indicizzata è racchiusa dentro una funzione:

-- L'indice su DATORD non viene usato:
-- la funzione va applicata a ogni riga
WHERE YEAR(DATORD) = 2026

-- Riscritta così, l'indice torna utilizzabile
WHERE DATORD >= '2026-01-01'
  AND DATORD <  '2027-01-01'

Vale lo stesso per UPPER(NOME) = 'ROSSI', per SUBSTR(CODICE, 1, 3) = 'ABC' (riscrivibile come CODICE LIKE 'ABC%') e per i confronti fra tipi diversi, dove il database deve convertire una colonna prima di poterla confrontare.

Un caso che su IBM i si incontra spesso è il confronto fra un campo numerico e una costante alfanumerica, tipico dei file DDS storici dove i codici sono numerici. Se CODCLI è packed(7:0), scrivere WHERE CODCLI = '0001234' costringe a una conversione riga per riga. Va confrontato con un numero: WHERE CODCLI = 1234.

Misurare invece di ricordare

Le impressioni ingannano, soprattutto perché la seconda esecuzione di una query è quasi sempre più veloce della prima: i dati sono già in memoria. Per numeri affidabili serve il monitor del database:

STRDBMON OUTFILE(MIALIB/MONITOR) JOB(*ALL)

Lo si lascia acceso durante una finestra rappresentativa — mezz'ora di lavoro reale è già indicativa — e poi lo si ferma con ENDDBMON. Il file che resta è una tabella normale, interrogabile in SQL: ogni riga è un evento, e i record con QQRID = 3000 contengono i tempi delle singole query.

Il monitor ha un costo in prestazioni, quindi si accende quando serve e si spegne subito dopo. Su un sistema molto carico conviene limitarlo a un singolo job invece che a *ALL.

Un ordine di lavoro che funziona

1. Riprodurre il rallentamento e prendere il tempo di partenza.
2. Visual Explain sulla query incriminata: cercare i Table Scan.
3. Controllare SYSIXADV per quella tabella.
4. Verificare che nessuna condizione applichi funzioni alle colonne filtrate.
5. Creare un indice per volta e rimisurare.
6. Dopo qualche settimana, ricontrollare che gli indici creati vengano usati davvero: la vista QSYS2.SYSTABLEINDEXSTAT riporta il numero di query che li hanno sfruttati.

Il passaggio 6 è quello che salta sempre, ed è quello che evita di ritrovarsi, fra due anni, con quaranta indici di cui nessuno sa più niente.

Una nota sui due motori

Db2 for i ha due ottimizzatori: il CQE, più vecchio, e l'SQE, molto più capace. Alcune costruzioni fanno ricadere una query sul CQE, e lì gran parte dei suggerimenti sopra vale meno. Le condizioni esatte cambiano da una release all'altra, quindi conviene verificarle sulla documentazione della propria versione: Visual Explain, in cima al piano, dichiara sempre quale motore è stato usato.

Su quali versioni funziona

Index advisor e la tabella QSYS2/SYSIXADV — documentati almeno da IBM i 7.3, e presenti nella documentazione 7.4 e successive. Il consulente indici raccoglie i dati da solo, senza bisogno di attivare nulla.

QSYS2.INDEX_ADVICE — procedura che condensa i suggerimenti duplicati in una vista di QTEMP. È un'aggiunta successiva alla tabella grezza: se sulla tua release non esiste, si interroga direttamente SYSIXADV come mostrato nell'articolo, che funziona ovunque.

Visual Explain — oggi sta in Run SQL Scripts di IBM i Access Client Solutions. Prima di ACS la stessa funzione stava in System i Navigator (e ancora prima in Operations Navigator): il piano di accesso disegnato è lo stesso, cambia solo il programma da cui lo si apre.

STRDBMON — comando CL storico, presente da molte release.

Il livello esatto di PTF con cui ogni funzione del database è stata abilitata è pubblicato da IBM nella pagina Db2 for i – Technology Updates, indicata fra le fonti. È il posto giusto dove verificare cosa è disponibile sulla propria macchina.

Fonti

• Index advisor — IBM Documentation 7.4
• Displaying index advisor information — IBM Documentation 7.4
• QSYS2.INDEX_ADVICE procedure — IBM Support
• Db2 for i — Technology Updates (livelli PTF per ogni funzione)