Kein Folientitel - SK Consulting Services GmbH

(*)
IBM DB2 for z/OS
DB2 Version 9 - Anwendungsentwicklung
(DB2_V9_DEVnews.ppt)
(*) ist eingetragenes Warenzeichen der IBM International Business Machines Inc.
Januar, 2010
1
DB2 Version 9 (AE)
DB2 V9 –Erweiterungen für die Anwendungsentwicklung
Neuerungen in den Statistiken (Histogramme)
„optimistic concurrency control & update“
INSTEADOF Trigger
Automatisches Erzeugen eines Objekts
Index „on expression“
„cultural sort & case insensitive comparison“
„skip locked rows“
FETCH CONTINUE
Spatial Support
XML (eig. Kapitel)
LOB „File reference variable“ & FETCH CONTINUE
Native SQL Procedure Language
Neue Spezialregister
CURRENT SCHEMA
„unified debugger“ (OSC)
Januar, 2010
2
DB2 Version 9 (AE)
DB2 V9 – Verbesserung am RUNSTATS (Histogramm Statistiken)
•
•
•
•
Sinnvoll für “nicht gleichverteilte” Werte mit hoher “cardinality”
“Histogram statistics” zielen auf Querverweise von Datenwerten
Summiert die Datenverteilung auf Intervall-Ebene
DB2 nutzt „equal-depth“ Histogramme
 Jedes Quantil betrifft annähernd dieselbe Anzahl “rows”
 Beispiel - 1, 3, 3, 4, 4, 6, 7, 8, 9, 10, 12, 15 (in Folge), geplittet in 3 Quantile
RUNSTATS
• Kann maximal 100 Quantile für eine Spalte anlegen
• Gleiche Werte in einer Spalte BEDEUTEN auch immer Zuordnung zum selben Quantil
• Quantile werden aufsummiert, aber:
 DB2 versucht möglichst wenige Quantile zu erzeugen und dabei große “gaps” zu vermeiden
 Ein “high-” und ein “low-value” Wert einer Spalte kann in separaten Quantilen abgelegt werden
 NULL wird immer einem eigenen Quantil zugeordnet
• Existieren weniger als 100 Spaltenwerte, so wird auf verteilte Statistiken zurückgesetzt
• Dieses “feature” wird nicht von LOAD bzw. REORG unterstützt
• Unterstützt “column groups” ebenso, wie einzelne Spalten
Januar, 2010
3
DB2 Version 9 (AE)
DB2 V9 – Verbesserung am RUNSTATS Histogramm Statistiken)
Beispiel: YRS_OF_EXPERIENCE Spalte in der Tabelle EMPLOYEE
Range Prädikat:
SELECT T.EMPID FROM EMPLOYEE T
WHERE T.YRS_OF_EXPERIENCE
BETWEEN 5 AND 10;
Non-local Prädikat:
SELECT T1.EMPID FROM EMPLOYEE T1,
OPENJOBS T2
WHERE T1.SPECIALTY = T2.AREA
AND T1.YRS_OF_EXPERIENCE >
T2.YRS_OF_EXPERIENCE;
Die Histohramm-Statistiken dazu:
Januar, 2010
4
DB2 Version 9 (AE)
DB2 V9 – Verbesserung am RUNSTATS (Histogramm Statistiken)
DB2 erzeugt “histogram statistics” in gleicher/vergleichbarer “Tiefe”- heisst, dass es die gesamte
Menge aller Wertevorkommen in Intervalle unterteilt, in denen jedes denselben prozentualen Anteil
aller “rows” enthält.
Diese “histogram statistics intervals” besitzen folgende Eigenschaften:
•
Jedes Intervall umfasst annähernd dieselbe Anzahl, bzw. denselben prozentualen Anteil
an “rows”. Ein hochfrequenter “Einzelwert” kann ein Intervall alleine für sich beanspruchen.
•
Ein Einzelwert wird niemals in mehr als EINEM Intervall vorkommen. Das heisst, die
maximale Anzahl von Intervallen ist identisch mit der Anzahl der ”distinct values” der
entsprechenden Spalte. Diese maximale Anzhal der Intervalle kann niemals den Wert 100
überschreiten. Dies ist die maximale Anzahl von Intervallen, die DB2 unterstützt.
• Angrenzende Intervalle übergehen manchmal Werte, die nicht in der Tabelle
vorkommen, insbesonders dann, wenn dies dazu führt, sich eine grosse Menge
“fehlender” Werte innerhalb des Intervalls zu sparen.
Beispiel: Ein Wert 30 (wie oben gezeigt) habe die “frequency” 1%. Wenn man ihn im siebten
Intervall platziert, so wird das die Prozentsätze im sechsten und siebten Intervall
ausbalancieren. Andererseit wird damit eine grosse “skipped range” zum siebten Intervall
aufgetan.
• HIGHVALUE und LOWVALUE können sowohl “inklusiv” als auch als “exklusiv”
gehandhabt werden. Ein Intervall repräsentiert jedoch in der Regel eine “non-overlapped
value range”.
Januar, 2010
5
DB2 Version 9 (AE)
DB2 V9 –Histogramm Statistiken (cntnd.)
•
•
•
NULL Werte, falls welche existieren, belegen ein eigenes Intervall.
Da DB2 einzelne Werte nicht in zwei unterschiedlichen Intervallen platzieren kann, wurde die
maximale Anzahl Intervalle auf die maximal mögliche Anzahl unterschiedlicher Werte in der
Spalte begrenzt- kann aber bei DB2 niemals die Intervallanzahl von 100 überschreiten.
“Histogram statistics” sind hilfreich, wenn in (math. / Vergleichs-) Reihen entsprechende
Lücken klaffen.
Beispiel 1:
Man nutzt INTEGER (oder schlimmer noch: VARCHAR) zur Darstellung von YEAR-MONTH
Daten
•
Der Optimizer kann nicht erkennen, dass es zwischen 200512 und 200601 keine (gültigen)
Werte gibt. Denn
 200513 bis 200600 sind zwar gültige numerische Werte, aber keine gültigen Monats- und
Jahreswerte
•
Der Optimizer trifft folgende Annahme:
 BETWEEN 200512 AND 200601
90 gültige Werte, aber nur 2 gültige
Datumswerte
•
Das liefert mehr „rows“ als
 BETWEEN 200501 AND 200512
12 gültige Werte und 12 gültige
Datumswerte
•
Mit Histogrammstatistiken kann die „Anzahl“ von diversen Datenwerten dargestellt werden
 Das erlaubt eine genauere Schätzung für die Filterfaktoren
Januar, 2010
6
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„optimistic locking“
Fetch
row1
Lock row1
Unlock row1
Fetch
row2
Lock row2
Unlock row2
UPDATE WHERE
CURRENT OF…
Lock row2
Compare by VALUE
UPDATE row2
If values match
Will die Applikation eine FETCH Operation ausführen, um den CURSOR auf eine bestimmte Row zu
positionieren, so führt DB2 folgende Schritte durch:
a. Lock der Row
b. Ausführen des FETCH
c. Freigabe (Release) des Lock
Bei einem „positioned update / delete“ auf der Row passiert folgendes:
a. Lock der Row
b. Neu-Evaluation der Prädikate, um sicherzustellen, dass die Row immer noch zur Resultatstabelle gehört
Januar, 2010
7
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen („optimistic locking“)
Built-in timestamp für jede Row oder Page
• Automatischer Update von DB2
• Timestamp Prädikat ermöglicht die Prüfung ob die Row geändert wurde
CREATE TABLE EMP_INFO
(
EMPNO
CHAR(6)NOT NULL,
EMP_INFOCHANGE
EMP_ADDRESS
EMP_PHONENO
PRIMARY KEY (EMPNO)
NOT NULL GENERATED
ALWAYS FOR EACH ROW
ON UPDATE AS ROW
CHANGE TIMESTAMP,
VARCHAR(300),
CHAR(4),
)
Suche alle Änderungen innerhalb der letzten 30 Tage vin der Tabelle EMP_INFO
SELECT *
WHERE
AND
Januar, 2010
FROM EMP_INFO
ROW CHANGE TIMESTAMP FOR EMP_INFO <= CURRENT TIMESTAMP
ROW CHANGE TIMESTAMP FOR EMP_INFO >= CURRENT TIMESTAMP - 30 days
8
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„optimistic locking“
SELECT C1, ROW CHANGE TOKEN FOR TAB, RID(TAB)
INTO :h1, :h_rct. :h_rid
FROM TAB WHERE TAB.C1 = 10
……
UPDATE TAB
SET TAB.C2 = 10
WHERE RID(TAB) = :h_rid
ROW CHANGE TOKEN FOR TAB = :h_rct
Januar, 2010
9
DB2 Version 9 (AE)
DB2 V9 – INSTEADOF Trigger
•
Heute werden Views häufig zur Kontrolle von lesenden Zugriffen eingesetzt

Viele Views sind also “not updatable”

das heisst Kontrolle der Zgriffe auf die Basistabellen für Insert, Update und Delete

Triggers können zu “controlled updates” verhelfen
•
Neuer Triggertyp

enthält einen allgemeinen Mechanismus für alle “read / write” Zugriffe

Kann nur für Views definiert werden

es gibt nur 1 INSTEAD OF INSERT, UPDATE, DELETE pro VIEW

Die angeforderte “update” Operation gegen einen View wird von der Trigger-Logik übersteuert

damit vollkommen transparent für die Applikation

erforderte
aber diverse CREATE TABLE WEATHER (CITY VARCHAR(25), TEMPF DECIMAL(5,2));
VIEW CELCIUS_WEATHER (CITY, TEMPC) AS
Änderungen CREATE SELECT
CITY, (TEMPF-32)*5.00/9.00 FROM WEATHER;
in den DB2
CREATE TRIGGER CW_INSERT INSTEAD OF INSERT ON CELCIUS_WEATHER
KatalogtaREFERENCING NEW AS NEWCW DEFAULTS NULL
FOR EACH ROW MODE DB2SQL
bellen (SYSINSERT INTO WEATHER VALUES (NEWCW.CITY, 9.00/5.00 * NEWCW.TEMPC+32);
TABLES,
CREATE TRIGGER CW_UPDATE INSTEAD OF UPDATE ON CELCIUS_WEATHER
SYSREFERENCING NEW AS NEWCW OLD AS OLDCW DEFAULTS NULL
TRIGGERS )
FOR EACH ROW MODE DB2SQL
UPDATE WEATHER AS W
SET W.CITY = NEWCW.CITY, W.TEMPF = 9.00/5.00*NEWCW.TEMPC+32
WHERE W.CITY = OLDCW.CITY;
Januar, 2010
10
DB2 Version 9 (AE)
DB2 V9 – automatisches Erzeugen von DB-Objekten
In DB2 V8, wird, wenn man ein CREATE TABLE Statement ohne Angabe eines TS und einer DB
benutzt, von DB2 implizit ein “default TS” in der Datenbank DSNDB04 angelegt. Um das Leben
der von DB2 betroffenen Menschen zu erleichtern, bietet DB2 V9 weitere Wege, das Erzeugen von
Tabellen und zugehöriger Objekte schneller werrden zu lassen.
DB2 V9 besitzt die Fähigkeit folgende Objekte implizit zu erzeugen, abhängig davon, wie man das
CREATE TABLE SQL Statement kodiert:
•
•
•
•
•
•
Database
Tablespace
Primary key index
Unique key index
ROWID index falls die ROWID Spalte als GENERATED BY DEFAULT angelgt ist
LOB Tablespace, “auxiliary table”, “auxiliary index”
Eigenschaften der automatisch
erzeugten Objekte
Januar, 2010
11
DB2 Version 9 (AE)
DB2 V9 – automatisches Erzeugen von DB-Objekten
•
Implizite db: DSN00001 – DSN60000 (wrap around)

Es gibt diverse neue Bufferpool „zparms“

TBSBPxxx für 4k – 32k, TBSBPXML für XML,

TBSBPLOB für LOB

IDXBPOOL für Indexe
•
Implizite ts: Segmented im CM; UTS (“partitioned-by-growth”) im
NFM

IMPDSDEF zparm: Default für DEFINE Parameter

IMPTSCMP zparm: Default für COMPRESS Parameter

Define index for primary, unique and ROWID
CREATE TABLESPACE ohne IN Klausel nutzt immer noch die DSNDB04…..
Januar, 2010
12
DB2 Version 9 (AE)
DB2 V9 –Erweiterungen
INDEX on <expression>
CREATE TABLE employee ( id
lastname
firstname
salary
bonus
INTEGER NOT NULL,
VARCHAR(20) NOT NULL,
VARCHAR(20) NOT NULL,
DEC(15,2) NOT NULL,
FLOAT)
Folgender Index:
CREATE INDEX
(
SELECT
WHERE
upper_empname ON employee
UPPER(lastname, 'EN_US'),
UPPER(firstname, 'EN_US'), id )
id FROM
employee
bonus + salary > 100000 ;
CREATE INDEX total_compensation ON employee (salary + bonus)
Januar, 2010
13
DB2 Version 9 (AE)
DB2 V9 –Erweiterungen
INDEX on <expression>
Man erzeuge folgenden “unique index”:
CREATE UNIQUE INDEX empname ON employee
(SUBSTR(firstname,1,1) CONCAT '. ' CONCAT lastname);
In jedem Fall wird die EINDEUTIGKEIT des Index gegen die Werte, die letztendlich im Index gespeichert
werden, geprüft, nicht gegen die Werte, die in der Tabelle abgelegt sind.
In diesem Fall wird DB2 einen “duplicate key” Fehler zurückgeben, auch wenn es so scheinen mag, dass keine
doppelten Werte in der Tabelle zu finden sind.
Es gibt nämlich bereits den Mitarbeiter Michael Smith. Würde nun die Mitarbeiterin Monica Smith der Tabelle
hinzugefügt, so würden beide über den UNIQUE “Index on expression” zum selben IX-Wert führen: M. Smith.
Restriktionen:
• Jede „key-expression“ muss eine Referenz auf eine Tabellenspalte haben
• Alle Tabellenreferenzen müsse unqualifiziert angegeben sein
• Referenzierte Spalten können nicht vom Typ LOB, XML, DECFLOAT sein.
• Referenzierte Spalten können keine FIELDPROCs oder ein SECURITY LABEL enthalten.
• Eine „Key-expression“ darf folgendes NICHT enthalten:
 „subquery“,
 „aggregate function“,
 eine „not deterministic function“,
 eine Funktion mit einer externen Aktion,
 eine „user-defined function“,
 eine Referenz auf eine Sequenz, eine „host variable“, einen „parameter marker“, ein Spezialregister, einen
„CASE“-Ausdruck…. usw.
Januar, 2010
14
DB2 Version 9 (AE)
DB2 V9 –Erweiterungen
INDEX on <expression>
Überlegungen zu „on index on expression“
Finden von Fehlern auf den Ausdrücken für einen Index
Fehler, die während der Evaluation einer “expression” auf einen Index erkannt werden, werden erst in diesem
Augenblick zurückgegeben. Das kann sein bei UPDATE, INSERT oder einem DELETE Statement. Auch ein SELECT
aus einem DML”data change” Statement, oder Utilities, wie REBUILD INDEX, CREATE INDEX, bzw. REORG
TABLESPACE können Fehler erkennen lassen
Beispiel:
Die Evaluation des Ausdrucks 10 / column_1 liefert einen Fehler, wenn der Wert in column_1 = 0 ist. Der Fehler wird
während eines CREATE INDEX geliefert, wenn die Tabelle nicht leer ist und eine “row” mit einem Wert 0 in der
Spalte “column_1” enthält. Der Fehler kann auch auftreten, wenn INSERT oder UPDATE durchgeführt werden.
Länge eines Resultats einer “expression” bei “string types”
Ist der Ergebnisdatentyp einer “key-expression” ein “string type” und die Länge des Resultats kann nicht zur BINDZeit errechnet werden, so wird die Länge auf die maximal erlaubte Länge des entsprechenden Datentyps gesetzt
oder auf die größte Länge, die DB2 schätzen kann. In diesem Fall wird ein CREATE INDEX fehlschlagen, da die
Gesamtlänge des “key” das Limit eines “index key” überschreitet.
Beispiel:
Die Länge des Resultats mit folgendem Ausdruck: REPEAT('A', CEIL(1.1)) ist VARCHAR(32767) und die
Resultatslänge aus SUBSTR(DESCRIPTION,1,INTEGER(1.2)) sei die Länge der Spalte DESCRIPTION.
Hier kann ein CREATE INDEX Statement auf diese Ausdrücke nicht erfolgreich durchgeführt werden, da die “total
key length” das Limit für „index keys“ überschreitet.
Januar, 2010
15
DB2 Version 9 (AE)
DB2 V9 –Erweiterungen
„cultural sort & case insensitive comparison“
Diese Änderung betrifft folgende Anforderung:
1.
In nationalen Sprachumgebungen, wie Tschechisch, Slovakisch, Ungarisch usw. haben die nationalen Alphabete oft
spezielle Buchstaben, die aus zwei “characters” bestehen. Diese werden im aktuellen DB2 nicht richtig sortiert.
Beispiel:
Im Tschechischen wird der Buchstabe ch als EIN Laut (nicht als 2 Buchstaben) verwendet und wie folgt sortiert:
a....b....c.....d.....e.....f.....g.....h, ch, i......j......
Und nicht:
a....b....c.....ch.....d..... (wie es z. Zt. Von DB2 als SORT-Ergebnis zurückgegeben wird)
Der Buchstabe “ch” sollte also korrekterweise zwischen h und i und nicht zwischen c und d einsortiert werden.
Beispielquery:
Die folgende Query sortiert nach V4.0.1 (UCA), ignoriert “spaces”, Interpunktuation und Symbole und nutzt dazu die
schwedische Alphabetisierung ohne Groß-/Kleinschreibung zu berücksichtigen:
SELECT
FROM
ORDER
Januar, 2010
FIRSTNME, LASTNAME
DSN8910.EMP
BY COLLATION_KEY(LASTNAME, ’UCA400R1_AS_LSV_S2’);
16
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„cultural sort & case insensitive comparison“
2.
Da das Web immer mehr auch in Mainframe Architekturen eingebunden wird und Daten auf unterschiedlichen
Plattformen gespeichert werden, sind die “alten” “upper case” Anzeigen bei Geschäfts- und Endbenutzern
weniger gefragt .
Aus dem“ease-of-use” Standpunkts, will man die Daten im “mixed case” Format sehen. Dies wird zur Herausforderung, wenn die Daten am Host gespeichert und zudem Bestandteil von Query-Prädikaten sind. Um die
Query-Effizienz zu maximieren, ist es üblich, alle Daten in “upper case” Format zu speichern. Das lässt Suchen
einfach und “indexable” werden. Macht es aber auch schwierig, die Daten dann in ein “mixed” Format zur
Anzeige zu “zurück” zu überführen.
Suchen auf “mixed case” Daten wiederum ist mehr als problematisch, da ein LIKE-Prädikat und eine “scalar
function” erforderlich sind, was einen Zugriffspfad alles andere als optimal werden läßt. Die Verbesserung in
DB2 9 macht es möglich, die Daten in “mixed case” Format zu speichern und dann “case insensitive” Suchen
durchzuführen, die keien TS Scans verursachen.
Beispiel: “Minnesota”, “MINNESOTA” und “minnesota” werden korrekt über Query zurückgegeben ohne etwas
fromulieren zu müssen wie: ….like where upper(:hv) = upper(column)…
“Cultural sort” ist Bestandteil des verbesserten DB2 “national language support.”
DB2 führt dazu eine neue Funktion : COLLATION_KEY ein. Die Funktionen LOWER und UPPER wurden geändert
Hier wird die Funktion COLLATION_KEY auf die Spalten LASTNAME und SALES_PERSON
angewendet, um die “sort keys” so einzustellen, dass eine korrekter Vergleich möglich wird (Quebec(frz.)):
SELECT E.WORKDEPT FROM EMPLOYEE AS E INNER JOIN SALES AS S
ON COLLATION_KEY(E.LASTNAME, ’UCA400R1_LFR’) =
COLLATION_KEY(S.SALES_PERSON, ’UCA400R1_LFR’)
WHERE S.REGION = ’Quebec’;
Januar, 2010
17
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
CURRENT SCHEMA
DB2 V8 führte das Spezialregister CURRENT SCHEMA ein und erlaubte damit auch das Statement SET CURRENT
SCHEMA. Die Restriktion in DB2 V8 ist, dass das CREATE Statements nicht erlaubt ist, wenn der Wert von CREATE
SCHEMA vom Wert des CURRENT SQLID Spezialregister differiert.
DB2 V9 hebt diese Restriktion auf.
Für die meisten Objekte ist der CURRENT SCHEMA Wert der Qualifier der dynamisch erzeugten
(CREATed) Objekte , für die explizit KEIN Qualifier beim CREATE spezifiziert wurde.
CURRENT SQLID bleibt der “object owner”.
Januar, 2010
18
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„skip locked rows“
Die Option SKIP LOCKED DATA ermöglicht einer Transaktion die “rows”, die inkompatibel von einer anderen
Transaktion gesperrt sind, einfach nicht zu beachten.
Neues Schlüsselwort im SELECT, UPDATE, DELETE Statement und UNLOAD
Da die Option SKIP LOCKED DATA diese Zeilen einfach übergeht, kann für manche Applikationen die Performance
steigen, da die “lock wait time” wegfällt. ABER: Man sollte Option SKIP LOCKED DATA nur für solche Applikationen
nutzen, die es auch vertragen, ohne die übergangenen “rows” ein brauchbares Ergebnis präsentieren zu können.
Nutzt eine Transaktion die Option SKIP LOCKED DATA, so wird sie die Daten, die über Locks gesperrt sind
weder Lesen noch modifizieren.
Beispiel:
Angenommen, eine Tabelle EXTABLE existiert in einem TS mit “row-level locking” bzw. “page-level locking”
und die “rows” verteilen sich über mehrere Pages. EXTABLE besitzt 2 Spalten C1 and C2.mit den Daten:
C1
1
2
3
4
C2
AAAA
BBBB
CCCC
DDDD
Eine Transaktion setzt nun folgendes UPDATE Statement ab:
UPDATE EXTABLE SET C1 = 99 WHERE C1 < 3;
Eine andere Transaktion gibt das folgende SELECT Statement,
bevor die erste Transaktion „committet“ , an DB2:
SELECT COUNT (*) FROM EXTABLE WHERE C2 >= ’AAAA’ SKIP LOCKED DATA;
Gibt es nun keinen Index auf C2, liefert DB2 den Wert 2, da DB2 die zwei “rows”, die gesperrt sind , übergeht.
Was ist der Unterschied zwischen “uncommitted read” und “skip locked rows” ?
Januar, 2010
19
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
FETCH CONTINUE
Diese Verbesserung erweiteret das FETCH SQL Statement für Applikationen, die LOB bzw. XML Spalten aus
tabellen lesen sollen und die Lände dieser LOB/XML Werte nicht bekannt oder so groß ist, dass die Applikation das
entsprechende LOB nicht direkt im Speicher abbilden kann.
VOR DB2V9 musste ein Programm explizit Speicher zuweisen, um solche LOBs aufnehmen zu können.
PROBLEM: “shortage” auf dem “virtual memory” in manchen Umgebungen.
Die Erweiterungen zum FETCH Statement diesbezüglich sind die Klausel WITH CONTINUE und CONTINUE.
Beispiel – „statisc SQL“:
CREATE TABLE T1 (C1 INT, C2 CLOB(100M), C3 CLOB(32K), C4 XML);
[1]
[2]
[3]
[4]
[5]
[6]
EXEC SQL BEGIN DECLARE SECTION
DECLARE CLOBHV SQL TYPE IS CLOB(32767);
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT C2 FROM T1;
EXEC SQL OPEN CURSOR1;
EXEC SQL FETCH WITH CONTINUE CURSOR1 INTO :CLOBHV;
if (sqlcode >= 0) + sqlcode <> 100
loop until LOB is completely fetched (no truncation occurred – compare returned length to
provided buffer length)
write current piece of data to output file
if the column was truncated
EXEC SQL FETCH CURRENT CONTINUE CURSOR1 INTO :CLOBHV;
endif
endloop
endif
[7] EXEC SQL CLOSE CURSOR1;
Januar, 2010
20
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
FETCH CONTINUE
Beispiel – „dynamic SQL“:
Angenommrn in T1 gibt es eine “row”, in der C1 ein gültiges “integer,” C2 100 MB Daten, C3 c32 KB, und C4
XML Daten enthält.
[1]
[2]
[3]
EXEC
EXEC
EXEC
EXEC
SQL
SQL
SQL
SQL
DECLARE CURSOR1 CURSOR FOR DYNSQLSTMT1;
PREPARE DYNSQLSTMT1 FROM 'SELECT * FROM T1';
DESCRIBE DYNSQLSTMT1 INTO DESCRIPTOR :SQLDA;
OPEN CURSOR1;
[4]
Prepare for FETCH:
Zuweisen der Datenpuffer (32K für jedes CLOB, XML) Stzen der “data pointers” und
Längen in der SQLDA.
[5]
EXEC SQL FETCH WITH CONTINUE CURSOR1 INTO DESCRIPTOR :SQLDA;
[6]
if truncation occurred on any LOB or XML column
loop through each column
if column is LOB or XML and was truncated
allocate larger buffer area for any truncated columns, move first piece larger area
reset data pointers, length fields in SQLDA
endif
endloop
[7]
EXEC SQL FETCH CURRENT CONTINUE CURSOR1 INTO DESCRIPTOR :SQLDA
endif
Arbeiten mit den zurückgegebenen Daten
...
[8] EXEC SQL FETCH WITH CONTINUE CURSOR1 INTO DESCRIPTOR SQLDA;.
[9] EXEC SQL CLOSE CURSOR1;
Januar, 2010
21
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„spatial support“
IBM Spatial Support for DB2 for z/OS bietet eine Reihe von speziellen , sogen. “spatial data types”, die man
verwendet, um “real-world entities” zu modellieren, z.B.: Standorte von Kunden, Grenzen in Parks, Lauflinien
von Geleisen usw.
Man kann diese “spatial data” über “spatial functions” modifizieren und bearbeiten. Sie können über ein SQL
Statement Anwendung finden. Man kann auch Indexe auf “spatial data” setzen, die wiederum vom DB2
Optimizer erkannt und verwendet werden können.
Direkte Integration mit DB2
•
„Spatial data types“:
 ST_Point, ST_LineString, ST_Polygon, etc
•
„Spatial functions“:
 ST_Buffer, ST_Contains, ST_Distance, ST_Within, etc
•
“Spatial stored procedures” – zur Koordination und Verweis der
Systemadministration
Implementiert die Open Geospatial Consortium (OGC) SQL Specifikation und ISO
SQL/MM “Spatial Standard “ für Typen und Funktionen
Beispiele für sogen.: „Spatial Applications“
Versicherungsindustrie: Generieren von Quoten und Risikobewertung auf Basis der geografischen Lage von VN
Einzelhandel: Anzeige des Kundenpotentials rund um ein Geschäft, um Werbewirksamkeit feststellen zu können
Immobilienhandel: Lokalisieren der Grundstücke auf Basis anliegender Schulen und Umgebungsinformationen
Energieversorger : Vermittlerdichte aufgrund der Anforderungen und der Kosten für Anschluss und Lieferung
Januar, 2010
22
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„spatial support“
Spatial Queries:
1. Alle Kunden mit einer Hausratversicherung innerhalb von 1
000 Meters von einem Fluss entfernt, die KEINE
Überschwemmungsversicherung haben….
2.
Was ist der nächste Einzelhandelsladen der Kunden, die während
2006 mehr als EUR 3K in den Stadtgrenzen von Chikago
ausgegeben haben?
3. Suche alle familiengerechten Wohnungen/Häuser in Stuttgart ,
die im Umkreis einer Schule (5 km) liegen und zu verkaufen sind
SELECT
FROM
WHERE
Januar, 2010
c.id, c.name,
customer c, river r
distance (c.location, r.path) < 5
23
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„omnifind textsearch“
•
•
•
•
Bietet Textsuchen für CHAR / VARCHAR / CLOB / XML
“Omnifind” nutzt einen sogen. “text index” Server
nutzt effiziente Kommunication zum DB2 z/OS Server
Omnifind “text indexe” werden in DB2 geseichert(Grund: backup/recovery Gründe)
Es existieren diverse STP, ie die Textsuche unterstützen:
•
START – DB2 verbindet zum OF Server
•
CREATE IX
•
UPDATE IX
•
ALTER IX, DROP IX, CLEAR EVENTS
•
STOP – „text search“ wird abgeschaltet
Januar, 2010
SELECT
FROM
WHERE
customer
claim_table
contains (REORT, „Das suche ich“)
SELECT
WHERE
int1 FROM text_search
CONTAINS(CLB, „is, IBM') = 1
SELECT
WHERE
int1 FROM text_search
CONTAINS(CLB, „is OR IBM') = 1
24
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„native SQL Procedures“
Wurden SQL Prozeduren VOR DB2 V9 erzeugt, so nutzte diese SQL Code und /oder C Code. Die Logik im
“SQL procedure body” wurde nach C Code überführt und zuletzt existierte ein compiliertes C Programm
ausserhalb des DB2 Katalogs.
Mit DB2 V9, gibt es weiterhin “external SQL stored procedures”, die einen C Compiler erforderlich machen und
es existieren immer noch “external load modules” ausserhalb von DB2, aber es gibt auch “native SQL
procedures”, die die Nutzung eines C Programms nicht mehr erfordern.
Diese Verbesserungen in DB2 V9 betreffen nur “native SQL procedures”:
• “Nested compound statements in SQL stored procedures”
• “New stored procedure related special registers”
• “DB2 and DSN commands for native SQL procedures”
• “Changes to DB2-supplied dynamic SQL processing applications”
• “Differences from external SQL procedures”
Mit dem V9 “new function mode”, werden die “procedural statements” einer NSSTP in eine Form konvertiert,
die es zulässt, dass dieses Objekt im DB2 Katalog direkt abgelegt werden kann – ähnlich den SQL Statements.
Parameterliste und Prozeduroptionen werden in den Datenbankkatalogtabellen abgelegt, wie in
vorangegangenen releases.
CREATE PROCEDURE UPDATE_BALANCE
•
•
•
•
•
Januar, 2010
Bessere SQL Portabilität zwischen den
unterschiedlichen DB2 Plattformen
Einfachere Integration in die bestehende
Applikationsentwicklungsumgebung
Bessere Unterstützung der AE-Standards
Versionierung der STP‟s ist möglich
ALTER ACTIVATE version n …..
(
IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL(9,2) ) VERSION V1
LANGUAGE SQL READS SQL DATA
BEGIN
DECLARE CUSTOMER_NAME CHAR(20);
SELECT
CUSTNAME
INTO
CUSTOMER_NAME
FROM
ACCOUNTS
WHERE
CUSTNO = CUSTOMER_NO;
END
25
DB2 Version 9 (AE)
DB2 V9 – Erweiterungen
„unified debugger“
Mit dem Unified Debugger kann man u.a. die Ausführung von “SQL procedure“ Code verfolgen, zeilenweise
“breakpoints” setzen und Varaiableninhalte ansehen bzw. Modifizieren.
Der Unified Debugger unterstützt “external” und “native SQL” Prozeduren inklusive “nested stored procedures”.
Um den Unified Debugger auf “SQL procedures” einzusetzen, müssen “breakpoints” in den Routinen bzw.
“executable files“ vorgesehen sein:
Grant DEBUGSESSION privilege
neue Systemauthorisierung
Vorbereiten der “stored procedures” für das “debugging”
bei “native SQL procedures” mit ALLOW DEBUG MODE
und WLM ENVIRONMENT FOR DEBUG MODE
bei “external SQL procedures” über DSNTPSMP bzw. das
Development Center , um die SQL Procedur mit der Option
BUILD_DEBUG zu erzeugen
Bei “Java procedures” sollte die Option ALLOW DEBUG
MODE eingeschaltet werden und eine entsprechende passende
WLM Umgebung für das “debugging” von JAVA ausgewählt
werden Der Java Code muss mit der Option –G kompiliert
werden.
Januar, 2010
26
DB2 Version 9 (AE)
DB2 V9 – Verbesserung des Data Warehousing und OLTP ReportingSystem
QMF
„Drag and drop querying“, Reporting und Analyse
„Executive dashboards“ und Datenvisualisierung
Verbessertes OLAP mit DB2 „Cube Views“
Neu entwickelte „cross-platform development environment“
Neues „security model“ zur Zugriffskontrolle & „personalization“
Bereit für den WebSphere Application Server
Januar, 2010
27
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
•
•
•
•
•
Januar, 2010
Verfügbar über die „Accessories Suite“
kostenfrei für DB2 9 for z/OS
OSC 1.1 FP4
unterstützt auch
DB2 V8
Ersetzt Visual
Explain
VE Funktionen
sind im Data
Studio verfügbar
28
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
„Family Product Positioning“
Funktion
Queries from Cache, Catalog
Visual
Explain
V9 Optimization
Service Center
Optim Query
/ Workload Tuner
Yes
Yes
Yes
Access Path Advisor
Yes
Workload Query Advisor
Yes
Query Advisor
Yes
Workload Index Advisor
Yes
Query Index Advisor
Yes
Profile based Monitoring
Yes
Yes
Workload Statistics Advisor
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Query Statistics Advisor
Yes
Visual Plan Hint
Access Plan Graph
Query Formatter, Annotation
Januar, 2010
Yes
29
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
Virtuelle Indexe
•
auch in DB2 V8 mit PK46687
•
ermöglicht Design und Test neuer Indexe
•
stellt sicher, dass ein IX vom DB2 Optimizer auch verwendet wird
•
dazu eine neue Tabelle
DSN_VIRTUAL_INDEXES zur
Aufnahme der Definition eines VI
•
enthält eine Spalte ENABLE,
um den “virtual index” zu
aktivieren/deaktivieren
•
enthält Spalten, um die Katalogstatistiken zu halten (NLEAF, NLEVELS, ...)
•
Die Ausführung des EXPLAIN
Statements berücksichtigt dann
den/die “virtual indexes”
•
Die “Authorization-id”, mit der der
EXPLAIN ausgeführt wird, muss
derselbe sein, wie der creator /
qualifier des “virtual index“
Januar, 2010
30
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
Identifying Problem Query – Dynamic Statements
Januar, 2010
31
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
Grafische Darstellung der Pfade und Notationen
Januar, 2010
32
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
Behandlung von “Workload Exceptions” und Monitoring
Januar, 2010
33
DB2 Version 9 (AE)
DB2 V9 – „Optimization Service Center“
Optim Query Tuner und Index Advisor
Januar, 2010
34
DB2 Version 9 (AE)
DB2 V9 – „Data Studio“
Januar, 2010
35
DB2 Version 9 (AE)
Januar, 2010
36