Universita' degli studi di Roma

"La Sapienza"

Corso di Basi di Dati - Prof.ssa Tiziana Catarci.Anno Accademico 1999/2000 - Gennaio 2000

Tesina di confronto tra:

Db2 Universal Server 6.1 EE

Oracle 8i

Informix Dynamic Server 2000

• Implementazione SQL92: vincoli ed interrogazioni.

• I trigger: implementazione di trigger in Java e SQL.

• Indici: i tipi di indicizzazione disponibili.

• Ottimizzazione: intervenire sull'ottimizzatore delle query.

Menegoni Fabio - Moschetti Marco - Salce Gianluca

IntroduzioneQuesto studio riguarda il confronto di alcuni aspetti dei tre piu' importanti DBMS presenti sulmercato attuale del software per la gestione delle basi di dati:

Oracle 8i (8.1.5) - IBM DB2 6.1 EE - Informix Dynamic Server 2000

Tutti e tre disponibili per molte piattaforme, sono stati provati nella versione per Windows NT 4su un personal computer dotato di un processore AMD K6/200 e 64 MB di RAM.

Confrontare dei software come DBMS e' sicuramente un'impresa tutt'altro che semplice. Le lorodimensioni sono tali che un confronto completo e approfondito da tutti i punti di vistarichiederebbe mesi di lavoro a persone con una certa esperienza. Anche limitando l'analisi soload alcuni aspetti di implementazione e tuning, l'impresa e' stata ben piu' ardua di quanto stimatoinizialmente; infatti, questi programmi non sono particolarmente amichevoli, a causa ovviamentedella loro complessita'. Inoltre la quantita' di documentazione fornita e' semplicementedisorientante anche per persone che hanno una certa familiarita' con l'uso del computer.

Gli argomenti sui quali e' posta l'attenzione, per la loro rilevanza, sono:

• Implementazione SQL92: come esprimere vincoli ed interrogazioni.• I trigger: implementazione di trigger in Java.• Indici: i tipi di indicizzazione disponibili.• Ottimizzazione: come intervenire sull'ottimizzatore delle query.

Il Software a disposizioneIBM DB2 Universal Database V6.1 EE, versione per Windows NT, Free 60 days evaluationcopy richiesta gratuitamente sul sito IBM. DB2 SDK 5.1 e DB2 Client Application Enabler 5.1,messoci a disposizione dal Dipartimento di Informatica e Sistemistica, Universita' di Roma.Oracle 8i Enterprise Edition (8.1.5), Free 30 days evaluation copy for Windows NT, fornitadurante il seminario su Oracle tenuto durante il corso di Basi di Dati. Informix Dynamic Server2000, V9.2, in versione Evaluation, ottenuto dal sito web di Informix. Per implementare i triggerin Java, e' stato necessario servirsi della versione per Linux 2.2.

Note sull'installazioneL'installazione non e' semplice come puo' sembrare. E' richiesta la presenza di alcuni servizi direte di NT4 senza i quali l'installazione non va a buon fine.Per quello che riguarda l'occupazione su disco, DB2 ed Informix richiedono sui 200-250 Mb perl'installazione completa, Oracle richiede molto piu' spazio, circa 1.5 Gb.DB2 e Oracle allocano praticamente tutta le memoria disponibile sul calcolatore, Informixinvece alloca la memoria solo quando ne ha bisogno rendendo agevole l'uso per altreapplicazioni su quella macchina. DB2 offre questa possibilita' a posteriori da un pannello dicontrollo. Oracle 8i permette di specificare dei parametri per limitare l'uso della memoriaallocata a diversi tipi di buffer (redo buffer log e buffer cache principalmente) mentrel'allocazione degli shared pool e del System Global Area non e' direttamente controllabiledall'amministratore se non per aspetti come le politiche di pre-paging che comunque non sonofinalizzate a limitare l'uso di memoria, ma a disporne in anticipo per limitare il traffico di I/O.

Note sulla documentazioneLa documentazione disponibile per tutti e tre i DBMS e' vasta ed esauriente.Oracle la offre sotto forma di file HTML con uno strumento per la ricerca in javascript.DB2 offre la possibilta' di accedere a documenti nazionalizzati, sia in PDF sia in HTMLmediante un tool, l'information centre, che la mostra in aree tematiche permettendo di effettuarericerche mediante keyword.La documentazione di Informix e' fornita sul loro sito web in formato PDF.

Strumenti di interazione con i DBMSTutti mettono a disposizione uno strumento grafico per la creazione di database. Quello di Oraclemette a disposizione una serie di opzioni proprietarie, riguardanti le estensioni multimediali, chesconosciute al neofita e selezionate di default, allungano la creazione del database a circa un'ora.DB2 mette a disposizione invece delle opzioni per far gestire dal sistema i file relativi al databasediminuendo le prestazioni, ma rende possibile un utilizzo meno ristretto del computer sul quale sitrova installato il DBMS.Degna di nota e' la presenza di un tool grafico su DB2 e Informix per la gestione dei database: daquesto e' possibile creare tabelle, vincoli, trigger e vedere il contenuto delle tabelle in manierapratica, veloce ed intuitiva; sempre DB2 ed Informix offrono un editor SQL molto piu' evoluto diquello presente su Oracle che sembra essere uno strumento primordiale e ostico. Infatti, questieditor offrono la possibilita' di scrivere e modificare il codice SQL introdotto e vedere i risultatisu una pagina diversa da quella riservata al codice.

Il databaseLo schema di base di dati che e' stato utilizzato nello studio e' piuttosto semplice ed e' statoimportante per non perdere di vista i casi reali; era necessario avere a disposizione vincoli reali,ed e' servito per darci l'idea di come si implementa uno schema concettuale all'interno di unDBMS. Queste sono le specifiche.

L’agenzia di pubbliche relazioniLa base di dati di un’agenzia di pubbliche relazioni contiene le seguenti informazioni:

• un catalogo di clienti, che possono essere aziende o persone fisiche;

• un insieme di informazioni su dei banchetti organizzati nell’ambito di manifestazionio in occasione di singoli avvenimenti (congressi, matrimoni, cresime, ecc.) per contodei clienti;

• gli elenchi degli invitati ai singoli banchetti; un elenco di ristoranti con le lorocaratteristiche. Tra i ristoranti si distinguono quelli caratteristici, che possono offrireparticolari specialita'; le specialita' sono offerte solo dai ristoranti caratteristici;

• un insieme di menu', tra i quali si distinguono quelli contenenti le specialita' offertedai ristoranti caratteristici

Lo schema E/R

Le tabelleDal diagramma E/R e dalle specifiche e' stato possibile identificare le relazioni da implementarenei DBMS, unitamente ad un insieme di vincoli che garantiscono l'integrita' della base di dati.

• AZIENDA (codice fiscale, citta', via, #telefono, capitale_sociale, #dipendenti)

• BANCHETTO (codicebanchetto, occasione, data, n_partecipanti, codice_fiscale,nome_ristorante, localita'_ristorante, id_menu';)

• COMPRENDE (id_menu' , nome portata )

• INVITATO (codicebanchetto , nome )

• MENU_STANDARD (id , costo)

• OFFRE_S (nomeristorante , localitaristorante , nome_specialita')

• OFFRE_M (nomeristorante , localitaristorante , id_menu')

• PERSONA (codicefiscale , citta, via, #telefono, cognome, nome, professione)

• PORTATA (nome , tipo) SPECIALITA(nome, tipo)

• RISTORANTE (nome , localita' , #posti)

• RISTORANTE_CARATTERISTICO (nome , localita')

Vincoli• Per la tabella BANCHETTO: il codice fiscale deve appartenere ad AZIENDA o a

PERSONA.

• Per le tabelle AZIENDA e PERSONA: devono rispettare il vincolo imposto dalla gerarchiaISA: ciascun codice fiscale deve essere unico nelle due tabelle.

• Per RISTORANTE_CARATTERISTICO: deve esistere in RISTORANTE.

• Per ogni banchetto deve esistere almeno un invitato. SPECIALITA' deve essere inPORTATA. SPECIALITA' deve essere offerta da RISTORANTE_CARATTERISTICO.

• Un banchetto deve avere necessariamente un menu'.

• MENU' deve contenere almeno una portata.

• Una portata deve essere contenuta almeno in un MENU'.

• Un BANCHETTO deve avvenire in un RISTORANTE.

• Se cancello un record da RISTORANTE, devo cancellare l'eventuale record inRISTORANTE_CARATTERISTICO.

• Se cancello un record da RISTORANTE_CARATTERISTICO, devo cancellare daSPECIALITA' i piatti offerti da quel ristorante.

• Il tipo di portata in SPECIALITA' deve essere congruente con il tipo in PORTATA.

• Un cliente non puo' ordinare lo stesso menu' per piu' di due banchetti consecutivi (vincoloimposto).

• Il costo di un banchetto non puo' essere superiore al capitale sociale di un'azienda se il clientee' un'azienda.

Implementazione SQL92Quella che segue e' l'implementazione in SQL92 del database di test; nell'eseguire questaoperazione, abbiamo verificato che alcune possibilita' offerte dallo standard non sono disponibilinei DBMS in prova.In particolare l'assenza della clausola ASSERT e l'impossibilita' di specificare una SELECTall'interno di una CHECK rendono necessario l'uso di trigger. Di seguito riportiamo degli esempirappresentativi per ogni classe di problemi.

L'entita' CLIENTE non puo' essere rappresentata cosi' come compare nello schema E/R. Lascelta e' stata quella di avere due tabelle separate: PERSONA ed AZIENDA. Essendo codicefiscale una chiave per CLIENTE, l'inserimento di un record in una delle due tabelle deveverificare che nell'altra non esista un record con lo stesso codice fiscale. Questo richiede unaCHECK all'interno della CREATE TABLE, ma la CHECK di cui abbiamo bisogno ha unaSELECT annidata al suo interno. Questa opzione, possibile in SQL92, non e' permessa danessuno dei tre DBMS in prova. E' necessario quindi utilizzare un trigger. E' stato possibileimplementare tutti i vincoli di integrita' referenziale, come previsto. L'entita'RISTORANTE_CARATTERISTICO e' stata modellata come una tabella la cui chiave primariareferenzia la chiave primaria di RISTORANTE. Inoltre e' stata scelta la clausola ON DELETECASCADE per cancellare un record di RISTORANTE_CARATTERISTICO quando vienecancellata la chiave in RISTORANTE. Anche questa opzione non ha dato nessun problema.

Creazione tabelle in SQLCREATE TABLE Azienda (

cod_fiscale CHAR(16) NOT NULL,citta CHAR(16) NOT NULL,via CHAR(25),telefono CHAR(20) NOT NULL,capitale_soc INTEGER,dipendenti INTEGER,PRIMARY KEY (cod_fiscale));

CREATE TABLE Persona (cod_fiscale CHAR(16) NOT NULL,citta CHAR(16) NOT NULL,via CHAR(25),telefono CHAR(20) NOT NULL,cognome CHAR(25) NOT NULL,nome CHAR(25) NOT NULL,professione CHAR(25),PRIMARY KEY (cod_fiscale));

CREATE TABLE Ristorante (nome CHAR(25) NOT NULL,localita CHAR(25) NOT NULL,posti INTEGER NOT NULL,PRIMARY KEY (nome, localita));

CREATE TABLE Ristorante_carat (nome CHAR(25) NOT NULL,localita CHAR(25) NOT NULL,PRIMARY KEY (nome, localita),FOREIGN KEY(nome, localita) REFERENCES Ristorante ON DELETE CASCADE);

CREATE TABLE Portata (nome CHAR(25) NOT NULL,tipo CHAR(25) NOT NULL,

PRIMARY KEY (nome));

CREATE TABLE Specialita (nome CHAR(25) NOT NULL, tipo CHAR(25) NOT NULL,nomerist char(25) NOT NULL,locrist char(25) NOT NULL,PRIMARY KEY (nome),FOREIGN KEY (nome) REFERENCES Portata ON DELETE CASCADE ,FOREIGN KEY (nomerist, locrist) REFERENCES Ristorante_carat ON DELETE

CASCADE);

CREATE TABLE Menu (id CHAR(20) NOT NULL,costo INTEGER NOT NULL,PRIMARY KEY (id));

CREATE TABLE Comprende (id_menu CHAR(20) NOT NULL,nome_portata CHAR(25) NOT NULL,PRIMARY KEY (id_menu, nome_portata),FOREIGN KEY (id_menu) REFERENCES Menu,FOREIGN KEY (nome_portata) REFERENCES Portata);

CREATE TABLE Banchetto (cod_banchetto CHAR(20) NOT NULL,occasione CHAR(25),data DATE,num_part INTEGER,cod_fiscale CHAR(16) NOT NULL,nome_ristorante CHAR(25) NOT NULL,loc_ristorante CHAR(25) NOT NULL,id_menu CHAR(20) NOT NULL,PRIMARY KEY (cod_banchetto),FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES Ristorante,FOREIGN KEY (id_menu) REFERENCES Menu);

CREATE TABLE Invitato (cod_banchetto CHAR(20) NOT NULL,nome CHAR(25) NOT NULL,PRIMARY KEY (cod_banchetto, nome),FOREIGN KEY (cod_banchetto) REFERENCES Banchetto);

CREATE TABLE Offre_s (nome_ristorante CHAR(25) NOT NULL,loc_ristorante CHAR(25) NOT NULL,nome_specialita CHAR(25) NOT NULL,PRIMARY KEY (nome_ristorante, loc_ristorante, nome_specialita),FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES

Ristorante_Carat,FOREIGN KEY (nome_specialita) REFERENCES Portata);

CREATE TABLE Offre_m (nome_ristorante CHAR(25) NOT NULL,loc_ristorante CHAR(25) NOT NULL,id_menu CHAR(20) NOT NULL,PRIMARY KEY (nome_ristorante, loc_ristorante, id_menu),FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES Ristorante,FOREIGN KEY (id_menu) REFERENCES Menu);

SQL92: Le queryIn questa sezione e' possibile vedere il comportamento dei 3 DBMS rispetto a dieciinterrogazioni in SQL92. Dopo aver creato le tabelle e aver popolato la base di dati analizziamocome i vari sistemi implementano la parte relativa al linguaggio di interrogazione dello standardSQL-92.

Query 1Per conto di quale cliente e' stato servito il pranzo comprensivo della specialita'

‘Lepre in Salmi'’?

SELECT pe.cod_fiscaleFROM persona pe, banchetto b, comprende co, portata pWHERE pe.cod_fiscale=b.cod_fiscale AND b.id_menu= co.id_menu AND co.nome_portata=p.nome AND p.nome='Lepre in salmi';

Oracle ok DB2 ok Informix ok

Query 2A quali banchetti ha preso parte Mario Rossi?

SELECT b.cod_banchettoFROM invitato i, banchetto bWHERE i.cod_banchetto = b.cod_banchetto AND i.nome = 'Mario Rossi';

Oracle ok DB2 ok Informix ok

Query 3Per ogni menu' standard, quanti ristoranti lo offrono?

SELECT m.id_menu,count (m.id_menu) AS numero_ristorantiFROM offre_m mGROUP BY m.id_menu;

Oracle ok DB2 ok Informix ok

Query 4Quanti ristoranti offrono un menu' standard, che comprenda 2 portate

SELECT COUNT (DISTINCT (M.nome_ristorante, M.loc_ristorante) As TotRistFROM ( SELECT C.id_menu, Count (*) AS NumPort FROM comprende C GROUP BY C.id_Menu) AS PortTot, offre_m MWHERE PortTot.id_menu = m.id_menu AND PortTot.Numport = 2;

Oracle NO DB2 NO Informix NO

Perche' non funziona: Nessuno dei 3 DBMS accetta piu' di un argomento nella clausolaDISTINCT. Oracle non permette l'uso della parola chiave AS nella FROM per ridenominare letabelle, mentre Informix e DB2 funzionano correttamente sia in sua presenza che in sua assenza.

Riformulando la l’interrogazione:

SELECT COUNT (DISTINCT M.Nome_ristorante)FROM Offre_m M, (SELECT C.id_menu, COUNT (*) AS NumPort FROM comprende C GROUP BY C.id_menu) PortTotWHERE PortTot.id_menu = m.id_menu AND PortTot.Numport = 2;

Oracle ok DB2 ok Informix NO

Perche' non funziona: Informix non permette di specificare una SELECT annidata dentro unaFROM.

Query 5Quali aziende hanno ordinato più di 2 banchetti?

SELECT a.cod_fiscaleFROM azienda aWHERE a.cod_fiscale IN (SELECT b.cod_fiscale FROM banchetto b GROUP BY b.cod_fiscale HAVING COUNT (b.cod_banchetto)2);

Oracle ok DB2 ok Informix ok

Query 6Quanti posti hanno i ristoranti prenotati dalla azienda con il piu' alto capitale

sociale?

SELECT R.postiFROM ristorante R, banchetto B, azienda AWHERE A.cod_fiscale=B.cod_fiscale AND B.nome_ristorante=R.nome AND B.loc_ristorante=R.localita AND a.capitale_soc ALL (SELECT A1.capitale_soc FROM Azienda A1);

Oracle ok DB2 ok Informix ok

Query 7

Quali sono i nomi delle portate che compaiono nel maggior numero di menu'?

SELECT Temp.Nome_PortataFROM (SELECT CO.Nome_Portata, COUNT (CO.id_Menu) AS TotMenu FROM Comprende CO GROUP BY CO.Nome_Portata) TempWHERE Temp.TotMenu=(SELECT MAX(Temp.TotMenu) FROM Temp);

Oracle NO DB2 NO Informix NO

Perche' non funziona: Nessuno permette la creazione di una tabella temporanea all'interno dellaclausola FROM che venga anche referenziata in una SELECT all'interno della WHERE

Riformulando l'interrogazione diversamente tutto funziona correttamente

SELECT C1.nome_portataFROM Comprende C1GROUP BY C1.nome_portataHAVING COUNT(C1.id_menu) = ALL(SELECT COUNT(C.id_menu) AS tot_menu FROM Comprende C GROUP By C.nome_portata);

Oracle ok DB2 ok Informix ok

Query 8Esistono ristoranti non caratteristici che offrono specialita'?

SELECT S.Nome_RistoranteFROM Offre_S SWHERE S.Nome_Ristorante NOT IN (SELECT R.Nome FROM Ristorante_carat R);

Oracle ok DB2 ok Informix ok

Query 9Quali invitati hanno partecipato a 2 o piu' banchetti offerti da clienti diversi?

SELECT I.NomeFROM Invitato IGROUP BY I.NomeHAVING 2<(SELECT COUNT (DISTINCT B.Cod_fiscale) FROM Invitato I1,Banchetto B WHERE I.Nome=I1.Nome AND B.Cod_Banchetto=I1.Cod_Banchetto);

Oracle ok DB2 ok Informix ok

Query 10Quali sono gli invitati che hanno preso parte ai banchetti con i menu' piu' costosi e,

in quali ristoranti?

SELECT I.NOMEFROM Iinvitato I, Banchetto B, Menu MWHERE I.cod_banchetto=B.cod_banchetto AND M.id=B.id_menu AND M.costo IN (SELECT MAX(M1.costo) FROM MENU M1);

Oracle ok DB2 ok Informix ok

Conclusioni

Nessuno dei 3 DBMS accetta piu' di un argomento nella clausola DISTINCT (Query 4)

Informix non permette di specificare una SELECT annidata dentro una FROM. (Query 4)

Nessuno permette la creazione di una tabella temporanea all'interno della clausola FROM chevenga anche referenziata in una SELECT all'interno della WHERE.(Query 7)

Oracle non permette l'uso della parola chiave AS nella FROM per ridenominare le tabelle,mentre Informix e DB2 funzionano correttamente sia in sua presenza che in sua assenza.

TriggerTutti e tre i DBMS mettono a disposizione del progettista un SQL ampliato con istruzioniprocedurali che puo' essere usato per scrivere dei trigger. Questo linguaggio non e' standard el'idea di scrivere trigger in Java nasce dalla speranza di poter superare questo limite. Abbiamoscelto ed implementato come rappresentativo dei vincoli realizzabili solo attraverso trigger, ilprimo relativo alle tabelle Banchetto, Azienda e Persona.

Banchetto Trigger

Il vincolo che devono soddisfare le istanze delle tabelle Banchetto, Azienda e Persona deldatabase implementato e' il seguente: l'aggiornamento o l’inserimento di una riga in Banchettodeve contenere un codice fiscale presente anche nelle tabelle Persona oppure Azienda.Per soddisfare questo vincolo e' stata scritta una procedura in Java invocata da un trigger chegenera una eccezione se il vincolo e' violato. La generazione di un'eccezione da parte di untrigger provoca il rollback della istruzione SQL che ha attivato il trigger, cioe' INSERT oUPDATE;

Trigger in JavaNei tre DBMS che stiamo esaminando la implementazione del trigger e' simile, nel senso cheoccorre scrivere la procedura Java, pubblicarne il prototipo nel DBMS effettuando il casting deitipi di dato passati come argomento ai tipi di dato disponibili in SQL, quindi creare un trigger ecollegare la trigger-action alla procedura.

Banchetto Trigger in Java su Oracle 8i

In Oracle 8i la macchina virtuale Java e' disponibile direttamente nel server, unitamente allapossibilita' di far compilare a quest'ultimo il codice utente. Questo ci permette di scrivere ilcodice Java di seguito alla CREATE PROCEDURE e di farlo compilare al DBMS. Le istruzioniSQL che realizzano questo sono:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "BTrefAP" ASimport java.sql.*;import java.io.*;import oracle.jdbc.driver.*;public class BT { public static void refAP (String new_cf) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select cod_fiscale fromPersona " + "where cod_fiscale = '" + new_cf + "'" + "UNION " + "select cod_fiscale from Azienda " + "where cod_fiscale = '" + new_cf + "'"); if (!rs.next()) { System.out.println("Violato vincolo di referenza suAzienda e Persona"); throw new SQLException("violazione vincolo di referenzasu Azienda e Persona"); } rs.close(); stmt.close(); }

}

La pubblicazione della procedura appena creata si ottiene mediante la:CREATE OR REPLACE PROCEDURE TRG(new_cf VARCHAR2) AS LANGUAGE JAVA NAME 'BT.refAP(java.lang.String)';

L'ultimo passo, necessario a collegare la procedura ad un trigger si ottiene in questo modo:CREATE OR REPLACE TRIGGER BT BEFORE INSERT OR UPDATE OF cod_fiscale ON Banchetto FOR EACH ROW CALL TRG (:new.cod_fiscale)

L'implementazione del vincolo richiesto in Oracle non ha quindi rappresentato particolariproblemi.

Banchetto Trigger in Java su DB2 6.1

DB2 offre la possibilita' di scrivere funzioni definite dall'utente (udf) in vari linguaggi (c, c++,rexx,...), tra cui anche Java, che vengono registrate nel server in maniera analoga a quantoavviene in Oracle 8i.La differenza fondamentale tra i due DBMS consiste nel fatto che mentre inOracle la macchina virtuale Java e' presente nel server stesso, DB2 si serve di una macchinavirtuale esterna: e' necessario installare il JDK1.1 sulla macchina dove gira il DBMS, impostarealcune variabili d'ambiente al quale quest'ultimo fa riferimento (CLASSPATH, ...) e far puntareun valore di una tavola di sistema alla directory radice del JDK.Per implementare il BanchettoTrigger come fatto in Oracle, quindi, occorre scrivere la classeJava, la quale effettua i necessari controlli sulla validita' degli inserimenti e degli aggiornamenti,pubblicarla nel server db2 come funzione che ritorna un intero (ad esempio: 0=inserimentopossibile, 1=inserimento scorretto) e quindi collegarla ad un trigger. Le difficolta' sorgono nelmomento in cui si scopre dalla documentazione della sintassi SQL di DB2 (IBM DB2 UniversalDatabase SQL Reference, Version 5.2, Document Number S10J-8165-01, comune alle versioni5.2 e 6.1) che la CREATE TRIGGER puo' contenere, come trigger-action solo funzioni, siadefinite dall'utente che di sistema, e che una UDF non puo' contenere al suo interno istruzioniSQL!Ne segue l'impossibilita' di definire il trigger di cui abbiamo bisogno utilizzando la stessafilosofia con cui lo abbiamo implementato in Oracle.

Banchetto Trigger in Java su Informix Dynamic Server 2000

Solo Informix Dynamic Server 2000 ha il supporto per funzioni definite dall'utente scritte inJava. Inoltre, la versione dimostrativa per la piattaforma Windows NT disponibile presso il sitodi Informix non e' completa: la documentazione infatti fa riferimento ad una gerarchia didirectory inesistente. E' stato necessario servirsi della versione per Linux per poter scriverefunzioni in Java.A differenza di quanto avviene con Oracle, Informix si serve del JDK 1.1 di Sun per eseguire ecompilare il codice in Java, ed e' necessario effettuare una serie di modifiche in un catalogo disistema del DBMS per specificare i percorsi di ricerca della macchina virtuale Java, delleeventuali librerie esterne e del codice da eseguire. La procedura necessaria a utilizzare unafunzione definita dall'utente in un trigger consiste dei seguenti passi:

• scrittura della procedura in Java, (nel nostro caso, la procedura e' quella usata per Oracle, conle necessarie modifiche per l'invocazione dei metodi JDBC di Informix), compilazione ecreazione di un archivio jar;

• esecuzione di una procedura predefinita nel DBMS per installare il jar nel database("install_jar()");

• registrazione della funzione mediante l'istruzione CREATE PROCEDURE;

CREATE PROCEDURE BTRefAP(new_cf VARCHAR) WITH (CLASS = "jvp") EXTERNAL NAME 'BanchettoTRG:BT.refAP' LANGUAGE JAVA;

L'istruzione crea la procedura BTRefAP() collegandola al metodo refAP() della classe BT che sitrova nell'archivio BanchettoTRG.jar.Infine, la creazione del trigger e' molto simile a quella fatta per Oracle:

CREATE TRIGGER BT INSERT ON Banchetto REFERENCING NEW AS new FOR EACH ROW EXECUTE PROCEDURE BTRefAP(:new.cod_fiscale)

La difficolta' nella scrittura di procedure in Java con Informix risiede tutta nel fatto che e' statonecessario impostare un discreto numero di parametri del DBMS per permettergli di eseguire ilcodice.

Trigger in SQL proceduraleAbbiamo implementato il trigger sulla tabella BANCHETTO anche in SPL/SQL in Informix,PL/SQL in Oracle, DB2SQL in DB2.Benche' non siano identici, anche uno sguardo rapido rivelauna fortissima somiglianza sintattica fra il codice sorgente nei tre prodotti; scrivere un triggercon questo metodo risulta piu' semplice e immediato rispetto al Java, ma non immediatamenteportabile. Esiste tuttavia una differenza fondamentale che distingue il DBMS di Informix daglialtri due: mentre Oracle e DB2, quando specificata la clausola BEFORE INSERT nella creazionedel trigger intendono quello che il senso comune suggerisce (prima dell'esecuzione dellaINSERT esegui il codice del trigger, quando la riga che stiamo per immettere soddisfa lecondizioni specificate nell'istruzione WHERE), in Informix esiste lo clausola INSERT BEFORE,che sta a significare che, a prescindere dal contenuto della riga che stiamo inserendo (difatti nelblocco di istruzioni che segue la BEFORE non e' possibile referenziare la nuova riga), occorreeseguire in qualunque caso le istruzioni della BEFORE.

Banchetto Trigger in PL/SQL su Oracle 8i

Dal diagramma sintattico dell'istruzione CREATE TRIGGER, sembrerebbe possibile scrivere iltrigger nel seguente modo:

CREATE OR REPLACE TRIGGER banchetto_trg BEFORE INSERT OR UPDATE OF cod_fiscale ON banchetto REFERENCING NEW AS n FOR EACH ROW WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM aziendaa) AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROM persona p)) (Raise_application_error(-20000, 'Codice fiscale non valido'));

Invece, cercando di eseguire questa istruzione si viene a conoscenza del fatto che non e' possibileeffettuare una query SQL all'interno della WHERE. Occorre quindi trovare una soluzionealternativa: seguendo il suggerimento per la soluzione di un problema analogo nelladocumentazione, abbiamo dichiarato ed utilizzato una coppia di cursori, uno per la tabella

PERSONA ed uno per la tabella AZIENDA, aventi come parametro il codice fiscale che si vuoleinserire o di cui si vuole effettuare un aggiornamento. Ciascun cursore ritorna il valoreNOTFOUND se fallisce nella ricerca del codice fiscale nelle suddette tabelle e questo cipermette di generare un'eccezione. Il codice che gestisce l'eccezione esegue una chiamata allaroutine di sistema RAISE_APPLICATION_ERROR() che ferma l'esecuzione di tutta laprocedura, stampa un messaggio di errore e un numero di errore in un intervallo riservato aglierrori definiti dall'utente. Per default, non essendo questo numero di errore inviato ad un gestoredi eccezioni, viene eseguito il rollback dell'istruzione SQL che ha provocato l'esecuzione deltrigger, cioe' INSERT o UPDATE (vedi 'Oracle 8i - Application Developer's Guide', part numberA68003-01, pag. 10.44). Il codice e' il seguente:

CREATE OR REPLACE TRIGGER banchetto_trgBEFORE INSERT OR UPDATE OF cod_fiscale ON BANCHETTOFOR EACH ROWDECLARE Dummya CHAR(20); Dummyp CHAR(20); Invalid_cf EXCEPTION; CURSOR Dummy_cursora (cf CHAR) IS SELECT a.cod_fiscale FROM azienda a WHERE a.cod_fiscale = cf; CURSOR Dummy_cursorp (cf CHAR) IS SELECT p.cod_fiscale FROM persona p WHERE p.cod_fiscale = cf;BEGIN OPEN Dummy_cursora (:new.cod_fiscale); OPEN Dummy_cursorp (:new.cod_fiscale); FETCH Dummy_cursora INTO Dummya; FETCH Dummy_cursorp INTO Dummyp; IF Dummy_cursora%NOTFOUND THEN CLOSE Dummy_cursora; IF Dummy_cursorp%NOTFOUND THEN RAISE Invalid_cf; END IF; CLOSE Dummy_cursorp; END IF;EXCEPTION WHEN Invalid_cf THEN Raise_application_error(-20000, 'Codice fiscale non valido!'); CLOSE Dummy_cursorp;END;

L'esecuzione di prove di inserimento nella tabella BANCHETTO con diversi valori del codicefiscale ci hanno permesso di verificare che il comportamento del trigger e' quello desiderato.

Banchetto Trigger in DB2SQL su DB2 6.1

La possibilita' offerta da DB2 di includere una query nella WHERE ha reso la scrittura deltrigger molto piu' semplice, rispetto ad Oracle. Questo e' il codice:

CREATE TRIGGER trg2 NO CASCADE BEFORE INSERT ON banchetto REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN(n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda a UNION SELECT p.cod_fiscale FROM persona p)) SIGNAL SQLSTATE '75000' ('Not present in Azienda or Persona')

Banchetto Trigger in SPL SQL su Informix Dynamic Server 2000

La trigger action, come nei due casi precedenti, consta della generazione di una eccezione. Datal'impossibilita' di effettuare una chiamata diretta all'istruzione RAISE EXCEPTION all'internodella definizione del trigger, occorre innanzi tutto definire una procedura che effettui una talechiamata e quindi collegarla alla trigger action. Di seguito il semplice codice:

CREATE PROCEDURE errore() RAISE EXCEPTION -746, 0, 'errore: codice fiscale inserito nonvalido!'END PROCEDURE

Definizione del trigger:CREATE TRIGGER banchetto INSERT OR UPDATE OF cod_fiscale ON BANCHETTO REFERENCING NEW AS n FOR EACH ROW WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM aziendaa) AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROMpersona p)) (EXECUTE PROCEDURE errore())

Rispetto a DB2, nella WHERE e' vietato l'uso dell'operatore UNION, cosa che ha reso necessariol'uso degli operatori logici.Da notare che l'eccezione esegue un rollback dell'istruzione che ha generato l'invocazione deltrigger solo se il database sul quale stiamo operando e' stato creato con il logging attivo. Questonon avviene di default, ed e' necessario specificarlo all'atto della creazione del database. Questosi ottiene con la seguente istruzione:CREATE DATABASE AgenziaRelazioniPubbliche WITH LOG;

Gli indiciIn questa sezione ci proponiamo di analizzare quali possibilita' vengono offerte dai software perla gestione degli indici e in particolar modo quali tipi vengono messi a disposizione delprogettista per ottimizzare al massimo le prestazioni.

B-Tree

Tutti e tre i prodotti mettono a disposizione i B-Tree che sono il modo piu' frequente di gestiregli indici. Le opzioni per questo tipo di indicizzazione sono molto complete e sono uguali pertutti. E' possibile:

specificare se l'indice deve essere in ordine crescente o decrescente; indicare la percentuale dispazio libero su ogni nodo dell'albero al momento delle sua creazione, secondo la previsione dicrescita del database; richiedere la “clusterizzazione” dell'indice. richiedere l'unicita' della chiavecreare l'indice su piu' colonne della tabella (per aumentare l'unicita' della chiave).

Hash

Oracle e' l'unico ad offrire questo potente metodo d'indicizzazione. Il manuale non parla di indicidi tipo hash ed effettivamente non e' possibile utilizzare la CREATE INDEX per ottenere questotipo di indice.La procedura e' piu' complessa perche' le tabelle devono essere memorizzate su discodifferentemente dalle condizioni normali. Bisogna preparare lo spazio su disco prima: e'necessario creare un "CLUSTER" specificando la dimensione della chiave, il numero di chiavi,la dimensione del cluster. Al momento di creazione della tabella, questa deve essere inserita nel"CLUSTER" creato. E' possibile utilizzare una funzione messa a disposizione dal sistema, ma e'anche possibile crearla.

R-Tree

Per quello che riguarda metodi di indicizzazione basati su albero, Informix mette a disposizioneun altro tipo di albero chiamato R-Tree (range tree). Questa struttura e' studiata per migliorare leprestazioni delle interrogazioni che includono nello spazio di ricerca un intervallo di valorianziche' un valore preciso e per tutte le strutture multidimensionali a due o tre dimensioni piu'eventualmente il tempo. La struttura di un R-Tree e' molto simile a quella di un B-Tree sebbene idati memorizzati al suo interno siano differenti. Il suo obiettivo e' di individuare un insieme didati che sia molto piu' piccolo dell'insieme di partenza con la massima velocita' possibile,piuttosto che trovare subito l'insieme finale. L'insieme temporaneo trovato e' conservativo:spesso contiene piu' dati di quelli richiesti, ma contiene sempre tutte le soluzioni. La visita di unR-Tree permette di scartare una grande quantita' di dati non utili alla ricerca senza analizzarli.Questo viene fatto eliminando dati che cadono fuori dall'area di interesse. Su questo argomentola Informix mette a disposizione una sezione dedicata che spiega in dettaglio il funzionamento.

Bitmap

Sia Oracle che Informix mettono a disposizione un interessante metodo di indicizzazione: il tipobitmap.Questo e' basato sull'associazione di una mappa di bit alla chiave di ricerca. Quindi se ci sonopochi tipi diversi di chiave la mappa di bit si mantiene piccola e cosi' il file indice. Inoltre un filepiccolo viene caricato in poco tempo diminuendo accesso a disco e migliorando le performance.

Funzione

Sia Oracle che Informix offrono un sistema di indicizzazione basato su funzioni definitedall'utente. Queste funzioni possono essere espressioni algebriche, codice PL/SQL, C o Java. Ilvalore ritornato dalla funzione viene utilizzato come chiave per la gestione dell'indice. Unesempio riportato nella documentazione di entrambi i software e' quello della funzione Area. Suuna tabella contenente due colonne che rappresentano altezza e larghezza di una certa superficiea cui fa riferimento il record, e' possibile creare un indice sull'area, cosi' da poter ottimizzareun’interrogazione nella cui WHERE sia presente l'area. La funzione ha come parametri altezza elarghezza e restituisce il valore dell'area che viene utilizzata come chiave per l'indice. Informixdice chiaramente che questo tipo di indicizzazione e' basato su B-Tree o R-Tree; benche' Oraclenon lo chiarisca espressamente, l'uso del B-Tree si intuisce dalla possibilita' di eseguire query suun intervallo di dati.

Indici programmabili

Oracle ed Informix mettono a disposizione la possibilita' di programmare completamente lefunzioni di indicizzazione permettendo di scrivere il codice relativo alla loro completa gestione.In Oracle questi vengono chiamati "Domain indexes" e le funzioni vengono caricate attraversol'istruzione IndexType. In Informix questo avviene attraverso un modulo chiamato "DataBlade".

Altre possibilita'

Tutti e tre i DBMS mettono a disposizione la possibilita' di dividere le tabelle su piu' dischi perottimizzare il loro accesso. Oracle offre alcune opzioni veramente interessanti: e' possibilerichiedere la compressione degli indici per diminuire l'accesso a disco a spese della CPU e lapossibilita' di avere tabelle “clusterizzate”, ovvero tabelle che condividono colonne in comunevengono messe nella stessa area del disco per migliorare le prestazioni del join.DB2 offre la possibilita' di fare una "Summary Table", ovvero una tabella che contiene al suointerno dati di piu' tabelle ed evita quindi la necessita' di fare dei join su certe colonne dellestesse.

Conclusioni

Appare chiaro che Oracle e' da questo punto di vista il piu' versatile in quanto mette adisposizione del progettista tutti gli strumenti disponibili attualmente eccetto gli R-Tree. Informixoffre meno possibilita', ma e' ancora molto versatile. Il DB2 invece e' il piu' povero di tutti inquanto mette a disposizione i pur sempre validi B-Tree.

Query optimizerIn questa sezione vogliamo esaminare come il progettista puo' vedere e migliorare le scelte delquery optmizer. Tutti i tre prodotti danno la possibilita' di vedere il piano scelto dal queryoptimizer, influenzarne le scelte o escludere del tutto l'ottimizzatore.

Piano di esecuzione di una query

I comandi SQL EXPLAIN (per DB2), SET EXPLAIN (per Informix) o EXPLAIN PLAN (perOracle) permettono di esaminare il piano di esecuzione attuale di una query; questo consistenella descrizione dell'ordine e della modalita' con cui vengono eseguite le operazioni. E' cosi'possibile conoscere le scelte fatte dall'ottimizzatore per poterle successivamente modificare.DB2, a differenza degli altri due prodotti, mette a disposizione anche uno strumento grafico.Riportiamo un esempio relativo alla prima query:

Strategie utilizzate dall'ottimizzatore

Le strategie offerte sono due: basata sulle regole e basata sui costi.

L'approccio basato sulle regole e' disponibile solo in Oracle per mantenere la compatibilita' conle vecchie versioni del DBMS. L'ottimizzatore sceglie il percorso di accesso ai dati basandosisulla disponibilita' del percorso di accesso per l'istruzione corrente e sul punteggio assegnato a

tale percorso. Lo svantaggio di questo modello consiste nel fatto che tale punteggio e' fissatostaticamente e quindi non modificabile in funzione di necessita' specifiche.

L'approccio basato sui costi e' disponibile su tutti e tre i prodotti ed e' il piu' performante. Si basasu una stima del costo di esecuzione di un piano (I/O, CPU, tempo...) per ciascun piano preso inesame dall'ottimizzatore. Per fare questa stima il sistema mantiene delle tabelle contenenti dati estatistiche relativi alla popolazione della base di dati. Vengono utilizzati degli istogrammi perraccogliere delle informazioni statistiche. Tutti questi dati sono mantenuti in tabelle di sistema.

Ogni volta che si modifica in maniera sostanziale la base di dati (caricamento di un numerorilevante di righe o modifica dello schema di una tabella) si dovra'; procedere all'acquisizione dinuove informazioni. Informix e DB2 non mettono a disposizione nessuno strumento perl'aggiornamento automatico delle statistiche a differenza di Oracle. Dato l'elevato costodell'operazione di aggiornamento, Informix mette a disposizione due diversi livelli di dettaglioper l'acquisizione dei dati. Anche per Oracle e' possibile scegliere tra due diversi tipi di raccoltadei dati: uno per righe, piu' dettagliato, e l'altro per blocchi, piu' veloce.

Metodi d'accesso

In mancanza di indici, il metodo d'accesso ai dati e' la lettura sequenziale.I tre DBMS mettono a disposizione, inoltre, degli altri metodi di scansione relativi a certe loropeculiarita'.Oracle nomina la presenza di un metodo chiamato “Cluster Scan” per scandire le “ClusterTable”.Il metodo “Sample Table Scans” serve per fare statistiche rilevando righe campione da unatabella. Viene utilizzata in query adibite a questo scopo.DB2 offre un metodo chiamato “Table Scan” per scandire una “Summary Table” ed evitare unjoin.

In presenza di indici, sono supportati tutti i metodi classici, ovvero l'accesso diretto al record,l'accesso a piu' record in un intervallo e l'uso del solo indice per avere i dati richiesti quandopossibile.DB2 parla di un “Multiple Index Access”: questo metodo implica l'uso di piu' indici disponibilisu una certa tabella. Viene applicato, per esempio, nel caso in cui la WHERE contengacondizioni di AND e di OR su colonne sulle quali sia disponibile un indice. Ogni indice vieneutilizzato per creare una lista di Record ID che soddisfano il predicato in questione. Per avere lalista finale di record da prelevare bisogna: nel caso OR eliminare i duplicati tra le liste, nel casoAND la creazione di una bitmap dinamica velocizza la creazione di una lista in cui compaionosolo i record presenti su entrambe.

Metodi di Join

Il Nested Loop Join, Hash Join sono comuni a tutti e tre i prodotti. Oracle e DB2 offrono inoltreanche Sort-Merge Join e Star Join. Quest'ultimo serve solo quando nel join vengono usate chiaviprimarie o chiavi straniere tra una tabella di tipo “Fact tables” e una di tipo “Lookup table”.Queste due tabelle sono generalmente usate nei datawearehouse: “Fact tables” sono tabelle moltograndi, mentre “Lookup table” sono tabelle molto piu' piccole che contengono informazioni sudegli attributi delle prime.Oracle nomina Cluster Join per le tabelle di tipo “Cluster Table”.

Influenzare l'ottimizzatore

Oracle ed Informix consentono di sostituire alle scelte dell'ottimizzatore le proprie e inoltremettono a disposizione delle istruzioni per poter influenzare le scelte dell'ottimizzatore:permettono intervenire sulla query mediante indicazioni che possono essere aggiunte all'internodell'istruzione SQL di SELECT, UPDATE o DELETE per forzare alcune scelte. Oracle lechiama “hints”, Informix le chiama "direttive di ottimizzazione".E' possibile modificare:

• La strategia dell'ottimizzatore (basato sulle regole, basato sui costi)

• Scelta dell'obiettivo: miglior tempo di esecuzione, miglior tempo di risposta.

• Il tipo di percorso di accesso

• L'ordine di join per un'operazione di join

• Quale tipo di join scegliere

DB2 ed Informix hanno inoltre delle classi di ottimizzazione per facilitare il compito delprogettista. DB2 offre la possibilita'; di scegliere tra 10 classi di ottimizzazione (0-9) cheimpegnano in modo differente le risorse del nostro sistema, Informix da' la possibilita'; di variareil tempo speso nella ricerca del piano piu' veloce modificando la classe di ottimizzazione.

Le classi del DB2 sono:

Classe 0: Per questa classe l'ottimizzatore utilizza il minimo numero possibile di risorse: taleclasse dovrebbe essere utilizzata solo per query molto semplici e che accedono a tabelle benindicizzate. Vengono considerate solo statistiche la cui distribuzione sia uniforme. Vengonoconsiderati solo Block Nested Loop Join e percorsi d'accesso tramite indice. La scelta del pianoviene effettuata tramite un algoritmo goloso.

Classe 1: Vengono considerate statistiche a distribuzione non uniforme. Sono considerati ilMerge Join e la scansione completa di una tabella. E' ora considerato anche lo Star Join. Utilizzal'algoritmo goloso.

Classe 2: Per questa classe l'ottimizatore utilizza entrambi i tipi di statistiche: a distribuzioneuniforme e non. Sono inoltre disponibili tutti i metodi di riscrittura della query. Utilizzal'algoritmo goloso.

Classe 3: Questa classe utilizza, come le successive e a differenza delle precedenti, il DynamicProgramming Join Enumeration per la scelta del piano di esecuzione della query. Questoalgoritmo esplora piu' a fondo lo spazio delle soluzioni, ed e' raccomandato in presenza di joinmultipli; a differenza della seconda classe pero' non utilizza tutti i metodi di riscrittura e solostatistiche a distribuzione non uniforme. Utilizza anche il Multiple Index Access

Classe 5: Come detto e' la classe di default. Utilizza tutte le statistiche e la tutte le regole diriscrittura della query. Inoltre, per interrogazioni particolarmente complesse, vengonoparzialmente utilizzate regole euristiche per limitare il tempo speso nella selezione di un pianod'accesso.

Classe 7: Differisce dalla classe 5 per un uso completo delle regole euristiche.

Classe 9: Mette a disposizione tutti i metodi di riscrittura, tutte le statistiche, tutti i possibili

metodi di join e tutti i metodi d'accesso.

NB: le classi 4 e 6 non sono utilizzate.

E' inoltre possibile inserire nella SELECT delle clausole per influenzare l'ottimizzatore in casispecifici per:

• Recuperare solo quelle righe che permettono un'interrogazione read-only per evitare lockesclusivi che possono penalizzare le prestazioni del sistema.

• Limitare il numero di tuple recuperato. E' possibile in applicazioni tipo “browser” di volerrecuperare solo alcune righe.

• Gestire in maniera piu' efficente il rilascio di memoria dovuto ad un cursore.

Conclusioni

Informix ed Oracle sono abbastanza simili nella filosofia d'approccio all'ottimizzazione,permettendo d'intervenire su un gran numero di parametri a diversi livelli fino ad arrivare allacompleta esclusione dell'ottimizzatore. DB2 si differenzia dai precedenti mettendo a disposizionedei profili predefiniti di ottimizzazione: L'impressione e' stata quella che DB2 offraun'interazione piu' ad alto livello impedendo volontariamente l'intrusione nelle scelte del sistema.