WIXLIB

Esercitazione 5DB2 – Cataloghi e IndiciSistemi Informativi L-BHome Page del rsione elettronica: esercitazione5.pdfCataloghi di sistema Sono insieme di tables e views che descrivono la struttura logica e fisicadegli oggetti di un DB, ovvero contengono metadati Gli schemi relativi sono: SYSIBM: tables a uso interno di DB2 SYSCAT: viste definite sulle tables in SYSIBM, a uso degli utenti SYSSTAT: statistiche sul DB Le viste in SYSCAT (in DB2 sono 142!) costituiscono il cosiddettoINFORMATION SCHEMA e alcune di queste sono standardizzate Non sono direttamente modificabiliLe cosiddette statistiche, sono invece (in parte) modificabili utilizzando leviste dello schema SYSSTAT (9 in totale) Ese5: Cataloghi e indiciSistemi Informativi L-B21

SYSCAT.SCHEMATAOgni tupla descrive uno schemaPrincipali attributi: NameDescriptionSCHEMANAMENome dello schemaOWNERUtente che ha creato lo schema1CREATE TIMETimestamp di creazione1Il campo OWNER, in questo catalogo così come nei successivi, è stato introdotto in DB2 a partire dalla versione 9,soppiantando il campo DEFINER ormai deprecato.Ese5: Cataloghi e indiciSistemi Informativi L-B3SYSCAT.TABLES Ogni tupla descrive una table o view (“oggetti”) del DBTables e views dei cataloghi sono anch’esse inclusePrincipali attributi:NameDescriptionTABSCHEMASchema dell’oggettoTABNAMENome dell’oggettoProprietario dell’oggetto1OWNERTYPETipo dell’oggetto (‘T’ tabella, ‘V’ vista)CREATE TIMETimestamp in cui l’oggetto è stato creatoCOLCOUNTNumero di colonneCHILDRENNumero di vincoli di foreign key che fanno riferimento a una key di questo oggettoPARENTSNumero di foreign key definite per questo oggettoSELFREFSNumero di foreign key autoreferenzialiCHECKCOUNTNumero di check per questo oggettoKEYCOLUMNSNumero di colonne nella primary keyEse5: Cataloghi e indiciSistemi Informativi L-B42

SYSCAT.VIEWSOgni tupla fornisce dettagli aggiuntivi per una vistaPrincipali attributi: NameDescriptionVIEWSCHEMASchema della vistaVIEWNAMENome della vistaOWNERUtente che ha creato la vistaVIEWCHECKTipo di check applicato per le modifiche alla vista (‘C’ cascaded check option,‘L’ local check option, ‘N’ no check option)READONLY‘N’ la vista può essere modificata dagli utenti con gli opportuni privilegi, ‘Y’ la vista non può essere aggiornata a causa della sua definizioneTEXTEse5: Cataloghi e indiciCLOB contenente la definizione SQL della vistaSistemi Informativi L-B5SYSSTAT.TABLES Ogni tupla fornisce le statistiche per una table o viewPrincipali attributi:NameDescriptionTABSCHEMASchema dell’oggettoTABNAMENome dell’oggettoCARDCardinalità (numero di tuple)NPAGESNumero totale di pagine in cui esiste almeno una tuplaFPAGESNumero totale di pagine (del file)OVERFLOWEse5: Cataloghi e indiciNumero di record in overflowSistemi Informativi L-B63

SYSCAT.COLUMNS Ogni tupla descrive una colonna di una vista o di una tabellaPrincipali attributi:NameTABSCHEMADescriptionSchema dell’oggetto cui appartiene la colonnaTABNAMENome dell’oggetto cui appartiene la colonnaCOLNAMENome della colonnaTYPENAMENome del tipo della colonna (VARCHAR, SMALLINT, ecc.)LENGTHMassima lunghezza dei valori della colonnaDEFAULTValore di default se definito, altrimenti NULLNULLSKEYSEQEse5: Cataloghi e indici‘Y’ se la colonna ammette NULL, altrimenti ‘N’Posizione numerica della colonna all’interno della primary keySistemi Informativi L-B7SYSSTAT.COLUMNS Ogni tupla descrive le statistiche di una colonna di una vista o di unatabellaPrincipali attributi:NameTABSCHEMATABNAMEDescriptionSchema dell’oggetto cui appartiene la colonnaNome dell’oggetto cui appartiene la colonnaCOLNAMENome della colonnaCOLCARDNumero di valori distintiLOW2KEYSecondo valore minoreHIGH2KEYSecondo valore maggioreNUMNULLSNumero di valori nulliAVGCOLLENEse5: Cataloghi e indiciNumero medio di byte di un valoreSistemi Informativi L-B84

SYSCAT.INDEXES Ogni tupla descrive un indicePrincipali attributi:NameDescriptionINDSCHEMASchema dell’indiceINDNAMETABSCHEMATABNAMECOLCOUNTEse5: Cataloghi e indiciNome dell’indiceSchema della table su cui è costruito l’indiceNome della table su cui è costruito l’indiceNumero di colonne su cui è costruito l’indiceSistemi Informativi L-B9SYSCAT.INDEXCOLUSE Ogni tupla descrive su quali colonne è costruito un indicePrincipali attributi:NameDescriptionINDSCHEMASchema dell’indiceINDNAMENome dell’indiceCOLNAMENome della colonnaCOLSEQCOLORDEREse5: Cataloghi e indiciPosizione della colonna (1,2, )Ordinamento dei valori (‘A’ ascending; ‘D’ descending)Sistemi Informativi L-B105

SYSSTAT.INDEXES Ogni tupla descrive le statistiche di un indicePrincipali attributi:NameDescriptionINDSCHEMASchema dell’indiceINDNAMETABSCHEMATABNAMENome dell’indiceSchema della table su cui è costruito l’indiceNome della table su cui è costruito l’indiceCOLNAMENome della colonnaCOLCARDNumero di valori distintiNLEAFNumero di pagine foglia dell’indiceNLEVELSFULLKEYCARDNumero di livelli dell’indiceNumero di valori distinti di chiave nell’indiceAVGCOLLENEse5: Cataloghi e indiciNumero medio di byte di un valoreSistemi Informativi L-B11Altri cataloghi Nel seguito viene riportata la definizione di altri cataloghi importanti, cheperò non utilizziamo in questa esercitazioneEse5: Cataloghi e indiciSistemi Informativi L-B126

SYSCAT.TABCONST Ogni tupla descrive un constraint di tipo CHECK, UNIQUE, PRIMARYKEY o FOREIGN KEYPrincipali attributi:NameDescriptionCONSTNAMENome del constraintTABSCHEMASchema della tabella cui si applica questo constraintTABNAMENome della tabella cui si applica questo constraintOWNERUtente che ha creato il constraintTYPETipo di constraint (‘F’ foreign key, ‘K’ check, ‘P’ primary key, ‘U’ unique)ENFORCEDEse5: Cataloghi e indici‘Y’ se il constraint è attivo, altrimenti ‘N’Sistemi Informativi L-B13SYSCAT.KEYCOLUSE Ogni tupla descrive una colonna coinvolta nella definizione di una chiaveprimaria, chiave o foreign keyPrincipali attributi:NameDescriptionCONSTNAMENome del constraintTABSCHEMASchema di appartenenzaTABNAMENome della tabellaCOLNAMENome della colonna coinvoltaCOLSEQEse5: Cataloghi e indiciPosizione della colonna nella definizione della chiaveSistemi Informativi L-B147

SYSCAT.REFERENCES Ogni tupla descrive una foreign keyUtile da utilizzare assieme a SYSCAT.KEYCOLUSE!Principali attributi:NameDescriptionCONSTNAMENome del constraintTABSCHEMASchema di appartenenza della tabella dipendente (quella che contiene la FK)TABNAMENome della tabella dipendenteOWNERUtente che ha creato il constraintREFTABSCHEMASchema della tabella cui si fa riferimentoREFTABNAMENome della tabella cui si fa riferimentoREFKEYNAMENome del constraint nella tabella cui si fa ero di colonne coinvolte nella foreign keyDELETERULEPolitica per la cancellazione (‘A’ no action, ‘C’ cascade, ‘N’ set null, ‘R’ restrict)UPDATERULEPolitica per l’aggiornamento (‘A’ no action, ‘R’ restrict)Ese5: Cataloghi e indiciSistemi Informativi L-B15SYSCAT.CHECKS Ogni tupla descrive un check constraintPrincipali attributi:NameDescriptionCONSTNAMENome del checkOWNERUtente che ha creato il checkTABSCHEMATABNAMESchema di appartenenzaNome della tabella cui è applicato il checkCREATE TIMETEXTEse5: Cataloghi e indiciTimestamp di creazioneCampo CLOB contenente la definizione del checkSistemi Informativi L-B168

SYSCAT.COLCHECKS Ogni tupla descrive una colonna coinvolta in un check constraintUtile da utilizzare assieme a SYSCAT.CHECKS!Principali attributi:NameDescriptionCONSTNAMENome del checkTABSCHEMASchema di appartenenzaTABNAMENome della tabella cui è applicato il checkCOLNAMEUSAGENome della colonna coinvoltaUso della colonna, vale ‘R’ per i check, altri valori per altri usi nontrattati nel corsoEse5: Cataloghi e indiciSistemi Informativi L-B17SYSCAT.TRIGGERS Ogni tupla descrive un triggerPrincipali attributi:NameDescriptionTRIGSCHEMASchema del triggerTRIGNAMETABSCHEMATABNAMETEXTEse5: Cataloghi e indiciNome del triggerSchema della table su cuì è definito il triggerNome della table su cuì è definito il triggerCLOB contenente la definizione SQL del triggerSistemi Informativi L-B189

Esempi di query su cataloghiSELECT TABSCHEMA, COUNT(*) FROM SYSCAT.TABLES GROUP BY TABSCHEMA-- numero di table/views in ogni schemaSELECT TABNAME FROM SYSCAT.TABLESWHERE TABSCHEMA ‘SYSCAT;-- i cataloghi in SYSCATSELECT INDNAME from SYSCAT.INDEXESwhere INDSCHEMA ‘SILB001’;-- indici di uno schemaSELECT TABNAME,NCARD,NPAGES,FPAGES,OVERFLOW FROM SYSSTAT.TABLESWHERE TABSCHEMA ‘SILB001;-- statistiche delle table di uno schemaSELECT TRIGNAME, CAST(TEXT AS VARCHAR(500)) AS SQLDEFFROM SYSCAT.TRIGGERSWHERE TABSCHEMA ‘SILB001;-- definizioni SQL dei trigger di uno schema-- il CAST serve per dimensionare il campo SQLDEF (TEXT è un CLOB!)Ese5: Cataloghi e indiciSistemi Informativi L-B19Collezionare le statistiche Le informazioni statistiche sulle tabelle vengono utilizzate principalmentedall’ottimizzatore per eseguire in modo efficiente le interrogazioniNon vengono aggiornate automaticamente modificando una tabella, mavanno aggiornate con un apposito comando RUNSTATSEsistono molte varianti, noi usiamo:RUNSTATS ON TABLE MySchema.TableName WITH DISTRIBUTIONON ALL COLUMNS AND DETAILED INDEXES ALL La forma WITH DISTRIBUTION forza DB2 a collezionare statistichedettagliate sulle distribuzioni dei valori delle varie colonne (quante volte èripetuto il valore più frequente, quante volte il secondo, ecc.)Ese5: Cataloghi e indiciSistemi Informativi L-B2010

Explain tables Ogni volta che DB2 elabora uno statement SQL produce un cosiddetto“piano di accesso” (access plan), che fornisce dettagli su come larichiesta viene effettivamente eseguitaInformazioni dettagliate su tali piani di accesso possono esseremantenute in un insieme di tabelle chiamate Explain tablesLe Explain tables possono essere esaminate mediante tool grafico del Command Editor (Visual Explain) interrogazioni SQLLo schema associato alle Explain table coincide con quello dellouserid (es. SILB001): quindi ogni utente del sistema genera uninsieme distinto di Explain tablePrima di poter usare le Explain table occorre crearle modalità grafica (Control Center o Command Editor) a linea di comando ( solo lato server)db2 –tf EXPLAIN.DDL (sqllib/misc/EXPLAIN.DDL)Ese5: Cataloghi e indiciSistemi Informativi L-B21Creazione Explain table: modalità grafica Eseguire una query qualsiasida Command EditorEse5: Cataloghi e indiciSistemi Informativi L-B2211

Explained statements historyEse5: Cataloghi e indiciSistemi Informativi L-B23Esercizio 1: prestazioni degli indici Proviamo a usare DB2 per cercare di capire come viene scelto l'indicemigliore da usare nell’esecuzione di una queryA tale scopo andremo a creare e popolare una tabella (IDXTEST) consolo 2 valori nel campo A, ma più valori distinti nel campo BAvendo due indici, uno su A e uno su B, e una querySELECT * FROM IDXTEST WHERE A :a AND B :b i due predicati avranno un diverso "fattore di selettività“ (FILTER FACTOR),e ciò influirà sulla scelta dell’indice scelto da DB2Eseguiamo le query dal Command Editor di DB2 per poter vederel’access planCominciamo creando la tabella IDXTEST:CREATE TABLE IDXTEST(A INT, B INT, C CHAR(250))Il campo C serve solo a creare record “grandi” e quindi una relazione conmolte pagineEse5: Cataloghi e indiciSistemi Informativi L-B2412