DB2 LUW - eine Übersicht für Einsteiger ()

(*)
IBM DB2 V9
for LUW
DB2 – „application development“ (Teil II)
(*) ist eingetragenes Warenzeichen der IBM International Business Machines Inc.
Aug2012
1
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
• Unit of Work, Unit of Recovery
 COMMIT, ROLLBACK
 SAVEPOINTs
Aug2012
2
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• Erläuterung der EXPLAIN Funktion





Einführung in die DB2 Optimizer Arbeitsweise
Zugriffspläne lesen, verstehen und zur SQL-Optimierung analysieren
Explain durchführen
SQL Analyse-Tools von IBM (Visual EXPLAIN, db2advis etc.)
Analyse Tools von Drittanbietern
• DB2 Utilities und Statistiken
• DB2 Optimizer Richtlinien
•
Aug2012
Monitoring und Fehleranalyse
3
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
• Unit of Work, Unit of Recovery
 COMMIT, ROLLBACK
 SAVEPOINTs
Aug2012
4
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung von Objekten bei DB2 LUW
„Q replication“
“Q replication” ist eine Replikationslösung, die WebSphere MQ Message Queues nutzt, um Transaktionen
zwischen “source” und “target databases” zu übertragen. “Q replication” wird in 3 Formen wirksam:
• Unidirectional
• Bidirectional
• „peer-to-peer“
„DB2 Database Partitioning Feature“
DB2 LUW ist in höchstem skalierbar und in der Lage, Hardware Plattformen vom “1-Prozessor Laptop”,
“single” und “clustered Symmetric Multiprocessing (SMP) “ Server bis hin zu parallelen systemen mit
hunderten von Knoten und mehreren Prozessoren pro Knoten zu unterstützen. Diese extrem
anpassungsfähige Funktionalität und die Granularität beim gesteuerten Wachstum ist eine herausragende
Fähigkeit von DB2 LUW. Ein “feature” hierfür ist das Database Partitioning Feature (DPF).
„Table partitioning“
“table partitioning” wurde erst in DB2 9 eingeführt. Es erlaubt den “database administrators” (DBAs)
Tabellen in Partitions aufzuteilen und diese in separaten oder mehreren Tablespaces zu speichern. Nebenbei
liefert dieses “feature” eine einfachere Verwaltung, verbesserte Performance und ebenfalls zusätzliche
Skalierbarkeit in der DB2 Umgebung bei großen DBs.
Aug2012
5
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
„Q replication“
“Q replication” ist eine Replikationslösung mit WebSphere MQ “messages queues” , um so Transaktionen
zwischen Quell- und Ziel-Datenbanken zu übertragen.
“Q replication” gibt es in drei Ausprägungen:
Unidirectional
Bei einer “unidirectional Q replication”, werden Änderungen am “Quell-Knoten” aufgezeichnet und zum
Zielknoten repliziert. “unidirectional Q replication” kann von einer “source” auf ein oder mehrere “targets”
erfolgen. Bei “unidirectional Q replication”, wird das “target” typischerweise “read-only” genutzt.
Bidirectional
Bei einer “bidirectional Q replication”, replizieren sich zwei Tabellen auf zwei Servern gegenseitig.
Änderungen auf einer der Tabellen werden auf die entsprechende korrespondiesende Tabelle repliziert. Man
kann nicht “subset of rolls” replizieren und jede Tabelle muss dieselbe Anzahl Spalten und dieselben Datentypen für diese Spalten aufweisen - Schemas und Tabellennamen können jedoch unterschiedlich sein.
Peer-to-peer
Bei einer “peer-to-peer Q replication” werden Tabellen über zwei oder mehr Server hinweg repliziert.
Wie bei der “bidirectional Q replication” müssen die replizierten Tabellen dieselbe Struktur aufweisen Schemas und Tabellennamen können jedoch unterschiedlich sein.
“Q replication” kann es in unterschiedlichsten Konfigurationen geben: Replikation zwischen DBs auf
demselben Server oder “remote servers”, als “one-to-many relationship” oder “many-to-one relationship”.
Aug2012
6
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
„Q replication“ - Beispiel
„Unidirectional setup“
Hier ein schrittweise dargestelltes Beispiel eines “unidirectional Q replication setup” mit zwei “remote
servers”.
Folgende Schritte sind für eine
“unidirection Q replication”
Umgebung erforderlich:
1. Setup of the database on both the
source and target database servers.
2. Setup of WebSphere MQ objects on
the source server and the target server.
3. Start the listener and the channel on
the source and the target
4. Optional: Test the queues.
5. Create the Q Capture Control tables.
6. Create the Q Apply Control tables.
7. Create a Q subscription.
8. Configure target to source server
connectivity.
9. Start Q Capture.
10. Start Q Apply.
Aug2012
7
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht über DPF
Ein Hardware Trend, den man beobachten kann, ist es, “enterprise” Systeme aufzubauen, indem man eine
Gruppe kostengünstiger kleiner Server zu Verfügung stellt. Die Kunden möchten profitieren, da die Datenvolumina wachsen und es für sie sehr schwierig ist, abzuschätzen, wie groß ihre Datenbank in einigen
Jahren sein wird. DieDatenbankarchitektur muss flexibel genug sein, auch Vorteile aus dem Hinzufügen
von “processing power” eines weiteren Serverknotens zum existenten “cluster”, zu ziehen.
Das „Database Partitioning Feature“ (DPF) – verfügbar in der DB2 for LUW Enterprise Server Edition
(ESE) erweitert die Funktionalität des relationalen “ database managers” für den Einsatz in einer parallelen,
“multi-node” Umgebung. Mit DPF wird die DB skalierbar,
indem man neue Server hinzudefinieren und die Datenbanken über diese hinweg verteilen kann; d.h. mehr CPUs,
mehr Speicher und mehr “disks” von jedem zusätzlichen
Server für die Datenbank. DB2 ESE mit DPF ist ideal für
DWHs, “data mining” und Last, die vom “online analytical
processing” (OLAP) kommt. Es kann aber auch in einem
“online transaction processing” (OLTP) Umfeld gut
funktionieren.
Hier im Bild: DB2 partitioniert” auf “networked
machines”. Eine Applikation kann sich an jede “database
partition“ anmelden.
Aug2012
8
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht über DPF
Ist eine “database” partitioniert, so wird die DB in mehrere unabhängige Teile aufgeteilt – jeder Bestandteil
hat seine eigenen Daten in “table space containers”, eigene “configuration files”, Indexe und
“transaction logs”. Auf einen physischen Server können mehrere Partitions zugewiesen sein, die sich dann
die Ressourcen dieses Servers teilen. Eine “database partition” wird auch manchmal als “Knoten”(“node”)
bezeichnet – ähnlich, wie ein Server in einem “cluster” auch “node” genannt wird. So brauchen “nonreplicated partitioned databases” alle “nodes” verfügbar, um aktiv werden zu können. Das folgende Bild zeigt
DB2 “partitioning” auf SMP Maschinen.
DB2 implementiert DPF über eine “shared nothing” Architektur. Der Vorteil dieser Architektur ist, dass
man keine spezielle Hardware braucht
und jeder Server lediglich einen Teil
der Arbeit zu leisten hat. Als Ergebnis
gewinnt man Performance aus den
“not sharing resources” des Netzes.
Die “Shared Nothing Architecture”
eliminiert den “overhead” des “distributed lock Management”.
Die DB-Größe ist skalierbar über die
Plattenanzahl und die Anzahl der
Prozessoren.
Aug2012
9
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht über DPF
Die DPFArchitektur ist höchst skalierbar und benötigt minimale Kommunikation zwischen den Knoten. Die
DPF “shared nothing” Architektur setzt DB2 in die Lage beste Skalierbarkeit und /oder Kosten/Performance
anzubieten – oder beides zusammen.
Eine “single-partition database” ist per definitione
eine DB, die nur aus EINER “database partition”
besteht. Alle Daten der DB werden in dieser Partition
abgelegt.
Eine “multi-partitioned database” ist eine DB, die
nur aus ZWEI oder MEHR “database partitions”
besteht. Da die Daten über die “database partitions”
verteilt sind, kann man die Leistung von “multiple
processors” auf mehreren “physical “nodes” nutzen,
um die Informationsanforderungen zu erfüllen.
DB2 unterstützt zudem ein “partitioned Storage”
Modell, bei dem der #1 IX (partitioning key) genutzt
wird , eine Tabelle über ein “set of database partitions” zu verteilen; d,h, die Daten werden physisch
auf mehr als einer “database partition” gespeichert
und können dennoch so zugegriffen werden, als
befänden sie sich am gleichen Platz (in einer Tabelle?).
Aug2012
10
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht „Table Partitioning“
Das “Table partitioning feature” ist ein Tabellenorganisationsschema in dem die Tabellendaten in Partitions
gemäß den Werten in einer oder mehreren “table partition key columns” der Tabelle aufgeteilt werden. Jede
Partition kann separat auf einem “storage” Objekt – “data partition” oder “range” genannt – abgespeichert
werden.
Diese “storage objects” können sich
• Auf unterschiedlichen TS
• In einem einzelnen TS oder
• Einer Kombination aus beidem
befinden.
Die Entscheidung, wo diese “storage objects” platziert sein sollen, fällt zu Zeitpunkt CREATE/ALTER
TABLE über die Klausel PARTITION BY .
DB2 9 unterstützt “data partitions” / “data ranges” auf der Basis verschiedener Attribute. Eine Möglichkeit
wäre die Partitionierung nach Datumsdaten. Hier kann man Daten nach Jahr / Monat / ??? in Partitions
gruppieren. Man kann auch numerische Attribute, z.B ID’s etc. , partitionieren. Ein Beispiel wäre auch,
Mandanten, z.B. Bankfilialen , nach Partitions zu trennen.
Obwohl man Partitionen über Namen und/oder Nummern referenzieren kann, können sie für Applikationenj
vollständig transparent sein: Sie können über die Angabe von Spalten- und Tabellenname ihre Daten
erreichen. – Sie müssen nicht wissen, in welcher Partition die Daten gespeichert sind.
Aug2012
11
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht „Table Partitioning“
“partitioned tables” können mehr Daten enthalten als eine einfache Tabelle. Sie können aus bis zu 32767
“data partitions” bestehen.
“Data partitions” können hinzugefügt, angehängt bzw. abgehängt werden .
Mit ALTER TABLE ... ATTACH / DETACH können „roll in“/“roll-out“ Arbeiten einfach erledigt werden.
Der Einsatz von Partitions kann auch zur Verbesserung von Query- Performance beitragen. So werden
nur relevante Daten durchsucht, die sich evtl. In EINER Partition befinden.
Die SQL “engine” kann während des “data read” Partitionen ausblenden, wenn sie keine Daten enthalten,
die zum Resultat einer Query beitragen können.
Die Spalten, die in der Klausel PARTITION BY die Partitionierung bestimmen heissen “table partitioning
key columns”. Man kann die Klauseln DISTRIBUTE BY und PARTITION BY kombinieren und die
Datenpartitionen so über mehrere “table spaces” verteilen.
Die Db2 Organisationsschemas umfassen
•
DISTRIBUTE BY HASH
•
PARTITION BY RANGE
•
ORGANIZE BY DIMENSIONS
•
“Partitioned hierarchical”, “temporary tables”, “range-clustered tables” und “partitioned views” sind
derzeit nicht unterstützt. Ebenso, wie die Datentypen Long Varchar, Long Vargraphic, BLOB,
CLOB DBCLOB, XML und DATALINK nicht zur Partitionierung zugelassen sind.
Aug2012
12
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht „Table Partitioning“
Vorteile von „table partitioning"
Schnelles “roll-in” und “roll-out” , einfachere Administration großer Tabellen, flexible IX Platzierung
und erhöhte Query Performance können Vorteile von “table partitioning” sein. Und:
•
Bei großen DWHs in denen man häufig daten hinzufügt oder löscht – auf der Basis von bekannten
Kriterien ; z.B.: Datumsdaten. In solchen Situationen kann ein System vom einfacheren “roll-in” /
“roll-out” der Tabellendaten profitieren.
•
Große DWH mit großen Tabellen und
Query-Kriterien, die einen Split der
Daten über mehrere “devices” zulassen.
•
Man braucht eine “Hierarchical
Storage Management” (HSM) Lösung,
die besonders effektiv sein soll.
•
Man will Indexe auf unterschiedliche
“devices” legen, abhängig von der
Geschwindigkeit der Platte und der
Nutzungsfrequenz der jeweiligen
Indexe.
Index distribution on a partitioned table
Aug2012
13
DB2 for LUW – verteilte Umgebungen / Objekte
Möglichkeiten der Verteilung bei DB2 LUW
Übersicht „Table Partitioning“
Hier ein Index auf einer “partitioned table”, der
zusätzlich noch über mehrere “database partitions“
verteilt ist.
CREATE TABLE lineitem (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate DATE,
l_year_month INT GENERATED ALWAYS AS
(YEAR(l_shipdate)*100 +
MONTH(l_shipdate)))
PARTITION BY RANGE (l_shipdate)
(STARTING
('1/1/1992')
ENDING
('12/31/1992')
EVERY 1 MONTH);
Aug2012
14
DB2 for LUW – verteilte Umgebungen / Objekte
Funktionen der DBs und DRDA Protokolle (“Distributed Relational Database Architecture „):
Die Konversation beim DRDA Protokoll:
Aug2012
15
15
DB2 for LUW – verteilte Umgebungen / Objekte
“High Availability Options” bei DB2 LUW
DB2 bietet verschiedene Möglichkeiten, die hohe Verfügbarkeit von DB Servern sicherzustellen. Im großen
und ganzen sind das:
• High Availability and Disaster Recovery (HADR)
• Automatic Client Reroute (ACR)
• SQL replication
• Q replication
Einige DB2 Funktionen erhöhen ebenfalls die Verfügbarkeit von Operationen, Applikationen, Daten,
Transaktionen etc. wie z.B: “online backup” und die sogen. “in-place reorganization”.
Zusätzlich dazu gibt es eine Reihe von Software und Hardware Angeboten seitens der IBM und anderer
Provider, die helfen, die Verfügbarkeit einer DB2 Umgebung höchstmöglich zu halten.
Hardware-basierte Lösungen
• RAID
• “Remote storage mirroring“
• „clustering“ Lösungen
• Replikationslösungen (SQL, Q Replication …)
• Hybrid Nutzung von “shared disk cluster” und HADR
HADR – High Availability Disaster Recovery
Aug2012
16
16
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
Aug2012
17
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
PL/SQL (Procedural Language/Structured Query Language) Statements können kompiliert und über
jegliche DB2® Interfaces ausgeführt werden.
Dies reduziert die Komplexität beim Anpassen von bestehenden PL/SQL Lösunegn(Oracle), um sie so am
DB2 Data Server zum Laufen zu bringen (98% Deckungsgrad).
Die unterstützten Schnittstellen umfassen:
•
•
•
DB2 “command line processor “(CLP)
DB2 CLPPlus
IBM® Data Studio
PL/SQL Statements können allerdings nicht per “default” von diesen Schnittstellen bedient werden. Es
muss der “PL/SQL statement execution support” auf dem DB2 Server eingeschaltet sein.
PL/SQL features
PL/SQL Statements und Skripte können unter DB2 kompiliert und zur Ausführung gebracht werden:
Anonyme Blöcke; zum Beispiel






DECLARE…BEGIN…END
CREATE OR REPLACE FUNCTION Statement
CREATE OR REPLACE PACKAGE / PACKAGE BODY Statement
CREATE OR REPLACE PROCEDURE Statement
CREATE OR REPLACE TRIGGER Statement
DROP PACKAGE / PACKAGE BODY Statement
PL/SQL Prozeduren und “functions” können von PL/SQL Statements oder von DB2 SQL PL Statements aus
gestartet werden - via CALL Statement
Aug2012
18
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
Creating PL/SQL procedures and functions from a CLP script
PL/SQL Prozeduren und “functions” können aus einem DB2 command line processor (CLP) Skript aus
erzeugt werden.
Vorgehensweise
(1) Formulieren einer PL/SQL “procedure” / “function” Definition innerhalb einer CLP Skript - Datei.
Terminieren jedes Statements mit “new line” und einem “forward slash” Zeichen (/). Andere Statement Terminationszeichen
werden ebenfalls unterstützt.
(2) Speichern der “file” – hier “script.db2”.
(3) Ausführen des Skripts aus CLP. Wurde ein “forward slash” – Zeichen oder ein “Semikolon” als Terminator für die
Statements eingesetzt, setzt man folgendes Kommando ab:
db2 -td/ -vf script.db2
Wurde ein anderes Zeichen als Terminator für die Statements eingesetzt – beispielsweise @ - so muss man über das Kommando
dieses Zeichen bekanntgeben:
db2 -td@ -vf script.db2
Restriktionen beim PL/SQL support
Es sit wichtig die Restriktionen der Kompilierung von PL/SQL zu kennen bevor man dazu übergeht, PL/SQL
Kompilationsergebnisse auf Fehler zu untersuchen oder “runtime” Probleme zu analysieren:
•
•
•
•
PL/SQL Funktionen und Triggers können nicht auf “partitioned database” Umgebungen erstellt werden.
Der Datentyp NCLOB wird in PL/SQL Statements orde in PL/SQL Kontexts nicht unterstützt, wenn die DB nicht als “Unicode
database” definiert ist. In “Unicode databases” wird NCLOB auf DB2® DBCLOB Datentypen “gemapped”
Der Datentyp XMLTYPE ist nicht unterstützt.
TYPE Deklarationen sind nicht erlaubt in Funktionen, TSP’s , Triggers oder in “anonymous blocks”.
Aug2012
19
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
Transparenz(Obfuscation)
“Obfuscation” kodiert dne “body” eines DDL Statements bei DB-Objekten wie Routinen, Triggers, Views,
und PL/SQL Packages. Dies soll helfen, den Code als geistiges Eigentum zu schützen, da kein User den
Code lesen kann, DB2 Database for Linux, UNIX und Windows kann damit aber arbeiten.
“Blocks “(PL/SQL)
PL/SQL Block Strukturen können in PL/SQL Prozeduren, Funktionen bzw. Trigger Definition enthalten sein
bzw. als “anonymous block” Statement ausgeführt werden.
Prozeduren (PL/SQL)
Der DB2 Server unterstützt die Kompilierung und Ausführung von PL/SQL Prozeduren. PL/SQL
“procedures” siege DB2 STP’s.
“Functions” (PL/SQL)
Der DB2 Server unterstützt die Kompilierung und Ausführung von PL/SQL Funktionen. PL/SQL
“functions” siehe DB2 “functions”.
“Collections” (PL/SQL)
Die Verwendung von PL/SQL “collections” wird ebenfalls vom DB2 Server unterstützt. Eine PL/SQL
“collection” ist eine Menge geordneter datenelemente mit demselben Datentyp. Individuelle Datenelemente
in diesem “set” können über “subscript notation” in Klammern angesprochen werden.
“Variables” (PL/SQL)
Variable müssen deklariert werden bevor sie verwendet werden können.
Aug2012
20
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
PL/SQL support
“Basic statements” (PL/SQL)
Die Statements in Programmen einer PL/SQL Applikation include: assignment, DELETE, EXECUTE
IMMEDIATE, INSERT, NULL, SELECT INTO, and UPDATE.
“Control statements” (PL/SQL)
“Control statements” sind die Programm-Statements, die PL/SQL zu einem vollwertigen prozeduralen
Ersatz für SQL machen können.
“Cursors” (PL/SQL)
Ein cursor ist einen “control structure” mit Namen, die von einem Anwendungsprogramm genutzt werden
kann, auf Daten-”rows” in einer Resultatsmenge zu verweisen und diese zu lesen. Anstatt eine Query immer
wieder auszuführen, kann man Ccursor nutzen, um das Query-Resultat satzweise zu bearbeiten.
Triggers (PL/SQL)
Trigger – siehe DB2 Trigger
Packages (PL/SQL)
PL/SQL Package Definitionen werden von DB2 ebenfalls unterstützt. Ein PL/SQL Package ist eine
“collection” von “functions”, “procedures”, “variables”, “cursors”, “user-defined types” und Sätzen, die
über einen gemeinsamen “qualifier” genutzt werden können – dem “package”-Namen
Aug2012
21
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
PL/SQL “features”
Die folgenden PL/SQL Statements können in DB2 zur Ausführung kommen:
•
•
•
•
•
•
•
•
“Anonymous blocks”; z.B. DECLARE…BEGIN…END
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE PACKAGE
CREATE OR REPLACE PACKAGE BODY
CREATE OR REPLACE PROCEDURE
CREATE OR REPLACE TRIGGER
DROP PACKAGE
DROP PACKAGE BODY
PL/SQL STP’s und Funktionen können aus anderen PL/SQL Statements oder aus DB2 SQL PL Statements aufgerufen
werden. Man kann auch eine PL/SQL “procedure” aus SQL PL über ein CALL Statement aufrufen.
Die folgenden Basis – Programmelement können in PL/SQL und SQL PL verwendet werden:
• Associative arrays
• Record types
• VARRAY types
Variable Deklarationen:
• %ROWTYPE
• %TYPE
“Basic statements”, Klauseln und Statement Attribute:
“Assignment” Statement
NULL Statement
RETURNING INTO Klausel
Statement Attribute inkl. SQL%FOUND, SQL%NOTFOUND, und SQL%ROWCOUNT
Aug2012
22
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 und PL/SQL
“Control statements” und Strukturen
• CASE
• “Exception handling”
• EXIT
• FOR
• GOTO
• IF
• LOOP
• WHILE
“Static cursors”:
• CLOSE
• Cursor FOR loop Statement
• FETCH (inkl. FETCH INTO eine %ROWTYPE Variable)
• OPEN
• “Parameterized cursors”
• “Cursor” Attribute
REF CURSOR Unterstützung:
• Variable und Parameters des Typs REF CURSOR
• REF CURSORs
• OPEN FOR
• Zurückgeben von REF CURSORs an JDBC Applikationen
“Error handling support”:
• RAISE_APPLICATION_ERROR Prozedur
• RAISE statement
• SQLCODE Funktion
• SQLERRM Funktion
Aug2012
23
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
• Unit of Work, Unit of Recovery
 COMMIT, ROLLBACK
 SAVEPOINTs
Aug2012
24
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
SQL ???? ... Alles eine Frage der Formulierung und Mengendefinition ...
… to the airport
please…
Aug2012
25
DB2 for LUW – Cursor
DB2 Anwendungsentwurf - Cursortypen
Non-Cursorbefehle
•
SELECT .... (nur eine Zeile, sonst –811)
•
INSERT
•
UPDATE
•
DELETE
•
MERGE
Diese Befehle unterliegen keinen Einschränkungen
im Vergleich zum interaktiven Betrieb ...
Aug2012
26
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Cursor
Das Problem ist: DB2 kann als relationales DBMS Mengenverarbeitung -, die meisten
Programmiersprachen und Entwicklungssysteme können das aber nicht (“1-row-at-a-time”)
select
from
where
name, vorname, geb_dat
mitarbeiter_tab m
gehalt > 2600
Ergebnis ???? – wieviele “rows”
Ein Cursor wird in einem AP benötigt, um einzelne “rows” aus einem “result set” zu lesen und/oder zu
verarbeiten. Um einen Cursor nutzen zu können, muss man folgendes tun:
•
DECLARE des Cursors. Das Statement DECLARE CURSOR enthält den SQL Text den der Cursor ausführen soll. Ist
der Cursor mit "with hold“ erklärt, so bleibt er über einen COMMIT-Punkt hinweg geöffnet – ansonsten schließt eine
COMMIT-Anweisung den Cursor.
Anmerkung: Das Statement DECLARE CURSOR ist eine Deklarationsanweisung, und wird bei der Definition nicht
ausgeführt.
•
OPEN des Cursors. Jetzt werden alle Hostvariablen an DB2 übergeben und das Ergebnis des Cursors in den DB2-Pools
dargestellt.
•
FETCH der einzelnen “rows” aus der “Cursor-Menge”. Man führt soviele FETCHes aus, wie nötig. Werden keine
“rows” zurückgeliefert, so ist der SQLCODE des FETCH 100.
•
CLOSE des Cursor.
Aug2012
27
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Cursor
1.
Definition der Ergebnismenge
=>
DECLARE CURSOR
2.
Zeilenweises Einlesen ins Programm("host-variables")
=>
FETCH ... INTO ...
DECLARE <cname> CURSOR FOR ...
OPEN <cname> .....
FETCH <cname> INTO ...
SQLCODE = + 100
CLOSE <cname>...
Aug2012
28
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Declare und Verwendung eines Cursors in in einem AP mit dynamischem SQL
Hier sind sich dynamisches und statisches SQL sehr ähnlich. Ist der Cursor deklariert, so ist er auch mit seiner Query
verbunden. Mit dem Statement FETCH wird der Cursor auf die aktuelle(nächste) “row” positioniert und die Datenwerte aus
dieser Zeile den “host variables” zugewiesen.
Beim “static” SQL ist die Query ein SELECT Statement in Textformat, während beim “dynamic” SQL die Query über einen
Statement-Namen in einem PREPARE Statement angegeben ist: referenzierte “host variables” werden über PM s(“parameter
markers”) dargestellt.
Der Hauptunterschied zwischen statischem und dynamischem Cursor ist, dass ein statischer Cursor zum “precompile” Zeitpunkt
und ein dynamischer Cursor zur Laufzeit “prepared” wird. Die “host variables” werden über PM’s repräsentiert und ebenfalls
zur Laufzeit beim OPEN CURSOR mit Werten versorgt:
Aug2012
29
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Cursor
1) Abgrenzung eines SQL-Statements: zwischen EXEC SQL und einem Terminator
2) Definition der eingesetzten DB2-Ressourcen(Tabellen, Views): alle DB2-Objekte sollten über DECLARE TABLE
definiert sein, um dem DB2-Precompiler zu erlauben, Syntax und die Kompatibilität der Datenfelder zu prüfen
3) Vorsicht mit reservierten Worten: z.B. HV dürfen nicht mit „SQL“ beginnen, Externe Namen(auch Plan-Namen)
dürfen nicht mit „DSN“ beginnen
4) Einsatzmöglichkeiten von HV: statische / dynamische SQL-Statements erlauben nur an bestimmten Stellen HV
5) Kennzeichnung von HV: sämtlichen HV muss innerhalb eines SQL-Statements ein „:“ vorangestellt sein.
6) Nutzung des SQL-Standards über die Precompile-Optionen; z.B.: SQLSTD (YES)
- Die SQLCA wird automatisch (ohne INCLUDE) vom Precompiler generiert
- Die SQL-Returncodes SQLCODE und/oder SQLSTATE müssen explizit definiert werden
7) Nach jedem ausführbaren SQL-Statement sind die SQLCODES zu prüfen
- SQLCODE und SQLWARN
- SQLSTATE
- Weitere Felder
- Bei Fehlern innerhalb eines Änderungsprozesses ROLLBACK aktivieren ansonsten drohen Konsistenzprobleme
Diese Codierregeln sollten bei DB2 selbstverständlich sein
Aug2012
30
DB2 for LUW – Cursor
DB2 Anwendungsentwurf - Cursortypen
Deklaration des Cursors... DECLARE CURSOR
•
Eine Cursordeklaration ist ausschließlich deklarativ.
•
Für die Cursorverarbeitungsfähigkeit sind folgende Schlüsselworte verantwortlich:
•
FOR SELECT ...
Die Result Table wird hiermit beschrieben. Sie kann updateable oder non-updateable sein. Hierzu sind folgende
Parameter relevant:
FOR UPDATE OF:
Die Suchbedingung wird mit WHERE CURRENT OF ... Vorgegeben
Beim Datenzugriff wird die Page im LOCK-Mode „U“ gesperrt.
Es können auch Spalten verändert werden, die nicht innerhalb der Result
Table des Cursors definiert sind.
Alternativ zu ORDER BY, FOR FETCH ONLY bzw. FOR READ ONLY
FOR FETCH ONLY bzw. FOR READ ONLY
Diese Angabe kennzeichnet die Result Table grundsätzlich als „read only“.
LOCK-Avoidance erfolgt, sofern CURRENTDATA (NO) spezifiziert ist
WITH HOLD
Aug2012
Bei einem COMMIT bleiben Result Table und die Positionierung
innerhalb der Result Table erhalten. Bei CICS sichert ein expliziter
CLOSE die Wiederverwendbarkeit des Thread, da CICS den Cursor nicht
automatisch schliesst.
Empfehlung: bei klassischen Transaktionen ohne WITH HOLD arbeiten
31
DB2 for LUW – Cursor
DB2 Anwendungsentwurf - Cursor
•
CLOSE CURSOR sollte genutzt werden, um belegte Ressourcen freizugeben - insbesondere nach der Nutzung eines
Cursors mit der Klausel WITH HOLD und/oder der Verwendung von „scrollable cursors“ (seit DB2 V7).
•
Aber nicht nur dann, denn wenn ein Cursor nach Fehler nicht geschlossen wird, bleiben SKCT-Teile unnötigerweise im
EDM-Pool erhalten.
•
Auch die Abfrage auf einen SQL Returncode bei Close Cursor ist nicht überflüssig:
COMMIT und ROLLBACK Operationen schließen die Cursor.
•
SQLCODES -404, -652, -679, -802, -901, -904, -909, -910, -911, und -913 veranlassen das Schließen des Cursors.
•
Ist der Cursor dann geschlossen, so erfolgt auf weitere FETCHEs und CLOSE CURSOR Aktionen der SQLCODE 501. Wurden bisher keine SQL Return Codes gesprüft, so ist die Logik des AP zu korrigieren, damit sichergestellt ist,
dass der CURSOR “offen” ist, wenn FETCH / CLOSE Statements ausgeführt werden.
•
In vielen Batch Jobs definiert man oft CURSOR, die LOCKS über einen COMMIT Punkt hinaus halten. Wann
immer man die entsprechende Verarbeitung erledigt hat, sollte man den verarbeiteten Cursor unmittelbar schliessen.
Ansonsten bleiben die sogenannten “shared locks” erhalten und können andere Jobs zum Absturz bringen.
•
Das Schliessen eines Cursors nach seiner Verarbeitung scheint logisch, aber viele Entwickler machen kein CLOSE
CURSOR und ermöglichen es so, dass Locks gehalten werden, bis das Programm beendet. Dies wiederum kann
Ursache für “deadlock/timeout” Probleme sein, da möglicherweise ein Job auf die Durchführung eines Update wartet,
während ein anderer die Page auf einem “shared lock” festhält.
Aug2012
32
DB2 for LUW – Cursor
DB2 kann für die Bereitstellung der Result Table mehrere Zugriffsalternativen nutzen:
• Direkte Positionierung auf die Basistabelle
Die Result Table wird direkt auf den Basisdaten positioniert. Das AP erhält die Daten mit der Konsistenz zum
jeweiligen Anforderungszeitpunkt. Sie kann nur für Einzeldaten garantiert werden. Für Mengen ist die
Konsistenz undefiniert (!)
• Bereitstellen der Result Table über eine Interimstabelle
Die RT kann durch komplettes Durchsuchen der Basisdaten gewonnen werden, muss aufgrund von ORDER
BY intern sortiert und dann in einer temporären Tabelle bereitgestellt werden (Materialisierte RT)
• Für den Anwendungsentwickler ist weiterhin wichtig, ob die RT
-
Read-only (bei JOIN und ORDER BY) oder
updateable ist (für „positioned UPDATE“ und „embedded CHANGE“ möglich)
Gegen konkurrierendes Update geschützt ist
• Desweiteren ist interessant, ob die Basistabelle
-
aktuell ist
„read-only“ oder updateable ist (bei JOIN und ORDER BY)
Gegen konkurrierendes Update geschützt ist
Performanceprobleme:
Aug2012
-
temporäre Result Tables sind aufwendig zu erstellen
bei grossen Datenmengen mit SORT usw. umso mehr
„sequential prefetch“ kann, muss aber nicht eingesetzt werden
33
DB2 for LUW – Cursor
Für jeden Cursor Typ in DB2 gibt 3 READ-Typen
1. Read Only
2. Updatable
3. Ambiguous Cursor
•
Read Only Cursor:
Bei Read Only Cursors kann DB2 „record blocking“ durchführen und so mehrere Sätze gleichzeitig lesen.
Es braucht sich nicht um LOCK-Anforderungen zu kümmern, die es bräuchte, sollten die Sätze
verändert werden (Was es ohne READ ONLY nicht weiß!!!). Gibt man also FOR FETCH ONLY an,
so hilft das dem DB2 schneller zu lesen ohne LOCKS einzusetzen.
Benutzt man Order by oder Group by, so nimmt DB2sowieso an, dass es sich um einen „Read
only Cursor“ handelt.
•
Updatable Cursor:
Ein Cursor ist „updatable“, wenn FOR UPDATE im SELECT angegeben wurde; d.h. dass
alle „rows“ über ein „Update Where Current Of..“ Statement modifiziert werden können. Das
funktioniert sowieso nur bei einem SELECT Statement, das eine EINZIGE Tabelle enthält.
•
Ambiguous cursors
Ein Cursor ist „ambiguous“, wenn DB2 aus der Cursordefinition heraus nicht
entscheiden kann, ob es sich um einen „read-only“ oder einen „updatable“ Cursor handelt. Mit
anderen Worten, der SELECT für den Cursor wurde weder mit FOR READ ONLY noch mit FOR
UPDATE spezifiziert. Er ist „ambiguous“ – mehrdeutig(!).
Für einen solchen Cursor macht DB2 weder „record blocking“, auch wenn die BLOCKING Option im
BIND für diese Applikation gesetzt ist.
Aug2012
34
DB2 for LUW – Cursor
DB2 Cursor
WITH UR kann nur mit SELECT Queries eingesetzt werden. In diesem Fall liest DB2 ALLE „qualifying rows“ (inklusive
der „uncommitted rows“ ) . Wenn nun der ändernde Prozess ROLLBACK absetzt, so hat man „falsche“ Daten gelesen.
Generell nutzt man WITH UR, um LOCKS auf einer Tabelle zu vermeiden. WITH UR verursacht nicht einmal „claims“.
Das Lesen von „uncommitted data“ führt zu einer gewissen Unsicherheit in der Datenkonsistenz.
•
Einsetzen der ISOLATION (UR):
o
o
o
•
Wenn keine Fehler auftreten können:
– „Reference tables“ (Teile mit <teilenummer>, die sich seltenst ändert bzw. in RI eingebunden ist)
– Tabellen mit eingeschränkten Zugriffen („security“, „legacy“ Tabellen, …)
Wenn Fehler akzeptiert werden können ( … bei statistischen Auswertungen, DWH, … )
Wenn die Daten bereits Inkonsistenzen enthalten
Vermeiden der ISOLATION (UR):
o
Wenn Berechnungen passen müssen
o
Wenn die geforderte Antwort auf jeden Fall genau sein muss
o
Wann immer man unsicher ist, ob die ISOLATION (UR) schädlich sein könnte oder nicht….
Bei langlaufenden Queries auf Clients können nach Erfahrungen so bis zu 30% CPU-Zeit gespart werden.
Aug2012
35
DB2 for LUW – Cursor
DB2 Anwendungsentwurf - Cursor
Anmerkung: Bei der Verwendung von dynamischem SQL ist der Cursor immer mehrdeutig
(„ambigous“).
SQL-Anweisungen mit Blockung Aktualisierbare SELECT-Anweisungen (die Anweisungen
UPDATE/DELETE WHERE CURRENT OF verwenden) stellen nicht geblockte Abfragen dar, daher sollten
sie nur verwendet werden, wenn dies absolut erforderlich ist.
Eine aktualisierbare SELECT-Anweisung stellt sicher, dass die Zeile zwischen dem Zeitpunkt des Beendens
von SELECT und dem Zeitpunkt der Eingabe von UPDATE/DELETE nicht geändert wird. Wenn diese
Stufe des gemeinsamen Zugriffs für Ihre Anwendung nicht wichtig ist, kann stattdessen DELETE oder
UPDATE mit Suchkriterien verwendet werden, die auf von einer nicht aktualisierbaren SELECT-Anweisung
übergebenen Werten basieren.
Für SELECT mit Lesezugriff geben Sie FOR FETCH ONLY an (außer unter VM und VSE, da es dort
nicht unterstützt wird).
Aug2012
36
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Statisches und dynamisches SQL
Verwenden Sie so oft wie möglich statisches SQL. Hierdurch werden mehrdeutige Cursor und die
Vorbereitung von SQL-Abschnitten zur Laufzeit vermieden. Wenn dynamisches SQL sich nicht vermeiden
lässt, können folgende Maßnahmen ergriffen werden, damit der Datenaustausch auf dem Netzwerk
minimiert und die Leistung verbessert wird:
•
Wenn die Anweisung eine Anweisung SELECT ist und eine Vorbereitung erforderlich ist, führen Sie PREPARE ... INTO
für einen SQL-Deskriptorbereich durch. Für den SQL-Deskriptorbereich sollte die vollständige Größe zugeordnet
werden, die für die verwendeten Einstellungen erforderlich ist.
•
Wenn die maximale Anzahl von Spalten x beträgt und sich voraussichtlich nichts ändern wird, sollte ein SQLDeskriptorbereich mit x SQLVARs zugeordnet werden.
•
Wenn die Anzahl möglicher Spalten unsicher ist (und Hauptspeicher keinen Engpass darstellt), sollte die maximale
Anzahl von SQLVARs (256) verwendet werden.
•
Wenn der zugeordnete Wert für den SQL-Deskriptorbereich nicht groß genug zum Speichern des übergebenen SQLDeskriptorbereichs ist, muss das Programm eine weitere Anweisung DESCRIBE mit einem SQL-Deskriptorbereich
absetzen, der groß genug ist, um das Ergebnis erneut zu speichern. Hierdurch wird der Datenaustausch auf dem Netzwerk
erhöht.
•
Die Folge aus PREPARE und DESCRIBE sollte nicht verwendet werden. Die Verwendung der Anweisung
PREPARE.....INTO bietet eine bessere Leistung.
Aug2012
37
DB2 for LUW – Cursor
DB2 Anwendungsentwurf
Statisches und dynamisches SQL
•
Binden Sie statische SQL-Anweisungen COMMIT / ROLLBACK statt „dynamic“ COMMIT / ROLLBACK.
•
Wenn es sich nicht um eine Anweisung SELECT, COMMIT oder ROLLBACK handelt, sollte EXECUTE IMMEDIATE
statt einer Folge aus PREPARE und EXECUTE zum Ausführen der Anweisung gegeben werden.
•
ODBC-Anwendungen verwenden dynamisches SQL. Sie können die Leistung unter Umständen mithilfe der statischen
CLI/ODBC-Profilierungsfunktion verbessern. Mit dieser Funktion können Sie ODBC-Aufrufe erfassen und in statische
Anweisungen umsetzen, die in einem PACKAGE gespeichert werden. Die tatsächliche Leistung, die Sie erhalten, hängt
allerdings von der Komplexität der Anwendung ab.
Weitere Überlegungen zu SQL
•
Die Verwendung des Befehlszeilenprozessors (CLP) ist im Allgemeinen langsamer als die Verwendung von dynamischer
SQL im Programm, weil der CLP die Eingabedaten syntaktisch analysieren muss, bevor die SQL-Anweisungen an die DBControl Component übergeben werden.
•
Der CLP formatiert die Daten auch bei deren Eingang, was für Ihre Anwendung möglicherweise gar nicht erforderlich ist.
•
SQL-Anweisungen in einer interpretierten Sprache (z. B. REXX) sind wesentlich langsamer als dieselben SQLAnweisungen in einer Compilersprache (z. B. C).
•
Es gibt zwei Typen der Anweisung CONNECT (Typ 1 / Typ 2). Bei Verbindungen des Typs 2 wird beim Herstellen einer
Verbindung zur DB die vorherige Verbindung in Ruhezustand versetzt, aber nicht beendet. Durch das spätere
Umschalten zu einer im IDLE befindlichen Verbindung kann der Systemaufwand für das Laden von Bibliotheken und das
Einrichten interner Datenstrukturen umgangen werden. Aus diesem Grund kann die Verwendung von Verbindungen des
Typs 2 die Leistung von Anwendungen verbessern.
Aug2012
38
DB2 for LUW – Cursortypen
Beachtenswertes und Programmierempfehlungen
1.
Arbeiten Sie mit einem DB2 Qualitätssicherungstool. Beginnen Sie damit frühzeitig in der
Realisierungsphase. Sie werden rechtzeitig auf Probleme im physischen Datenbankdesign aufmerksam
gemacht. Kritische Queries können vor Produktionseinsatz identifiziert werden. Sie können Ihr Indexdesign
für die ganze Anwendung optimieren anstatt pro Statement im ‘Nebel’ zu stochern.
2.
Nutzen Sie die Möglichkeiten des SQL Analyse-Tools bei der Weiterentwicklung und Pflege der
Anwendungen.
3.
Falls Sie Anforderungen an Performance und hohe Verfügbarkeit haben, schreiben Sie die DB2
Statements NICHT wie Zugriffe auf sequentielle Dateien oder andere Datenbanksysteme. DB2 ist nicht
ADABAS oder IMS! DB2 arbeitet mengenorientiert.
4.
Falls Sie mit CASE-Tools(z.B. ERWIN) arbeiten, übernehmen Sie nicht einfach generierte DDL oder
DML. Achten Sie darauf, daß Schlüssel und Indizes sinnvoll aufgebaut sind. Keycolumns mit geringer
Kardinalität gehören nicht an den Anfang. Das verwirrt nur den DB2 Optimizer.
5.
Die Reihenfolge der Spalten in der Tabelle ist für DB2 ohne Bedeutung.
6.
Verwenden Sie immer EXPLAIN(YES) und überprüfen Sie die Zugriffspfade.
7.
Benutzen Sie generierte Strukturen, um für Tabellen/Views entsprechende Datenstrukturen zu erzeugen.
Für alle Prädikate gilt, daß Datentyp, Länge und Nachkommastellen von Spalten, Hostvariablen und
Literalen übereinstimmen müssen.
8.
Selektieren Sie nur die Spalten einer Tabelle, die benötigt werden. Jede ausgewählte Spalte bedeutet
zusätzlichen CPU-Verbrauch. Selektieren Sie keine Spalten, die in der Anwendung schon bekannt sind und
beispielsweise in der WHERE-Klausel in einem ‘Gleichheitsprädikat’ verwendet werden.
Aug2012
39
DB2 for LUW – Cursortypen
Beachtenswertes und Programmierempfehlungen
8.
Selektieren Sie nur die Spalten einer Tabelle, die benötigt werden. Jede ausgewählte Spalte bedeutet zusätzlichen
CPU-Verbrauch. Selektieren Sie keine Spalten, die in der Anwendung schon bekannt sind und beispielsweise in der
WHERE-Klausel in einem ‘Gleichheitsprädikat’ verwendet werden.
9.
Verwenden Sie nie ‘SELECT *’. Falls die Tabelle geändert wird, müssen Sie alle entsprechenden Programme ändern.
Dazu kommt der erhöhte System-Overhead.
10.
Verwenden Sie keine 1:1 Views auf Tabellen. Sie haben keinen funktionalen Vorteil.
11.
Verwenden Sie in allen (Batch-)Programmen, die Tabellen ändern, ein standardisiertes Checkpoint/Restart
Verfahren. Setzen Sie regelmäßig Commit-Punkte (etwa alle 1000 - 5000 Änderungen). Die Frequenz sollte
einstellbar sein. Bauen Sie das Checkpoint/Restart Verfahren auch in kurzlaufende Programme oder
‘Einmalprogramme’ ein. Häufig werden aus diesen Kurzläufern in Sonderaktionen oder durch Datenwachstum
Endlosprogramme, die stundenlang ohne Commits laufen. Dadurch wird das DB2 übermäßig belastet, die eigenen
und andere Anwendungen werden behindert. Im Fehlerfall kann der Rollback lange dauern. Ein Wiederaufsetzen
am Anfang bedeutet zusätzlichen Zeitverlust und Ressourcenverbrauch. Benutzen Sie die WITH HOLD Option
beim DECLARE CURSOR.
12.
Versuchen Sie langlaufende Programme in mehrere Teile zu zerlegen, die parallel arbeiten können.
13.
Vermeiden Sie unnötige SORT Operationen. DISTINCT wird bei UNION beispielsweise nicht benötigt. Benutzen
Sie Indizes, die den Sort unterstützen.
14.
Vermeiden Sie NULL Felder. Sie erhöhen in den meisten Fällen nur den Programmieraufwand.
15.
Vermeiden Sie VARCHAR Felder. Sie erhöhen den Programmieraufwand und den "System-Overhead".
Aug2012
40
DB2 for LUW – Cursortypen
Beachtenswertes und Programmierempfehlungen (contn‘d)
16.
Kontrollieren Sie im Programm sorgfältig jeden auftretenden SQLCODE.
17.
Vermeiden Sie unnötige Cursor. Falls Sie in 95% der Fälle nur einen Satz zurückbekommen, fangen Sie mit einem
einfachen SELECT an. Beim SQLCODE -811 öffnen Sie dann einen entsprechenden Cursor.
18.
Die gleiche Technik können Sie unter Umständen beim INSERT oder UPDATE verwenden, falls Sie nicht wissen,
ob ein bestimmter Schlüssel existiert (-803). Sie können so ein unnötiges SELECT vermeiden.
19.
Verwenden Sie eine Retry-Logik bei bestimmten Fehlern (-911). Begrenzen Sie die Anzahl der Versuche; z.B. auf 5.
20.
Definieren Sie für jede Tabelle einen Primary Key.
23.
Geben Sie immer einen Index für das CLUSTERING an. Das muß nicht der Index auf dem Primary Key sein. Dieser
Index bestimmt, in welcher Reihenfolge die Daten physisch beim Insert im Tablespace abgelegt werden. Dies wird auch
beim REORG benutzt. Verwenden Sie den CLUSTER Parameter nicht, kann das unvorhersehbare Effekte bei den
Zugriffspfaden bewirken. Der Optimizer bevorzugt bei Join Operationen die „Clustering Indizes“.
24.
Definieren Sie einen Index möglichst als UNIQUE. DB2 kann solche Indizes wesentlich effizienter nutzen.
Uniqueness erleichtert dem Optimizer die Indexnutzung bei der Auswahl des Zugriffspfades.
25.
Um dem Optimizer möglichst genaue Informationen zu liefern, sollte das RUNSTATS Utility zumindest nach einer
Anwendungsimplementierung ausgeführt werden.
26.
RUNSTATS auf eine leere Tabelle kann die Ursache für ungewollte Tablespacescans sein.
Aug2012
41
DB2 for LUW – Cursortypen
Beachtenswertes und Programmierempfehlungen(contn‘d)
27.
Rechnen Sie nicht in der WHERE Klausel (...WHERE Betrag = :HV + 3). Damit verhindern Sie, daß
DB2 einen Index für dieses Feld benutzt. Das gleiche gilt auch für Datumsfelder. Meist lässt sich das mit
wenig Aufwand im Programm selber bestimmen.
28.
Prüfen Sie im Einzelfall, „OPTIMIZE FOR n ROWS“ zu nutzen. DB2 wählt dann unter Umständen
einen anderen Zugriffspfad. Für n = 1 wird aus einem Hybrid Join immer ein Nested Loop Join. Die
Ergebnismenge wird nicht verändert. Benötigen Sie die gesamte Result Table, kann dies allerdings zu
einem insgesamt schlechteren Zugriffspfad führen.
29.
Verwenden Sie bei reinen Lesezugriffen FOR FETCH ONLY.
30.
Schließen Sie CURSOR unmittelbar nachdem die Verarbeitung abgeschlossen wurde oder wenn die
“EOF-Bedingung” eingetreten ist (SQLCODE= +100).
Grund: Ressource-Nutzung: Zwischen-Ergebnistabellen („result tables“)
bleiben solange erhalten, bis ihre zugehörigen CURSOR geschlossen sind.
31.
Wird eine Ergebnistabelle aufgebaut, so sollten nicht mehr “rows” als
notwendig angefordert werden. Wenn möglich, so sollte in der
WHERE-Klausel ein Anfangs- und eine Endwert spezifiziert sein.
Grund: Sperren und “ressource”-Nutzung werden miniminiert. Seit
Version 3.1 werden alle “page”-S-Locks gehalten bis die
letzte „result table“ vollständig erstellt wurde.
Aug2012
42
DB2 for LUW – Cursortypen
Beachtenswertes und Programmierempfehlungen(contn‘d)
32.
Nutzen Sie die ORDER BY-Klausel, um Zeilen in einer gewünschten Sequenz zu erhalten
(CLUSTER’d oder anders)
Grund: Tabellen sind in der CLUSTER-Sequenz nur nach REORG. Zwischenzeitlich erfolgte INSERTed
“rows” können in einer beliebigen “page” stehen. Um die gewünschte Sequenz sicherzustellen
muß ORDER BY benutzt werden.
33.
Kein “kritisches” Batch-Programm sollte mehr als 15 Minuten benötigen, um im Falle eines Fehlers
einen RESTART durchzuführen.
Grund: Problematik kritischer Systeme
geeignete RESTART-Methoden evaluieren.
34.
Stellen Sie sicher, daß ein Commit-Vorgang (UOR) abgeschlossen ist, bevor Informationen am
Terminal/PC ausgegeben werden.
Grund: Sperren; alle Sperren werden beibehalten bis eine folgende Verarbeitung sie freigibt, oder bis ein
“deadlock” /“timeout“ geschieht.
Aug2012
43
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
***********************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
***********************************************************************
**
** SOURCE FILE NAME: advsql.sqb
**
** SAMPLE: How to read table data using CASE
**
**
This sample shows how to read table data using advanced
**
SQL statements with CASE.
**
Aug2012
44
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
***********************************************************************************************************
**
** SQL STATEMENTS USED:
**
BEGIN DECLARE SECTION
**
END DECLARE SECTION
**
CONNECT
**
DECLARE
**
FETCH
**
OPEN
**
**
***********************************************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2 applications, visit the DB2 Information
Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
***********************************************************************************************************
Identification Division.
Program-ID. "advsql".
Aug2012
45
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
Data Division.
Working-Storage Section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 userid
01 passwd.
49 passwd-length
49 passwd-name
01 lname.
49 lname-length
49 lname-name
01 wdept
pic x(8).
pic s9(4) comp-5 value 0.
pic x(18).
pic s9(4) comp-5 value 15.
pic x(15).
pic x(3).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc
77 counter
pic x(80).
pic s9(4) comp-5 value 0.
Procedure Division.
Main Section.
display "Sample COBOL program: ADVSQL".
display "the statment to be executed:".
display " ".
Aug2012
46
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
display
display
display
display
display
display
"SELECT LASTNAME, WORKDEPT FROM EMPLOYEE".
"
WHERE CASE".
"
WHEN BONUS+COMM = 0 THEN NULL".
"
ELSE SALARY/(BONUS+COMM)".
"
END > 10".
" ".
* Get database connection information.
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT
* format with the length
inspect passwd-name
before initial "
statement must be entered in a VARCHAR
of the input string.
tallying passwd-length for characters
".
display " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
Aug2012
47
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
call "checkerr" using SQLCA errloc.
* declare the cursor for the advanced SQL statement.
EXEC SQL DECLARE c1 CURSOR FOR
SELECT LASTNAME, WORKDEPT FROM employee
WHERE CASE
WHEN BONUS+COMM = 0 THEN NULL
ELSE SALARY/(BONUS+COMM)
END > 10 END-EXEC.
EXEC SQL OPEN c1 END-EXEC.
move "OPEN" to errloc.
call "checkerr" using SQLCA errloc.
display "LASTNAME
WORKDEPT".
* FETCH the rows from the table corresponding to the SQL statement.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
display "
", counter, " record(s) selected".
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Aug2012
48
DB2 for LUW – DB2
DB2--Routinen und PL/SQL
DB2 Anwendungen
COBOL-Beispiel:
Fetch-Loop Section.
move spaces to lname-name.
EXEC SQL FETCH c1 INTO :lname, :wdept END-EXEC.
if SQLCODE not equal 0
go to End-Fetch-Loop.
display lname-name, " ", wdept.
add 1 to counter.
End-Fetch-Loop. exit.
End-Prog.
stop run.
Aug2012
49
DB2 for LUW – Fehleranalyse
DB2 und Anwendungsentwicklung – Fehleranalyse (SQLCA)
Aug2012
50
DB2 for LUW – Fehleranalyse
DB2 und Anwendungsentwicklung – Fehleranalyse (SQLCA)
Aug2012
51
DB2 for LUW – Fehleranalyse
DB2 und Anwendungsentwicklung – Fehleranalyse (SQLCA)
Aug2012
52
DB2 for LUW – Fehleranalyse
DB2 und Anwendungsentwicklung – Fehleranalyse (SQLCA)
SQLWARN0 bis 10:
Aug2012
diverse Warnings
53
DB2 for LUW – Fehleranalyse
DB2 und Anwendungsentwicklung –
Fehleranalyse
Aug2012
54
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
• Unit of Work, Unit of Recovery
 COMMIT, ROLLBACK
 SAVEPOINTs
Aug2012
55
DB2 for LUW – Locking und COMMIT/ROLLBACK
1.
Sperren von Systemressourcen = "locking"
Schutz gegen Fehler bei konkurrierender Verarbeitung über LOCK/LATCH gegen :
- gleichzeitige Veränderung von Daten
- Bearbeitung ungesicherter Daten
Aug2012
56
DB2 for LUW – Locking und COMMIT/ROLLBACK
Aufgabe von Sperren auf Systemressourcen = "locking"
DB2--Maßnahmen zur Datensicherheit:
DB2
Datensicherheit
Fehler des OS und
der Systemsoftware
- beim nächsten Restart werden die Daten automatisch
in einen konsistenten Zustand gebracht
Fehler auf den
Platten
- Logging hilft zusammen mit Utilities und archivierten
Sicherungsbeständen
- Log-Inhalte werden benötigt
- Dual-Logging
- hierarchisches Log-Konzept
Fehler in AP's
und interaktiven Prozessen
- Isolation, LUoW und COMMIT, UoR
- ROLLBACK einer LUoW / UoR
DB2-Maßnahmen in Abhängigkeit Nutzungsmöglichkeiten:
„local DB's" / „remote" DB's
Aug2012
57
DB2 for LUW – Locking und COMMIT/ROLLBACK
Aufgabe von Sperren auf Systemressourcen = "locking"
1.
Um “concurrency” Probleme auszuschliessen
2.
Um parallele Verarbeitungsmodi zuzulassen
3.
DB2 kennt im Grossen und Ganzen zwei
Typen von LOCKs:
•
•
Locks zur Kontrolle des Lesevorgangs
Locks zur Kontrolle des Modifikationsvorgangs
Das ist natürlich vereinfacht gesagt.
DB2 verwendet mehrere Varianten dieser
zwei LOCK-Typen.
Aug2012
58
DB2 for LUW – Locking und COMMIT/ROLLBACK
Die Begriffe beim "locking"
LUW = UoW
Logical Unit of Work / Unit of Work als Einheit der physischen
Transaktion bei DB2 Prozessen
Eine Transaktion kann aus einer oder mehreren UoW's bestehen
SYNCPOINT / CHECKPOINT begrenzen eine UoW
UoR
Unit of Recovery bei DB2 die Konsistenz-Einheit innerhalb einer Lokation.
Die UoR ist eine Untermenge der UoW's. Sie beginnt mit der ersten
Datenänderungsanforderung eines Thread und endet mit COMMIT / ROLLBACK
bzw. Transaktionsende
Probleme Lost Update: wird ein Satz von einer AP-1 verändert, so darf keine andere Applikation während der
Wartezeiten der AP-1 diese Daten verändern.
Beispiel: Eine "online"-AP schreibt eingelesene Daten nach einer bestimmten Zeit
(verändert) wieder zurück
Zugriff auf Daten, deren UoW noch nicht abgeschlossen ist:
werden Daten innerhalb einer UOW verändert, so dürfen diese bis zum Ende der
UOW/UoR (COMMIT / ROLLBACK ) keiner anderen AP zugänglich sein
Wiederholtes Einlesen von Daten innerhalb einer UoW:
funktioniert nur, wenn der Datenzustand nicht verändert wurde
Deadlock/Timeout:
Aug2012
da das DBMS Sperren einsetzt, um die Konsistenz der Daten zu sichern, können
Situationen entstehen, in denen sich zwei UOW's/UoR‘s gegenseitig behindern
59
DB2 for LUW – Locking und COMMIT/ROLLBACK
DB2 und Locking – Lock Charakteristika bei DB2
Das alles macht das Locking aus:
Lock-Objekt
die zu schützende Ressource
Lock-Niveau
Umfang der Sperrung auf einem Lock-Objekt
Lock-Dauer
Dauer der Sperre auf einem Lock-Objekt
Isolation-Level
Isolations-Grad und Sperrdauer lesender Anforderungen im Hinblick auf
konkurrierende Anwendungen (CS, RS, RR, UR )
Lock-Modus
die von DB2 ermittelte Art der Sperre ( IS, IX, S, Z, …. )
LOCKSIZE TABLE
LOCKSIZE ROW
2
2
Table 5
PK = 20
1
PK = 40
Pgm 1
1
Table 8
PK = 15
PK = 35
Aug2012
Page 5
PK = 20
PK = 40
Pgm 2
Pgm 1
Pgm 2
1
Page 8
PK = 15
2
PK = 35
60
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Die Sperrenverwaltung ist einer der Faktoren, die sich auf die Anwendungsleistung auswirken. Effiziente
Sperrenverwaltung kann helfen, die Leistung von Datenbankanwendungen zu maximieren:
Sperren und Steuerung des gemeinsamen Zugriffs
Zur Steuerung des gemeinsamen Zugriffs sowie zur Verhinderung eines unkontrollierten Datenzugriffs aktiviert der DBM Sperren
für Pufferpools, Tabellen, Datenpartitionen, Tabellenblöcke oder Tabellenzeilen.
Sperrgranularität
Wenn eine Anwendung eine DB-Objekt Sperre aktiviert hat, kann eine andere Anwendung auf dieses Objekt evtl. nicht zugreifen.
Deshalb sind Sperren auf Zeilenebene bzgl. eines maximalen gemeinsamen Zugriffs besser als Sperren auf Blockebene,
Datenpartitionsebene oder Tabellenebene; das gesperrte Datenvolumen, wird minimiert.
Sperrenattribute
Die Sperren des Datenbankmanagers verfügen über eine Reihe von Basisattributen:
Modus: Der Typ des Zugriffs, der dem Sperreneigner gewährt wird, sowie der Typ des Zugriffs, der Benutzern gewährt wird, die
das gesperrte Objekt gleichzeitig verwenden. Dies wird auch als Status der Sperre bezeichnet ( IN, IS, IX, S, U, X, Z….).
Objekt: Die Ressource, die gesperrt ist. Der einzige Typ von Objekt, den Sie explizit sperren können, ist eine Tabelle. Der DBM
aktiviert Sperren auch für andere Typen von Ressourcen, wie „rows“ und TS. Für MDC-Tabellen können außerdem Blocksperren
aktiviert werden und für partitionierte Tabellen können Sperren auf Partitions aktiviert werden. Das Objekt, das gesperrt wird,
bestimmt die Granularität der Sperre.
Zähler für Sperre: Der Zeitraum, während dessen eine Sperre aktiviert ist. Die Isolationsstufe, unter der eine Abfrage ausgeführt
wird, beeinflusst den Zähler.
Aug2012
61
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Faktoren mit Auswirkungen auf Sperren
Eine Reihe von Faktoren beeinflusst den Modus und die Granularität von Sperren des Datenbankmanagers:
•
•
Die Art der Verarbeitung, die von der Anwendung ausgeführt wird
 Anwendungsverarbeitung mit Lesezugriff (z.B. FOR READ ONLY, FOR FETCH ONLY)
 Anwendungsverarbeitung mit Änderungsabsicht (z.B. FOR UPDATE, USE AND KEEP UPDATE LOCKS, USE AND
KEEP EXCLUSIVE LOCKS… ).
 Anwendungsverarbeitung mit Änderung (z.B. UPDATE-, INSERT- und DELETE – nicht UPDATE WHERE CURRENT OF
oder DELETE WHERE CURRENT OF. Für diesen Typ sind exklusive Sperren (IX oder X) erforderlich).
 Cursorgesteuerte Anwendungsverarbeitung ( Dieser Verarbeitungstyp umfasst Anweisungen mit UPDATE WHERE
CURRENT OF und DELETE WHERE CURRENT OF…)
Die Datenzugriffsmethode
 Ein Zugriffsplan ist die Methode, die der Optimierer zum Abrufen von Daten aus einer bestimmten Tabelle auswählt. Der
Zugriffsplan kann erhebliche Auswirkungen auf Sperrmodi haben.
 Wenn eine Indexsuche zum Auffinden einer bestimmten Zeile verwendet wird, wählt der Optimizer gewöhnlich Sperren auf
Zeilenebene (IS) für die Tabelle aus. Wenn die Tabelle EMPLOYEE zum Beispiel einen Index für die Spalte EMPNO
(Personalnummer) hat, könnte ein Zugriff über diesen Index ausgeführt werden:
select * from employee where empno = '000310'
 Wenn kein Index verwendet wird, muss die gesamte Tabelle der Reihe nach durchsucht werden, um die erforderlichen Zeilen zu finden.
In diesem Fall wählt der Optimizer wahrscheinlich eine einzelne Sperre auf Tabellenebene (S) aus. Wenn zum Beispiel kein Index für
die Spalte SEX vorhanden ist, könnte eine Table Scan eangewandt werden:
select * from employee where sex = 'M'
•
Die Werte verschiedener Konfigurationsparameter
Aug2012
62
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Sperrtypenkompatibilität
Die Sperrenkompatibilität wird zu einem Problem, wenn eine Anwendung eine Sperre für ein Objekt aktiviert hat und eine
weitere Anwendung eine Sperre für dasselbe Objekt anfordert. Wenn die beiden Sperrmodi kompatibel sind, kann die
Anforderung für eine zweite Sperre für das Objekt zugelassen werden.
Auszug aus der Kompatibilitätenliste:
Aug2012
63
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Sperren der nächsten Schlüssel
Während der Einfügung eines Schlüssels in einen Index, wird die Zeile, die dem Schlüssel entspricht, der auf den neuen Schlüssel
im Index als Nächstes folgt, nur gesperrt, wenn diese Zeile momentan durch eine Indexsuche unter der Isolationsstufe
'Wiederholbares Lesen' (RR) gesperrt ist. Wenn dies geschieht, wird die Einfügung des neuen Indexschlüssels ausgesetzt, bis die
Transaktion, die die RR-Suche ausgeführt hat, abgeschlossen wird.
Sperrmodi und Zugriffspläne für Standardtabellen
Der Typ von Sperre, den eine Standardtabelle aktiviert, hängt von der geltenden Isolationsstufe sowie vom verwendeten
Datenzugriffsplan ab.
Nur ein beispiel:
Sperrmodi für MDC-Tabellen- und Satz-ID-Indexsuchen
Der Typ von Sperre, den eine Tabelle mit mehrdimensionalem Clustering (MDC-Tabelle) bei einer Tabellen- oder Satz-IDIndexsuche aktiviert, hängt von der geltenden Isolationsstufe sowie vom verwendeten Datenzugriffsplan ab.
Sperrmodi für MDC-Blockindexsuchen
Der Typ von Sperre, den eine Tabelle mit mehrdimensionalem Clustering (MDC-Tabelle) bei einer Blockindexsuche aktiviert,
hängt von der geltenden Isolationsstufe sowie vom verwendeten Datenzugriffsplan ab.
Aug2012
64
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Sperrverhalten für partitionierte Tabellen
Zusätzlich zu einer Sperre für die gesamte Tabelle wird eine Sperre für jede Datenpartition einer partitionierten Tabelle aktiviert.
Auf diese Weise können die zu sperrenden Bereiche besser differenziert und der gemeinsame Zugriff im Vergleich zu einer nicht
partitionierten Tabelle erhöht werden. Die Datenpartitionssperre wird in der Ausgabe des Befehls db2pd, von
Ereignismonitoren, von Verwaltungssichten und von Tabellenfunktionen ausgewiesen.
Wenn auf eine Tabelle zugegriffen wird, wird zunächst eine Tabellensperre aktiviert. Anschließend werden
Datenpartitionssperren nach Bedarf aktiviert. Aufgrund der verwendeten Zugriffsmethoden und Isolationsstufen ist es möglich,
dass Datenpartitionen gesperrt werden müssen, die nicht zur Ergebnismenge gehören.
Bei einer Suche in einem IX unter der Isolationsstufe 'Cursor Stability' (CS) können Sperren für Partitionen, auf die zuvor
zugegriffen wurde, beibehalten werden, um den Aufwand für eine erneute Aktivierung von Partitionssperren zu verringern.
Partitionssperren beinhalten auch den Aufwand für die Sicherstellung des Zugriffs auf TS. Bei nicht partitionierten Tabellen
wird der Zugriff auf TS durch Tabellensperren gesteuert. Partitionssperren werden aktiviert, auch wenn exklusive oder eine
Sperre (Share) auf Tabellenebene für den gemeinsamen Zugriff aktiviert ist.
Sperrenumwandlung
Die Änderung des Modus einer Sperre, die bereits aktiviert ist, wird als Lock-Promotion bezeichnet.
Lock-Promotion erfolgt, wenn ein Prozess auf ein Datenobjekt zugreift, für das er bereits eine Sperre aktiviert hat, und der
Zugriffsmodus eine noch stärker einschränkende als die aktuelle Sperre erfordert. Ein Prozess kann immer nur eine Sperre für
ein Datenobjekt gleichzeitig aktiviert haben, obwohl er eine Lock für dasselbe Datenobjekt mehrfach indirekt anfordern kann.
Einige Sperrmodi gelten nur für Tabellen, andere nur für Zeilen, Blöcke oder Datenpartitionen. Für Zeilen oder Blöcke
findet eine Umwandlung in der Regel statt, wenn eine X-Sperre benötigt wird und eine S- oder U-Sperre (Update) aktiviert ist.
Lock-Promotion findet implizit bei der Ausführung einer Abfrage statt. Die Systemmonitorelemente lock_current_mode und
lock_mode können Informationen zu Lock-Promotions bereitstellen.
Aug2012
65
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Wartestatus und Zeitlimitüberschreitungen für Sperren
Das Erkennen von Überschreitungen der Sperrzeit ist eine DBM-Funktion, die verhindert, dass Anwendungen unendlich lange
auf die Freigabe einer Sperre warten.
Eine Transaktion könnte beispielsweise auf eine Objekt warten, das von einer anderen Anwendung gesperrt ist. Der andere
Benutzer hat seine Workstation verlassen, ohne ein Ende der UoR zu veranlassen. Um nun eine Blockade zu vermeiden, setzt
man den DBCONFIG-Parameter locktimeout auf maximale Zeitdauer, die eine Anwendung auf eine Sperre warten muß.
Durch die Einstellung dieses Parameters können globale Deadlocks besser vermieden werden, ibesonders in Anwendungen mit
DUoWs („Distributed Units of Work“). Wenn die Zeit der Sperrenanforderung länger dauert, als die im Parameter locktimeout
definierte Zeit, wird ein Fehler an die anfordernde Anwendung gegeben und Transaktion mit ROLLBACK rückgängig gemacht.
Beispiel: Wenn Anwendung APPL1 versucht, eine Sperre zu erhalten, die bereits für Anwendung APPL2 aktiv ist, empfängt
Anwendung APPL1 den SQLCODE -911 (SQLSTATE 40001) mit Reason-Code 68, wenn das Zeitlimit überschritten ist. Der
Standardwert für locktimeout ist -1, d. h., dass die Erkennung von Überschreitungen der Sperrzeit ausgeschaltet ist.
Für Tabellen-, Zeilen-, Datenpartitions- und MDC-Blocksperren kann eine Anwendung den Wert des Parameters locktimeout
überschreiben, indem sie den Wert des Spezialregisters CURRENT LOCK TIMEOUT ändert.
Aug2012
66
DB2 for LUW – Locking und COMMIT/ROLLBACK
Sperrenverwaltung bei DB2
Deadlocks
Ein Deadlock entsteht, wenn zwei Anwendungen Daten sperren, die die jeweils andere Anwendung benötigt. Daraufhin kommt es
zu einer Situation, in der weder die eine noch die andere Anwendung ihre Ausführung fortsetzen kann.
Da Anwendungen Sperren für Daten, die sie benötigen, nicht von sich aus freigeben, ist ein Detektorprozess erforderlich, der
Deadlocks auflöst. Der Deadlock-Detektor überwacht Agenten, die auf Sperren warten, und wird in Intervallen aktiv, die durch
den DB-Konfigurationsparameter dlchktime angegeben werden.
Ermittelt der Deadlock-Detektor einen Deadlock, definiert er einen der Prozesse im Deadlock als „selected“ Prozess, für den
Rollback durchgeführt werden muss. Dieser Prozess wird aktiviert und gibt den SQLCODE -911 (SQLSTATE 40001) mit
Reasoncode 2 an die aufrufende Anwendung
zurück. Der DBM macht „non commited“
Transaktionen aus dem Prozess automatisch
rückgängig (ROLLBACK). Sind die RollbackOperation beendet, werden Sperren, die zum
Prozess gehörten, freigegeben und die anderen
am Deadlock beteiligten Prozesse können
fortfahren.
Zur Gewährleistung einer guten Leistung
muß man einen geeigneten Wert für den
Konfigurationsparameter dlchktime auswählen. Ein zu kurzes Intervall verursacht
unnötigen Systemaufwand, ein zu langes
Intervall lässt zu, dass Deadlocks eine
Weile bestehen bleiben.
Aug2012
67
DB2 for LUW – Locking und COMMIT/ROLLBACK
DB2 erlaubt 4 Ebenen von Locking
Hohe Performance
Hohe Parallelität
•
•
•
•
•
Tablespace
Partition (für “partitioned table spaces”)
Table (für “segmented table spaces”)
Page
Row
„Positioned Updates/Deletes“
können zum Problem werden
Aug2012
68
DB2 for LUW – Locking und COMMIT/ROLLBACK
Programmierung von parallelfähigen Anwendungen
DB2® Prozesse fordern oder vermeiden die Anforderung von Locks abhängig von bestimmten generellen Parametern. Um
die Datenintegrität sicherzustellen, verarbeitet ein Applikationsprozess Locks implizit unter der Kontrolle von DB2.
Es ist für einen Prozess nicht erforderlich, explizit einen Lock anzufordern, um “uncommitted data” zu verstecken. Deshalb
machen Sie sich keine Sorgen um DB2 Locks. Aber Sie können dazu beitragen, dass Ressourcen besser genutzt und
Parallelverarbeitung gefördert wird, indem Sie verstehen welchen Effekt die Parameter haben, mit denen DB2 Locks
kontrolliert.
“Concurrency” und Locks Definition: Concurrency ist die Fähigkeit, dass mehr als eine Applikation dieselben Daten zur
annähernd gleichen Zeit zugreifen kann.
Promotion grundlegender “concurrency”: Bei Befolgen von grundlegenden Empfehlungen kann man “concurrency” im DB2
System favorisieren.
Aspekte von “transaction locks”: Das Verständnis von Größen/Ebenen, Dauer, Modus und Objekten von “transaction locks”
hilft, zu verstehen, warum ein Prozess wartet oder auf “timeout” läuft bzw. auf “deadlock” geht und wie man diese Situation
ändern kann
Optionen zum “Tuning von Locks”: Bestimmte Optionen haben Einfluss darauf, wie DB2 mit “transaction locks”
umgeht(Anzahl “rows” pro Transaktion, Anzahl Locks pro TS etc.).
Kontrolle von DB2 Locks für LOBs/XML: Messungen helfen, zu erfahren, wie DB2 mit Locks auf LOB Daten und XML
Daten umgeht.. .
“Claims” und “drains” zur Kontrolle der “concurrency”: DB2 Utilities, Kommandos und einige ALTER, CREATE und
DROP Statements können den Zugriff auf bestimmte Objekte übernehmen, unabhängig von irgendwelchen Transaktionslocks
auf dem Objekt..
Aug2012
69
DB2 for LUW – Locking und COMMIT/ROLLBACK
Anleitung und Anmerkungen zum Locking
Achten Sie auf “user lock escalation”
Sammelt ein einzelner User mehr “lock”-Einträge in Der LOCKLIST als erlaubt, so wird das Programm über einen -904
SQLCODE informiert. Das Programm kann nun einen ROLLBACK absetzen und eine Meldung ausgeben, um eine höhere
COMMIT Frequenz zu erhalten oder die Lock-Strategie haben zu eskalieren: LOCK TABLE Statement.
Man sollte sich jedoch im Klaren sein, was ein LOCK TABLE verursacht, bevor man ihn auslöst.
Achten Sie auf die “lock promotion”
Bindet man ein Package mit ISOLATION RR, so entscheidet der Optimizer zuweilen, dass ein Table Lock besser für die
Performance sei als “row locks”. Dann hebt der Optimizer das “locking level” auf “table locking”, ohne Rücksicht auf die
Angabe im LOCKSIZE Parameter aus der DDL. Dieser Vorgang heisst: “lock promotion”.
Zur Kontrolle der “lock escalation” sollten DBM/DB Parameter verwendet werden
Die DBM/DBParameter für Locking-Kontrolle in DB2 definieren den Schwellwerte für der Anzahl “row locks”, die parallel für
eine Tabelle/TS über eine einzelne DB2 Applikation (“thread”) gehalten werden dürfen. Danach eskaliert DB2 alle “locks” für
Objekte mit LOCKSIZE ANY nach folgender Regel:
Alle “row locks” auf Daten werden auf “table locks” eskaliert.
Wird der Schwellwert für die maximale Anzahl von “row locks”, die von einer einzelnen DB2 Applikation gleichzeitig gehalten
werden können, erreicht und die Applikation fordert weitere Locks an, erhält sie eine Meldung “ressource not available”
(SQLCODE of -904).
Aug2012
70
DB2 for LUW – Locking und COMMIT/ROLLBACK
Anleitung und Anmerkungen zum Locking
Nutzen Sie LOCK TABLE mit Vorsicht
Nutzen Sie LOCK TABLE, um die Effizienz des Locking in Programmen, die viele “page lock requests” setzen, zu steigern.
Es gibt zwei Typen von LOCK TABLE:
LOCK TABLE...IN SHARE MODE (S-lock auf alle genannten Tables )
LOCK TABLE...IN EXCLUSIVE MODE (X-lock auf die Tabelle im Statement)
Die “table locks” aus dem LOCK TABLE Statement werden gehalten bis zum nächsten COMMIT Punkt.
Fördern Sie “lock avoidance”
Um DB2 zu veranlassen, Locks zu vermeiden, sollte man folgendes versuchen:
• Wann immer praktikabel ISOLATION(CS) beim BIND (Package/Plan) angeben
• Vermeiden von „ambiguous cursors”, indem man FOR READ ONLY (FOR FETCH ONLY) setzt, wenn
der Cursor nicht dazu gedacht ist, Daten zu modifizieren.
Achten Sie auf “concurrent access” mit Partitionsunabhängigkeit
“Partition independence” ermöglicht es, mehr Jobs/Prozesse parallel zu fahren. Diese Fähigkeit kann Systemressourcen
belasten. Und so sollte man CPU Nutzung und I/O Verhalten genau beobachten, bevor man parallele Jobs einsetzt. Es kann
auch sein, dass man dann einige der Jobs wieder serialisieren muss.
Sehen Sie genau hin, wenn Sie einen Cursor WITH HOLD definieren
CURSOR WITH HOLD verursacht Locks und “claims”, die über den COMMIT-Punkt hinaus gehalten werden. Dies kann die
Anzahl von “timeouts” erhöhen und die Verfügbarkeit beinträchtigen. Bevor man die Klausel WITH HOLD in einem Cursor
einsetzt, sollte man sicher sein, damit auch Laufzeitvorteile zu erhalten.
Aug2012
71
DB2 for LUW – Locking und COMMIT/ROLLBACK
Anleitung und Anmerkungen zum Locking
Greifen Sie Tabellen, wenn möglich in derselben Reihenfolge zu
Designen Sie alle Programme so, dass Tabellen in derselben Reihenfolge zugegriffen werden können. Dies verringert die
Wahrscheinlichkeit von “deadlocks”:
Program 1
Program 2
Lock on DEPT
Lock on EMP
Request Lock on EMP
Request Lock on DEPT
In diesem Fall erfolgt ein “deadlock”. Aber, wenn beide Programme erst DEPT, dann EMP zugreifen, kann die “deadlock”Situation vollständig vermieden werden.
Designen Sie Anwendungen mit dem Hintergrund des Lock-Aufwands
Minimieren Sie den Effekt von Locking durch ein angemessenes Design der Anwendungsprogramme. Begrenzen Sie die
Anzahl “rows” durch Einsatz von genauen Prädikaten, die alle
ungewollten “rows” ausfiltern. Dies verringert die Anzahl von Locks,
auf Pages, die zwar zugegriffen, aber nicht benötigt werden ebenso,
wie “timeouts” und “deadlocks”.
Zudem sollte man Programme mit Update Aktionen so entwerfen,
dass der UPDATE so nahe am COMMIT Punkt verarbeitet wird,
wie nur möglich. Dies wiederum reduziert die Zeit, die Locks in einer
“unit of work” gehalten werden, was ebenfalls die Wahrscheinlichkeit
von “timeouts” und “deadlocks” verringert.
Aug2012
72
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• DB2 und verteilte Umgebungen/Objekte
• PL/SQL und DB2
 Sprachstruktur und Einsatz
 Beispiele
• Cursor-Verarbeitung




Types of cursors,
Scope of cursors
Create and manipulate cursors
Fehlerbehandlung und SQLCA
• Lockingmechanismen und „lock avoidance“
 ISOLATION Levels
 Sperrmechanismen
 Sperrkompatibilität
• Unit of Work, Unit of Recovery
 COMMIT, ROLLBACK
 SAVEPOINTs
Aug2012
73
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
COMMIT - Unit of work(LUW) / Unit of Recovery(UoR)
Als “logical unit of work” in einer Applikation bezeichnet man ein “set of SQL” Statements, das einen
“business process” ausmacht. Jeder Fehler während einer “logical unit of work”, erfordert einen
ROLLBACK aller Modifikationen in dieser LUW, damit die DB bezüglich der Geschäftsregeln
konsistent bleibt. Auch nach dem Ende einer LUW sollen die Daten, die in einem Geschäftsprozess
manipuliert wurden, konsistent sein.
Eine “unit of recovery” stellt die Menge an UPDATE, DELETE und/oder INSERT Statements, die
von einem “point of consistency” zu einem anderen Applikationsprozess ausgeführt werden/wurden, dar.
Eine “unit of recovery” beginnt mit der ersten Änderung der Daten, nach Anlauf des Jobs oder in der
Folge auf den letzten “point of consistency” und endet am “commit point”.
Commit
Alle Applikationsprozesse, die Locks anfordern und einige Sekunden laufen, sollten periodisch COMMITs
durchführen. Die die Gründe für COMMITs:
•
Jeder Prozess, der DB2 modifiziert, fordert Locks auf Objekten an und verhindert dadurch, dass
andere Prozesse diese Objekte frei zugreifen können. Werde3n diese Locks unn ötig lange gehalten, so
leidet die Parallelverarbeitung in DB2. Exzessiv lange Lockdauer führt zu “deadlocks” und “timeouts”.
•
Jeder Prozess, der auf DB2 zugreift - inklusive von “read-only” Anwendungen – legen “claims” auf
Objekte, die verhindern, dass “drains” beendet werden können. “Drains” werden typischer Weise von
Utilities, die dafür sorgen sollen, dass “database objects” kontinuierlich verfügbar sind, eingesetzt.
Aug2012
74
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
•
In gut designten Umgebungen werden die meisten “read-only applications” keine “page” bzw. “row” Level Locks anfordern. Sie versuchen “lock avoidance”. Ein Ansteigen der Anzahl oder der Dauer von X
Locks im System wird jedoch die Möglichkeiten von “lock avoidance” verringern. Die Anforderung von
zusätzlichen Locks wird sich auf die “elapsed time” von “read-only transactions” auswirken und die
Last im RLM Umfeld erhöhen, was wiederum zu einer Verschlechterung der gesamten System
Performance führen wird.
•
Wann immer ein “batch program” abnormal beendet oder ein System Fehler auftritt, wird DB2 die
Änderungen an den Daten, die nicht “committed” wurden, zurücksetzen. Werden keine COMMITs
abgesetzt, so kann die Datenmenge für den “backout” sehr groß sein und der Prozess lange dauern. Der
Restart wird außerdem alle UPDATEs erneut anstoßen. Eine höhere Frequenz von COMMITs reduziert
das Datenvolumen für ROLLBACKs und den Aufwand beim Restart.
•
COMMITs in einem “batch process” erfordern es, dass eine Applikation für Restart designed sein muss. .
Ein COMMIT beendet eine “unit of recovery” und bestätigt alle “database updates”, die in der “unit
of recovery” getätigt wurden. DB2 führt bei einem COMMIT folgende Aktionen durch:
(1) Alle “page” / “row” Locks werden freigegeben – mit Ausnahme derer, die von einem offenen Cursor
mit der Klausel WITH HOLD gehalten werden
(2) Ist die RELEASE(COMMIT) BIND Option gesetzt, so werden alle Tabellen- TS- und Partition Locks
freigegeben außer denen, die von Cursors mit WITH HOLD gehalten werden
Aug2012
75
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
(3) Alle “claims” werden freigegeben - außer ….WITH HOLD
(4) Bei RELEASE(COMMIT) werden auch alle Statistiken , die von einem “index look-aside” und/oder
einem “sequential detection” Prozess gehalten werden, weggeworfen
(5) Bei RELEASE(COMMIT) werden auch alle vom “thread” erstellten IPROCs bzw. UPROCs gelöscht
(6) Ein “write” wird aus dem Log Buffer auf die “active logs” veranlaßt. Die Zeit, die für diesen
“immediate write” der Log Buffer benötigt wird, hängt von der Menge der Daten, die seit dem letzten
“log wirte” verändert wurden, ab . Das Streuen der “updates” über die gesamte “unit of recovery”
erhöht die Chance, dass die Log-Daten bereits geschrieben sind, wenn der COMMIT erfolgt.
Das Aufsparen aller “update”, “insert” und “delete” Aktivitäten bis zum Zeitpunkt unmittelbar VOR dem
COMMIT kann die Sperrdauer minimieren. Es kann aber auch dazu führen, daß die Menge der zum
COMMIT-Zeitpunkt zu schreibenden Log-Daten ansteigt und der COMMIT-Vorgang des halb länger
dauert.
UPDATE COUNTER_TBL SET SEQ_NBR = SEQ_NBR + 1
SELECT SEQ_NBR INTO :SEQ_NBR
-- Application processing logic inserting into ORDER table for example
COMMIT
Aug2012
-- the end of the transaction
76
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
Probleme, wenn kein COMMIT gesetzt wird:
•
•
•
•
•
Pages im BP bleiben reserviert
Erreichen des Buffer-Thresholds
Lock--Table / LockLock
Lock-List (von RLM verwaltet) bläht sich auf und kann überlaufen
Log--Einträge müssen gehalten werden (Problematisch, wenn die Einträge nicht mehr auf dem aktiven Log
Log
vorhanden, sondern bereits archiviert sind)
Langläufer mit Abbruch verursachen lange ROLLBACKROLLBACK-Zeiten und einen großen ROLLBACK-Aufwand
Langläufer sperren veränderte Ressourcen innerhalb der UoW
bis zur Verhinderung des beabsichtigten
Parallelbetriebs
... Deshalb ist der Einsatz von COMMIT‘s zu empfehlen
ODER ????
Aug2012
77
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
Der Einsatz von COMMIT ist dringend zu empfehlen, bringt aber nicht nur Vorteile !
•
Die Anwendung wird in logische Abschnitte unterteilt, für deren Synchronisation der Entwickler verantwortlich ist.
Regel: COMMIT nur nach Abschluss einer logischen Arbeitseinheit
- Nach Bearbeiten jedes n.ten Kunden
- Ein COMMIT löst alle Sperren auf
- Bei Abbruch sind sofort ALLE Ressourcen wieder frei (trotz „partial“ COMMIT)
•
Das Programm muss generell restartfähig sein
- DB2 Tabellen müssen positioniert werden
- Evtl. bringen sequentielle Bestände Probleme (wegen Synchronisation mit COMMIT
COMMIT)
Lösung:
CLOSE des Bestandes direkt VOR dem COMMIT oder
keine Ausgabe sequentieller Bestände auf DB2-Tabellen oder zwei Schritte
- Bei Abbruch sind sofort ALLE Ressourcen wieder frei (trotz „partial“ COMMIT)
•
Ein COMMIT kann offene Cursor schliessen
- Result Table und Positionierung gehen verloren
Wann wird eine UoW beendet?
•
•
Aug2012
Explizit: durch ein entsprechendes Kommando
Implizit: mit Ende des Programms(Thread bzw. Task)
78
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
Das Setzen von COMMIT-Punkten kann in einem “Langläufer” dazu führen, dass der CPU-Aufwand und
auch der Aufwand für das Schreiben der LOCKs steigt.
Aber man gewinnt auch:
•
Reduzierte “lock contention” besonders
in “DPF environments”
•
Verbesserte Effektivität bei der “lock
avoidance” auch hier besonders in
“DPF environments”
•
Reduktion der “elapsed time” bei einem
DB2 System Restart nach Systemfehler
•
Reduktion der “elapsed time” für einen
Rollback einer “unit of work”, nach einem
Anwendungsfehler, oder einem gezielten
ROLLBACK.
•
Mögliche Parallelverarbeitung mit
Utilities wie beispielsweise dem „online
REORG“.
Runtime
System
Transaction
Monitor1
Batch
RLM
DB2 - Subsystem
Logging
DPF
Facility
DB2-Katalog
Aug2012
TM2
DB2-Daten
Log-Dateien
79
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
Wirkungen am Ende einer UoW:
• Freigabe sämtlicher Result Tables und Work
Work--Files
Files,, sowie Buffer
Buffer--Bereiche
-
Ausnahme:
Cursor mit WITH HOLD
Dabei bleiben auch TEMP-Tables erhalten (!)
• Freigabe sämtlicher Sperren
-
Ausnahme:
. für Cursor mit WITH HOLD werden „X“- oder „U“-Locks auf „S“ gesetzt
. alle „prepared statements“ bleiben erhalten
. LOCK TABLE-Sperre bei entspr. BIND Parameter
• Abschluss der UoR auf dem aktiven LOG:
LOG: Damit endet die ROLLBACKROLLBACK-Fähigkeit
• Freigabe der mit PREPARE aufbereiteten dynamischen SQLSQL-Statements
• Freigabe der zur Freigabe vorgemerkten Connections
... Zu den letzten beiden freigegebenen Ressourcen gibt es
zusätzliche Bedingungen(siehe DB2 Manuals)
Aug2012
80
DB2 for LUW – COMMIT / ROLLBACK / SAVEPOINTs
Commit (cntn‘d)
Verwenden Sie in allen (Batch-)
Programmen, die Tabellen ändern, ein
standardisiertes Checkpoint/Restart
Verfahren.
Transaktionsmonitor
Transaktion 1
UOW
Checkpoint/Restart Verfahren auch in
kurzlaufende Programme oder ‘Einmalprogramme’ einbauen.
Häufig werden aus diesen Kurzläufern in
Sonderaktionen oder durch Daten-wachstum Endlosprogramme, die stundenlang
ohne Commits laufen. Dadurch wird das
DB2 übermäßig belastet, die eigenen und
andere Anwendungen werden behindert. Im
Fehlerfall kann Rollback sehr lange dauern.
Ein Wiederaufsetzen am Anfang eines
Cursors bedeutet zusätzlichen Zeitverlust
und Ressourcenverbrauch. Die Option
WITH HOLD beim DECLARE CURSOR
oder probate Wiederaufsetzverfahren
sollten genutzt werden
Aug2012
Thread A
UOW2
UOR
keine, da
keine Manipulation
der Daten
COMMIT
Transaktion 2
UOW1
DB2
SELECT
(1. SQL-Aufruf)
Ende der Transaktion
regelmäßig Commit-Punkte (etwa alle
1000 - 5000 Änderungen ist GROSSZÜGIG!!! )
Die Frequenz sollte einstellbar sein.
„runtime“ System
Thread B
SELECT
(1. SQL-Aufruf)
INSERT
Syncpoint/
Checkpoint
UOR1
COMMIT
UOR2
UPDATE
Ende der Transaktion
Linux/Unix kennt
kein UOW-Konzept
COMMIT
Thread C
Dialog 3
SELECT
(1. SQL-Aufruf)
INSERT
UPDATE
UOR
DELETE
Dialogende
COMMIT
81
DB2 for LUW – SAVEPOINTs
COMMITs und SAVEPOINTs
Was ist ein „savepoint“ ?
Ein "savepoint" ist ein mit Namen versehenes "entity" das den Status eines Datenzustandes zu einem bestimmten
Zeitpunkt innerhalb einer "logical" UoW repräsentiert.
"savepoints" können gesetzt und freigegeben werden.
Es ist möglich einen "rollback" der Daten zu dem Status durchzuführen, den der benannte "savepoint" repräsentiert.
Ein "rollback" setzt alle "savepoints", welche nach dem "savepoint" auf den wir zurücksetzen, gesetzt wurden, zurück.
Daten (DML) und Schema (DDL) Änderungen durch die Transaktion, nachdem eine "savepoint" gesetzt ist kann zu dem
"savepoint“ zurückgesetzt werden, zu dem es die logische Anwendung benötigt. Das allgemeine Ergebnis einer Transaktion
wird davon nicht beeinträchtigt. Es gibr keine Grenze bezüglich der Anzahl von "savepoints" die gesetzt werden können. Der
Geltungsbereich eines „savepoint" ist abhängig vom DBMS auf welchem er gesetzt wurde.
Warum "savepoints" nutzen ?
"savepoints" ermöglichen die Kodierung von Zusammenhängen, oder von „what-if“ – Logiken.
Sie sind brauchbar für Programme mit intelligenter Fehlerbehandlung, oder für „stored procedure“-Updates, wenn ein
Fehler aufgetreten ist und nur die Änderungen der „stored procedure“ mit „rollback“ behandelt werden sollen.
Der Verwaltungsbedarf eines "savepoint" ist klein (Messungen zeigen, dass die Kosten eines "savepoint" mit einem
einfachen „fetch“ gleichzusetzen sind).
Die Freigabe eines "savepoint" soll aus Sicht der Anwendung und deren logischer Perspekive erfolgen.
Aug2012
82
DB2 for LUW – SAVEPOINTs
COMMITs und SAVEPOINTs
Beispiele für einen „savepoint“ ?
Ein gutes Beispiel für die Verwendung von "savepoints" ist der Vorgang von Flugbuchungen:
John D. aus Australien plant einen Urlaub in Dänemark. Er bittet den Vertreter eines Reisebüros die Flüge zu buchen. Maximum 4 Abschnitte
(3 Stops) für beide Richtungen. Er verlässt Alice Springs, Australien und fliegt nach Melbourne. Von Melbourne aus kann er nach Singapur
und von dort nach Kopenhagen fliegen, oder über Kuala Lumpur und Amsterdam nach Kopenhagen. Das Bild zeigt die möglichen zu
fliegenden Strecken von A lice Springs nach Kopenhagen.
• erst machen wir die Flugreservierung nach
Melbourne und definieren einen „savepoint"
#FIRSTSTOP.
• dann reservieren wir den Flug nach Singapor
und definieren einen "savepoint" namens
#SECONDSTOP.
• Nun stellen wir fest, dass von Singapor nach
Kopenhagen keine Plätze frei sind.
• wir setzen ROLLBACK TO SAVEPOINT
#FIRSTSTOP, da wir die Reservierung nach
Melbourne nicht verlieren wollen.
• dann wir machen die Reservierung nach
Kuala Lumpur und setzen einen "savepoint"
namens #SECONDSTOP.
• und nach Amsterdam mit "savepoint"
#THIRDSTOP.
Aug2012
83
DB2 for LUW – SAVEPOINTs
COMMITs und SAVEPOINTs
Beispiele für einen „savepoint“ ?
• und nach Kopenhagen mit "savepoint" #DESTINATION. Jetzt sind wir an unserem Bestimmungsort und haben nicht mehr als 3
Stops eingelegt. Wir können alle "savepoints" außer #DESTINATION freigeben.
• nun versuchen wir die Rückkehr-Reservierung erst nach Singapur mit einem "savepoint" #FIRSTSTOP.
• Es gibt keine Plätze von Singapor nach Melbourne und wir benötigen einen ROLLBACK TO SAVEPOINT #DESTINATION.
Das ist Kopenhagen. Der Rollback gibt auch den #FIRSTSTOP "savepoint" frei.
• dann versuchen wir die Reservierung von Kopenhagen nach Amsterdam mit einem "savepoint" #FIRSTSTOP
• und nach Kuala Lumpur mit "savepoint" #SECONDSTOP.
• und nach Melbourne mit "savepoint" #THIRDSTOP.
• wenn die Reservierung von Melbourne nach Alice Springs fehlschlägt, möchten wir die ganze Reservierung mit ROLLBACK
zurücksetzen; d.h. an den Anfang einer dem "logical" UoW. Wenn wir ein Platz nach Alice Springs finden können und die Anzahl der
Stops nicht mehr als 3 beträgt, können wir die UOW mit COMMIT abschliessen.
Dieses Beispiel zeigt dass es manchmal einen Bedarf nach zusätzlichen Zeitpunkten für "rollback" gibt. Sie ändern nicht die Logik - noch
die Notwendigkeit – von COMMITs.
"savepoint" Merkmale
Das SAVEPOINT Statement wird zum Setzen eines "savepoint" verwendet. Nach Ausführung sollte man den SQL-Returncode genau
prüfen, um sicherzustellen, dass der "savepoint" auch wirklich gesetzt ist. Es ist auch empfehlenswert, einen möglichst sprechenden
Namen zu wählen (maximale Länge 128 Bytes). „savepoint“-Statements und verwandte Angaben ( SAVEPOINT, ROLLBACK TO
SAVEPOINT und RELEASE SAVEPOINT ) können von einem AP oder von einer „stored procedure“, die mit MODIFIES SQL
DATA definiert ist, gesetzt werden. Diese Statements können nicht verwendet werden,
•
während einer „User Defined Function (UDF)“
•
innerhalb eines Triggers
Aug2012
84
DB2 for LUW – SAVEPOINTs
COMMITs und SAVEPOINTs
"savepoint" Merkmale
WICHTIG: "savepoints" sind kein Ersatz für COMMITs.
Beispiel: Einstellen eines "savepoint"
EXEC SQL SAVEPOINT START_OVER
UNIQUE
ON ROLLBACK RETAIN CURSORS
ON ROLLBACK RETAIN LOCKS ;
Tip: Man kann einen "savepoint", der als UNIQUE spezifiziert ist, wiederverwenden, wenn der frühere "savepoint" mit gleichem Namen
vor der Wiederverwendung freigegeben wurde ( durch die Verwendung eines ROLLBACK oder eines RELEASE SAVEPOINT ).
"savepoint" Einschränkungen
•
•
•
•
•
•
•
•
keine "savepoints" in „global transactions“.
keine "savepoints" in „triggers“.
keine "savepoints" in UDF.
keine "savepoints" in „stored procedures“ die von einem „trigger“ oder einer UDF gerufen wird.
„rollback“ auf einen "savepoint" annulliert nicht die Änderungen auf temporären Tabellen.
„rollback“ auf einen "savepoint" gibt keine „locks“ frei.
Die Cursorposition ändert sich nicht, bei „rollback“ auf einen "savepoint".
"savepoint" Namen können nicht über Host-Variablen spezifiziert sein.
ROLLBACK
Aug2012
85
DB2 for LUW – Locking und COMMIT/ROLLBACK
COMMITs und SAVEPOINTs
Aug2012
86
DB2 for LUW – Locking und COMMIT/ROLLBACK
COMMITs - Zusammenfassung
Achten Sie auf
•
Effiziente Bereitstellung der Result Tables
•
Konsistentes und ressourcenschonendes Anzeigen von
Ergebnissen
•
Konzeption der UoW‘s
•
Effiziente WiederaufsetzWiederaufsetz-Techniken bei Cursorverarbeitung
•
Möglichkeiten die Suchtechniken zu verbessern
•
Queueing bei asynchroner Verarbeitung
•
Sinnvollen Einsatz von COMMIT‘s
•
Konkurrierende UPDATE-Situationen
UPDATE
•
Vermeiden von DEADLOCK‘s
... Alles das gehört zu effizienten DB2DB2-Anwendungen ....
Aug2012
87
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• Erläuterung der EXPLAIN Funktion





Einführung in die DB2 Optimizer Arbeitsweise
Zugriffspläne lesen, verstehen und zur SQL-Optimierung analysieren
Explain durchführen
SQL Analyse-Tools von IBM (Visual EXPLAIN, db2advis etc.)
Analyse Tools von Drittanbietern
• DB2 Optimizer Richtlinien
• DB2 Utilities und Statistiken und Systemkommandos
•
Aug2012
Monitoring und Fehleranalyse
88
DB2 for LUW – Die Funktion EXPLAIN
EXPLAIN Statement
Das Statement EXPLAIN eruiert Informationen aus dem “access plan”, die vom Optimizer für das
entsprechende SQL-Statement zusammengestellt werden.Die Daten werden in den “explain tables” abgelegt.
Ein “explainable statement” ist
•
ein gültiges XQuery Statement
•
Eines der folgenden SQL Statements:




CALL,
Compound SQL (Dynamic),
DELETE, INSERT, VALUES, VALUES INTO…, MERGE, UPDATE,REFRESH, SELECT, SELECT INTO,
SET INTEGRITY,
Aufruf
Das Statement kann in einem “application program” stehen oder interaktiv aufgerufen werden. EXPLAIN ist
ein ausführbares Statement, das dynamisch “prepared” werden kann.
Das Statement , das “explained” wird, wird nicht ausgeführt
Beschreibung diverser Parameter
“PLAN SELECTION” zeigt an, dass Informationen aus der Phase “plan selection” bei der Query Kompilierung in die “explain
tables” eingetragen werden soll.
ALL
ist ein Equivalent zu PLAN SELECTION.
PLAN Die Option PLAN soll Syntax-Kompatibilität mit anderen DB-Systemen herstellen
Aug2012
89
DB2 for LUW – Die Funktion EXPLAIN
EXPLAIN Statement (cnt’nd)
Beschreibung diverser Parameter
FOR SNAPSHOT zeigt an, dass nur ein “explain snapshot” gezogen und in die SNAPSHOT Spalte der Tabelle
EXPLAIN_STATEMENT geschrieben werden soll. Es wird keine weitere EXPLAIN Information aufgezeichnet als die,
aus den Tabellen EXPLAIN_INSTANCE und EXPLAIN_STATEMENT.
Die “explain snapshot information” ist für die Nutzung im Visual Explain gedacht.
WITH SNAPSHOT gibt an, dass zusätzlich zur allgemeinen “explain information” noch eine “explain snapshot” gezogen
werden soll. Das “default” Vderhalten des EXPLAIN Statement ist, keinen “explain snapshot” zu erstellen.
default (weder FOR SNAPSHOT noch WITH SNAPSHOT ist angegeben) schreibt die “explain” Informationen in die “explain
tables”. Es wird kein “snapshot” für die Nutzung mit Visual Explain erstellt. .
SET QUERYNO = <integer > belegt das Feld QUERYNO in der Tabelle EXPLAIN_STATEMENT mit einer angegebenen
Nummer, um das entsprechende Statement bei der Analyse wiederfinden zu können. Bei einem “static” EXPLAIN
Statement ist der “default”-Wert die “statement number” aus dem entsprechenden Precompiler.
SET QUERYTAG = <string-constant> belegt das Feld QUERYTAG in der Tabelle EXPLAIN_STATEMENT mit einer
angegebenen Zeichenfolge , um das entsprechende Statement bei der Analyse wiederfinden zu können. Es können
beliebige Zeichen bis zu 20 Bytes Länge verwendet werden. Default sind <blanks>.
FOR
Aug2012
<explainable-sql-statement> zeigt das SQL Statement, das “explained” werden soll. Das kann ein beliebiges, gültiges
Statement sein (s.o).
90
DB2 for LUW – Die Funktion EXPLAIN
Beispiele
(1) Explain eines einfachen SELECT Statement ujnd Kennzeichnung mit QUERYNO = 13.
EXPLAIN PLAN SET QUERYNO = 13 FOR SELECT C1 FROM T1
(2) Explain eines einfachen SELECT Statement ujnd Kennzeichnung mit QUERYTAG = 'TEST13'.
EXPLAIN PLAN SELECTION SET QUERYTAG = 'TEST13' FOR SELECT C1 FROM T1
(3) Explain eines einfachen SELECT Statement ujnd Kennzeichnung mit QUERYNO = 13 und QUERYTAG = 'TEST13'.
EXPLAIN PLAN SELECTION SET QUERYNO = 13 SET QUERYTAG = 'TEST13' FOR SELECT C1 FROM T1
(4) Versuch EXPLAIN Information zu erhalten, wenn die EXPLAIN Tabellen nicht existieren:
EXPLAIN ALL FOR SELECT C1 FROM T1
Fehler: SQLSTATE 42704.
(5) Das folgende Statement funktioniert, wenn es im “package cache” gefunden wird und mit REOPT ONCE kompiliert wurde.
EXPLAIN ALL WITH REOPT ONCE FOR SELECT C1 FROM T1 WHERE C1 = :<host variable>
(5) Diese Beispiel nutzt die Funktion db2-fn:xmlcolumn, die den “case- sensitiven” Namen einer XML Spalte als Argument b
esitzt und eine XML “sequence” bestehend aus einer “concatenation” von XML Spaltenwerten zurückgibt.
Die tabelle heisst BUSINESS.CUSTOMER mit der XML Spalte INFOEine einfache Xquery, die alle Dokumente aus der
Spalte INFO liefert lautet :
EXPLAIN PLAN SELECTION FOR XQUERY 'db2-fn:xmlcolumn ("BUSINESS.CUSTOMER.INFO")'
Ist ein Spaltenwert NULL, so ist die “return sequence” der entsprechenden “row” leer.
Aug2012
91
DB2 for LUW – Die Funktion EXPLAIN
Explain tables
Die “explain tables” erhalten Informationen des Zugriffspfads, wenn der EXPLAIN aktiviert ist. Die
“explain tables” müssen VOR Ausführung des EXPLAIN angelegt werden. So geht’s:
(1) Aufruf der Prozedur SYSPROC.SYSINSTALLOBJECTS:
db2 CONNECT TO database-name db2
CALL SYSPROC.SYSINSTALLOBJECTS ('EXPLAIN‘
, 'C‘
, CAST (NULL AS VARCHAR(128))
, CAST (NULL AS VARCHAR(128)))
Erzeugt alle “explain tables” im Schema SYSTOOLS . Um sie unter einem anderen Schema zu erzeugen,
spezifziert man einen “schema name “ als letzten Parameter im Call.
(2) Ausführen der EXPLAIN.DDL DB2® “command file”:
db2 CONNECT TO database-name db2 -tf EXPLAIN.DDL
Erzeugt alle “explain tables” im aktuellen Schema. Die Datei liegt im Directory DB2PATH\misc directory
bei Windows Systemen und in INSTHOME/sqllib/misc directory bei Linux und UNIX
Betriebssystemen. DB2PATH ist der Pfad, auf dem die DB2 Kopie installiert ist und INSTHOME ist das
“instance home directory”.
Das Explain facility nutzt folgende IDs als Schema, wenn die “explain tables” geschrieben werden:
•
•
Aug2012
“session authorization ID” bei dynamischem SQL
“statement authorization ID” bei statischem SQL
92
DB2 for LUW – Die Funktion EXPLAIN
Explain tables
Das jeweilige Schema kann mit einem “set” von Explain Tabellen bestückt werden. Werden keine Schemabezogenen EXPLAIN-Tabellen angelegt, so sucht das “Explain facility” im Schema SYSTOOLS nach den
Tabellen.
Das Füllen der “Explain tables” durch die Funktion EXPLAIN aktiviert weder Triggers noch “referential”
bzw. “check constraints”. Beispiel: Ein “insert” Trigger auf der Tabelle EXPLAIN_INSTANCE und ein
EXPLAIN, der Einfügungen in diese tabelle verursacht, würde den Trigger nicht auslösen.
Die EXPLAIN Tables sind














Aug2012
ADVISE_INDEX table
ADVISE_INSTANCE table
ADVISE_MQT table
ADVISE_PARTITION table
ADVISE_TABLE table
ADVISE_WORKLOAD table
EXPLAIN_ACTUALS table
EXPLAIN_ARGUMENT table
EXPLAIN_INSTANCE table
EXPLAIN_OBJECT table
EXPLAIN_OPERATOR table
EXPLAIN_PREDICATE table
EXPLAIN_STATEMENT table
EXPLAIN_STREAM table
93
DB2 for LUW – Die Funktion EXPLAIN
Explain Beispiel
Access Plan:
----------Total Cost:
Query Degree:
45835.2
1
Rows
RETURN
(
1)
Cost
I/O
|
0.333333
NLJOIN
(
2)
45835.2
11880
/----------+-----------\
542955
6.13925e-07
FETCH
FILTER
(
3)
(
5)
7202.28
28.3295
6848
4
/------+------\
|
107
1.62886e+06
1
IXSCAN
TABLE: L4_FDB
GRPBY
(
4)
L4_FDB_GP_STAMM_ALLGEMEIN (
6)
35.7012
Q7
28.3293
0
4
|
|
1.62886e+06
0.5
INDEX: SYSIBM
NLJOIN
SQL120824170333980
(
7)
Q7
28.3293
4
/------+------\
Aug2012
Cardinality of the result of the
operator below (# of rows)
Operator name
Operator sequence number
cost
I/O cost
94
DB2 for LUW – Die Funktion EXPLAIN
Explain Beispiel(contn’d)
4
/------+------\
1
0.5
IXSCAN
FILTER
(
8)
(
9)
14.1645
14.1647
2
2
|
|
1.62886e+06
1
INDEX: L4_FDB
GRPBY
L4_FDB_GP_STAMM_AXX_U2_IDX ( 10)
Q4
14.1646
2
|
0.333333
IXSCAN
( 11)
14.1646
2
|
1.62886e+06
INDEX: L4_FDB
L4_FDB_GP_STAMM_AXX_U2_IDX
Q1
Ein IX führte hier zu einer Kostensenkung von 138.488 CPU auf 45.835,2 CPU-Kosten!
Aug2012
95
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• Erläuterung der EXPLAIN Funktion





Einführung in die DB2 Optimizer Arbeitsweise
Zugriffspläne lesen, verstehen und zur SQL-Optimierung analysieren
Explain durchführen
SQL Analyse-Tools von IBM (Visual EXPLAIN, db2advis etc.)
Analyse Tools von Drittanbietern
• DB2 Optimizer Richtlinien
• DB2 Utilities und Statistiken und Systemkommandos
•
Aug2012
Monitoring und Fehleranalyse
96
DB2 for LUW – Performance & Tuning ( AP und SQL )
Der Optimizer und die Suche nach dem Zugriffspfad
SELECT d.name, sum(e.id), avg(e.compensation)
FROM employees e, departments d, projects p
WHERE d.location in (‘SVL’, ‘ARC’)
and d.id = e.dept
and e.id = p.employeeID
and p.class in (‘top secret’, ‘confidential’)
GROUP BY d.name
ORDER BY d.name
Annahmen:
– Index:
Letztendlicher Zugriffspfad
ix1(d.location)
ix2(e.id), ix3(e.dept)
ix4(p.class)
– Cardinality:
card(e) = 300000
card(d) = 20000
card(p) = 50000
– Filtering factor:
FF(P1) = 1%,
FF(P4) = 10%
Aug2012
Projects
Department
Employee
# von „join sequences“ (3! = 6)
# von „access methods“ (RSCAN, ISCAN, Midx, etc)
# von „join methods“ (NLJ, SMJ, HBJ)
Gesamtzahl von Zugriffspfaden
Kostenschätzung (elapsed time)
Zugriffspfadwahl (kürzeste“elapsed time“)
97
DB2 for LUW – Performance & Tuning ( AP und SQL )
Maxime bei SQL: Sei so selektiv wie möglich !
1.
Holen Sie das absolute Minimum an Daten in die DB2 „Engine“


2.
Filtern Sie alle „non„non-qualifying
qualifying““ Daten aus der Resultatsmenge

3.
Formulieren möglichst wirksamer Prädikate
(Nutze „stage1“-Prädikate)
Erledigen Sie die Verarbeitung in der kleinsten Menge funktionaler SQLSQL-Statements

5.
Vorgabe möglichst präziser Prädikate(Filtern) und ausschließlich Anforderung benötigter Daten
(Kodiere Prädikate, die selektiv sind)
Geben Sie nur das absolute Minimum an Daten an den „requestor
„requestor““ zurück

4.
Reduktion der Zeilenanzahl durch genaue Prädikate in der WHERE-Klausel, soweit möglich
(Suche die kleinste „row“-Menge)
Reduktion der Spaltenanzahl, soweit sinnvoll und möglich
(Lies nur die Spalten, die wirklich benötigt werden)
Formulieren kompakter und komplexer SQL-Statements
(Reduziere die Anzahl der SQL-Statements)
Erledigen Sie soviel Funktionalität wie möglich im SQLSQL-Statement (nicht im Programm)
Grundsätzlich gilt: Alles, was nicht in Stage1 verarbeitet werden kann, wird in
Stage2 erledigt.
Aug2012
98
DB2 for LUW – Performance & Tuning ( AP und SQL )
Performance&Tuning von DB2 SQL - Grundsätzliche Empfehlungen
(1) Definition von Vergleichsspalten mit gleicher Länge und gleichem Format

Definition von Vergleichsspalten mit gleicher Länge und gleichem Format
(Achte auf Datentyp und –kompatibilität)
(2) Vornanstellen der effizientesten Filter-Prädikate

Effiziente Prädikate an den Anfang der WHERE-Klausel
(Kodiere die selektivsten Prädikate zuerst)
(3) Anstreben einer möglichst effizienten Index-Unterstützung - Dabei auf Filterfaktoren und inhaltliche
Streuung achten
(4) Erledigen Sie die Verarbeitung in der kleinsten Menge funktionaler SQL-Statements
(5) Bei Work-Files auf Mengengerüste achten. Work-Files werden in zentralen Multi-User-Ressourcen
gehalten und sind daher besonders Performance-relevant

Work-Files sind zentrale Multi-User-Ressourcen und daher besonders performance-relevant
(Prüfe alle SQL-Statements mit EXPLAIN)
(6) Grundsätzlich sollten ALLE lesenden SQL’s mit FOR FETCH ONLY versehen werden – soweit syntaktisch
möglich.
(7) WITH UR ist beim Lesen meist OK, hat mit der FFO-Klausel aber nichts zu tun.
(8) WHENEVER mit GO TO ist in jedem Fall mit einer „ernsthaften“ Fehlerbehandlung zu ersetzten.
(9) Alle Cursor, die geöffnet wurden, sind auch wieder zu schließen.
Aug2012
99
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Fromulieren von SQL Staterments
(10) Überlegen Sie, ob Sie beim Durchlesen ganzer Tabellen nicht auf „multi row“-FETCH
umstellen….(Performancegewinn beim Lesen bis zu 50%)
(11) Dasselbe gilt für den „multi-row“ INSERT, wenn grosse Mengen von Sätzen eingefügt werden
sollen(Performancegewinn beim INSERT >15%)
(12) Wiederholbare oder redundante SQL Statements gibt es innerhalb eines Prozesses normalerweise
nicht allzu viele. Aber geht man davon aus, dass ein Prozess mehrfach wiederholt wird, dann schon,
z.B. täglich häufig angestoßene OLTP Transaktionen.
Man stelle sich vor, eine Aktion, wie z.B. „SELECT current date“ wird pro Tag zwei millionenmal
ausgeführt! – Da ist es besser das Spezialregister „SET :hv = CURRENT DATE“ zu verwenden – aber
die gibt es nur im „embedded SQL“.
(13) Wenn die normale Verarbeitung durch mehrere funktionale Ebenen läuft, dann kann das SQLStatement zu einem einzigen zusammengefasst werden, das alle Spalten zurückgibt.
Manchmal jedoch ist es nicht offensichtlich, wo Redundanz eigentlich entsteht. – Es gibt keinen
Fehler im SQL-Statement, aber es handelt sich um den klassischen Fall des so genannten „cut and
paste“ SQL, was immer zu einem Desaster führen muss.
In unserem Fall existieren in einem Programm drei Cursor. Jeder wird über „cut and paste“ vom
Original aus aufgebaut. Jeder Cursor basiert auf einem eigenen „rule set“ und gibt ein anderes
Resultat zurück. Das SQL dieser drei Cursor wird nun zu einem SQL SELECT zusammengefasst. Die
Unterschiede der folgenden SQL SELECTs bestehen in der WHERE - Klausel und den Hostvariablen,
die die Ergebnisse aufnehmen:
Aug2012
100
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Zusammenfassen von SQL Statements
SELECT Statement1:
SELECT Statement2:
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
SELECT
FROM
WHERE
AND
AND
AND
AND
SUM(cumqty) INTO
kd_konto
ar
teile_typ
teile_mengen-klasse
gruppen#
teile_jahr
transaktion_code
AND
transaktion_datum
=
( SELECT
FROM
SUM(cumqty)
INTO
:ws-cat-1
kd_konto
ar
teile_typ
=
:ws-item-typ
teile_mengen-klasse
=
:ws-amount-class
gruppen#
=
:ws-group-num
teile_jahr
=
:ws-teile-jahr
transaktion_code
IN
(208, 400, 434, 441)
transaktion_datum
=
( SELECT
MAX(transaktion_datum)
FROM
kd_konto )
WHERE
AND
AND
AND
AND
)
AND
Aug2012
=
=
=
=
IN
:ws-item-typ
:ws-amount-class
:ws-group-num
:ws-teile-jahr
( 210, 211, 221,
223, 224, 225 )
MAX(transaktion_datum)
kd_konto )
SELECT Statements(konsolidiert):
SELECT Statement3:
SELECT
:ws-cat-2
SUM(cumqty) INTO
ar
teile_typ
teile_mengen-klasse
gruppen#
teile_jahr
transaktion_code
IN
:ws-cat-3
transaktion_datum
( SELECT MAX(transaktion_datum)
FROM kd_konto )
=
FROM kd_konto
=
:ws-item-typ
=
:ws-amount-class
=
:ws-group-num
=
:ws-teile-jahr
( 034, 100, 104, 105, 106, 332, 334, 341
SELECT
SUM( CASE WHEN transaktion_code = 034 ... = 341
THEN cumqty END )
SUM( CASE WHEN transaktion_code = 208 ... = 441
THEN cumqty END )
SUM( CASE WHEN transaktion_code = 210 ... = 225
THEN cumqty END )
INTO :ws-cat-1, :ws-cat-2, :ws-cat-3
FROM
kd_konto
ar
WHERE
teile_typ
=
:ws-item-typ
AND
teile_mengen-klasse
=
:ws-amount-class
AND
gruppen#
=
:ws-group-num
AND
teile_jahr
=
:ws-teile-jahr
AND
transaktion_code IN
( 208, 400, 434, 441, 210, 211, 221, 223,
224, 225, 034, 100, 104, 105, 106, 332, 334, 341 )
AND
transaktion_datum
=
( SELECT MAX(transaktion_datum)
FROM kd_konto )
101
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
(14) Viele "subqueries" sind in JOINs umformulierbar
(Nutze JOINs anstatt „subqueries“)
(15) Existenzprüfungen sind oft effizienter als OUTER Joins
(Nutze erprobte Methoden zur Existenzprüfung(EXISTS))
(16) Prüfen von Subqueries als einzelne Queries
(Subqueries sind zu „tunen“)
(17) Vermeiden aufwendiger SQL-Statements (nur weil sie leichter zu verstehen sind als komplexe
Formulierungen)
(Vermeide UNIONs(ALL))
(18) SQLs entsprechend der gültigen (eingesetzten Version von DB2) formulieren
(Benutze die ON-Klausel für alle JOIN-Prädikate)
(19) Überprüfen gererierter SQL-Statements
(Verwende nie von Tools generiierte SQL-Statements)
(20) Nutzen neuer (kritischer) Funktionen bei Release-Wechseln und Einbau in bestehende Abläufe.
(Sie können die Performance nachhaltig beeinflussen!)
Aug2012
102
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Existenzprüfungen
“How NOT to Check for Existence”
SELECT COUNT(*)
FROM
AGT…. WHERE…
Oft ist dies der erste Versuch, wenn man über Existenzprüfungen nachdenkt.
Aber: Man will ja nur wissen, ob EINE “row” WENIGSTENS existiert oder nicht. Es gibt keinen Grund, dazu alle
betreffenden Sätze zu lesen und zu zählen.
SELECT
1
FROM SYSIBM.SYSDUMMY1
WHERE
EXISTS
(SELECT
1
FROM
AGT…. WHERE… )
Es gilt als häufig kommuniziertes Missverständnis der EXISTS Klausel, dass die Subquery beendet wird, sobald die
Existenz einer “row” festgestellt wird und es egal ist, ob eine “row” zur Ergebnismenge gehört oder Millionen!
Leider stimmt das so nicht. Das trifft nur dann zu, wenn die Subquery eine “correlated subquery” ist. Ist sie das nicht,
so kann eine solche Query in der Performance sogar noch schlechter sein als der SELECT COUNT(*). Sie verursacht ja
die Kosten des Zugriffs auf die sysibm.sysdummy1 und die Kosten für den Zugriff auf alle “rows”, die mit der
WHERE Klausel spezifiziert wurden..
Aug2012
103
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Existenzprüfungen - – „correlated“ vs „non-correlated“ Subqueries
Wie schon erwähnt ist auch eine „non-correlated“ Subquery für die „existence“ Prüfung keine effiziente Alternative. Eine
„non-correlated“ Subquery hat, wie der Name schon sagt, keine Verknüpfung bzw. Referenz zur sogenannten „outer query”
SELECT
WHERE
STUDENT_NAME FROM
SCHOOLNUM
STUDENT_TABLE
= (SELECT
SCHL_NO
FROM SCHOOL_NAME_TABLE
WHERE
NAME = ‘PALO VERDE’);
Es wird keinerlei Information aus der “outer query” benötigt, um die “inner query” ablaufen zu lassen. DB2 verarbeitet die
“inner query” zuerst, da die “outer query” von deren Resultat abhängt. DB2 durchläuft dafür folgende Schritte:
1. Zugriff auf die “inner table” – über TS Scan oder Indexzugriff
2. Sortieren der Resultate und Entfernen der Duplikate
3. Ablage des Resultats in einer Zwischentabelle
4. Zugriff auf die “outer table”, Vergleichen alle qualifizierenden “rows” mit denen in der Zwischentabelle
SELECT STUDENT_NAME, CUM
WHERE CUM
=
FROM
STUDENT_TABLE A INNER JOIN GRADE_TABLE B
ON A.STUDENT_ID = B.STUDENT_ID
(SELECT MAX(CUM) FROM GRADE_TABLE C
WHERE A.STUDENT_ID = C.STUDENT_ID);
Hier ist es nicht möglich, die “inner query” als erstes auszuführen, da die Werte der “outer query” noch nicht bekannt sind.
DB2 durchläuft in diesem Falle folgende Schritte:
1. Zugriff auf die “outer table” – über TS Scan oder Indexzugriff
2. Für jede qualifizierende “row” der “outer table” wird die Subquery gestartet
3. Übergabe der Resultate an die “outer query” –“row“-weise
4. Prüfen der “outer query” gegen die “inner query” Resultate – “auch “row”-weise
Aug2012
104
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Existenzprüfungen
Sie erinnern sich noch ?????
SELECT
1
FROM SYSIBM.SYSDUMMY1
WHERE
EXISTS
(SELECT
1
FROM AGT…. WHERE… )
Machen wir also die „non-correlated query“ zu einer „correlated query“.
SELECT
WHERE
1
FROM
EXISTS
SYSIBM.SYSDUMMY1
(SELECT
1
FROM
X
AGT…. WHERE…
AND X.IBMREQD = X.IBMREQD
)
Die Spalte IBMREQD ist die einzige Spalte der Tabelle SYSDUMMY1. Durch die Kodierung des “always true” Prädikates in der
Subquery, wird die Subquery nicht ausgeführt, bis die erste (und einzige) “row” von SYSDUMMY1 gelesen wird.
Jetzt funktioniert die EXISTS Klausel genauso wie erwartet. SYSDUMMY1 besitzt nur 1 “row”. Diese wird gelesen und dann die
“correlated subquery” ausgeführt.
Über die EXISTS Klausel wird beim ersten Treffer die Suche beendet und die Information TRUE an die „outer query“ gegeben.
Um das Thema zu ergänzen, sollte auch folgende Möglichkeit evaluiert wrde, vor allem dann, wenn eine reine Existenzprüfung durchgeführt
werden soll:
SELECT
FETCH
Aug2012
‘Y’ FROM <table> T
FIRST ROW ONLY
WHERE
<col1> = :<hostvariable1> AND <col2> = :hostvariable2>
105
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Existenzprüfungen
“Existence check” mit OPTIMIZE FOR n ROWS….
Manche Verfasser von SQL Performance Artikeln meinen wörtlich “For existence checking in V6 and earlier, the technique
of coding a cursor with OPTIMIZE FOR ONE ROW, opening the cursor, and simply fetching one row, has generally
proven to be the existence checking method that provides the best performance.”
Aber die Kodierung eines Cursors mit der Klausel OPTIMIZE FOR ONE ROW teilt DB2 mit, dass man beabsichtigt
lediglich die erste “row” abzuholen, ohne Rücksicht darauf, wieviele “rows” zurückgeliefert werden.
Der Optimizer wird Optimierungsfunktionen wie “List”- und “Sequential”-Prefetch ausschalten, abhängig davon, ob der
Zugriffspfad davon profitieren kann oder nicht. Ohne diese Klausel entscheidet sich der Optimizer für den effizientesten
Zugriff für das Gesamtresultat. Das ist aber verständlicherweise nicht der beste Pfad für den Zugriff auf nur eine “row”.
EXEC SQL
DECLARE CURS-OPT CURSOR FOR
SELECT 1
FROM SSASIDB1.AATP_PERIOD_ATTEND
OPTIMIZE FOR 1 ROW
END-EXEC.
WHERE ABSENCE_CODES LIKE '%AAA%'
Das folgende Messbeispiel zeigt, dass ein “optimized cursor”, ja sogar ein “non-optimized cursor” sich als schneller erweist
als eine WHERE EXISTS Klausel in unserem vorangegangenen Beispiel.
Das Beispielprogramm produzierte folgende Resultate:
•
•
•
•
Exists Klausel mit “correlated subquery”:
“Non-optimized” Cursor:
“Optimized cursor”:
Select count:
6254 microseconds
1726 microseconds
1121 microseconds
2 Minuten 14 Secunden
PROBLEM: OPTIMIZE stellt keine gültige Syntax für einzelne SELECTs dar !!!!!!!
Aug2012
106
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Existenzprüfungen
“Existence check” mit OPTIMIZE FOR n ROWS….
Die FETCH FIRST (1) ROW ONLY Klausel, erstmals implementiert in DB2 V7, begrenzt die Anzahl zurückzuliefernder
“rows” auf genau eine einzige Zeile. Dabei spielt es keine Rolle, wieviele “rows” sich für die WHERE Klausel qualifiziert
haben.
Die Klausel beinhaltet auch die OPTIMIZE FOR 1 ROW Angabe.
Sie kann auch in einem “singleton select” kodiert werden, wo sie den Optimizer informiert, den besten Zugriffspfad für die
Rückgabe nur einer “row” zu suchen und nicht intern einen zweiten FETCH vorzusehen, den SQL normalerweise vornimmt,
um dann auf einen Fehlercode –811 zu laufen.
“Aufgrund der Begrenzung des Resultats auf EINE “row” und der Fähigkeit, den Optimizer über diese Absicht zu
informieren, ist diese Technik genau das, was ab DB2 V7 den „existence check“ ausmachen sollte.“
Zusammenfassung
Für einen “existence check” gilt also folgende Priorisierung:
(1) OPEN eines Cursor (optional mit der OPTIMIZE FOR ONE ROW Klausel) und Fetch einer
EINZIGEN „row“
(2) oder Nutzung der WHERE EXISTS Klausel mit einer “correlated subquery”
Sehr wichtig: Man achte auf die unterschiedlichen Möglichkeiten des ”existence checks” und wenn ein
Weg sich als zu ineffizient zeigt, sollte man einen anderen Weg gehen.
Aug2012
107
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Datumsarithmetik
Gestern, heute, morgen - Datumsarithmetik im DB2
DB2 besitzt eine Reihe von Funktionen, die für Datumsberechnungen verwendet werden können. Ähnliche Funktionen gibt es
auch für Uhrzeitberechnungen, jedoch werden diese nicht so oft Verwendung finden.
Für die Speicherung und Verarbeitung eine Datums gibt es im DB2 das spezielle Format Date. Datumsarithmetik läßt sich nur
dann nutzen, wenn das Datum in diesem Format vorliegt (Hostvariablen müssen ggf. mit der skalaren Funktion Date ()
umgewandelt werden). Analog gilt für die Uhrzeit das interne Format time und die skalare Funktion time(). Für ganz exakte
Zeitstempel gibt es noch das Format timestamp, das Datum und Uhrzeit auf die Mikrosekunde genau speichert.
Das Spezialregister current date
Das Spezialregister current date beinhaltet das aktuelle Datum und ist nahezu universell einsetzbar.
Beispiele:
INSERT INTO tabelle ( sp1,sp2,sp3 ) VALUES ( current date , :host2 , :host3 ) ;
SELECT sp1,sp2, current date FROM tabelle ...;
UPDATE ... SET sp1 = current date WHERE ...;
SELECT ... WHERE sp1 < current date
Set :hostvariable
= current date ;
analog existieren auch die Spezialregister current time und current timestamp
Aug2012
108
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Datumsarithmetik
Die einfachste Form der Datumsarithmetik besteht darin, zu einem Datum eine bestimmte Zeitspanne zu addieren oder zu
subtrahieren. DB2 erlaubt, Tage, Monate , Jahre sowie eine Kombination daraus zu addieren/subtrahieren. Dies ist überall
dort erlaubt, wo im SQL-Statement ein normaler Ausdruck stehen darf. Der Ergebnistyp ist wieder Date.
Beispiele:
Set :host1 = DATE (:host2) + 1 YEAR + 5 MONTH + 10 DAYS ;
( in der Hostvariable< host2> muss hier natürlich ein gültiges Datum stehen . <host1> enthält dann das Datum, das 1 Jahr 5
Monate und 10 Tage nach <host2> liegt )
Select ... WHERE sp1 > current date - 1 MONTH;
Selbstverständlich können auch Hostvariablen als Zeitspanne verwendet werden:
SET:datneu = Date (:datalt) + :n YEARS - :m DAYS;
Als Zeitspanne ist dabei möglich:
für ein Datum:
für eine Uhrzeit:
für einen Timestamp zusätzlich:
- Tage ( day )
- Stunden ( hour )
- Mikrosekunden ( microsecond )
- Monate ( month ) - Minuten ( minute )
- Jahre ( year )
- Sekunden ( second )
grundsätzlich erlaubt DB2 auch die Pluralformen ( also days , months , years usw. )
Wird durch eine Addition oder Subtraktion von Monaten oder Jahren ein Monatsende überschritten, so gleicht DB2 das
Ergebnis automatisch an. Ein '31.01.2001' + 1 Month würde eigentlich den 31.02.2001 ergeben, DB2 gibt hier den
Monatsletzten ( '28.02.2001' ) zurück
Aug2012
109
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Datumsarithmetik
Die Date - Duration
Das ist eine Besonderheit im DB2. Sie ist das Ergebnis einer Subtraktion zweier Date-Felder.
hre externe Darstellung ist eine Decimal(8) - Zahl, bestehend aus JJJJMMTT (positionsgerecht).
( Hinweis: Im PL/1 sollten bei Dezimalvariablen keine geraden Stellenanzahlen verwendet werden, da sie
den Compiler zu "Umwegen" zwingen können. DB2 toleriert auch ein DECIMAL(9) - Feld. )
Beispiel:
Date ( '17.03.2003') - Date ('08.01.1999')
ergibt die Dezimalzahl 40209 ( die Differenz ist 4 Jahre, 2 Monate, 9 Tage ).
Eine Date-Duration kann ihrerseits zu einem Datum addiert oder davon subtrahiert werden und ergibt
wieder ein neues Datum.
Aber Vorsicht: DATE ist auch eine Konvertierungsfunktion!
Aug2012
110
DB2 for LUW – Performance & Tuning ( AP und SQL )
Grundsätzliche Empfehlungen zum Formulieren von SQL Statements
Datumsarithmetik
Beispiel:
EXEC SQL SELECT COUNT(*)
INTO :H-COUNT00
FROM CLTSXI00
WHERE RC = 0
AND DATE(LOGDAT) <= :DATUM-CLTSXI00
END-EXEC.
Die Konvertierungsfunktion heisst also …. Keine Indexnutzung für das entsprechende Prädikat !
Lösung:
1. Man definiere das Tabellenfeld mit DATE ( PIC X(10) ) oder TIMESTAMP ( PiC X(26) )
2. Man definiere einen Index auf das Feld LOGDAT oder LOGDAT + RC
3. Man definiere die Hostvariable datumskonform
4. Man formuliere die Query wie folgt um:
SELECT
INTO
FROM
WHERE
AND
WITH UR
Aug2012
COUNT(*)
:h-count00
cltsxi00
RC = 0
LOGDAT < :datum-cltsxi00
111
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• Erläuterung der EXPLAIN Funktion





Einführung in die DB2 Optimizer Arbeitsweise
Zugriffspläne lesen, verstehen und zur SQL-Optimierung analysieren
Explain durchführen
SQL Analyse-Tools von IBM (Visual EXPLAIN, db2advis etc.)
Analyse Tools von Drittanbietern
• DB2 Optimizer Richtlinien
• DB2 Utilities und Statistiken und Systemkommandos
•
Aug2012
Monitoring und Fehleranalyse
112
DB2 for LUW – Utilities und Kommandos
Übersicht über Utilities und CLP Commands
ACTIVATE DATABASE
…..
ARCHIVE LOG
ATTACH
AUTOCONFIGURE
BACKUP DATABASE
BIND
CATALOG DATABASE , DCS DATABASE ……
CHANGE DATABASE COMMENT , …..
….
DEACTIVATE DATABASE
…..
DESCRIBE
DETACH
…….
EXPORT
FORCE APPLICATION
GET ADMIN CONFIGURATION , ALERT CFG,..….
GET CONNECTION STATE , INSTANCE , …..
…..
GET SNAPSHOT
….
IMPORT
….
INSPECT
Aug2012
LIST ACTIVE DATABASES , APPLICATIONS , ….
……
LIST HISTORY
LIST INDOUBT TRANSACTIONS
…..
LIST PACKAGES/TABLES
LIST TABLESPACE CONTAINERS
LIST TABLESPACES
LIST UTILITIES
LOAD
……
PRECOMPILE
…..
QUIESCE
….
REBIND
RECOVER DATABASE
……
REORGCHK
RESET ADMIN CONFIGURATION , ALERT CFG, ….
…..
RESTART DATABASE
RESTORE DATABASE
……
ROLLFORWARD DATABASE
……
113
DB2 for LUW – Utilities und Kommandos
Übersicht über Utilities und CLP Commands
……
RUNSTATS
SET CLIENT , RUNTIME DEGREE , SERVEROUTPUT, TABLESPACE CONTAINERS
…..
START DATABASE MANAGER
START HADR
STOP DATABASE MANAGER
STOP HADR
…..
UNCATALOG DATABASE
…..
UNQUIESCE
UPDATE ADMIN CONFIGURATION
……
Aug2012
114
DB2 for LUW – RUNSTATS
Einführung
Das Utility RUNSTATS erfasst Statistikdaten für Tabellen, Indizes und „statistical views“, um präzise Informationen für
den Optimierer zur Auswahl von Zugriffsplänen bereitzustellen.
RUNSTATS ist sinnvoll in folgenden Situationen:
•
Nachdem Daten in eine Tabelle geladen und geeignete Indizes erstellt wurden.
•
Nachdem ein neuer Index für eine Tabelle erstellt wurde.
•
Nachdem eine Tabelle mit dem REORG reorganisiert wurde.
•
Nachdem eine Tabelle und zugehörige Indizes mit UPDATE-, INSERT- oder DELETE-Operationen in erheblichem
Umfang geändert wurden.
•
Vor dem BIND von Anwendungsprogrammen, deren Leistung von kritisch ist.
•
Zum Vergleich von aktuellen und früheren Statistiken
•
Wenn der Wert für die Größe des PREFETCHING(PREFETCHSIZE) geändert wurde.
•
Nachdem der Befehl REDISTRIBUTE DATABASE PARTITION GROUP ausgeführt wurde.
•
Wenn XML-Spalten vorhanden sind.
Wird RUNSTATS nur zum Sammeln von Statistikdaten für XML-Spalten verwendet, werden Statistikdaten für Nicht-XMLSpalten, die während einer Ladeoperation oder durch eine frühere Ausführung des RUNSTATS gesammelt wurden, behalten.
Wenn Statistikdaten zuvor für einige XML-Spalten gesammelt wurden, werden diese ersetzt oder, falls die aktuelle RUNSTATSOperation diese Spalten nicht umfasst, gelöscht.
Zur Verbesserung der Leistung von RUNSTATS und zur Einsparung von Plattenspeicherplatz für Statistiken sollte man
nur die Spalten angeben, für die Datenverteilungsstatistiken erfasst werden sollen.
Aug2012
115
DB2 for LUW – RUNSTATS
RUNSTATS – Beispiele
(1) Sammeln von Statistiken nur auf einer Tabelle, auf allen Spalten – ohne “distribution statistics”:
RUNSTATS ON TABLE db2user.employee
(2) Sammeln von Statistiken nur auf einer Tabelle, auf den Spalten empid und empname – mit “distribution statistics”:
RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION ON COLUMNS (empid, empname)
(3) Sammeln von Statistiken nur auf einer Tabelle, auf allen Spalten – mit “distribution statistics” mit einem entsprechenden
“frequency limit” für die Tabelle und Übernahme der Einstellungen für “num_quantiles” aus der DB2-Konfiguration :
RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION DEFAULT NUM_FREQVALUES 50
(4) Sammeln von Statistiken nur auf einer Tabelle, auf allen Spalten – mit “distribution statistics” und auf der “column
group” JOB, WORKDEPT, and SEX:
RUNSTATS ON TABLE db2user.employee ON ALL COLUMNS
AND COLUMNS ((JOB, WORKDEPT, SEX)) WITH DISTRIBUTION
(5) Sammeln von Statistiken auf einem “set of indexes”:
RUNSTATS ON TABLE db2user.employee for indexes db2user.empl1, db2user.empl2
(6) Sammeln von Basis-Statistiken auf allen “indexes” (ausschliesslich):
RUNSTATS ON TABLE db2user.employee FOR INDEXES ALL
(7) Sammeln von Basis-Statistiken auf der Tabelle und allen “indexes” mit “sampling” auf den “detailed index statistics”:
RUNSTATS ON TABLE db2user.employee AND SAMPLED DETAILED INDEXES ALL
Aug2012
116
DB2 for LUW – RUNSTATS
RUNSTATS – Beispiele
(8) Sammeln von Statistiken auf allen Spalten, die in IX benutzt werden und für alle IX:
RUNSTATS ON TABLE db2user.employee ON KEY COLUMNS AND INDEXES ALL
(9) Sammeln von Statistiken für T1 für die individuellen Spalten c1 und c5 und auf den “column groups” (c2, c3) und (c2, c4).
“Multicolumn cardinality” ist sehr nützlich für den Optimizer, wenn er Filterfaktoren für Prädikate auf Spalten berechnen
soll, die “correlated” sind.
RUNSTATS ON TABLE db2user.T1 ON COLUMNS (c1, (c2, c3),
(c2, c4), c5)
(10)Registrieren eines “statistics profile” zum Sammeln von Statistiken auf einer Tabelle, auf allen Spalten, mit “distribution statistics” und
einem eigenen “frequency” Limit für die Tabelle, während der Wert für “num_quantiles” aus der Konfiguration genommen wird. Die
“statistics“ werden nicht modifiziert:
RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION DEFAULT
NUM_FREQVALUES 50 SET PROFILE ONLY
(11)Modifizieren eines vorher registrierten “statistics profile” durch Ändern der NUM_FREQVALUES Werte von 50 auf 30. Das
Kommando modifiziert die “statistics“ wie angegeben:
RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION DEFAULT
NUM_FREQVALUES 30
UPDATE PROFILE
(12) Sammeln der Statistiken einer Tabelle mit Aufnahme der Optionen in das “statistics profile” dieser Tabelle:
RUNSTATS ON TABLE db2user.employee USE PROFILE
Aug2012
117
DB2 for LUW – RUNSTATS
RUNSTATS – Vorgang
Der DB2 Optimizer benötigt möglichst genaue “statistics”, um so den optimalen Zugriffspfad auswählen zu können; d.h.
Angaben über Anzahl „rows“, Kardinalitäten von Werten, Schätzvorgaben zur Selektivität von Query-Prädikaten etc wären
sehr hilfreich.
Die einfachste Methode zum Erheben von Statistiken ist das Einschalten von “automatic runstats” wie im Folgenden
dargestellt. Dies ist seit DB2 Version 9.7. “default”:
DB2 UPDATE DB CFG USING AUTO_RUNSTATS ON
RUNSTATS kann CPU-intensiv sein.
Bevorzugt man die manuelle Ausführung von RUNSTATS und dies beeinflusst die Performance in der Produktion negativ,
so sollte man folgende Empfehlungen befolgen:
•
Sammeln der Statistiken auf allen Tabellen und Aktuell-Halten der Statistiken
•
Ist das RUNSTATS Kommando fertig, sollte man COMMIT absetzen, um die Locks von den Katalogtabellen wieder
zu entfernen
•
Für Systemtabellen sollte man folgende Anweisung benutzen:
REORGCHK UPDATE STATISTICS ON TABLE SYSTEM
•
RUNSTATS sollte genutzt werden, um Verteilungs- und IX-Statistiken zu erheben
RUNSTATS ON TABLE SCM.TAB WITH DISTRIBUTION
AND SAMPLED DETAILED INDEXES ALL
Aug2012
118
DB2 for LUW – RUNSTATS
RUNSTATS – Vorgang (cntn‘d)
Beispiel: das erste Kommando sammelt die “distribution statistics”, das zweite sammelt Statistiken auf dem Index TMP.I0 und die
“distribution statistics” werden nicht gespeichert.
RUNSTATS ON TABLE TMP.TAB0 WITH DISTRIBUTION
RUNSTATS ON TABLE TMP.TAB0 AND INDEXES TMP.I0
Empfehlungen:
Wenn man für eine gefüllte Tabelle einen IX anlegt (oder für die der RUNSTATS gerade Statistiken erstellt hat) , so sollten die “index
statistics” während des Indexaufbaus gesammelt werden. Im Folgenden Fragmente des CREATE INDEX Statements mit den relevanten
Optionen:
DB2 CREATE INDEX… …. COLLECT SAMPLED DETAILED STATISTICS
Man sollte nun die gesamten RUNSTATS Kommando-Optionen in einem Profile ablegen. Dieses “profile” sollt für den nächsten
RUNSTATS , der ein Profil verwenden soll, bereitgestellt sein. So sichert man die K onsistenz in der Anwendung der Optionen.
RUNSTATS ON TABLE SCM.TAB - <COMPLETE COMMAND> ...
SET PROFILE ONLY
-— Any time later _
RUNSTATS ON TABLE SCM.TAB USE PROFILE
-— Check profile
SELECT STATISTICS PROFILE
FROM SYSCAT.TABLES
WHERE (TABSCHEMA,TABNAME)
Aug2012
=
('SCM' 'TAB')
119
DB2 for LUW – RUNSTATS
Automatisieren von Utilities (und RUNSTATS)
Die DB2-Parameter
Automatic maintenance (AUTO_MAINT)
|
|-- Automatic database backup (AUTO_DB_BACKUP)
|
|-- Automatic table maintenance (AUTO_TBL_MAINT)
|
|---- Automatic runstats (AUTO_RUNSTATS)
|
|-------- Automatic statement statistics (AUTO_STMT_STATS)
|
|---- Automatic statistics profiling (AUTO_STATS_PROF)
|
|--------- Automatic profile updates (AUTO_PROF_UPD)
|
|---- Automatic reorganization (AUTO_REORG)
Das ist eine Hierarchie:
Wenn man einen “child”-Parameter auf ON setzen will, muss der zugehörige “parent” auch ON sein !
AUTO_RUNSTATS
führt automatisch RUNSTATS auf Tabellen aus... Wenig “impact” auf die anderen Prozesse (7-10%) – ohne Rücksicht
darauf auf welchen Wert UTIL_IMPACT_PRIORITY gesetzt wurde ... RUNSTATS wird dabei nur auf die Tabellen
ausgeführt,die sich um mehr als 10-15% geändert haben. db2 luw v9.7 schaltet diesen Parameter automatisch an,
wenn eine neue DB erzeugt wird...
Aug2012
120
DB2 for LUW – REORG
Übersicht - REORG
Reorganisiert einen Index oder eine Tabelle.
Vorgehensweise
Folgende Schritte sind für eine Tabellen- oder Indexreorganisation auszuführen:
1. Ermitteln Sie, ob und welche Tabellen oder Indizes reorganisiert werden müssen.
2. Wählen Sie eine Reorganisationsmethode.
3. Führen Sie die Reorganisation für die ermittelten Objekte aus.
4. Optional: Überwachen Sie den Fortschritt der Reorganisation.
5. Stellen Sie fest, ob die Reorganisation erfolgreich war. Bei einer Offlinetabellenreorganisation und einer beliebigen
Indexreorganisation erfolgt die Operation synchron; das Ergebnis ist beim Abschluss der Operation sichtbar. Bei der
Onlinetabellenreorganisation erfolgt die Operation asynchron; Details können über die Logfile abgerufen werden.
6. Erfassen Sie Statistikdaten für reorganisierte Objekte neu(RUNSTATS)
7. Führen Sie einen Rebind für Anwendungen durch, die auf reorganisierte Objekte zugreifen.
Mit der Option CLEANUP ONLY in der Index-Klausel wird eine Bereinigung ohne Neuaufbau der Indexes
vorgenommen.
Dieser Befehl kann nicht gegen Indizes von „declared temporary tables“ oder „global temporary tables“ laufen
(SQLSTATE 42995).
Aug2012
121
DB2 for LUW – REORG
REORG TABLE
Nach zahlreichen Änderungen an Tabellendaten können sich logisch sequenzielle Daten auf nicht mehr in der Sequenz
liegenden Pages befinden, sodass der DBM zusätzliche Leseoperationen ausführen muss, um auf die Daten zuzugreifen.
Wenn zahlreiche Zeilen gelöscht wurden, fallen auch zusätzliche Leseoperationen an. In diesem Fall kann man eine
Reorganisation der Tabelle durchführen, um die Tabelle mit dem Index neu zu ordnen und Speicherplatz wieder verfügbar
zu machen.
Man kann (und sollte) auch die Systemkatalogtabellen reorganisieren.
Da die Reorganisation einer Tabelle in der Regel mehr Zeit als das Aktualisieren von Statistikdaten beansprucht, kann
man den Befehl RUNSTATS ausführen, um die Statistiken auf den aktuellen Stand zu bringen. Wenn aktualisierte
Statistiken keine Leistungsverbesserungen bringen, kann evtl. ein REORG helfen.
Folgende Faktoren können darauf hinweisen, dass ein REORG erforderlich ist:
•
hohes Aufkommen an INSERT-, UPDATE- und DELETE-Aktivitäten an Tabellen
•
wesentliche Änderungen in der Leistung von Abfragen, die einen Index mit einem hoher „clusterratio“ verwenden
•
Die Leistung wird durch die Ausführung des Befehls RUNSTATS nicht besser.
•
Die Ausgabe des Befehls REORGCHK zeigt den Bedarf an einer Reorganisation an.
Aug2012
122
DB2 for LUW – REORG
REORG INDEX
Im Lauf der Modifikationen von Tabellen kann sich die Indexleistung verschlechtern.
•
Pages werden fragmentiert - es steigt der I/O Aufwand
•
Die physische Indexseitenreihenfolge stimmt nicht mehr mit der Reihenfolge der Schlüssel auf diesen Seiten überein,
was zu einem Index mit schlechter Clusterbildung führt – „sequential prefetch“ ist nicht mehr effizient und die Anzahl
der Wartezeiten auf I/O-Vorgänge erhöht sich.
•
Der Index entwickelt zu viele Levels. In einem solchen Fall sollte der Index reorganisiert werden.
Eine Indexreorganisation setzt folgendes voraus:
•
•
•
SYSADM, SYSMAINT, SYSCTRL, DBADM , SQLADM oder CONTROL für die Tabelle und ihre Indizes.
Jede Menge an freiem Speicherplatz in dem TS, in dem die Indizes gespeichert sind.
Zusätzlicher Log-Platz. REORG protokolliert seine Aktivitäten.
Wenn die Option MINPCTUSED beim CREATE INDEX angeben wurde, führt der Datenbankserver automatisch IndexPages zusammen, wenn ein Schlüssel gelöscht wird und der freie Speicherplatz geringer als der angegebene Prozentsatz ist.
Dieser Vorgang wird als Online-Indexdefragmentierung bezeichnet.
Um das IX „clustering“ wiederherzustellen, Speicherplatz freizugeben und die Anzahl von Page - Levels zu verringern,
kann man eine der folgenden Methoden anwenden:
•
•
•
Aug2012
Löschen und erneutes Erstellen des Index.
Verwenden von REORG TABLE mit Optionen, die eine Offlinereorganisation der Tabelle und ihrer IX ermöglichen.
Reorganisieren von IX „online“ mit REORG INDEXES. Diese Methode ist in einer Produktionsumgebung geeignet,
da sie Benutzern ermöglicht, die Tabellendaten zu lesen und zu schreiben, während ihre Indizes neu erstellt werden.
123
DB2 for LUW – REORG
REORG Methoden
Auswählen einer Methode zur Tabellenreorganisation
Es stehen zwei Methoden für die Tabellenreorganisation zur Verfügung: die klassische Reorganisation (offline) und die
Inplace-Reorganisation (online).
Die Offlinereorganisation ist Standard. Zur Angabe einer Onlinereorganisation verwendet man die Option INPLACE beim
REORG TABLE.
Jede Methode hat ihre Vor- und Nachteile.
Vorteile der Offlinereorganisation
•
Sie führt Tabellenreorganisationsoperationen am schnellsten aus, insbesondere wenn LOB-Daten (große Objekte) oder
LONG-Daten (Langfelddaten) nicht mit eingeschlossen werden.
•
Tabellen und Indizes haben nach Abschluss ein perfektes Clustering.
•
IX werden beim REORG einer Tabelle automatisch erneut erstellt, sodass keine separate IX-Erstellung nötig ist
•
verwendet einen TS für temporäre Tabellen zur Erstellung einer Spiegelkopie. Dadurch verringert sich der
Speicherbedarf für den TSh, in dem die Zieltabelle bzw. der Index gespeichert ist.
•
ermöglicht die Verwendung eines anderen Index als den Clusterindex zur Herstellung eines Datenclusterings.
Nachteile der Offlinereorganisation
•
Der Tabellenzugriff ist eingeschränkt: nur ein Lesen während der Sortier- und „BUILD“-Phase eines REORG
•
großer Platzbedarf für die Spiegelkopie der Tabelle, die reorganisiert wird.
•
Der REORG-Prozess bietet weniger Steuerungsmöglichkeiten: Eine Offline-REORG lässt sich nicht anhalten und
wieder starten.
Aug2012
124
DB2 for LUW – REORG
REORG Methoden
Auswählen einer Methode zur Tabellenreorganisation (cntn‘d)
Vorteile der Onlinereorganisation
•
Es besteht voller Tabellenzugriff außer während der SWITCH-Phase eines REORG
•
Mehr Steuerungsmöglichkeiten für den REORG-Prozess, der asynchron im Hintergrund ausgeführt wird. Der
Prozess kann angehalten, fortgesetzt oder gestoppt werden. Man kann zum Beispiel eine in Bearbeitung befindliche
REORG-Operation anhalten, wenn eine größere Anzahl von Aktualisierungs- oder Löschoperationen für die Tabelle
anstehen.
•
Der Prozess ist im Fall eines Fehlers wiederherstellbar.
•
Der Bedarf an Arbeitsspeicher ist geringer, weil die Tabelle inkrementell verarbeitet wird.
•
Die Vorteile der Reorganisation sind unverzüglich verfügbar, sogar vor Abschluss des REORG
Nachteile der Onlinereorganisation
•
Nicht optimales Daten- oder Indexclustering abhängig vom Typ der Transaktionen, die während eines REORG-auf
die Tabelle zugreifen.
•
Geringere Leistung als bei einem „offline“ ausgeführten REORG
•
Potenziell hoher Protokollierungsbedarf abhängig von der Anzahl der Zeilen, die versetzt werden, der Anzahl von
Indizes, die für die Tabelle definiert sind, und der Größe dieser Indizes.
•
Potenzielle Notwendigkeit einer nachfolgenden Indexreorganisation, da Indizes gepflegt, jedoch nicht neu erstellt
werden.
Aug2012
125
DB2 for LUW – REORG
REORG Methoden
Auswählen einer Methode zur Tabellenreorganisation (cntn‘d)
Vergleich zwischen Online- und Offlinereorganisation
Merkmal
Offlinereorganisation
Onlinereorganisation
Leistung
Schnell.
Langsam.
Clusterfaktor der Daten bei Abschluss
Gut.
Nicht optimal.
Gemeinsamer Zugriff (auf die Tabelle)
Reicht von 'kein Zugriff' bis 'Lesezugriff'.
Reicht von 'Lesezugriff' bis 'Vollzugriff'.
Beträchtlich.
Nicht erheblich.
Nicht erheblich.
Kann beträchtlich sein.
Weniger Kontrolle.
Mehr Kontrolle.
Nicht wiederherstellbar.
Wiederherstellbar.
Wird ausgeführt.
Wird nicht ausgeführt.
Unterstützung für alle Tabellentypen
Ja
Nein
Möglichkeit zur Angabe eines anderen Index als
des Clusterindex
Ja
Nein
Verwendung eines Tabellenbereichs für
temporäre Tabellen
Ja
Nein
Datenspeicherplatzbedarf
Protokollspeicherplatzbedarf
Benutzerkontrolle (Möglichkeit zum Anhalten
und Neustarten des Prozesses)
Wiederherstellbarkeit
Indexneuerstellung
Aug2012
126
DB2 for LUW – REORG
REORG Methoden
Auswählen einer Methode zur Tabellenreorganisation (cntn‘d)
Unterstützte Tabellentypen für Online- und Offlinereorganisationen
Tabellentyp
Offlinereorganisation unterstützt
Tabellen mit mehrdimensionalem Clustering (MDCJa1
Tabellen)
Onlinereorganisation unterstützt
Nein
Bereichsclustertabelle (RCT-Tabelle)
Nein2
Nein
Tabellen im Anfügemodus
Nein
Nein3
Tabellen mit Langfelddaten oder LOB-Daten (LOB,
große Objekte)
Ja4
Nein
Systemkatalogtabellen: SYSIBM.SYSDBAUTH,
SYSIBM.SYSROUTINEAUTH,
SYSIBM.SYSSEQUENCES, SYSIBM.SYSTABLES
Ja
Nein
Anmerkungen:
1.
Da das Clustering automatisch durch MDC-Blockindizes sichergestellt wird, dient die Reorganisation einer MDC-Tabelle lediglich zur
Freigabe von Speicherplatz. Es können keine Indizes angegeben werden.
2.
Der Bereich einer RCT-Tabelle (Bereichsclustertabelle) bleibt stets in Clustern angeordnet.
3.
Eine Onlinereorganisation kann ausgeführt werden, nachdem der Anfügemodus (APPEND) inaktiviert wurde.
4.
Die Reorganisation von Langfelddaten oder LOB-Daten (LOB, Large Object) kann eine geraume Zeit in Anspruch nehmen und führt zu keiner
Verbesserung der Abfrageleistung. Sie sollte nur zur Freigabe von Speicherplatz ausgeführt werden.
Aug2012
127
DB2 for LUW – REORG
REORG Beispiele
(1) Zum Reorganisieren einer Tabelle um Speicherplatz zu gewinnen und dabei den temporären TS mytemp1 zu verwenden,
nutzt man folgende Anweisung:
db2 reorg table homer.employee use mytemp1
(2) Zum Reorganisieren von Tabellen in einer “partition group”, bestehend aus den Knoten 1, 2, 3, und 4 eines “vier-nodesystems” nutzt man folgende Anweisung:
db2 reorg table employee index empid on dbpartitionnum (1,3,4)
db2 reorg table homer.employee index homer.empid
on all dbpartitionnums except dbpartitionnum (2)
(3) Um die “pseudo deleted keys” und “pseudo empty pages” in allen IX der Tabelle EMPLOYEE aufzuräumen, während
andere Trasaktionen lesend und schreibend auf die Tabelle zugreifen können sollen, gilt folgendes:
db2 reorg indexes all for table homer.employee allow write access cleanup only
(4) Um die “pseudo empty pages” in allen IX der Tabelle EMPLOYEE aufzuräumen, während andere Trasaktionen lesend und
schreibend auf die Tabelle zugreifen können sollen, gilt folgendes:
db2 reorg indexes all for table homer.employee allow write access cleanup only pages
(5) Um die Tabelle EMPLOYEE unter Nutzung des “system temporary tablespace” TEMPSPACE1 als “work area” zu
reorganisieren:
db2 reorg table homer.employee use tempspace1
Aug2012
128
DB2 for LUW – BACKUP & RESTORE
Übersicht
Eine Datenbank kann aufgrund von Hardware- oder Softwarefehlern (oder beidem) unbrauchbar werden.
Irgendwann treten Speicherprobleme, Stromausfälle oder Anwendungsfehler auf, und jedes Fehlerszenario erfordert
unterschiedliche Recoveryaktionen. Man schützt die Daten vor Verlust, indem man eine gut erprobte
Recoverystrategie bereithhält. Bei der Entwicklung der Recovery-Strategie sollte man unter anderem folgende Fragen
beantworten können:
•
Soll die Datenbank wiederherstellbar sein?
•
Wie viel Zeit steht für die Recovery der Datenbank zur Verfügung?
•
In welchen Abständen werden Backup-Operationen durchgeführt?
•
Wie viel Speicher kann für Backupkopien und Archivprotokolldateien zugeordnet werden?
•
Sind Backups auf TS-Eebene ausreichend, oder muss die gesamte Datenbank gesichert werden?
•
Sollte ein „ready“ System manuell oder über HADR (High Availability Disaster Recovery) konfiguriert werden?
Eine Strategie zur Recovery der Datenbank sollte sicherstellen, dass alle Informationen verfügbar sind, wenn sie für
eine Recovery der Datenbank benötigt werden.
Sie sollte einen Zeitplan für regelmäßige Backups enthalten. Im Fall von partitionierten Datenbanken sollten auch
nach einer Skalierung des Systems, d. h. nach dem Hinzufügen oder Löschen von Datenbankpartitionsservern oder knoten, Backups durchgeführt werden können.
Die Gesamtstrategie sollte auch Prozeduren zum Wiederherstellen von Befehlsscripts, Anwendungen,
benutzerdefinierten Funktionen, als Code gespeicherter Prozeduren in Betriebssystembibliotheken sowie von
Ladekopien berücksichtigenn.
Aug2012
129
DB2 for LUW – BACKUP & RESTORE
Übersicht
Beispiele
1. Hier wird die „database” WSDB, definiert auf allen 4 “database partitions“, nummeriert von 0 bis 3, gesichert. Der Pfad
/dev3/backup kann von allen “database partitions” bearbeitet werden. Die Database Partition 0 ist die “catalog partition”.
Der folgende Befehl startet einen “offline backup” aller “database partitions” von WSDB auf /dev3/backup. Er muss
von der „database partition” 0 abgesetzt werden:
db2 BACKUP DATABASE wsdb ON ALL DBPARTITIONNUMS TO /dev3/backup
Der Backup wird auf allen Partitions simultan gefahren. Alle vier “database partition backup images” werden im
Directory /dev3/backup abgelegt. Dieses kann ein “shared directory” sein, das von mehr als einer Partition zugegriffen
werden kann oder aber es kann ein “locally-mounted” Directory sein, das von jeder Partition individuell oder bearbeitet
werden kann. Man kann aber auch eine Kombination aus beidem haben.
Aug2012
130
DB2 for LUW – BACKUP & RESTORE
Beispiele - BACKUP
2. Hier wird die DB SAMPLE auf einen TSM Server gesichert. Es werden 2 parallele TSM Client Sessions genutzt.
DB2 kalkuliert die optimale Puffergröße für die Umgebung
db2 BACKUP DATABASE sample USE TSM OPEN 2 SESSIONS WITH 4 BUFFERS
3. Im folgenden Beispiel wird der Backup auf “table space-level” für die TS “syscatspace”, “userspace1” der “database”
payroll auf Band durchgeführt:
db2 BACKUP DATABASE payroll TABLESPACE (syscatspace, userspace1) TO/dev/rmt0, /dev/rmt1
WITH 8 BUFFERS WITHOUT PROMPTING
4. Das Schlüsselwort USE TSM OPTIONS kann zur Spezifikation der TSM Information für die Backup Operation
eingesetzt werden. Hier wird gezeigt, wie ein VOLL qualifizierter “file name” mit USE TSM OPTIONS angegeben
werden kann:
db2 BACKUP DB sample USE TSM OPTIONS @/u/dmcinnis/myoptions.txt
Die Datei myoptions.txt enthält folgende Information: -fromnode=bar -fromowner=dmcinnis
5. Im Folgenden wird eine wöchentliche “incremental backup” Strategie für eine “recoverable database” umgesetzt. Sie
umfasst eine wöchentliche “full database” Backup Operation, einen täglichen “non-cumulative (delta)” Backup und
einen „mittwöchentlichen“ „cumulative (incremental)“ Backup:
(Sun)
(Mon)
(Tue)
(Wed)
(Thu)
(Fri)
(Sat)
Aug2012
db2
db2
db2
db2
db2
db2
db2
backup
backup
backup
backup
backup
backup
backup
db
db
db
db
db
db
db
sample
sample
sample
sample
sample
sample
sample
use tsm
online incremental
online incremental
online incremental
online incremental
online incremental
online incremental
delta use
delta use
use tsm
delta use
delta use
use tsm
tsm
tsm
tsm
tsm
131
DB2 for LUW – BACKUP & RESTORE
RESTORE - Übersicht
In der einfachsten Form des DB2-Befehls RESTORE DATABASE muss man lediglich den Aliasnamen der Datenbank
angeben, die man wiederherstellen möchte. Beispiel:
db2 RESTORE DB sample
Da die Datenbank SAMPLE vorhanden ist und beim Absetzen des Befehls RESTORE DATABASE ersetzt wird, wird in
diesem Beispiel die folgende Nachricht zurückgegeben:
SQL2539W Achtung! Restore in eine bestehende Datenbank, die mit der
Datenbank des Backup-Images identisch ist. Die Datenbankdateien werden
gelöscht.Fortfahren ? (j/n)
Gibt man j an, sollte die Restoreoperation erfolgreich beendet werden können.
Für den Restore einer Datenbank ist eine exklusive Verbindung erforderlich, d. h., es können keine Anwendungen für die
Datenbank ausgeführt werden, sobald die Operation gestartet ist. Das Restore-Utility verhindert, dass andere
Anwendungen vor der erfolgreichen Beendigung der Operation auf die Datenbank zugreifen.
Der Restore eines TS kann hingegen online erfolgen.
Ein TS kann erst nach der erfolgreichen Beendigung der Recoveryoperation (und evtl. anschließender ROLLFORWARDRecovery) wieder verwendet werden.
Bei Tabellen, die sich über mehrere TS erstrecken, ist es ratsam, die betreffende Gruppe von TSen zusammen zu sichern
(und wiederherzustellen).
Bei der partiellen oder selektiven Restoreoperation kann man ein Backup-Image auf TS-Ebene oder ein Image eines
vollständigen Datenbank-Backups verwenden, aus dem man mindestens einen TS auswählt. Alle Protokolldateien, die
diesen TS ab dem Zeitpunkt der Erstellung des Backup-Images zugeordnet wurden, müssen vorhanden sein.
Aug2012
132
DB2 for LUW – BACKUP & RESTORE
RESTORE – Vorgehensweise und Beispiel
Es wird angenommen, dass eine wiederherstellbare Datenbank mit dem Namen SAMPLE vorhanden ist und diese drei
Datenbankpartitionen enthält:
Datenbankpartition 1 enthält die TSe SYSCATSPACE, USERSP1 und USERSP2 und ist die Katalogpartition.
Datenbankpartition 2 enthält die TSe USERSP1 und USERSP3.
Datenbankpartition 3 enthält die TSe USERSP1, USERSP2 und USERSP3.
Die folgende Prozedur veranschaulicht die Anwendung der Befehle RESTORE DATABASE und ROLLFORWARD
DATABASE über den CLP zur erneuten Erstellung der gesamten Datenbank bis zum Ende der Log-Protokolle.
1. Ausführen des Befehls RESTORE DATABASE mit der Option REBUILD in Datenbankpartition 1:
db2 RESTORE DB sample REBUILD WITH ALL TABLESPACES IN DATABASE
TAKEN AT BK31 WITHOUT PROMPTING
2. Ausführen des Befehls RESTORE DATABASE mit der Option REBUILD in Datenbankpartition 2:
db2 RESTORE DB sample REBUILD WITH ALL TABLESPACES IN DATABASE
TAKEN AT BK42 WITHOUT PROMPTING
3. Ausführen des Befehls RESTORE DATABASE mit der Option REBUILD in Datenbankpartition 3:
db2 RESTORE DB sample REBUILD WITH ALL TABLESPACES IN DATABASE
TAKEN AT BK43 WITHOUT PROMPTING
4. Ausführen des Befehls ROLLFORWARD DATABASE mit der Option TO END OF LOGS auf der Katalogpartition:
db2 ROLLFORWARD DB sample TO END OF LOGS
5. Ausführen Befehl ROLLFORWARD DATABASE mit der Option STOP:
db2 ROLLFORWARD DB sample STOP
Aug2012
133
DB2 for LUW – Utilities und Kommandos
DB2 Systemkommandos
Alle DB2 Systemkommandos werden unter sqllib/bin installiert. Einige der wichtigsten Kommandos in
DB2 findet man in den folgenden Listen. Mehr Informationen gibt es in den DB2 Manuals, und dort im
Mnaual “DB2 Command Reference”:
http://www.ibm.com/support/docview.wss?rs=71&uid=swg27015148
Instance Kommandos
Aug2012
134
DB2 for LUW – Utilities und Kommandos
Handling genereller „database tasks“
Aug2012
135
DB2 for LUW – Utilities und Kommandos
DB2 DAS Instance Kommandos
DB2 informationale Kommandos
Aug2012
136
DB2 for LUW – Utilities und Kommandos
DB2 grafische Tools
Aug2012
137
DB2 for LUW – Die Zukunft mit Tools
DB2 Interfaces in Zukunft
Anmerkung:
• das Control Center und die damit verbundenen Komponenten werden seit der
Version 9.7 nicht mehr empfohlen und in späteren Releases entfernt werden
• Empfehlung: Anwenden der neuen “suite” von GUI Tools zur DB2 Datenverwaltung
und zur Verwaltung von “data-centric” Programmen.
• Diese neuen Tools umfassen
 IBM Data Studio,
 OptimDevelopment Studio und
 Optim Database Administrator.
Aug2012
138
DB2 for LUW – Applikationsentwicklung
Kapitelinhalt
• Erläuterung der EXPLAIN Funktion





Einführung in die DB2 Optimizer Arbeitsweise
Zugriffspläne lesen, verstehen und zur SQL-Optimierung analysieren
Explain durchführen
SQL Analyse-Tools von IBM (Visual EXPLAIN, db2advis etc.)
Analyse Tools von Drittanbietern
• DB2 Utilities und Statistiken
• DB2 Optimizer Richtlinien
•
Aug2012
Monitoring und Fehleranalyse
139
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Annahmen
Sie sind KEIN Experte
Sie wollen nicht notwendigerweise ein Experte werden
Sie wollen nur Ihren Job gut machen
Sie wollen nicht alle möglichen Wege zum Ziel wissen
Ein guter Weg zum Ziel reicht
80% optimaler Performance ist ausreichend (?)
Performance “Wahrheiten”
Es gibt IMMER ein “bottleneck”!
Die 5 fundamentalen potentiellen “bottlenecks” sind:
1.
Application
2.
CPU
3.
Memory
4.
Disk
5.
Network
Aug2012
140
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
„Sizing“ einer Konfiguration - Idealerweise kennt man:







Die Applikation
Die Lastanforderungen für die Verarbeitung
Die Anzahl „concurrent users“/“jobs“
Die größten Tabellen
Typische Query Scenarios
Die Größe der Ergebnisse, die erzeugt werden
Die “response time” Zielsetzungen für Last und Queries

…
„Sizing“ – Anhaltspunkte( “Rules of Thumb”)




Aug2012
Die Wahl der Plattform
DPF („database partitioning facility“) - GB/node
Memory
Disk - „Space“ / „Spindles“ (LUNs)
141
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Plattform Auswahl

DB2 ist für alle größeren Plattformen optimiert (Linux, AIX, Windows, Solaris, HP-UX)

Weitaus mehr als eine Frage der Performance
 Integration mit anderen Systemen
 „Skills“ / Bedienbarkeit / Verwaltbarkeit…
 €€€€

Oft gibt es mehr als 1 “gute” Wahl !!!!!
DB2 mit und ohne DPF

Die Unterschiede werden KLEINER: Die Produkte konvergieren

Wenn, dann sollte man mit einem DPF auf einem Knoten starten (“1-node” DPF)
 Ähnliche Performance
 Enthält die Funktion zum späteren Wachsen

DPF ist wenig üblich für - OLTP, ERP, CRM

DPF ist üblich bei – „Business Intelligence“ Anwendungen
Aug2012
142
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Memory! – Wieviel brauche ich ????

Das hängt von vielen Faktoren ab….
 Von der Anzahl User(“connections”)
 Von der geplanten Query Last
 Davon, ob andere Software die Maschinen ebenfalls benutzen soll oder nicht (ist zu messen)

Heute sind 64-bit Prozessoren üblich
 Aber das OS (und DB2) kann immer noch nur 32-bit
 Trotzdem würde man 64-bit Durchsatz bevorzugen
4GB / Prozessor ist dabei heute ein guter “starting point“
Disk! Wieviel GB braucht man?

Mehr als man denkt!

Man vergesse NICHT:
 Working storage
 Tempspace
 Indexes, AST’s („abstract
syntax trees“) etc.
Raw data x 4 (unmirrored)
Raw data x 5 (RAID5)
Raw data x 8 (RAID10)
Aug2012
143
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Disk! Wieviele “Spindles” sind notwendig?

Man beginne mit einem ausbalancierten System

Nicht zu viele kleine Disks

Nicht zu wenige große Disks ( verursachen womöglich I/O „bottlenecks“)

Unterschiedliche Anforderungstypen

„Latency“ (Wartezeiten)

Durchsatz
Letztendlich sollten es 6-10 Disks pro CPU”core” sein und zusätzliche
Laufwerke für die Logs.
Physical Database Design

1 “database” pro Instanz

Der “create database” sollte möglicht EINFACH sein: Man nutze die „default
tablespace“ Definition

Wenn möglich mit AUTOMATIC STORAGE Option (seit 8.2.2):
CREATE DATABASE DB2 AUTOMATIC STORAGE YES ON X

Erweiterungen/Anpassungen später über “CREATE / ALTER
TABLESPACE”
Vergrößern Sie nicht einfach den “default Tablespace“ mit dem
„createdb“ Befehl
Aug2012
144
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Auswahl der Page Size

Nutzen Sie nie mehr als 2 Page Größen

Beispielsweise 4K und 16K oder 8K und 32K

OLTP: kleinere Pages sind besser (e.g. 4K, 8K)

DSS: Grosse „pages sizes” bieten häufig Vorteile (e.g. 8K, 16K, 32K)
Seit 8.2.2 kann man jede beliebige “page size” als “base page size”
für die DB wählen
Tablespace Design

Stellen Sie sicher, dass „temp tablespaces“ existieren

1 pro “page size”

Die Anzahl “tablespaces” sollte möglichst klein sein

1 für “look up” Tabellen in einer “single node nodegroup”

1 pro “fact table” (größte Tabellen)

1 für alle anderen
Verteilen Sie jeden “tablespace” über mehrere / alle Disks
Aug2012
145
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Die Optimierung im Schema

Man muss entscheiden, WIE die Daten strukturiert werden sollen

Verteilen der Daten über die Knoten: Mit DPF „hash-partitioning“

Die Datenorganisation: Mit MDC („multi dimensional clustering“)

Hilfreiche andere Datenstrukturen

Existieren die richtigen Indizes? “Clustered”, “clustering”, “include columns” für “unique” IX

Können “Materialized query tables” (MQT) helfen?

Man sollte den Einsatz des Design Advisor überlegen
Was nun - DMS vs. SMS

Es gibt keine generelle Empfehlung (mehr)

DMS “raw” kann am schnellsten sein

DMS “file” wird immer mehr der bevorzugte Mechanismus
o Performance annähernd RAW
o Flexibilität annährend SMS (besonders seit 8.2.2.)

SMS ist immer noch die beste Wahl für TEMP TS (außer bei sehr großen “temps”)

Man kann auch einen Mix beschließen
Seit 8.2.2 läuft “direct I/O” (“concurrent I/O”) auch auf “temps” (SMS
und DMS File)
Aug2012
146
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Tablespace Tips für RAID Systeme

bei DSS Last

Man wählt eine EXTENTSIZE, die ein Vielfaches der RAID “stripe size” ist (idealerweise,
„raid_stripe_size“ * „num_disks_in_stripe“ = „extent_size“)

Kann die Notwendigkeit nach sich ziehen, DB2_PARALLEL_IO zu setzen ( wenn nur 1 Container im
TS)

bei OLTP Betrieb:

Ähnliches Layout wie bei DSS, aber das “prefetching” auf den TS sollte ausgeschaltet sein, wenn die
# der “prefetched pages read” gering ist: db2 “alter tablespace <tbname> prefetchsize 0”
Typischerweise haben die RAID “controller”/”Storage Manager” Caches, die selektiv für
“read”/”write” caching eingeschaltet werden können

Für DSS:

Einschalten des “read cache” mit “read ahead” Funktion – sinnvoll für “prefetching“

Einschalten des ”write cache” für “temps”

Für OLTP:

Ausschalten von “read”, als auch “write cache” bei großen Datenmengen und
Zugriff vorwiegend “random”

Einschalten des “read cache” bei kleinen Datenmengen und begrenztem Memory
(einige “caches” mit 2GB oder mehr)
Es macht sich bezahlt, zu verstehen, was in einem RAID Subsysystem passiert
Aug2012
147
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Table Design

Es ist OK mehrere Tabellen in einem Tablespace zu platzieren

Einmal definiert, wählt man die entsprechenden Optionen über ALTER Table

APPEND MODE – wenn die Inserts am Tabellenende erfolgen (ALTER TABLE ... APPEND ON)

LOCKSIZE – “table level locking” (ALTER TABLE ... LOCKSIZE TABLE)

PCTFREE – Reservieren von “space” nach “load”/”reorg” (ALTER TABLE ...PCTFREE 10)

“constraints” sollten nach der Erstellung der IX erfolgen
Beim Laden von Tabellen: “fastparse“, „totalfreespace“
Index Design

Man wähle die angemessenen Optionen

PCTFREE – sollte 0 sein bei “read-only tables”

PAGE SPLIT HIGH/LOW – für aufsteigend zu bgeschreibende IX

CLUSTER – Definition von “clustering index”

INCLUDE “columns” - extra “cols” in “unique” IX für “index-only” Zugriffe

ALLOW REVERSE SCANS – macht IXe “bi-directional”
CREATE INDEX ... ON ... (CUSTKEY) INCLUDE (C_ADDRESS) COLLECT STATISTICS …
Seit 8.2.2 kann man auch auf IX auf “global temporary tables”
COLLECT STATISTICS einsetzen
Aug2012
148
DB2 for LUW – Monitoring & Fehleranalyse
Tuning “Knobs”
Die „Magie"
optimaler
Performance ...
Performance Anleitung
Konfigurationsparameter
„registry settings“
Aug2012
149
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Operating System Tuning

Man sollte jedenfalls sicherstellen, dass man auf dem aktuellen „maintenance level“ ist !

„Paging space“

Auf 2 oder mehr separaten Disks

2x “real memory”

Option vmtune minperm/maxperm

Besonders bei SMS (AIX)
vmstat/iostat sollten regelmäßig genutzt werden!
Performance Configuration Advisor

Bietet ein “starter set” von “configuration” Parametern:
Database und Database Manager

Verfügbar über

Das DB2 CC / DataStudio

Einen API Call

Den „Command Line Processor“ (CLP)
DB2 AUTOCONFIGURE … APPLY DB AND DBM…
… eine ideale Methode zum STARTEN!!
Aug2012
150
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Database Manager Config Parms

INTRA_PARALLEL: grundsätzlich auf NO – nur bei geringer CPU Nutzung und ausschliesslich
komplexem SQL „environment“ auf YES

NUM_INITAGENTS / NUM_POOLAGENTS: durchschn. Anzahl erwarteter „concurrent“ User

SHEAPTHRES: abhängig von der Anzahl sort-intensiver „concurrent“ Users – normalerweise 10 x
– 100 x SORTHEAP

RQRIOBLK and ASLHEAPSZ: Erhöhen bei großen Resultatsmengen
Der “snapshot monitor” ist sehr gut geeignet, diese und noch mehr
Parameter zu überwachen
Database Configuration Parameters

DFT_DEGREE: typischerweise auf -1 gesetzt (ANY)

DFT_QUERYOPT: „Default” 5, häufig 2 oder 7 als Alternativen

APPLHEAPSZ, PCKCACHESZ: Erhöhen bei mehr Applikarionen/Packages

SEQDETECT: normalerweise auf dem „default” YES
SET CURRENT DEGREE und die BIND Option DEGREE bestimmen
den gewünschten Parallelitätsgrad beim Compile
Aug2012
151
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
BUFFERPOOLs

Ein einzelner höchst wichtiger Tuning Parameter

NIEMALS auf “default” stehen lassen

Normalerweise sind nur einige wenige BP definiert: mind. 2 für jede Page Größe ( 1Temp, 1 für
den Rest)

Man sollte SYSBUFFERPOOLS prügen nicht BUFFPAGE

SOFTMAX: kontrolliert die „dirty page writes“

Workload abhängig – beeinflusst Performance und “recovery time”

Kleiner Wert = kürzere “recovery time” = potentiell mehr “writes”

Hoher Wert = längere “recovery time” = weniger “writes” = potentiell bessere Performance
Ein vollständiges Bild ergibt die Kombination aus DB2 “monitoring“
und OS „monitoring“
Aug2012
152
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
SORTHEAP

Kritisch beim unterschiedlichen Einsatz für OLTP und DSS

Bei OLTP: klein - besonders bei hoher konkurrierender Verarbeitung – beeinflusst das PLAN
Optimizing

Bei DSS: Betrifft beide “private” und “shared” (SMP) Sorts, sowie “Sort”/”hash join”/
“table”-Verarbeitung im Speicher
Man beobachte die Schlüsselwerte im Monitor Output
Logging

Database Logs müssen auf ihre eigenen Disks geschrieben werden: permanent… gespiegelt
über “write-cache”….

LOGFILSIZ: sollte über den „default“ erhöht werden … 5000 4K Pages oder mehr

LOGPRIMARY: alle Logs sollten als „primary logs“ allokiert sein. LOGSECOND sollte nur für
„emergency space“ eingesetzt werden

LOGBUFSZ : sollte auf 512 oder mehr erweitert werden (nur wenn „log pages read“ hoch ist

MINCOMMIT: sollte auf dem „default“ 1 stehen bleiben (typischerweise nie
höher als 2-3)
Man nutze DB2 und OS Tools, um die Log Aktivitäten zu beobachten
Aug2012
153
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
I/O Configuration Parameters

NUM_IOSERVERS: einen pro Disk (bzw.pro RAID „array“ mit DB-Daten; Limit: 2 x #CPUs

NUM_IOCLEANERS: einen pro CPU

CHNGPGS_THRESH: niedriger als der „default“ ist ein empfohlener Wert; z.B. 40

Man kann auch die Funktion DB2_ALTERNATE_PAGE_CLEANING einsetzen
Die Empfehlungen im Administration helfen hier bei der Konfiguration der
“database“

“File system caching” beeinflusst die Performance sowohl von SMS als auch DMS „file
tablespaces“

In einem 64bit DB2 mit mehr als 3GB Memory, läßt man die Bufferpools das “caching” erledigen
und schaltet den “file system cache” aus

In einem 32 bit DB2 mit mehr als 3GB Memory, sollte man überlegen, den “system cache “ für TS
einzuschalten, “rows” verwalten, die häufig wiedergelesen werden; z.B: nutzt man dann den “file
system cache” als Erweiterung des “db2 bufferpool caching”

LOBS werden ebenfalls nicht im Bufferpool “gecached”, so sollte man sie , wenn möglich, im “file
system cache” vorhalten
Seit V8.2 kann man die Nutzung des “file system cache “ pro TS kontrollieren
OFF:
ON:
Aug2012
ALTER TABLESPACE <tablespace name> ... NO FILE SYSTEM CACHING
ALTER TABLESPACE <tablespace name> ... FILE SYSTEM CACHING
154
DB2 for LUW – Monitoring & Fehleranalyse
Fundamentale Aspekte zur Performance
Registry Settings

Typischerweise zum Einschalten von sehr neue Funktionen genutzt

Neue Features: DB2LINUXAIO = true (seit 8.2.2)

Seltene und unübliche Funktionen (use with caution)

DB2_SKIPINSERTED (8.2.2)

DB2_SKIPDELETED

DB2_EVALUNCOMMTTED
Überprüfen Sie die README Files jedes “fixpack” auf “new Features“
Aug2012
155
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Quellen der DB2 Performance Probleme
Aug2012
156
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#1: zu wenige Disks
•
Man muss I/O und CPU Nutzung ausbalancieren
 Databases sind I/O intensiv – bei Random I/O und “sequential” I/O
•
Erkennung über:
 Hohe I/O “waits”, hohe Disk Nutzung
 Das System arbeitet gut, bei geringer Belastung - und kippt unter Last “aus den Latschen”
•
Man kann berechnen:
 Random I/O’s (io’s/txn * #txn’s/sec = io’s/sec)
 Sequentielle I/O‘s als MB/sec
•
Lösung:
 Hinzufügen von Disks/LUN‘s und Rekonfigurieren der Disk Umgebung
Aug2012
157
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#2: Schlechte Konfiguration der Bufferpools
•
Man sollte den “real memory” gut genug / optimal nutzen
•
Man achte auf „defaults“:
 z.B. der DB Konfigurationsparameter “Buffpage” wird nur wirksam, wenn BUFFPOOL Größe mit –1 im
Katalog angelegt ist
•
Erkennung über:
 Speicher ist unterbeschäftigt
 Disks sind mehr beansprucht als erwartet
 Monitoring auf „Table Level“ I/O
•
Lösung:
 Review der TS-Zuweisung auf die Bufferpools
 Erhöhen der BP Größen
Aug2012
158
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#3: Logs schlecht konfiguriert
•
Logs werden ausschliesslich sequentiell geschrieben
 Ein Engpass für alle Transaktionen
•
Sowohl logische als auch physikalische Eigenschaften spielen eine Rolle
•
Erkennung über:
 Suchen nach “hot” Disk Scenarios
 Unerwartet schlechte Antwortzeiten
•
Lösung:
 Stellen Sie sicher, dass das Log isoliert ist und “write-cache” unterstützt wird
 Konfigurieren Sie Logs gemäß maximaler Performance
 Man kann “raw logs” oder auch “solid-state” Disk Log Devices einsetzen, um ein
Maximum an Logging Performance zu erhalten
Aug2012
159
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#4: SORTHEAP zu groß für OLTP
•
Große „sortheaps“ verursachen Sorts
•
Vorsicht beim Skalieren der user mit großen “sortheaps“
•
Erkennung über:
 Hoher Speicherverbrauch, „paging“
 Hoher Verbrauch an „temp“-Speicher
 Große Anzahl Sorts, lange Sortierzeiten (im Monitor)
•
Lösung:
 Reduzieren der “sortheap” Größe, Erhöhen der “sheapthres”
 Tuning der SQL’s mit hohen SORT-Anforderungen
Aug2012
160
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#5: Tempspace schlecht konfiguriert
•
Wichtig ist das Tuning für beide Arten von Queries: DSS und OLTP
•
Erkennung über:
 Hohe Disk-Nutzungsrate
 Tablespace ist gefüllt mit mehreren „temp Tablespaces“
•
Lösung:
 NUR 1 “temp tablespace” pro Page-Größe
 Verteilung über eine möglichst große Anzahl Disks
Aug2012
161
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#6: Fehlende oder „out-of-date“ Statistiken
•
Der intelligente DB2-Optimizer benötigt aktuelle Statistiken
 Man achte auf die Granularität, die dabei möglich ist
•
Erkennung über:
 Lang laufende Queries
 Erklärungsbedürftige Zugriffspläne
•
Lösung:
 Die richtigen Statistiken (RUNSTATS) auf alle Tabellen
 Einsatz von Detail- und Verteilungs-Statistiken
 Erhöhen der Parameter num_quantiles und num_freq_values
 “automatic” RUNSTATS
 Ein REORGCHK kann Statistiken für ALLE Tabellen anfordern
Aug2012
162
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#7: schlecht oder gar nicht „getuntes“ SQL
•
Je besser SQL formuliert ist, desto einfacher ist es zu evaluieren
 SQL ist sehr mächtig – Nutzen Sie diese „power“
•
Erkennung über:
 Dynamische SQL „snapshots“
 Suchen der „long-running“ SQl Statements
 Vergleich von „rows to read“ gegen „rows to be returned“
 Weitere Verfeinerungen in der Applikation
•
Lösung:
 Formulieren Sie nur, was Sie benötigen / wünschen
 Befolgen Sie die SQL-Tuning-Regeln !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 Setzen Sie den Design Advisor ein
Aug2012
163
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#8: „concurrency“ Probleme
•
Manche “concurrent” Users benötigen Anleitung
•
Erkennung über:
 Lange „lock wait“ Zeiten
 „lock timeouts“
 Schlechter Durchsatz
•
Lösung:
 Auswahl des passenden “isolation level”
 Aktuelle, passende Index-Strukturen (type-2, “compressed”, Block-IX
etc.)
 Vergrößern der “lock list” ( evtl. maxlocks)
 Setzen von “locktimeout” auf einen anderen Wert als -1
 Kürzere Transaktionen
 SKIPINSERTED oder SQL-Statements mit WITH UR / FOR FETCH ONLY
Aug2012
164
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#9: Naive JAVA Applikationen
•
Es gibt Tausende von Möglichkeiten JAVA zu schreiben – nicht alle sind OPTIMAL
•
Erkennung über:





Schlechte Trefferraten im „package cache“
Hohe CPU/Memory Verbräuche auf der „client“-Seite
Hohe Netzverweilzeiten
CLI Trace
JDBC Trace für alle DB2 „data sources“ (z.B: dataSource.setTraceFile(“<path
to an existing
directory>"); dataSource.setTraceLevel(<trace level>);
•
Lösung:
Nutzen von Parametermarkern in CLI/JAVA
SQLJ bei statischen, komplexen SQL, zum Vermeiden von “prepare time”
Auffinden von mimmemmode/trustapp Modes in CLI
Vergrößern des “package cache”
Untersuchen des “connection pool”
Ausschalten von AutoCommit in der JAVA Applikation und Kontrolle der Commits über
die Applikationslogik
 Gerade der AutoCommit wird oft zur Falle – bei hohen Insert/Update Folgen!






Aug2012
165
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
Die Top 10 Gründe schlechter Performance
#10: „Page cleaning“/Bufferpools
•
Bufferpools haben eine endliche Größe des Speichers(!)
 Manchmal sollten sie aus Performance-Gründen “getuned” werden
•
Erkennung über:
 Schlechte „hit ratio“
 Hohe CPU/Memory Verbräuche auf der „client“-Seite
 PageWrites vs AsyncPageWrites ist hoch (d.h. es erfolgen “page steals”/”synch writes”)
•
Lösung:
 Erhöhen der Anzahl “cleaners”
 Anpassen von CHNGPGS_THRESH
 Möglicherweise Anlegen zusätzlicher separater Bufferpools für Daten und
Indexes
Aug2012
166
DB2 for LUW – SQL Tuning & Monitoring
Fundamentale Aspekte zur Performance
…. Und noch ein paar “Todsünden”
•
Nicht korrekte Nutzung von “stored procedures”
 Zu wenige: zu viel Netzverkehr / Netzverweilzeiten
 Zu viele: nur 1 Statement pro Prozedur
 Falsche Implementierungssprache
•
Überbeanspruchen von UDF’s
 Gerade “trusted UDF’s generieren “overhead”
 Man kann häufig auch DB2-”built-in” Funktionen einsetzen
•
Überbeanspruchen von LOBs, LONG VARCHAR etc.
 Es gibt auch VARCHAR(32K) mit eben 32K Pages
•
MINCOMMIT ist zu hoch angesetzt
 Sollte auf dem „default“ 1 stehen bleiben
DB2 ist performant !!!!!!!!!
Aug2012
167
DB2 for LUW – SQL Tuning & Monitoring
DB2 bietet ausserdem eine Reihe von „snapshots“ und Monitoringhilfen
Hier eine kleine Auswahl
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Database snapshot showing intra_parallelism monitor element . .
Snapshot showing block remote cursor information ..
dbm snapshot for connections
db snapshot for connections
dbm snapshot for sorting
db snapshot for sorting
db snapshot for locking
db snapshot for buffer pools
db snapshot for catalogcache_sz and pckcachesz
Dynamic SQL snapshot
…
Application snapshot
db2exfmt output for the problem query
db2batch command
db2batch output
DB2 bietet für jede Information Funktionen an!!!!!!!!!
Aug2012
168
DB2 for LUW – Literatur
… und es gibt Literatur „satt“
Hier eine Auswahl
Administrative API Reference
SC27-2435-02 September, 2010
Administrative Routines and Views
SC27-2436-02 September, 2010
Call Level Interface Guide and Reference,Volume 1
SC27-2437-02 September, 2010
Call Level Interface Guide and Reference, Volume 2
SC27-2438-02 September, 2010
Command Reference
SC27-2439-02 September, 2010
Data Movement Utilities Guide and Reference
SC27-2440-00 August, 2009
Data Recovery and High Availability Guide and Reference
SC27-2441-02 September, 2010
Database Administration Concepts and Configuration Reference
SC27-2442-02 September, 2010
Database Monitoring Guide and Reference
SC27-2458-02 September, 2010
Database Security Guide
SC27-2443-01 November, 2009
DB2 Text Search Guide
SC27-2459-02 September, 2010
Developing ADO.NET and OLE DB Applications
SC27-2444-01 November, 2009
Developing Embedded SQL Applications
SC27-2445-01 November, 2009
Developing Java Applications
SC27-2446-02 September, 2010
Developing Perl, PHP, Python, and Ruby on Rails Applications
SC27-2447-01 September, 2010
Developing User-defined Routines (SQL and External)
SC27-2448-01 November, 2009
Getting Started with Database Application Development
GI11-9410-01 November, 2009
Getting Started with DB2 Installation and Administration on Linux and Windows GI11-9411-00 August, 2009
Installing DB2 Servers
GC27-2455-02 September, 2010
Installing IBM Data Server Clients
GC27-2454-01 September, 2010
Message Reference Volume 1
SC27-2450-00 August, 2009
Message Reference Volume 2
SC27-2451-00 August, 2009
Aug2012
169
DB2 for LUW – Literatur
… und es gibt Literatur „satt“
Hier eine Auswahl (contn’d)
Net Search Extender Administration and User's Guide
SC27-2469-02 September, 2010
Partitioning and Clustering Guide
SC27-2453-01 November, 2009
pureXML Guide
SC27-2465-01 November, 2009
Query Patroller Administration and User's Guide
SC27-2467-00 August, 2009
Spatial Extender and Geodetic Data Management Feature User's Guide and Reference SC27-2468-01 September, 2010
SQL Procedural Languages: Application Enablement and Support
SC27-2470-02 September, 2010
SQL Reference, Volume 1
SC27-2456-02 September, 2010
SQL Reference, Volume 2
SC27-2457-02 September, 2010
Troubleshooting and Tuning Database Performance
SC27-2461-02 September, 2010
Visual Explain Tutorial
SC27-2462-00 August, 2009
What's New for DB2 Version 9.7
SC27-2463-02 September, 2010
Workload Manager Guide and Reference
SC27-2464-02 September, 2010
XQuery Reference
SC27-2466-01 November, 2009
Installing and Configuring DB2 Connect Personal Edition
SC27-2432-02 September, 2010
Installing and Configuring DB2 Connect Servers
SC27-2433-02 September, 2010
DB2 Connect User's Guide
SC27-2434-02 September, 2010
Information Integration: Administration Guide for Federated Systems
SC19-1020-02 August, 2009
Information Integration: Configuration Guide for Federated Data Sources SC19-1034-02 August, 2009
Information Integration: SQL Replication Guide and Reference
SC19-1030-02 August, 2009
Information Integration: Introduction to Replication and Event Publishing GC19-1028-02 August, 2009
Aug2012
170
DB2 for LUW – Aufgaben
Aufgaben
1.
Welche Verteilungsoptionen kennt DB2 for LUW ?
2.
Wozu sind Cursor in DB2 vorgesehen?
- Welche Typen gibt es ?
- Was ist bei ihrer Verwendung zu beachten =
3.
Was verhindert DB2 mit seinen Lock-Prozessen?
- Welche Locktypen kennen Sie bei DB2 LUW 9.7 ?
- Welche Lock-Levels sind vorgesehen?
- Wie werden Indexe gelockt?
- Was verursacht der „drain“-Prozess?
4.
Was ist eine LUW / UoR ?
- Wie wird eine LUW/UoR begrenzt?
- Wie heisst die Einheit (programmtechnisch), die sich hinter einer UoR verbirgt?
- Was ist ein SAVEPOINT ?
5.
Wozu dient die Funktion EXPLAIN?
- Welche Informationen im EXPLAIN-Resultat sind wichtig?
- welche Funktion im DB2 erstellt die EXPLAIN-Daten?
6.
Was sind die wichtigsten Utilities für DB2-AE und Optimierung?
Aug2012
171
DB2 for LUW – Aufgaben
Aufgaben
7.
Formulieren Sie folgende Queries nach den gehörten Empfehlungen in effiziente Queries um:
a)
b)
8.
Aug2012
SELECT
,
,
FROM
WHERE
PERS_NR
NACHNAME
VORNAME
V_MITARBEITER
M
PERS_NR IN ( SELECT
FROM
WHERE
)
ORDER BY 2
SELECT
,
FROM
WHERE
GROUP
HAVING
PERS_NR
V_M_PROJ_AKT
PERS_NR IS NOT NULL
ABT_NR
AVG(GEHALT)
V_MITARBEITER
ABT_NR IS NOT NULL
BY ABT_NR
AVG(GEHALT) <= ALL ( SELECT AVG(GEHALT)
FROM
V_MITARBEITER
WHERE ABT_NR IS NOT NULL
)
Nennen Sie die Maxime für Optimierung bei DB2(relationalen DBMS)
172
DB2 for LUW – Ende AP Teil II
Aug2012
173