DB2 DB-Design und physische Strukturen

DB2 –Physisches Design
Standards, Tipps und Grundlagen
zur DB2-Datenmodellen und deren
Implementierung in der DB2-Physik
DB2 DB-Design
und
physische Strukturen
Ausgabe 2: 2004/2005
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 1 von 29
DB2 –Physisches Design
Inhaltsverzeichnis
1
VORWORT
2
GRUNDSÄTZLICHES ZU DB2 UND PERFORMANCE
2.1
Optimierungspotentiale bei DB2
2.2
Das physische DB-Design und seine Umsetzung nach DB2
2.2.1
Allgemeine Vorgehensweise
2.2.2
Vorgehensweise gemäß Ziel-DBMS
2.3
Das physische DB-Design bei DB2
2.3.1
Überführung des Informationsmodells - allgemeine Vorgehensweise
2.3.1.1 Übernahme von Satztypen
2.3.1.2 Zusammenlegen von Satztypen
2.3.1.3 Denormalisierung
2.3.1.4 Trennen von Satztypen
2.3.1.5 Schaffung zusätzlicher Tabellen
2.3.1.6 Ergänzen zusätzlicher Felder im physischen Datenmodell
2.3.2
Grundsätzliche Regel bei DB2
2.4
Überführen der Beziehungen
2.5
Einrichten der Zugriffspfade am Beispiel DB2
2.6
Festlegen der physikalischen DB-Struktur
2.7
Festlegen der DB2 Datentypen
3
BASIS-"REGELN" ZUM PHYSISCHEN DESIGN VON DB2 DATENBANKEN
3.1
"Keep Like Things Together"
3.2
"Keep Unlike Things Apart"
3.3
"Cluster Your Data"
3.4
"Consider Type 2 Indexes"
3.5
"Use Locksize ANY"
3.6
"Examine Small Tables"
3.7
"Partition the Data"
3.8
"Fewer Rows of Data per Page"
3.9
"Access Data in Consistent Order"
3.10
"Commit As Soon As Practical"
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 2 von 29
DB2 –Physisches Design
3.11
"Retry Application After Deadlock/Timeout"
3.12
"Close Cursors"
3.13
"Bind Plans With ACQUIRE(USE)"
3.14
"Bind With ISOLATION(CS) And CURRENTDATA(NO)"
4
4.1
DB2-OBJEKTE – STRUKTURIERUNGSEMPFEHLUNGEN
Datenbanken (Databases)
4.2
Tabellenspeicher (Tablespaces)
4.2.1
Ziel und Einsatz des PTS
4.2.2
Einsatz und Aufbau eines STS
4.2.3
Empfehlungen zu "tablespaces"
4.2.3.1 TABLESPACE- Definitionen
4.2.3.2 Die wichtigsten Definitionsparameter für TS
4.3
Tabellen (Tables)
4.3.1
Die Größe von Tabellen (Tables)
4.3.2
Große Tabellen (LARGE Tables)
4.3.2.1 Vorteile von LTS
4.3.2.2 Generelle Empfehlungen zu LTS
4.3.3
Partitionierung großer Tabellen (LTS)
4.3.3.1 Einsatz von „LTS Partitionierung“
4.3.3.2 LTS und „I/O Contentions“
4.3.3.3 Partitionierung und Verarbeitung von TS
4.3.4
INSERT-Algorithmen unter Nutzung des "partitioned Index"
4.3.4.1 Anwendbarkeit
4.3.4.2 Vorteile dieser Technik
4.3.4.3 Nachteile dieser Technik
4.3.5
INSERT-Algorithmen unter Nichtbeachtung des "partitioned Index"
4.3.5.1 Anwendbarkeit
4.3.5.2 Vorteile dieser Technik
4.3.5.3 Nachteile dieser Technik
4.3.6
"Combined Technique"-INSERT-Algorithmen
4.3.6.1 Anwendbarkeit
4.3.6.2 Vorteile dieser Technik
4.3.6.3 Nachteile dieser Technik
4.3.7
"Code-Tables"
4.3.8
"Flip-Flop Tables"
4.3.8.1 Vorteile dieser Technik
4.3.8.2 Bemerkungen zu dieser Technik
4.3.9
"Hot Spots"
4.3.10 Weitere Tips zum Table – Design
4.4
Indizes (Indexe)
4.4.1
Indexstrukturen und Konzepte
4.4.2
Indexarten
4.4.3
Die Indexdefinition
4.4.3.1 Die Parameter zur Indexdefinition
4.4.3.2 Wählen eines Wertes für PIECESIZE
4.4.4
Überlegungen zu Indizes
4.4.5
Die neue Index Typ-2-Struktur
4.4.6
Der Einsatz von "clustering" Indizes
4.4.6.1 Auswirkungen auf PCTFREE und REORG
4.4.6.2 Auswirkungen auf asynchrone l/O's
4.4.7
"Partitioned Index"
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 3 von 29
DB2 –Physisches Design
4.4.8
"Non-Unique Indexe"
4.4.8.1 Vorteile dieser Technik #1
4.4.8.2 Nachteile der Technik #1
4.4.9
Ersatzstrategien zu „Non-Unique“ Indizes über Design-Maßnahmen
4.4.9.1 Vorteile dieser Technik #2
4.4.10 Die Index-Klausel UNIQUE WHERE NOT NULL
4.4.11 "Non-Partititioned" Index "Pieces"
4.4.12 Indizes zur Vermeidung von Sort-Vorgängen
4.4.13 Indizes aus mehr als einer Spalte
4.4.13.1
Die Frage: Ein "multi-column" Index oder mehrere Indizes
4.4.13.2
Die Reihenfolge der Spalten im Index
4.4.14 Anlegen und Wiederfinden von langen IX-Einträgen
4.4.15 Match-Codes
4.4.16 Die Indizierung von VARCHAR Spalten
4.4.17 Weitere Tipps zum Index-Design
4.4.18 „Indexing“-Strategien für optimale Performance
4.4.18.1
Die generelle Vorgehensweise
4.4.18.2
Der “Proactive Approach”
4.4.18.3
Perfekte Indizierungsrichtlinien
4.4.18.4
Proaktives Tuning von Queries
4.4.18.5
Reaktives Query Tuning
4.4.19 Index Maintenance
4.4.20 Queries und mögliche Indizierungsstrategien
4.5
5
Views
PARALLELE VERARBEITUNG
5.1
Locking bei DB2
Die Begriffe beim "Locking
5.1.1
Probleme beim “Locking“
5.2
LOCKSIZE
5.2.1
Anzahl "rows per page"
5.2.2
Die Ebenen der Kontrolle
5.2.3
"Lock Escalation"
5.2.3.1 Mögliche Werte von LOCKMAX
5.2.3.2 Die "default" - Werte von LOCKMAX
5.2.4
"row locking" und seine Kosten
5.3
Index-Design und konkurrierende Verarbeitung
5.3.1
Konkurrierende Verarbeitung UPDATE und SELECT(Fall 1)
5.3.2
Konkurrierende Verarbeitung UPDATE und SELECT(Fall 2)
6
QUERIES UND PARALLELVERARBEITUNG
6.1
Queries und Partitionierung
6.1.1
Queries und Verarbeitung von PTS
6.1.2
Der notwendige Parallelitätsgrad von Queries
6.1.3
Einrichten von PTS für Parallelverarbeitung
6.2
7
SQL Formulierungen für parallele Verarbeitung
DB2 SPEICHERPLATZZUWEISUNG UND SPEICHERVERWALTUNG
7.1
Speicherplatzgruppen (Storage Group)
7.1.1
Speicherplatzgruppen und die Verteilung der Datasets
7.1.2
Tips zu STORAGE GROUPS
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 4 von 29
DB2 –Physisches Design
7.2
Der Buffer Manager
7.2.1
Grobe Berechnung der Berechnung DB2-Datenbank-Buffer
7.2.2
Bereitstellen der Bufferpools
7.2.3
Funktionsweise des Buffermanagers
7.2.4
Eine Methode zur “buffer pool“ Dimensionierung
7.2.4.1 „Statistics report information”
7.2.4.2 Das BP Konzept
7.2.4.3 Evaluation der BP-Grössen
7.3
DB2 Logging
7.3.1
LOGBUFSZ - Log buffer size
7.3.2
LOGFILSIZ Parameter
7.3.3
MINCOMMIT – DB Konfigurationsparameter
7.4
DB2 Work Files
7.4.1
„workfile spaces“
7.4.2
Hinzufügen von “workfiles”
7.5
Temporary Tables
7.5.1
“Created Temporary Tables”
7.5.2
Declared Temporary Tables
7.5.3
Declared Temporary Tables und Performance der AP
7.6
DB2 Katalog und Directory
7.6.1
Performance - Indikatoren im Katalog
7.6.2
Überwachung der Speicherorganisation
7.6.2.1 Vermeiden von „Contentions“ auf dem Katalog
7.6.2.2 Abfragen zur Überwachung der Speicherorganisation(Beispiele)
7.6.3
Der Umgang mit PLAN und PACKAGES
7.6.4
DB2 und Extents
8
8.1
DB2 SUBSYSTEM PERFORMANCE
Asynchroner Preformat
8.2
Parallel data set open
8.2.1
Performance
8.2.1.1 Measurement environment
8.2.1.2 “CPU bound” Testfall
8.2.1.3 I/O bound test case
8.3
“Virtual storage” Entlastung
8.3.1
Instrumentation enhancements
8.3.2
„Database address space” —Nutung des “virtual storage”
8.4
„Evaluate uncommitted“
8.4.1
Beschreibung von EVALUNC
8.4.2
Empfehlung
8.5
Reduziertes Logging für “variable-length rows”
8.6
DDL concurrency improvement
8.7
Die IRLM-Umgebung und die LOCK-Anforderungen
8.8
Traces
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 5 von 29
DB2 –Physisches Design
9
MVS-FAKTOREN
9.1
Speichertypen, die von DB2 UDB genutzt werden
9.2
Wie DB2 den Speicher nutzt
9.3
DSNZPARMS
9.3.1
Ausgabe der “current settings”
9.3.2
Parameter die für „online“ Änderungen verfügbar sind
9.3.3
Beispiele
10
ANHANG
10.1
Checkliste zum physischen DB-Design DB2
10.2
Index
10.3
Glossar
10.4
Literaturhinweise
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 6 von 29
DB2 –Physisches Design
1
Vorwort
„Information steht heute und auch in Zukunft im Mittelpunkt wirtschaftlichen Handelns. Information wurde zur treibenden Kraft der Informationsgesellschaft ... „. Das
Zitat von John Naisbitt über die Ressource “Information” in seinem Bestseller „Megatrends“ von 1988 sagt in Kürze immer noch alles über die Bedeutung der Information in unserer Gesellschaft aus.
Information ist ein denkbar abstrakter Stoff, der leichter, effizienter und produktiver
verwendet werden kann, wenn er geordnet und seinem sinnvollen Zusammenhang
gemäß dargestellt und angeboten wird. Datenbankmanagementsysteme sind die
Werkzeuge, mit denen Informationen strukturiert, verwaltet und bedarfsgerecht aufbereitet, geliefert werden können. Über sie werden moderne Informationssysteme
erst möglich.
DB2 von IBM ist eines dieser Datenbanksysteme, das in einer modernen ITUmgebung in der Lage ist, Informationsarchitekturen und -systeme über und für die
gesamte Unternehmenshierarchie umfassend zu ermöglichen.
Informationsverarbeitung ist dann effizient, wenn die richtigen Informationen zum
richtigen Zeitpunkt am richtigen Ort sind. Dazu bedarf es einer sorgfältigen Planung, einer technisch perfekten Implementierung und einer ständigen Kontrolle und
Abstimmung. Die Datenbank als Informationsspeicher muss in der Lage sein, die
gestellten Anforderungen sicher, konsistent und schnell zu erfüllen: Manche Informationen sind eben nur dann wertvoll, wenn sie hochaktuell sind. Und - jeder
Nutzer spezifischer Informationen kann seine eigenen individuellen und subjektiven
Ansprüche an diese Ressource “Information” stellen. Dies erfordert seitens der
Technologie hochperformante und flexible, aber auch stabile und sichere Systeme.
DB2 bietet alle Möglichkeiten, so eingestellt zu werden, dass alle erforderlichen Aktivitäten und Anwendungen auf effizienteste Art und Weise bedient werden. Dazu
müssen alle (System-)Parameter optimal gewählt und die Datenstrukturen nach
sorgfältiger Analyse in die physische DB2-Umgebung umgesetzt werden. Dies gilt
umso mehr, als mit der Ausweitung der Informationstechnik die Komplexität der Information selbst und die Quantität angebotener Datenmengen ständig zunimmt,
andererseits die Informationsqualität aber weiter verbessert und die verfügbaren
Informationen immer effektiver und genauer dargeboten werden sollen.
Denn: Ein Datenbanksystem selbst bringt den Unternehmen noch keinen oder
nur geringen Nutzen. Dieser entsteht erst aus der intensiven Nutzung verfügbarer
Information und der daraus resultierenden Wertschöpfung: Je mehr Nutzung, um so
besser für das Unternehmen. Die Erkenntnis, dass der Unternehmenserfolg, wie bei
den bekannten „klassischen“ Produktionsfaktoren - Finanzen, Material, Anlagen und
Personal - unmittelbar von einer erschöpfenden und werteffizienten Verwertung dieser „fünften Kraft“ Information abhängt, führte zur Suche nach neuen Konzepten in
einem neuen betriebswirtschaftlichen Umfeld - der Informationswirtschaft. Im
Zentrum dieser wirtschaftlichen Aspekte steht die Informationstechnologie - ihre
Möglichkeiten, ihre Produkte. Die Erwartungen an die Leistungsfähigkeit eines
DBMS sind folglich enorm hoch.
In dieser Handbuchserie werden unter DB2 Performance-Gesichtspunkten alle
wichtigen Fragen zu und die Möglichkeiten bei DB2 thematisiert. Die Serie besteht
aus folgenden Büchern:
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 7 von 29
DB2 –Physisches Design
01_Die Umgebung von DB2 – Eine Architekturübersicht
02_DB2 und das Relationenmodell von Dr. Codd
03_DB2 und SQL-Performance
04_Physisches DB-Design und DB2-Performance
05_ DB2 und effiziente Anwendungsentwicklung
06_Administration von DB2 Umgebungen
07_Tunig-Beispiele zu DB2: Erfahrungen aus der Praxis
08_DB2 im Client-Server Umfeld
09_Tools und hilfreiche Produkte zu DB2
Die Handbuch-Serie besteht natürlich nicht aus „Manuals“ im Sinne von Systemdokumentation – wie sie vom Hersteller sowieso angeboten wird -, sondern vielmehr
ist beabsichtigt DB2 vor unter Nutzbarkeits- und Performance-Gesichtspunkten
möglichst umfassend darzustellen.
Die gesamte Serie ist für Kenner, nicht in erster Linie für Neulinge im Umgang mit
DB2 konzipiert.
Das vorliegende Handbuch beschäftigt sich mit dem Thema: „Physisches DBDesign und DB2-Performance“ Es soll als Leitfaden dienen, das System ursprünglich, ständig und zukünftig optimal planen und einstellen zu können - immer
mit dem Ziel, höchstmöglicher Performance in allen direkt betroffenen und umliegenden Betrachtungsfeldern.
Viel Spaß beim Lesen und viel Erfolg bei der Nutzung von IBM’s DB2/UDB
Mit freundlichen Grüßen
S.K. Consulting Services GmbH
Sepp Kraus
Für die Mitarbeit an diesem Handbuch bedanken wir uns insbesondere bei den Firmen
ARAL AG, Bochum
AXA Versicherungen, Köln
BMW AG, München
Itellium GmbH & Co, Fürth
IT-Verlag, Sauerlach b. München
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 8 von 29
DB2 –Physisches Design
2
2.1
Grundsätzliches zu DB2 und Performance
Optimierungspotentiale bei DB2
Die Optimierungspotentiale bei relationalen Datenbanksystemen unterscheiden sich
generell – auch zwischen DB2 und Oracle, SQL Server und SYBASE - nur minimal.
Sicher ist, dass die höchsten Potentiale, um diese relationalen Datenbanksysteme
schneller zu machen im Bereich der Abfragesprache SQL und damit im Umfeld
der Anwendungsentwicklung und der Programme zu suchen ist (siehe Grafik unten).
Eine weitere Fehlerquelle ist das physische DB-Design, gefolgt von der Einstellung
der Systemparameter im DB2 selbst und im Betriebssystem (OS/390, z/OS, AIX,
UNIX usw.)
Empfehlenswert ist es, im Tuningfall dort zuerst zu suchen, wo das größte Potential
zum Lösen der Tuningaufgaben existiert. Man darf nur die anderen Bereiche nicht
vergessen.
In diesem Handbuch werden vorrangig die Problematiken des physischen Designs bei DB2 behandelt. Die Problematik der SQL-Verarbeitung findet man im
Band „SQL und DB2 Performance“ aus dieser Reihe „Tuning und Performance für
DB2-Umgebungen“.
2 = DB2
System
(10%)
3 = phys.
DBDB-Design
(20%)
4 = Anwendung
(60%)
1
2
3
4
1 = OS
System
(10%)
Bild-01: Tuningpotentiale im DB2-Umfeld
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 9 von 29
DB2 –Physisches Design
2.2
Das physische DB-Design und seine Umsetzung nach DB2
Auf dem Markt gibt es eine Vielzahl von Datenbankmanagementsystemen, die
sich zwar leicht in Gruppen, wie
•
Relational
•
CODASYL
•
Hierarchisch
•
Objektorientiert
einteilen lassen, sich aber in vielen Leistungsmerkmalen deutlich voneinander
unterscheiden.
Ziel des systemspezifischen DB-Design ist es, ausgehend vom konzeptionellen
Informationsmodell, systematische Überführungsschritte in das Ziel-DBMS
festzulegen, in denen insbesondere die Stärken des Zielsystems berücksichtigt
werden.
2.2.1
Allgemeine Vorgehensweise
Basis des physischen DB-Designs bilden die methodischen Säulen: Konzeptionelles Informationsmodell und Prozess- oder Funktionsmodell: Dabei liefern diese beiden Ergebnistypen generell folgende Anhaltspunkte für das Physische DatenbankDesign:
Konzeptionelles Informationsmodell
Funktionsmodell
D Grafik
D Grafik
D Dokumente
D Beschreibung
D (Mengen)
D (Häufigkeiten)
D („business rules" / Domänen)
D (Zugriffsarten / Verarbeitungswege)..
Bild-02: Allgemeine Vorgehensweise beim Informationsdesign
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 10 von 29
DB2 –Physisches Design
2.2.2
Vorgehensweise gemäß Ziel-DBMS
Jedes Ziel-DBMS, ob relational oder von anderer Architektur, verlangt nach individueller Anpassung der konzeptionellen Strukturen unter Performance- und Nutzungsgesichtspunkte
2.3
2.3.1
Das physische DB-Design bei DB2
Überführung des Informationsmodells - allgemeine Vorgehensweise
Die Überführung kann beim physischen DB-Design aus den folgenden Schritten
kumulativ oder selektiv erfolgen:
1. Überführung des Informationsmodells
Übernahme von Satztypen
Zusammenlegen von Satztypen
Normalisierung
Denormalisierung
Trennen von Satztypen
2. Ergänzen des DB-Modells
Schaffung zusätzlicher Tabellen
Ergänzen zusätzlicher Felder
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 11 von 29
DB2 –Physisches Design
2.3.1.1 Übernahme von Satztypen
In der Regel können bei relationalen Datenbanksystemen die konzeptionellen
Strukturen zumindest teilweise 1:1 in die Datenbank übernommen werden, d.h.
Entity-Type = Tabelle
Die Gefahr dabei ist, dass in der Datenbank viele "kleine" Tabellen entstehen, die im
Applikationsumfeld zu übermäßigen "Join"-Aktivitäten führen und damit die Performance, abhängig von der Anzahl solcher Aktionen, durchaus negativ beeinflussen.
Daher gilt grundsätzlich die Empfehlung, das konzeptionelle Datenmodell (KDM)
gemäß den Möglichkeiten der DBMS-Physik zu transformieren, sodass der technische Aufwand des Systems für den zukünftigen Produktionsbetrieb minimiert werden kann.
Beispiel:
KUNDE
KUNDE
KUNDE
(kd-nr)
(kd-nr)
•Kd-nr
Kd-adresse
......
Kd-konto (!)
Kd-saldo (!)
Kd-letzte-bewegung
Gehört zu
hat
KONTO
KONTO
(kto-nr)
(kto-nr)
Bild-03: Übernahme von Satztypen beim Informationsdesign
2.3.1.2 Zusammenlegen von Satztypen
Bei einer 1:1 - Beziehung kann eine Zusammenlegung günstig sein, falls beide
Satztypen in wichtigen Benutzersichten gemeinsam verwendet werden.
In den Applikationen ist dann kein „join" erforderlich.
ACHTUNG: Man berücksichtige die Einstiegspunkte, die
von den Funktionen vorgegeben werden. – So ist
in diesem Beispiel Kd-konto als Tabellenspalte
ein Schlüssel- bzw. Index-Kandidat
Bei einer 1:n - Beziehung ist eine Zusammenlegung von Satztypen dann sinvoll,
wenn der n-Satztyp ISOLIERT im Informationsmodell steht.
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 12 von 29
DB2 –Physisches Design
Unter isolierten Satztypen versteht man Entitätstypen, die zwar von einer "relationship-type" erreicht werden, die aber in keiner Beziehung zu anderen "entity types"
stehen. Eine Zusammenlegung ist insbesondere dann empfehlenswert, wenn der
Wunsch nach Detaildaten im abhängigen Objekt durch die Verfügbarkeit beispielsweise der aktuellsten ("letzten") Daten zum Objekt ersetzt werden kann.
Im folgenden Beispiel wird das Vorgehen dadurch erleichtert, dass die Anzahl
abhängiger "entities" pro übergeordnetem "entity" begrenzt ist.
KUNDE
KUNDE
(kd-nr)
(kd-nr)
Hat zur Folge (3)
KONTO
KONTO
(kto-nr)
(kto-nr)
Gehört zu
RECHNUNG
• Re-nr
Re-datum
Re-betrag
......
M-stufe (!)
M-datum (!)
M-kosten
Bild-04: Zusammenlegung von Satztypen beim Informationsdesign
ACHTUNG
Beachten Sie, inwieweit Informationen verloren gehen, wenn
Sie "entity-types" aus Performance-Gründen zusammenlegen.
KEINEZUSAMMENLEGUNG bei unterschiedlicher Existenzberechtigung / -dauer!!!
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 13 von 29
DB2 –Physisches Design
2.3.1.3 Denormalisierung
Denormalisierung bedeutet nichts anderes, als Datenstrukturen, die sich in der 3.
Normalform befinden, wieder in eine der vorgeordneten Normalformen zurückzuführen.
Denormalisierte Strukturen helfen insbesondere bei relationalen DBMS JOINOperationen zu verhindern und so die Performance in bestimmten Bereichen, d.h.
beim LESEN ( SELECT ...), zu steigern.
Beispiel:
Relation:
KUNDE
normalisiert: KUNDE
ORT
{ KD_NR, KD-Name, KD-PLZ, KD-ORT, KD-Strasse ..}
{ KD-NR. KD-Name, KD-OKZ, KD-STRNR ....}
{ OKZ, O-PLZ, O-ORT, EINWOHNERZAHL...}
KUNDE_WOHNT_IN
{OKZ. O-STRNR. KD-STRNR. ETAGE. RAUM...}
STRASSE
{OKZ, O-STRNR. STR_BEZEICHNUNG ...}
Unter der Annahme, dass die Kundendaten in 90% aller Fälle auch die Adressdaten
des Kunden enthalten müssen, ist eine Denormalisierung wie folgt empfehlenswert:
KUNDE
{ KD-NR, KD-NAME,..., KD-PLZ, KD-ORT, KD-STR,
KD-STR-NR, KD-HAUS, KD-ETAGE,....}
ACHTUNG
Bei jeder Denormalisierung entstehen Redundanzen, die
GEPFLEGT werden müssen, um die DB-Inhalte
KONSISTENT zu halten
Als Bedingung und sinnvolle Voraussetzung für denormalisierte Strukturen
gilt:
1.
Die Denormalisierung spart in hohem Maße DBMS-Zugriffe
("Joins")
2.
Der Änderungsaufwand der Redundanzen ist überschaubar
3.
Die Änderungshäufigkeit der entstandenen redundanten Daten ist
möglichst gering !
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 14 von 29
DB2 –Physisches Design
2.3.1.4 Trennen von Satztypen
Die Trennung von Satztypen kann dann sinnvoll sein, wenn die Daten eines Entitätstyps von vollständig unterschiedlichen Organisationseinheiten genutzt und bearbeitet werden sollen, vorgangsorientierte Daten verarbeitet und zur Verfügung
gestellt werden sollen.
Beispiel:
BANKTRANSAKTIONEN
•
•
TRANS-FILIALE
TRANS-LFD-NR
TRANS-ART
……..
TRANS-STATUS-1
TRANS-STATUS-2
…………
( 10 Mio. Zeilen )
bezeichnet den Zustand im
Bearbeitungsverlauf:
B = gebucht ( 5 Mio )
G = Vorlage zur Genehmigung
( 1 Mio )
C = zur Überweisung freigegeben
( 4 Mio )
D = überwiesen ( 27 Mio inkl.
Historiendaten)
usw.
bearbeitet ? D
Ja/Nein
( 8 Mio / 2 Mio )
Bild-05: Trennen von Satztypen beim Informationsdesign
Im o.g. Beispiel besteht das Problem darin, dass Anforderungen wie "...alle genehmigungspflichtigen Transaktionen... " oder " ... alle zur Überweisung freigegebenen
Transaktionen ..." aber insbesondere " ... alle nicht bearbeiteten Transaktionen ..."
bei DB2 zu einem "tablespace-scan", d.h. zum sequentiellen Lesen von 10 Millionen Datensätzen führen würde, da eine Indizierung über Spalten mit zu wenigen
Ausprägungen keine DB2-adäquate Selektivität aufweisen könnte.
Möglicherweise liegt in einem solchen Fall ein Analyse-Fehler vor. Andernfalls
könnte folgende Empfehlung gelten, um die benötigten Teilmengen möglichst genau
einzuschränken:
Relation_1: GEBUCHTE_TRANSAKTIONEN { .... }
Relation_2: GENEHMIGUNGSPFLICHTIGE_TRANSAKTIONEN { ....
}
Relation_3: ……
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 15 von 29
DB2 –Physisches Design
ACHTUNG
Das Problem von Kombinationen verschiedener StatusInformationen kann durch diese Vorgehensweise
NICHT gelöst werden: REDESIGN ist angesagt
Sollen die gleichen Daten mehreren unterschiedlichen Organisationseinheiten (OE) zur
Verfügung gestellt werden, z.B.. in "Client-Server"- oder verteilten Umgebungen, so stehen hier seitens des DBMS die Möglichkeiten der
-
Replikation
-
Partitionierung
von Tabellen zur Verfügung.
Vorausgesetzt die technische Implementierung des DBMS bietet diese "features" in
der aktuellen Version an.
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 16 von 29
DB2 –Physisches Design
2.3.1.5 Schaffung zusätzlicher Tabellen
In vielen Fällen kann die Schaffung zusätzlicher Spalten/Tabellen aus Performance-Gründen nützlich sein. Dies ist der Fall bei
Ergebnis- und Summendaten
Unterstützung von Aggregatsfunktionen im
Rahmen von Auswertungen
Historischen Daten
Eingestreut
separat
Bild-06: Aufbau zusätzlicher Tabellen beim Informationsdesign
Problematisch kann dies bei referentieller Integrität sein !
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 17 von 29
DB2 –Physisches Design
2.3.1.6 Ergänzen zusätzlicher Felder im physischen Datenmodell
Zusätzliche Felder sind Felder, die Zugriffe beschleunigen können, Updates sicherer
machen, Joins verhindern helfen usw. aber aufgrund der konzeptionellen Datenmodellierung nicht erforderlich wären.
Beispiele:
Abgeleitete Daten
z.B.
Brutto-Betrag, Summenfelder
Kennzeichen, ob in abhängigen Tabellen weitere Informationen vorliegen
oder nicht; z.B.
Zweit-/Dritt-Adressen
Für zusätzliche Zugriffsmöglichkeiten
z.B. Matchcode, phonetisierte Schreibweise, Großschrift
Als Protokollierungs- und Steuerungsinformation
z.B. Datum letzte Änderung, Langfrist-Sperrkennzeichen
Für Zugriffsschutz
z.B. Code für Benutzerberechtigung
Gültigkeitsinformationen
Technische Informationen
z.B. Visualisierungsinformationen (CRT, 3270 ... )
Weiterverarbeitungsinformationen
z.B. für Datawarehousing, Reporting, Aggregationen ...
Spezielle Sicherheitskennzeichen
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 18 von 29
DB2 –Physisches Design
2.3.2
Grundsätzliche Regel bei DB2
Speicherplatzersparnis ist nicht so wichtig wie weniger Tabellen.
Zum Thema Komprimierung vorab folgende Information:
Der Einsatz von Komprimierungsfunktionen macht viele Speicherplatzüberlegungen
annähernd überflüssig. Die heute möglichen Kompressionsraten und die damit verbundenen Platzersparnisse erfordern nur marginal höhere Aufwände bei CPU- und
Laufzeiten der Prozesse:
mögliche Kompressionsrate bei kommerziellen Daten 40 - 70 %
Erhöhung der CPU-Zeit ca. 15 %*
Verringerung der Laufzeit serieller Prozesse ca. 10 %*
(stark schwankend in Einzelfällen!!)
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 19 von 29
DB2 –Physisches Design
2.4
Überführen der Beziehungen
Beziehungen werden über Schlüsselredundanzen realisiert: „primary key" D „foreign key".
Dabei ist es wichtig zu wissen, ob nur eine oder BEIDE Richtungen der Beziehungen benötigt werden.
Fall 1:
Kd_nr
KUNDE
KUNDE
(kd-nr)
(kd-nr)
KUNDE
Gehört zu
vergibt
Auf_nr
Kd_nr
AUFTRAG
AUFTRAG
(auf-nr)
(auf-nr)
AUFTRAG
Bild-07: Beispiel-1 - Übernehmen der Beziehungen beim Informationsdesign
Im ersten Fall könnte man einen aus der "auf-nr" und der "kd-nr" zusammengesetzten Identifikator verwenden. Im zweiten Fall kann man davon ausgehen, dass
eine „künstliche“ Auftragsnummer - laufende Nummer - definiert wurde, die mit
den Kundendaten direkt nichts zu tun hat. Die "kd-nr" kommt lediglich als „foreign
key“ als Abbildung der Beziehung zwischen KUNDE und AUFTRAG zum Einsatz.
Beziehungen werden über Schlüsselredundanzen realisiert:
„primary key“ D „foreign key“
Es ist dabei wichtig zu wissen, ob nur eine
oder BEIDE Richtungen der Beziehungen benötigt werden.
Eine vollständige Indizierung der PK/FK-Beziehungen ist bei DB2 dringend zu
empfehlen:
•Von Tabelle zu Tabelle
•Bei „self referencing tables“
•Pro Tabelle mindestens:
1 Primary Key (PK)
1 „clustering key (CK)“
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 20 von 29
DB2 –Physisches Design
Fall 2:
Kd_nr
KUNDE
KUNDE
(kd-nr)
(kd-nr)
KUNDE
Gehört zu
vergibt
Auf_nr
AUFTRAG
AUFTRAG
(auf-nr)
(auf-nr)
AUFTRAG
Bild-08: Beispiel-2 - Übernehmen der Beziehungen beim Informationsdesign
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 21 von 29
DB2 –Physisches Design
2.5
Einrichten der Zugriffspfade am Beispiel DB2
DB2 kennt Methoden für
•
„Index based" Zugriffe und
•
„tablespace scan"
Ziel eines effizienten physischen DB-Design wird es sein, indexbasierte Zugriffe
zu ermöglichen.
Ob DB2 dann aber Indizes nutzt, hängt nicht allein vom DB-Design, sondern unter
anderem und vor allem auch von der Formulierung der SQL-„Query" ab. Allerdings
muss man pro „Tabelle" mindestens einen Index zur Verfügung stellen:
•
einen „primary key index"
•
evtl. „cluster index"
Ausgangspunkt für die Festlegung sind dabei die Einstiegspunkte der konzeptionellen Datenstruktur und die Nutzung der Daten in den Anwendungen!
Man beachte:
Bei konkurrierenden Einstiegspunkten sollte der Index gewählt werden, der möglichst „hochwertig" ist:
1. „cluster index"
2. „primary key" oder „unique index"
3. „normaler" Index
Wird der Datenbestand sehr häufig erweitert (INSERT ) oder verringert (DELETE),
so sollten im Sinne der Performance möglicht SELEKTIVE . aber WENIGE Indizes
auf den Tabellen liegen(siehe auch Kapitel: "Indizierung" Pkt. 1.3 75.4 ff.).
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 22 von 29
DB2 –Physisches Design
2.6
Festlegen der physikalischen DB-Struktur
1. Berechnen der TS-Größe
a)
b)
c)
d)
e)
primary space" / „secondary space"
Bestimmen zusammengehöriger Tabellen für EINEN TS
Bestimmen des TS-Typs (STS, PTS, normaler TS)
Festlegen des Füllgrades (PCTFREE, FREEPAGE)
COMPRESSYES/NO
2. Berechnen der IX-Parameter
3. Bestimmen der LOCKING und ISOLATION - Parameter
a)
b)
c)
d)
LOCKSIZE
ISOLATION Level RR / CS
Zuordnung zu STOGROUPS
DEGREE ANY(?)
4. Festlegen der Datentypen in den Tabellen
a)
b)
c)
d)
e)
wenige NULL-Felder eher NOT NULL WITH DEFAULT
keine Tabellen-PROC ( VALIDPROC, EDITPROC, FIELDPROC...)
keine LONGVARCHAR / VARCHAR - Felder
VARCHAR und NULL-Felder ans Ende der Tabelle
Definition der RI-Bedingungen (falls nötig)
5. Festlegen der „views"
6. Definition der DB2-Datenbanken
a)
b)
c)
nach organisatorisch / administrativen Gesichtspunkten
nach Datenverfügbarkeitsanforderungen
(mit Hilfe der Datenbankadministration !)
Test mit Hilfe „manuell" eingetragener RUNSTATS-Werte
7. Definition von „remote“-Zugriffswegen
T e s t d a te n b a n k e n s i n d s e l t e n g l e i c h d e r
PROD-Datenbank !!
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 23 von 29
DB2 –Physisches Design
2.7
Festlegen der DB2 Datentypen
Die Festlegung des Datentyps führt zu 2 wichtigen Bestimmungen bei DB2Tabellen:
1.
Inhalt (Wertebereich) der Spalte, z.B. kann INTEGER nur ganzzahlige
Werte aufnehmen von -2.147.483.648 bis + 2.147.483.647
2.
Länge des Datenfeldes, z.B. INTEGER ist 4 Byte lang.
Datentyp
Bedeutung
CHAR(n) / CHARACTER (n)
alphanumerisches Feld fester Länge
VARCHAR (n)
alphanumerisches Feld variabler Länge
LONG VARCHAR
"string" variabler Länge
32.767 Byte
BIT
repräsentiert nicht ausschließlich
CHAR und wird nicht auf druckbare
Zeichen konvertiert
siehe CHAR
SBCS
"single-byte-characters": Inhalte werden nach CCSID konvertiert D
CCSID: "coded chracter set identifier"
siehe CHAR
MIXED
repräsentiert SBCS oder DBCSZeichen: "double-byte-character"
siehe CHAR
DEC(n,m) / DECIMAL(n,m)
NUMERIC(n,m)
numerisch gepackt
Achtung: Leerstelle nach Komm
1 - 10**31 bis
10**31 - 1
<= 8
INT / INTEGER
binäres Vollwort
-2.147.483.648 bis
+2.147.483.647
4
SMALLINT
binäres Halbwort
-32.768 bis +32.767
2
REAL / FLOAT (n)
Gleitkommakonstante mit einf.
Genauigkeit
FP in 32 bits
75
75
-7.2×10 bis 7.2×10
4
FLOAT / FLOAT (n)
DOUBLE PRECI SION
Gleitkommakonstante mit dopp. Genauigkeit
FP in 64 bits
79
79
-7.2×10 bis 7.2×10
8
TIME
interne Zeitdarstellung
( ttmmss )
Zeitwert: 00.00.00 bis
24.00.00
3
DATE
interne Datumsdarstellung
( yyyymmdd )
Datum: 0001-01-01 bis
9999-12-31
4
TIMESTAMP
Datum + Zeit + Systemzeit
10
ROWID
eindeutiger Identfier einer TabZeile(nur BIT)
17(intern)
CLOB
variabel Large CHAR Objekt(SBCS
oder „mixed“)
2 GB -1
BLOB
variabel Large binary Objekt(for BIT
DATA)
2 GB -1
DBLOB
variabel double-Byte CHAR O
bjekt(DBCS)
1 GB -1
© S.K. Consulting Services
++49 8106 994390
max. Wert
Länge
0 < n < 255
n
4046 B für 4-KB pages
8128 B für 8-KB pages
16320 B für 16-KB pages
32704 B für 32-KB pages
www.sk-consulting.de
n
<= 32KB
Seite 24 von 29
DB2 –Physisches Design
3
3.1
Basis-"Regeln" zum physischen Design von DB2 Datenbanken
"Keep Like Things Together"
Fassen Sie alle Tabellen, die zur selben Applikation gehören in einer Datenbank
zusammen und geben Sie jedem Anwendungsprozess, der eigene Tabellen benötigt eine eigene "private" Datenbank. Im idealen Umfeld nutzt jeder Anwendungsprozess so wenige Datenbanken und Tabellen wie nur irgenwie möglich.
3.2
"Keep Unlike Things Apart"
Geben Sie den Benutzern unterschiedliche Authorisierungen für ihre Arbeit in unterschiedlichen Datenbanken; z.B. eine UID für die Arbeit in einer gemeinsamen und
eine UID für die Arbeit mit der "privaten" Datenbank.
3.3
"Cluster Your Data"
Versuchen Sie Daten, die zusammen zugegriffen werden sollen, mindestens in einer Tabelle, besser noch in einer einzigen "Page" zu halten. Eine Tabelle, die bei
Beginn der Verarbeitung leer ist und nach und nach gefüllt wird, ist nie in einem effizientem "cluster"-Zustand. Alle Einfügungen passieren am Ende der Tabelle und
verursachen Konflikte speziell während der Zeit in der die Tabelle annähernd leer ist
und der Index nur eine oder zwei Ebenen besitzt. Typ 2 Indizes können diesem
Manko Abhilfe schaffen.
3.4
"Consider Type 2 Indexes"
Bei Typ 2 Indizes werden nur die Daten gesperrt, nicht aber die Index-Pages. Dies
verursacht weniger Engpässe, als die Sperrung von Indexpages, da in den IXPages in der Regel mehr Daten gespeichert sind, als in einer Datenpage. Auch IXPage-Splits werden vom Typ 2 Index effizienter gehandhabt als vom "alten" Typ 1
Index.
Achten Sie auf die Einfügung von NULL-Werten, da DB2 diese IMMER als "highvalue" interpretiert. Besitzt nun ein zusammengesetzter Index einen NULL-Wert in
der ersten Spalte, so können "non-NULL" Indexwerte von diesem speziellen "high
value"-Split trotz Typ 2 Index nicht profitieren.
Beispiel:
SMITH ROBERT J
=> INSERT
Anschließend
SMITH ROBERT (null)
SMITH ROBERT Z
dann wird der letzte Satz nicht als "high key" verarbeitet (!)
3.5
"Use Locksize ANY"
LOCKSIZE ANY ist "default" beim CREATE TABLESPACE. Dies ermöglicht DB2
die Sperrebene selbst zu wählen. Dabei benutzt DB2 in der Regel "page" als SperrLevel und LOCKMAX aus dem System.
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 25 von 29
DB2 –Physisches Design
Bevor man LOCKSIZE auf TABLE, oder TABLESPACE setzt, sollte man die Anforderungen der Anwendungen bezüglich der parallelen Verarbeitungsalgorithmen
prüfen. Andererseits sollte man auch die Angabe von LOCKSIZE ROW dahingehend überprüfen, ob dadurch nicht ein übermäßiger Aufwand für Locking entsteht.
3.6
"Examine Small Tables"
Für kleine Tabellen mit hoher Parallelverarbeitungsqualität überprüfe man die Anzahl Pages auf dem TS und im Index. Ist der Indexeintrag kurz oder gibt es viele
doppelte Werte, dann kann man davon ausgehen, dass nur EINE "root-page" und
wenige "leaf-pages" existieren. In diesem Fall sollte man die Daten streuen, um
möglichst hohe parallel Verarbeitungsquoten zu erreichen. Oder man entscheidet
sich für Typ 2 Indizes und "row locks".
3.7
"Partition the Data"
Online Queries machen erfahrungsgemäß wenige Änderungen, sind kurz, laufen
aber häufig ab. Batch-Programme sind das genaue Gegenteil davon: Sie dauern
länger, ändern viele "rows", laufen aber selten. Die beiden Verarbeitungsarten sind
nicht besonders gut geeignet, parallel zu arbeiten. Es ist also wichtig, Online und
Batch voneinander zu trennen, falls dies möglich ist. Dazu geeignet ist eine entsprechende Partitionierung der Daten.
3.8
"Fewer Rows of Data per Page"
Über den Parameter MAXROWS kann man die maximale Anzahl von Datensätzen
pro Page festlegen, nutzt man z. B. MAXROWS 1, so besetzt jede "row" genau eine
Page. Dies kann eine Lösung sein, wenn "row locking" vermieden werden soll, wie
in "data sharing environments", da dort ein "row locking" übergroßen "overhead"
erzeugen würde. MAXROWS wirkt nicht auf Indizes.
3.9
"Access Data in Consistent Order"
Greifen unterschiedliche Applikationen auf dieselben Daten zu, sollte man versuchen diese dazu zu bringen dieselben Daten in derselben Sequenz zu lesen und zu
verarbeiten; z. B. 1, 2, 3, 5. In diesem Fall kann es sein, dass eine Applikation Zeit
verliert, aber es wird nie zu "deadlocks" kommen. Aus diesem Grund sollte man wo
möglich auch Tabellen in derselben Reihenfolge verarbeiten lassen.
3.10 "Commit As Soon As Practical"
Um unnötige Lock-Blockaden zu vermeiden, sollte nach Erreichen des "point-ofconsistency" so früh wie möglich ein COMMIT abgesetzt werden, auch in "readonly" - Programmen. Um fehlerhafte SQL-Statements - wie z.B. PREPARE, daran
zu hindern, Locks weiter zu halten, sollte man im Fehlerfall unbedingt einen
ROLLBACK absetzen.
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 26 von 29
DB2 –Physisches Design
3.11 "Retry Application After Deadlock/Timeout"
Man sollte eine Logik in die Programme einbauen, die die Funktion/Transaktion
nach einem "deadlock" oder einem "timeout" erneut versucht. Dies macht Eingriffe
seitens des Betriebspersonals bzw. des „Operatings“ überflüssig.
3.12 "Close Cursors"
CLOSE CURSOR sollte genutzt werden, um belegte Ressourcen freizugeben - insbesondere nach der Nutzung eines Cursors mit der Klausel WITH HOLD und der
Verwendung von „scrollable cursors“ (seit DB2 V7).
3.13 "Bind Plans With ACQUIRE(USE)"
3.14 "Bind With ISOLATION(CS) And CURRENTDATA(NO)"
4
DB2-Objekte – Strukturierungsempfehlungen
Der Begriff “Objekte” umfaßt bei DB2 alles, was mit der Sprache
SQL definiert und manipuliert
werden kann (Im Rahmen der DDL und der DML).
Bei DB2 fallen folgende Begriffe unter die Bedeutung DB2-Objekte:
Datenbanken (database)
Tabellen (table)
Benutzersichten (view)
Synonyme (synonym)
Indizes (index)
Tabellenspeicher (tablespace / partition )
Speicherplatz-Gruppen (storage group / file / extent)
Constraints / Triggers / „stored procedures“
User
Plans / Packages ....
Schemas usw.
Die Objekte, die für das physische Design von vorrangiger Bedeutung sind werden
in den folgenden Punkten detailliert besprochen.
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 27 von 29
DB2 –Physisches Design
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 28 von 29
DB2 –Physisches Design
ENDE
© S.K. Consulting Services
++49 8106 994390
www.sk-consulting.de
Seite 29 von 29