IBM DB2 V8

(*)
IBM DB2 V8
for z/OS
Neue Funktionen und „features“
für die AE(sql)
(DB2V8_AE_sqlneu)
(*) ist eingetragenes Warenzeichen der IBM International Business Machines Inc.
Jan 2005
1
DB2
DB2 V8
V8 SQL
SQL Neuerungen
Neuerungen
•
•
Neue Limits für SQL
Neue Funktionen im SQL
• „scalar full select“
•
•
•
•
•
•
•
•
•
•
•
Neue AP Funktionen
• „dynamic scrollable Cursors“
•
•
•
•
•
Jan 2005
„multiple DISTINCT‘s“
„multi row“ INSERT
„multi row“ Fetch
GET DIAGNOSTICS
INSERT innerhalb eines SELECT
Rekursicursives SQL
Expressions im GROUP BY
„common table expressions
Verbesserungen bei den diversen Objekten („identity columns“, „sequence object“)
Weitere Verbesserungen
SQL/PL Funktionen
STP/UDF Verbesserungen
Neue Spezialregister
Session variable + GETVARIABLE
Neue XML Funktionen
2
1.
1. Neue
Neue Limits
Limits im
im DB2
DB2 // SQL
SQL
Objekt
DB2 V7
DB2 V8
Länge des „Table name“ (*)
18
128
Länge des „Column name“
18
30
Max. Größe des „Index key“ (Bytes)
255
2000
Max. Hex / Character Literale
255
32.704
Max. Länge von Prädikaten
255
32.704
Max. SQL Statement Länge
32 KB
2 MB
Tables in einer Tabelle / Join
225 / 15
225 / 225
Max. Anzahl offene Datasets
32.767
100.000
Max. Anzahl Partitions beim PTS
254
4.096
(*) gilt auch für die meisten anderen DB2 Objekte, wie views, aliases, index, triggers, synonyms,...
Jan 2005
3
1.
1. Neue
Neue Limits
Limits im
im DB2
DB2 // SQL
SQL
Objekt
DB2 V7
DB2 V8
Max. Table /TS Grösse
16 TB
128 TB
Max. Anzahl Databases
65.279
-
Max. Anzahl Objekte pro Database
65.535
-
Anz. „concurrent threads“
(150.000) 2.00
5.000
Max. Zeilenlänge einer Tabelle
32 K
-
Max. Anzahl Spalten im View/Tabelle
750
-
Max. Anzahl Spalten in einem IX
64
-
Anzahl max. Dezimalstellen
31
-
Max. Grösse einer VARCHAR-Spalte
32.704
-
Jan 2005
4
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“
2.2 „multiple DISTINCT‘s“
2.3 „multi row“ Operationen
2.3.1 „multi row“ INSERT
2.3.2 „multi row“ FETCH
2.4 GET DIAGNOSTICS
2.5 INSERT innerhalb eines SELECT
2.6 Rekursives SQL
2.7 Expressions im GROUP BY
2.8 „common table“ /“nested table“ Ausdrücke
2.9 Verbesserungen bei div Objekten („identity columns“, „sequences“)
2.10 Sonstige
Jan 2005
5
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“
•
•
•
Ein „scalar fullselect“ ist ein „fullselect“, eingeschlossen in
Klammern, der einen einzelnen Wert zurückliefert ( sonst
SQLCODE -811)
Ermöglicht „scalar fullselect“ für Ausdrücke, die auch vor der
Version8 bereits verfügbar waren
Beispiel:
SELECT PRODUCT, PRICE FROM PRODUCTS
WHERE PRICE <= 0.7 * (SELECT AVG(PRICE)
FROM PRODUCTS);
Vorteile:
•
Verbessert Nutzbarkeit und Mächtigkeit von SQL
•
Erleichtert die Portierbarkeit
•
Passt zu den SQL Standards
Jan 2005
6
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“– DB-Modell
Jan 2005
7
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“– Beispiele(in der WHERE-Klausel )
Welche
Welche Produkte
Produkte kosten
kosten mehr
mehr als
als das
das doppelte
doppelte der
der preiswertesten
preiswertesten Produkte?
Produkte?
SELECT
SELECT
FROM
FROM
WHERE
WHERE
Jan 2005
PRODUCT,
PRODUCT, PRICE
PRICE
PRODUCTS
PRODUCTS A
A
PRICE
>=
2
*
PRICE >= 2 * (SELECT
(SELECT
FROM
FROM
MIN(PRICE)
MIN(PRICE)
PRODUCTS);
PRODUCTS);
8
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „nested scalar full select“– Beispiele(in der SELECT Liste)
Suche
Suche die
die die
die Kosten
Kosten für
für die
die Bestandsführung
Bestandsführung der
der einzelnen
einzelnen Produkte
Produkte und
und kalkuliere
kalkuliere
die
Summen
von
(price
*
onhand#)
für
jedes
Teil
im
Produkt.
die Summen von (price * onhand#) für jedes Teil im Produkt.
SELECT
SELECT
PRODUCT,
PRODUCT,
(SELECT
(SELECT COALESCE(SUM(X.COST),0)
COALESCE(SUM(X.COST),0) AS
AS INV_COST
INV_COST
FROM
FROM (SELECT
(SELECT ((
(SELECT
(SELECT PRICE
PRICE FROM
FROM PARTPRICE
PARTPRICE PP
WHERE
WHERE P.PART
P.PART == B.PART)
B.PART)
** (SELECT
ONHAND#
(SELECT ONHAND# FROM
FROM INVENTORY
INVENTORY II
WHERE
WHERE I.PART
I.PART =B.PART)
=B.PART)
)) AS
AS COST
COST
FROM
PARTS
FROM
PARTS B
B
WHERE
B.PROD#
WHERE
B.PROD# == A.PROD#
A.PROD#
)) X(COST)
X(COST)
))
FROM
FROM PRODUCTS
PRODUCTS A;
A;
Jan 2005
9
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“– Beispiele(in CASE Ausdrücken)
Gib
Gib Preisnachlässe
Preisnachlässe für
für alle
alle Teile,
Teile, die
die einen
einen hohen
hohen Bestand
Bestand aufweisen
aufweisen und
und hebe
hebe die
die
Preise
Preise für
für alle
alle Teile
Teile mit
mit niedrigem
niedrigem Bestand
Bestand an...
an...
CREATE
NEW_PARTPRICE
CREATE TABLE
TABLE
NEW_PARTPRICE LIKE
LIKE PARTPRICE;
PARTPRICE;
INSERT
NEW_PARTPRICE
INSERT INTO
INTO
NEW_PARTPRICE SELECT
SELECT ** FROM
FROM PARTPRICE;
PARTPRICE;
UPDATE
NEW_PARTPRICE
N
UPDATE
NEW_PARTPRICE N
SET
PRICE
=
CASE
SET PRICE = CASE
WHEN(
WHEN( (SELECT
(SELECT ONHAND#
ONHAND#
FROM
FROM INVENTORY
INVENTORY WHERE
WHERE PART=N.PART)
PART=N.PART) << 7)
7)
THEN
THEN 1.1
1.1 ** PRICE
PRICE
WHEN(
WHEN( (SELECT
(SELECT ONHAND#
ONHAND#
FROM
INVENTORY
FROM INVENTORY WHERE
WHERE PART=N.PART)
PART=N.PART) >> 20)
20)
THEN
THEN 0.8
0.8 ** PRICE
PRICE
ELSE
PRICE
ELSE
PRICE
END;
END;
SELECT
NEW_PARTPRICE;
SELECT ** FROM
FROM
NEW_PARTPRICE;
Jan 2005
10
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“– Restriktionen
„scalar full select“ ist nicht zulässig in
•
Einem „CHECK constraint“
•
einem „grouping“ Ausdruck
•
einem view mit WITH CHECK OPTION
•
einem CREATE FUNCTION (SQL „scalar“)
•
einer „column function“
•
Einer ORDER BY Klausel
•
Einer Join-Bedingung in der ON Klausel für INNER und OUTER
JOINS
Jan 2005
11
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.2 „multiple DISTINCT‘s“
•
•
Ermöglicht mehr als eine DISTINCT-Angabe in einer SELECT- oder
HAVING Klausel
Beispiel:
in DB2 V7 nur auf derselben Spalte:
SELECT COUNT(DISTINCT(A1)), SUM(DISTINCT A1) FROM T1
in DB2 V8 auch auf unterschiedlichen Spalten:
SELECT COUNT(DISTINCT A1), SUM(DISTINCT A2) FROM T1
Vorteile:
•
Verbessert Nutzbarkeit und Mächtigkeit von SQL
•
Kompatibilität innerhalb der DB2-Produktfamilie
•
Früher: SQLCODE -127
Jan 2005
12
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.2 „multiple DISTINCT‘s“ (Beispiele)
VOR Version 8 .....
SELECT
SELECT
SELECT
SELECT
DISTINCT C1, C2 FROM T1;
COUNT(DISTINCT C1) FROM T1;
C1, COUNT(DISTINCT C2) FROM T1 GROUP BY C1;
COUNT(DISTINCT(C1)),SUM(DISTINCT C1)FROM T1;
-- same col
Mit Version 8 .....
SELECT
SELECT
FROM
SELECT
FROM
DISTINCT COUNT(DISTINCT C1), SUM(DISTINCT C2) FROM T1;
COUNT(DISTINCT C1), AVG(DISTINCT C2)
T1 GROUP BY C1;
SUM(DISTINCT C1), COUNT(DISTINCT C1), AVG(DISTINCT C2)
T1 GROUP BY C1 HAVING SUM(DISTINCT C1) = 1;
Nicht unterstützt in Version 8 .....
SELECT
FROM
SELECT
FROM
Jan 2005
COUNT(DISTINCT A1,A2)
T1 GROUP BY A2;
COUNT(DISTINCT(A1,A2))
T1 GROUP BY A2;
13
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3 „multi row FETCH“ und „multi row INSERT“
•
„multi row FETCH“:
- Ein einzelnes FETCH Statement kann gleichzeitig mehr als eine
„row“ aus der „result table“ als „rowset“ zurückgeben
- Ein „rowset“ ist eine Gruppe von Datensätzen, die als eine
Einheit behandelt werden können
- Unterstützt „dynamic“ und „static SQL“ (Fetch ist IMMER „static“)
•
Multi-row INSERT:
- Ein einzelnes SQL Statement kann eine oder mehrere „rows“ in
eine Tabelle / View einfügen
- „Multi-row INSERT“ kann sowohl im „static“ als auch im
„dynamic SQL“ verwendet werden
Vorteile:
•
Verbessert Nutzbarkeit und Mächtigkeit von SQL
•
Die Performance wird erhöht über die Einsparung von sogen. „multiple
trips“ zwischen Applikation und der „database engine“; beim „distributed
access“ wird der „network traffic“ reduziert
Jan 2005
14
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“
Neue 3. Form eines INSERT
•
INSERT mit FOR "n" ROWS wird verwendet, um mehr als eine „row“ in eine
Tabelle / View einzufügen. Die werte werden dabei in einem
„Hostvariablen Array“ zur Verfügung gestellt.
FOR "n" ROWS
•
Bei „static SQL“ kann man FOR "n" ROWS im INSERT Statement angeben;
im „dynamic SQL“ muss FOR "n" ROWS im EXECUTE Statement
angegeben werden
•
Jeder „array“ repräsentiert Zellen für „multiple rows“ einer einzelnen Spalte
VALUES Klausel erlaubt die Specifikation von „multiple rows“
•
„Host variable arrays“ werden verwendet, um die Werte für einen Insert
anzugeben
Beispiel:
VALUES (:hva1, :hva2)
Jan 2005
15
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“ vs „single row INSERT“
„Single row“
„multi row“
Multi-row INSERT Statement - Spezialfall
INSERT INTO TAB1 VALUES ( 'my string' , :hva , CURRENT DATE) FOR 4 ROWS
Programm enthält
DB2 INSERT
Jan 2005
16
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“ (Typen)
ATOMIC (default)
• Wenn der INSERT für irgendeine „row“ schiefgeht, werden alle Änderungen, die
auf der DB durch den INSERT erfolgt sind, zurückgesetzt...
NOT ATOMIC CONTINUE ON SQLEXCEPTION
• Inserts werden unabhängig voneinander verarbeitet
• Treten Fehler während des INSERT auf, so wird die Verarbeitung fortgesetzt
processing continues
• Diagnostiken sind für jede fehlerhafte „row“ verfügbar über GET DIAGNOSTICS
• SQLCODE zeigt an ob:
- Alles schiefgegangen ist:
- Alles OK ist ausser „warnings“:
- nur ein Fehler aufgetreten ist:
Jan 2005
SQLSTATE 22530, SQLCODE -254
SQLSTATE 01659, SQLCODE +252
SQLSTATE 22529, SQLCODE -253
17
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1„multi row INSERT“ (Beispiele)
Man
Man soll
soll eine
eine variable
variable Anzahl
Anzahl von
von Sätzen
Sätzen einfügen
einfügen und
und für
für die
die Wertebereitstellung
Wertebereitstellung HV
HV
arrays
arrays verwenden.
verwenden. T1
T1 soll
soll dabei
dabei 11 Spalte
Spalte besitzen
besitzen und
und eine
eine variable
variable Zahl
Zahl von
von
Datensätzen
Datensätzen aufnehmen.
aufnehmen.
EXEC
EXEC SQL
SQL INSERT
INSERT INTO
INTO T1
T1 FOR
FOR :hv
:hv ROWS
ROWS
VALUES
VALUES (:hva:hvaind)
(:hva:hvaind) ATOMIC;
ATOMIC;
In diesem Beispiel repräsentiert die Hostvariable :hva das (Daten-)„array“ und :hvaind das
„array“ für die „indicator variables“. :hv ist der variable „row“-Zähler.
Jan 2005
18
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“ (Beispiele)
Man
Man soll
soll mehrere
mehrere Sätze
Sätze einfügen
einfügen und
und für
für die
die Wertebereitstellung
Wertebereitstellung HV
HV arrays
arrays verwenden.
verwenden.
T2
T2 soll
soll dabei
dabei 22 Spalten
Spalten besitzen
besitzen (C1
(C1 und
und C2).
C2). 10
10 Sätze
Sätze sollen
sollen eingefügt
eingefügt werden.
werden.
EXEC
EXEC SQL
SQL INSERT
INSERT INTO
INTO T2
T2 (C1,
(C1, C2)
C2) FOR
FOR 10
10 ROWS
ROWS
VALUES
VALUES (:hva1:hvaind1,
(:hva1:hvaind1, :hva2:hvaind2)
:hva2:hvaind2)
NOT
NOT ATOMIC
ATOMIC CONTINUE
CONTINUE ON
ON SQLEXCEPTION;
SQLEXCEPTION;
In diesem Beispiel repräsentieren die Hostvariable :hva1 und :hva2 die (Daten-)„arrays“.
:hvaind1 und :hvind2 sind die „arrays“ für die „indicator variables“.
Jan 2005
19
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“ („dynamic SQL“- Beispiele)
Angenommen
Angenommen die
die Tabelle
Tabelle PROG
PROG besitzt
besitzt 99 Spalten.
Spalten. Es
Es soll
soll ein
ein dynamisches
dynamisches SQLSQLStatement
Statement geschreiben
geschreiben werden,
werden, das
das 55 „rows“
„rows“ in
in PROG
PROG einfügt.
einfügt.
stmt
stmt == 'INSERT
'INSERT INTO
INTO PROG
PROG (C1,
(C1, C2,
C2, C3,
C3, C4,
C4, C5,
C5, C6,
C6, C7,
C7, C8,
C8, C9)
C9)
VALUES
(?,
?,
?,
?,
?,
?,
?,
?,
?)';
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
attrvar
attrvar == 'FOR
'FOR MULTIPLE
MULTIPLE ROWS
ROWS ATOMIC'
ATOMIC'
nrows
nrows == 55
EXEC
EXEC SQL
SQL PREPARE
PREPARE ins_stmt
ins_stmt ATTRIBUTES
ATTRIBUTES :attrvar
:attrvar FROM
FROM :stmt;
:stmt;
EXEC
EXEC SQL
SQL EXECUTE
EXECUTE ins_stmt
ins_stmt FOR
FOR :nrows
:nrows ROWS
ROWS
USING
:V1,
:V2,
:V3,
:V4,
:V5,
:V6,
USING :V1, :V2, :V3, :V4, :V5, :V6, :V7,
:V7, :V8,
:V8, :V9
:V9
In diesem Beispiel repräsentieret jede Hostvariable in der USING Klausel einen „array“
von Werten für die korrespondierende Spalte des INSERT Statements.
Jan 2005
20
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.1 „multi row INSERT“ (Überlegungen zu DRDA)
Kann auf jedem „Requester“ oder „Server“, der DRDA Version 3 unterstützt
implementiert werden.
Zwischen DB2 for z/OS Systemen
•
Kein Einfluß des Multi row INSERT auf das „blocking“
•
Ein einzelnes „rowset“ wird mit EINEM INSERT-Statement verarbeitet
•
Ein einzelner „multi row fetch“ oder INSERT kann im Netz maximal
10 MB umfassen.
•
Für „remote clients“ denen EIN „rowset“ in EINEM „network request“
zurückgegeben wird, wird beim „multi row fetch“ die Block-Grösse von 32K
ignoriert
Zwischen DB2 auf anderen Pattformen und DB2 for z/OS
•
•
Jan 2005
Keine Unterstützung von „multi row“ Operationen im „embedded SQL“
In ODBC/CLI Umgebungen:
- eingeschränkte Unterstützung des „multi row“ FETCH
- Unterstützung des „multi row“ INSERT
21
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“
Geändert wurden:
• DECLARE CURSOR Statement
• Die Verwendung von „Host variable arrays“
• FETCH Statement
• Das „Positioned UPDATE“ Statement
• Das „Positioned DELETE“ Statement
z.z.B.
B.C1
C1als
alsCursor
Cursoreiner
einerQuery
Queryzum
zumLesen
Leseneines
eines„rowset“
„rowset“von
vonder
derTabelle
TabelleEMP
EMP
EXEC
EXECSQL
SQL
DECLARE
DECLAREC1
C1CURSOR
CURSOR
WITH
WITHROWSET
ROWSETPOSITIONING
POSITIONING
FOR
SELECT
*
FROM
FOR SELECT * FROMEMP;
EMP;
WITH
WITHROWSET
ROWSETPOSITIONING
POSITIONINGzeigt
zeigtan,
an,ob
ob„multiple
„multiplerows“
rows“als
als„rowset“
„rowset“
über
einen
einzelnen
FETCH
gelesen
werden
können
über einen einzelnen FETCH gelesen werden können
Jan 2005
22
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“
Beispiel-1:
Fetch
Fetch des
des vorhergehenden
vorhergehenden „rowset“
„rowset“ und
und positioniere
positioniere den
den Cursor
Cursor auf
auf dieses
dieses „rowset“
„rowset“
EXEC
EXECSQL
SQL
FETCH
FETCHPRIOR
PRIORROWSET
ROWSETFROM
FROMC1
C1FOR
FOR33ROWS
ROWSINTO...
INTO...
----Oder
Oder---EXEC
EXECSQL
SQL
FETCH
FETCHROWSET
ROWSET
STARTING
STARTINGAT
ATRELATIVE
RELATIVE-3
-3FROM
FROMC1
C1FOR
FOR33ROWS
ROWSINTO...
INTO...
Beispiel-2:
Hole
Hole 33 „rows“
„rows“ beginnend
beginnend bei
bei „row“
„row“ Nummer
Nummer 20
20 unabhängig
unabhängig von
von der
der aktuellen
aktuellen
Cursorposition
Cursorposition
EXEC
EXECSQL
SQL
FETCH
FETCH
Jan 2005
ROWSET
ROWSETSTARTING
STARTINGAT
ATABSOLUTE
ABSOLUTE20
20
FROM
C1
FOR
3
ROWS
INTO...
FROM C1 FOR 3 ROWS INTO...
23
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („array“-Definitionen)
Beispiel COBOL (C1 mit „fetch“ auf 10 „rows“ über „multi-row FETCH“ Stmnt)
01
01OUTPUT-VARS.
OUTPUT-VARS.
05
05NAME
NAMEOCCURS
OCCURS10
10TIMES.
TIMES.
49
NAME-LEN
PIC
S9(4)
49 NAME-LEN PIC S9(4)USAGE
USAGECOMP.
COMP.
49
49NAME-TEXT
NAME-TEXTPIC
PICX(40).
X(40).
05
05SERIAL-NUMBER
SERIAL-NUMBERPIC
PICS9(9)
S9(9)USAGE
USAGECOMP
COMPOCCURS
OCCURS10
10TIMES.
TIMES.
01
IND-VARS.
01 IND-VARS.
10
10INDSTRUC1
INDSTRUC1PIC
PICS9(4)
S9(4)USAGE
USAGECOMP
COMPOCCURS
OCCURS10
10TIMES.
TIMES.
10
INDSTRUC2
PIC
S9(4)
USAGE
COMP
OCCURS
10
TIMES.
10 INDSTRUC2 PIC S9(4) USAGE COMP OCCURS 10 TIMES.
PROCEDURE
PROCEDUREDIVISION.
DIVISION.
EXEC
SQL
EXEC SQL
DECLARE
DECLAREC1
C1SCROLL
SCROLLCURSOR
CURSORWITH
WITHROWSET
ROWSETPOSITIONING
POSITIONINGFOR
FOR
SELECT
NAME,
SERIAL#
FROM
EMPLOYEE
SELECT NAME, SERIAL# FROM EMPLOYEE
END-EXEC.
END-EXEC.
EXEC
EXECSQL
SQLOPEN
OPENC1
C1END-EXEC.
END-EXEC.
EXEC
EXECSQL
SQL
FETCH
FETCHFIRST
FIRSTROWSET
ROWSETFROM
FROMC1
C1FOR
FOR10
10ROWS
ROWS
INTO
:NAME:INDSTRUC1,:SERIAL-NUMBER:INDSTRUC2
INTO :NAME:INDSTRUC1,:SERIAL-NUMBER:INDSTRUC2
END-EXEC.
END-EXEC.
Jan 2005
24
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („array“-Definitionen)
Beispiel PL/1 (Rückgabe von 10 „rows“ aus der DEPARTMENT Tabelle)
DCL
CHAR(3),
DCL DEPTNO(10)
DEPTNO(10)
CHAR(3),
DEPTNAME(10)
CHAR(29)
DEPTNAME(10)
CHAR(29)VAR,
VAR,
MGRNO(10)
CHAR(6),
MGRNO(10)
CHAR(6),
ADMRDEPT(10)
CHAR(3);
ADMRDEPT(10)
CHAR(3);
DCL
IND_ARRAY1(10)
BIN
DCL IND_ARRAY1(10)
BINFIXED(15);
FIXED(15);
DCL
IND_ARRAY2(10)
BIN
DCL IND_ARRAY2(10)
BINFIXED(15);
FIXED(15);
DCL
IND_ARRAY3(10)
BIN
FIXED(15);
DCL IND_ARRAY3(10)
BIN FIXED(15);
DCL
BIN
DCL IND_ARRAY4(10)
IND_ARRAY4(10)
BINFIXED(15);
FIXED(15);
EXEC
EXECSQL
SQL
DECLARE
DECLAREC1
C1SCROLL
SCROLLCURSOR
CURSOR
WITH
WITHROWSET
ROWSETPOSITIONING
POSITIONINGFOR
FOR
SELECT
*
FROM
DEPARTMENT;
SELECT *
FROM DEPARTMENT;
EXEC
OPEN
EXECSQL
SQL
OPENC1;
C1;
EXEC
EXECSQL
SQL
FETCH
FETCHFIRST
FIRSTROWSET
ROWSETFROM
FROMC1
C1FOR
FOR10
10ROWS
ROWS
INTO
:DEPTNO:IND_ARRAY1,
INTO
:DEPTNO:IND_ARRAY1,
:DEPTNAME
:DEPTNAME:IND_ARRAY2,
:IND_ARRAY2,
:MGRNO:IND_ARRAY3,
:MGRNO:IND_ARRAY3,
:ADMRDEPT:IND_ARRAY4;
:ADMRDEPT:IND_ARRAY4;
Jan 2005
25
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („array“-Definitionen)
Beispiel C/C++ (Declare integer und varying character array f. „multi-row fetch“)
long
longserial_num
serial_num[10];
[10];
struct
struct{{
short
shortlen;
len;
char
chardata
data[18];
[18];
}}name
[10];
name [10];
...
...
EXEC
EXECSQL
SQL
DECLARE
DECLAREC1
C1CURSOR
CURSORFOR
FORSELECT
SELECTNAME,
NAME,SERIAL_NO
SERIAL_NO
FROM
FROMEMPLOYEE
EMPLOYEE
WITH
WITHROWSET
ROWSETPOSITIONING;
POSITIONING;
...
...
EXEC
EXECSQL
SQL
OPEN
OPENC1;
C1;
...
...
EXEC
EXECSQL
SQL
FETCH
FETCHFIRST
FIRSTROWSET
ROWSETFROM
FROMC1
C1
;;
FOR
FOR10
10ROWS
ROWSINTO
INTO:NAME,
:NAME,:SERIAL_NUM
:SERIAL_NUM
Jan 2005
26
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („rowsets“)
„rowset“
•
Ein Gruppe von Zeilen aus einer RT, die mit einem einzelnen FETCH
Zurückgeliefert(der „eingefügt“) werden
•
•
Das Programm kontrolliert, wieviele „rows“ in einem „rowset“ geliefert werden
(es kontrolliert die Grösse des „rowset“)
- Kann im FETCH ... FOR n ROWS Statement angegeben werden (n <= 32767)
•
Jede Gruppe von „rows“ wird als „rowset“ behandelt
•
„single row“ und „multiple row fetches“ können beim „multi-fetch cursor“ gemischt
werden
FETCH
Jan 2005
FIRST ROWSET STARTING AT ABSOLUTE 10
FROM CURS1
FOR 6 ROWS INTO :hva1, :hva2;
27
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („rowset“ Positionierung)
„Result table“
FETCH FIRST ROWSET
FOR 3 ROWS
FETCH NEXT ROWSET
FETCH ROWSET STARTING
AT ABSOLUTE 8
FOR 2 ROWS
Anmerkung: Der Cursor wird auf ALL
„rows“ im aktuellen „rowset“ positioniert
Jan 2005
28
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ (Mischen von „row“ / „rowset“ Positionierung)
„Result table“
FETCH FIRST ROWSET
FOR 3 ROWS
FETCH NEXT ROWSET
FETCH NEXT
Anmerkung: FETCH NEXT ist relativ zur
ersten „row“ im „current rowset“
Jan 2005
29
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („partial rowsets“)
Anmerkung: Der 2. „rowset
fetch“ liefert einen SQLCODE
+100
SQLERRD(3) enthält die Anzahl
der zurückgegebenen „rows“
(2 in diesem Fall)
FETCH ROWSET STARTING
AT ABSOLUTE 11 FOR
FOR 3 ROWS
FETCH NEXT ROWSET
FOR 3 ROWS
Jan 2005
„Result table“
CUST_NAME
Ian
Mark
John
Karen
Sarah
Florence
Dylan
Bert
Jo
Karen
Gary
Bill
Geoff
Julia
Sally
FETCH ROWSET
STARTING AT
ABSOLUTE 5
FOR 3 ROWS
FETCH PREVIOUS
ROWSET
FOR 10 ROWS
Anmerkung: Der 2.
„rowset Fetch“ liefert
einen SQLCODE +20237
SQLERRD(3) enthält 4
30
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („isolation“)
•
Cursor wird auf „all rows“ im aktuellen „rowset“ positioniert
•
Locks werden auf „all rows“ im „rowset“ gehalten, abhängend vom
„isolation level“ und davon, ob eine „result table“ materialisiert hat
•
Dies beeinflußt den Vorgang eines „refetch“ derselben „rows“ bei
der Verwendung eines FETCH CURRENT auf das „current rowset“
2.3.2 „multi row FETCH“ (mit „scrolling“)
•
„scrolling“ im „rowset“:
- Bei „insensitive fetches“, können „updates“ durch das eigene AP zu Änderungen und
„holes“ führen
- Bei „sensitive fetches“, können „updates“ durch andere AP ebenfalls zu zu Änderungen
und „holes“ führen
- Beispiel: FETCH PRIOR ROWSET kann „update“ oder „delete holes“ an der Stelle
haben, wo die vorher gelesenen „rows“ gestanden haben
•
Jan 2005
„Row“-Inhalte können sich zwischen den „fetches“ verändern
31
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („dynamic scrollable Cursors“)
•
Startpunkt und Inhalte von „rowsets“ ändern sich beim „scrolling“
vorwärts und rückwärts
•
Man beachte, dass gerade nach dem „fetch“ des CURRENT ROWSET,
können andere Applikationen zwischenzeitlich „rows“ dort einfügen, wo
die Zeilen als Bestandteil eines „rowset“ zurückgegeben wurden
- „Refetching current rowset“ kann also unterschiedliche „rows“ zurückliefern,
unabhängig davon, ob RR ISOLATION verwendet wurde oder nicht
•
FETCH PRIOR ROWSET liefert die vorangehenden n „rows“, die vom
Anfang der „current cursor position“ qualifiziert wurden
- Deshalb werden n „rows“ solange zurückgegeben, wie der Beginn des
„rowset“ nicht erreicht ist
Jan 2005
32
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.3.2 „multi row FETCH“ („positioned UPDATE/DELETE“ / Beispiele)
Cursor CS1 ist auf ein „rowset“ bestehend aus 10 rows der Tabelle T1
positioniert:
Beispiel 1:
Das folgende UPDATE Statement soll alle 10 rows des „rowset“ ändern:
EXEC SQL
UPDATE T1 SET C1 = 5 WHERE CURRENT OF CS1
END-EXEC
Beispiel 2:
Das folgende UPDATE Statement soll die row 4 des „rowset“ verändern:
EXEC SQL
UPDATE T1 SET COL1='ABC'
WHERE CURRENT OF CS1 FOR ROW 4 OF ROWSET
END-EXEC
Jan 2005
33
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.4 GET DIAGNOSTICS
•
Gibt SQL Fehlerinformationen zurück
•
- Für allgemeine Statements
- Für jede beliebige Bedingung (wenn Mehrfachfehler auftreten)
Unterstützt SQL Fehlermeldungen > 70 Bytes (SQLCA limitation)
•
Muss „embedded“ sein – kann nicht dynamisch „prepared“ werden
INSERT
INSERTINTO
INTOT1
T1FOR
FOR55ROWS
ROWSVALUES(:ARRAY);
VALUES(:ARRAY);
GET
GETDIAGNOSTICS
DIAGNOSTICS :ERR_COUNT
:ERR_COUNT==NUMBER;
NUMBER;
DO
II
=
1
TO
ERR_COUNT;
DO II = 1 TO ERR_COUNT;
GET
GETDIAGNOSTICS
DIAGNOSTICSCONDITION
CONDITION:II
:II
:RC
=
RETURNED_SQLSTATE;
:RC = RETURNED_SQLSTATE;
END;
END;
Jan 2005
34
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.4 GET DIAGNOSTICS (Syntax)
Statement-info
GET DIAGNOSTICS
condition-info
Statement-info
,
hostvariable
=
Statement-info-item-name
DB2_GET_DIAGNOSTICS_DIAGNOSTICS
=
DB2_LAST_ROW
DB2_NUMBER_PARAMETER_MARKERS
DB2_RESULT_SETS
DB2_RETURN_STATUS
DB2_SQL_ATTR_CURSOR_HOLD
DB2_SQL_ATTR_CURSOR_ROWSET
DB2_SQL_ATTR_CURSOR_SCROLLABLE
DB2_SQL_ATTR_CURSOR_SENSITIVITY
DB2_SQL_ATTR_CURSOR_TYPE
MORE
NUMBER
ROW_COUNT
combined-info
Statement-info-item-name
Info über das zuletzt ausgeführte
Statement, zB. Grösse des Cursors
Einige Felder gelten nur für bestimmte Stmnts:
Get Diagnostics
Multi-row fetch
Prepare
Call
Open/Allocate
condition-info
CONDITION
hostvariable
Jan 2005
Hostvariable
integer
,
=
condition-info-item-name
Connection-info-item-name
35
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.4 GET DIAGNOSTICS (Syntax)
•
Zum Feststellen, wieviele „rows“ in einem UPDATE Statement verändert
wurden
GET DIAGNOSTICS :rcount = ROW_COUNT;
•
Um „multiple SQL errors“ in einem NOT ATOMIC „multi-row insert“ zu
behandeln
GET DIAGNOSTICS :numerrors = NUMBER;
... Dann einen „loop“ für die folgende Fehlerzahl:
GET DIAGNOSTICS CONDITION :i
:retstate = RETURNED_SQLSTATE
•
Um alle „diagnostic information“ eines SQL Statements sehen zu
können:
GET DIAGNOSTICS :diags = ALL STATEMENT
Beispiel-Output in :diags
Number=1; Returned_SQLSTATE=02000;
DB2_RETURNED_SQLCODE=+100;
für alle „items“ und für alle Bedingungen (getrennt durch „;“)
Jan 2005
36
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT
•
Users können automatisch eingefügte Werte erhalten
- Wie z. B: „Identity columns“, „sequence values“
- „User-defined defaults“, „expressions“
- von BEFORE INSERT Triggern modifizierte Spalten
- ROWIDs
Vorteile:
• Verbessert Nutzbarkeit und Mächtigkeit von SQL
Jan 2005
•
Vermindert die „network costs“ in Applikationsprogrammmen
•
Schlägt sich auf die prozedurale Logik in „stored procedures“ nieder
37
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (Beispiel)
DECLARE CS1 CURSOR FOR
SELECT EMP_ROWID
FROM FINAL TABLE
(INSERT INTO EMP_RESUME (EMPNO)
SELECT EMPNO FROM EMP)
ROWID
NOT NULL
GENERATED
ALWAYS
NOT NULL WITH
DEFAULT
'PROJECT NAME
UNDEFINED'
SELECT PROJNAME INTO :name_hv
FROM FINAL TABLE
(INSERT INTO PROJ (PROJNO,DEPTNO,RESPEMP)
VALUES (:projno-hv,:deptno-hv,:respemp-hv))
Jan 2005
38
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (Änderungen im ORDER BY)
CREATE TABLE EMPLOYEE
(EMPNO
INTEGER GENERATED ALWAYS
AS IDENTITY,
.....
DECLARE CS2
SCROLL CURSOR
WITH ROWSET POSITIONING FOR
SELECT
EMPNO
FROM FINAL TABLE
(INSERT INTO EMPLOYEE (NAME, TELE)
FOR 3 ROWS
VALUES(:HVA1, :HVA2)
)
ORDER BY INPUT SEQUENCE
Jan 2005
INTEGER
GENERATED
ALWAYS AS
IDENTITY
39
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (Beispiel für TRIGGER)
CREATE TRIGGER TRIG1
NO CASCADE BEFORE INSERT ON EMPLOYEE
REFERENCING NEW AS NEWSALARY
FOR EACH ROW
MODE DB2SQL
WHEN (NEWSALARY.LEVEL = 'Associate')
SET NEWSALARY.SALARY = NEWSALARY.SALARY + 5000.00
…..
SELECT
FROM
Jan 2005
:name_hv ='New Hire'
NAME,SALARY INTO :name-hv, :salary-hv
:salary_hv =40000.00
FINAL TABLE
(INSERT INTO EMPLOYEE(NAME,SALARY,LEVEL)
VALUES('New Hire',35000.00,'Associate'))
40
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (Beispiel für WITH HOLD Cursors)
Beispiel: Der User deklariert den Cursor, öffnet den Cursor, liest 2
„rows“, setzt einen COMMIT und liest dann die 3. „row“….
DECLARE CS1 CURSOR
WITH HOLD FOR
SELECT EMP_ROWID
FROM FINAL TABLE
(INSERT
INTO EMP_RESUME(EMPNO)
SELECT EMPNO FROM EMP)
OPEN CS1
<-------------- fügt 5 rows ein
FETCH CS1 INTO :hv1 <-------------- erhält die „rowid“ für die erste „row“
FETCH CS1 INTO :hv2 <--------------- erhält die „rowid“ für die zweite „row“
COMMIT
<-------------- alle 5 inserts sind „committed“
FETCH CS1 INTO :hv3 <-------------- erhält die „rowid“ für die dritte „row“
Jan 2005
41
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (Beispiel für WITH HOLD Cursors)
Beispiel: Setzt die Applikation einen „savepoint“ VOR dem Öffnen
des Cursors und veranlasst dann einen ROLLBACK auf den
„savepoint“, werden alle INSERTs hinfällig
DECLARE
CS2 CURSOR FOR
SELECT EMP_ROWID FROM FINAL TABLE
(INSERT INTO EMP_RESUME(EMPNO)
SELECT EMPNO FROM EMP)
SAVEPOINT A ON ROLLBACK RETAIN CURSORS
OPEN CS2
SAVEPOINT B ON ROLLBACK RETAIN CURSORS
...
ROLLBACK TO SAVEPOINT B
ROLLBACK TO SAVEPOINT A
Jan 2005
setzt 1. Savepoint
insert der rows
setzt 2. Savepoint
rows immer noch in
der „table“ EMP_RESUME
alle „inserted rows“ sind
zurückgesetzt
42
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.5 INSERT innerhalb eines SELECT (einige Überlegungen)
FETCH FIRST clause
-
Hat keinen Einfluß darauf, welche „rows“ eingefügt werden
Alle „rows“ aus dem NSERT Statement werden in das Zielobjekt
eingefügt
Die „result table“ enthält lediglich die „rows“, für die die FETCH FIRST
Klausel gültig ist
DECLARE CURSOR
-
Der Cursor ist IMMER „read-only“
OPEN CURSOR
-
Jan 2005
SQLERRD3 wird gesetzt, um die Auswirkungen des INSERT
Statements zu zeigen („number of rows inserted“)
43
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL
WITH
RPL (PART, SUBPART, QUANTITY) AS
(
Initialisierungs-SELECT
Initialisierungs-SELECT
SELECT
FROM
WHERE
ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
PARTLIST ROOT
ROOT.PART = '01‚
UNION ALL
Interations-SELECT
Interations-SELECT
SELECT
FROM
WHERE
)
CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
RPL PARENT, PARTLIST CHILD
PARENT.SUBPART = CHILD.PART
Haupt-SELECT
Haupt-SELECT
SELECT
PART, SUBPART, SUM(QUANTITY) AS QUANTITY
FROM
RPL
GROUP BY PART, SUBPART
Jan 2005
44
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Der Intitialisierungs-SELECT
SELECT
FROM
WHERE
Jan 2005
ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
PARTLIST ROOT
ROOT.PART = '01‚
45
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Erste Iteration
SELECT
FROM
WHERE
Jan 2005
CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
RPL PARENT, PARTLIST CHILD
PARENT.SUBPART = CHILD.PART
46
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Zweite Iteration
SELECT
FROM
WHERE
Jan 2005
CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
RPL PARENT, PARTLIST CHILD
PARENT.SUBPART = CHILD.PART
47
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Haupt-SELECT
SELECT
PART, SUBPART, SUM(QUANTITY) AS QUANTITY
FROM
RPL
GROUP BY PART, SUBPART
Jan 2005
48
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Kontrolle der Rekursionstiefe
WITH
RPL ( LEVEL, PART, SUBPART, QUANTITY) AS
(
Initialisierungs-SELECT
Initialisierungs-SELECT
SELECT
FROM
WHERE
0, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
PARTLIST ROOT
ROOT.PART = '01‚
UNION ALL
Interations-SELECT
Interations-SELECT
SELECT
FROM
WHERE
)
PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART,
CHILD.QUANTITY
RPL PARENT, PARTLIST CHILD
PARENT.SUBPART = CHILD.PART AND PARENT.LEVEL < 2
Haupt-SELECT
Haupt-SELECT
SELECT
LEVEL, PART, SUBPART, SUM(QUANTITY) AS QUANTITY
FROM
RPL
GROUP BY LEVEL, PART, SUBPART
Jan 2005
49
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.6 Rekursives SQL – Empfehlungen
• Tabellen mit „LEVEL“-Feld
• Desk-check „recursive
SQL“ Statement
• Test des „recursive SQL“
gegen KLEINE „test
tables“
• Kontrollieren der
Rekursion
Jan 2005
50
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.7 Expressions im GROUP BY
SELECT
SUBSTR(CHAR(HIREDATE,ISO),1,3)
CONCAT '0 - 9' AS HIREDECADE,
MIN(SALARY) AS MINIMUM_SALARY
FROM
EMPLOYEE
GROUP BY SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9'
Jan 2005
51
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.8 „common table expressions“
WITH
E AS
(
SELECT
EMPNO, LASTNAME,SALARY,
SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9'
AS HIREDECADE
FROM EMPLOYEE
),
M (HIREDECATE, MINIMUM_SALARY) AS
(
SELECT
S.HIREDECADE, MIN(S.SALARY)
FROM
E
GROUP BY S.HIREDECADE
SELECT
FROM
Jan 2005
)
E.EMPNO, E.LASTNAME, E.HIREDECADE,
E.SALARY, M.MINIMUM_SALARY
E
INNER JOIN
M
ON
E.HIREDECADE = M.HIREDECADE
52
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.8 „nested table expressions“
SELECT E.EMPNO, E.LASTNAME, E.HIREDECADE, E.SALARY, M.MINIMUM_SALARY
FROM
(
SELECT
EMPNO, LASTNAME,SALARY,
SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9'
AS HIREDECADE
FROM EMPLOYEE
) AS
E
INNER JOIN
(
SELECT
FROM
S.HIREDECADE, MIN(S.SALARY) AS MINIMUM_SALARY
(
SELECT
SUBSTR(CHAR(HIREDATE,ISO),1,3)
CONCAT '0 - 9' AS HIREDECADE, SALARY
FROM E
EMPLOYEE
) AS
S
GROUP BY S.HIREDECADE
) AS
M
ON E.HIREDECADE = M.HIREDECADE
Jan 2005
53
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.1 „identity columns“
Vorteile .....
•
•
•
•
•
Bessere Performance und Parallelverarbeitung als bei „application generated counters
Garantierte Eindeutigkeit innerhalb eines „subsystems und einer „data sharing group“
„Recoverability“ im Falle eines DB2 Systemfehlers
Fehler in einem „data sharing member“ beeinflußt nicht die anderen „member“ beim
Generieren der „key values“
Einfache Implementierung
What's new? .....
•
•
•
•
•
•
Jan 2005
„Identity Column“ eingeführt in der DB2 Version 6
IC werden automatisch von DB2 generiert
Eindeutige, sequentielle, recoverfähige Werte
Nützlich beim Generieren von „unique primary key values“
in Version 8 kann das ALTER TABLE Statement „identity columns“ enthalten
Spezifikationen zum Modifizieren von Attributen von existierenden „identity columns“
54
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.1 „identity columns“ (Überlegungen)
Vor Version 8: für „unload“ und „reload“ von Daten musste die „identity
column“ mit GENERATED BY DEFAULT definiert sein
• Mit der Option GENERATED ALWAYS war die einizige Option für
unload/reload das folgende Vorgehen:
-
Unload der Tabelle
DROP der tabelle
Re-CREATE der Tabelle MIT GENERATED BY DEFAULT
Reload der Tabelle
• Ansonsten wird DB2 neue Werte für alle „rows“ während des „reload“
generieren. Dies mag nicht gewünscht sein…
Mit Version 8: Spezifiziert man GENERATED ALWAYS und will später die Daten
mit unload/reload behandeln kann man jetzt wie folgt vorgehen:
-
Jan 2005
ALTER TABLE ALTER COLUMN SET GENERATED BY DEFAULT
Unload der Tabelle
Reload der Tabelle
ALTER TABLE ALTER COLUMN SET GENERATED ALWAYS
55
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.1 „identity columns“ (Verbesserungen)
Dynamisches ALTER von „Identity column“ Attributen
• ALTER TABLE ALTER COLUMN erweitert:
- Möglichkeit zur Modifikation der „identity column“ Attribute und
- Spezifikation der Weiterführung der „sequences“
• Es werden nur die zukünftigen Werte von dieser Änderung betroffen sein
• Kein ALTER des „data type“ der „identity column“ möglich
• Ungenutzte „cache- Werte“ gehen verloren, wenn die „column attributes“
geändert wurden
Unterstützung neuer „keywords“ um die Portierung von anderen Plattformen
zu erleichtern:
• NO MINVALUE
• NO MAXVALUE
• NO ORDER, ORDER
• INCREMENT BY kann 0 sein
• MINVALUE = MAXVALUE ist möglich
Jan 2005
56
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.1 „identity columns“ (Verbesserungen)
• CREATE TABLE...
• colname datatype...
GENERATED ALWAYS / BY DEFAULT
AS IDENTITY
(
START WITH n,
INCREMENT BY n,
CACHE 20 / NO CACHE / CACHE n
CYCLE / NO CYCLE
MINVALUE / NO MINVALUE
MAXVALUE / NO MAXVALUE
ORDER / NO ORDER
)
DB2 V6
DB2 V7
DB2 V8
• Möglich ist MINVALUE = MAXVALUE
• Möglich ist INCREMENT BY 0
Jan 2005
57
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ – Das Problem
Die übliche Implementierung besteht aus einer Tabelle mit einer „row“, die die
„sequence number“ enthält
• Jede Transaktion sperrt diese „table“, erhöht die Zahl, „commited“ (d.h.zu einem
Zeitpunkt kann nur EINE Transaktion die „sequence number“ erhöhen)
• ODER: Eine Variante wäre, SELECT MAX( ) + 1...WITH RR zu nutzen, mit
nachfolgendem INSERT des wiedergewonnen Schlüssels
• Problem:
- Die Page, die den Zähler enthält, bildet einen „hot spot“ in der DB und führt damit
zu „unpredictable transaction delays“, verursacht durch das Setzen des „intersystem P-lock“ Flags auf diese Page und durch das Kennzeichen „buffer invalid“
und den folgenden „refresh“.
- „Contention“ verhindert einen effizienten Durchsatz von Transaktionen und eine
befriedigendes Applikationsverhalten
- Stürzt ein „member“ ab, so kann es passieren, dass die Locks, gehalten vom
fehlerhaften „member“, den Zugriff von anderen „members“ auf den „shared
counter“ verhindern
„Identity columns“ bieten nur eine teilweise Lösung des Problems…
Jan 2005
58
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ – Die Lösung
Vermeiden des „concurrency“ und Performance Problems indem APs ihre
eigenen „sequence numbers“ (hotspots) generieren
DB2 Sequences ermöglichen mehreren Transaktions parallel das Erhöhen
der „sequence number“ und garantieren, dass jede Zahl EINDEUTIG
istunique
„Sequences“ können von mehreren Usern zugegriffen und erhöht werden,
ohne einen WAIT-Zustand durchlaufen zu müssen
•
DB2 wartet nicht auf den COMMIT von Transaktionen, die eine
„sequence“ erhöht haben, bevor diese durch eíne andere Transaktion
erneut erhöht werden kann
Fehler in einem DB2 „member“ einer Gruppe verhindert niemals den
Zugriff (R/W) auf die „sequence“ durch die „überlebenden members“
•
Jan 2005
Es gibt keine „locks“, die den Zugriff auf eine „sequence“ verhindern
könnte
59
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ und „idendity columns“ – Ein Vergleich
Jan 2005
Sequences
Identity columns
„Stand-alone object „
an eine „table“ gebunden
Kann eine „sequence“ für mehrere
„tables“ oder mehere „sequences“ in
einer „table“ anwenden
„One to one“ Beziehung
zwischen „identity“ und „tables“
Erhält man mit NEXT VALUE FOR /
PREVIOUS VALUE FOR Ausdrücken
Erhält man mit IDENTITY_VAL_LOCAL
Funktion – innerhalb eines Agenten
Kann über ALTER SEQUENCE
geändert werden
Kann über ALTER TABLE
(ALTER COLUMN) geändert werden
VOR V8 keine Änderung möglich
60
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ und SQL
CREATE SEQUENCE
• Erzeugt eine „sequence“ auf dem entsprechenden „application server“
• Kann in einem „application program“ genutzt oder interaktiv verwendet
werden
ALTER SEQUENCE
• Kann die Werte von INCREMENT BY, MIN VALUE, MAXVALUE,
CACHE, CYCLE verändern und einen RESTART WITH mit einer
anderen „sequence“ durchführen
DROP SEQUENCE
COMMENT ON SEQUENCE
GRANT/REVOKE ON SEQUENCE
NEXT VALUE FOR und PREVIOUS VALUE FOR
Jan 2005
61
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ – CREATE Beispiel
CREATE
CREATESEQUENCE
SEQUENCESEQTEST1
SEQTEST1AS
ASINTEGER
INTEGER
START
STARTWITH
WITH
11
INCREMENT
INCREMENTBY
BY 11
MINVALUE
MINVALUE
11
MAXVALUE
MAXVALUE
55
CYCLE
CYCLE
CACHE
CACHE
55
NO
NOORDER;
ORDER;
Jan 2005
62
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ – Anwendbarkeit
Applikationen können die Werte „next sequence“ wie folgt erhalten:
NEXT VALUE FOR < sequence- name >
PREVIOUS VALUE FOR < sequence-name >
( NEXT VALUE funktioniert n ur innerhalb derselben „session“ )
Kann in folgenden Situationen benutzt werden:
SELECT Statement oder SELECT INTO Statement in der Select-Klausel (ausser bei
DISTINCT, UNION, GROUP BY oder ORDER BY)
INSERT Statement in der VALUES Klausel
INSERT Statement in der Select-Klausel des „fullselect“
Update Statement in der SET Klausel (ausser in der „select-clause“ eines „fullselect“ einer
„expression“)
VALUES oder VALUES INTO Statement (ausser in der „select-clause“ eines „fullselect“ einer
„expression“)
CREATE PROCEDURE, FUNCTION, TRIGGER
Set :hv = NEXT VALUE FOR <sequence>
Jan 2005
63
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.2 „sequences“ – Restriktionen (NEXT/PREVIOUS VALUE)
• Join Bedingung eines „full outer join“
• Default Wert einer Spalte im CREATE oder ALTER TABLE
Statement
• „Generated column“ Definition in einem CREATE oder ALTER
TABLE Statement
• „Materialized query table“ Definition in einem CREATE oder
ALTER TABLE Statement
• Bedingung eines CHECK „constraint“
• Eingabewertspezifikation für LOAD
• CREATE VIEW Statement
Jan 2005
64
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.3 „sequences“ – Beispiel-1
1)
„sequence“ definiert mit START WITH 1, INCREMENT BY 1
SELECT NEXT VALUE FOR MYSEQ FROM SYSIBM.SYSDUMMY1;
Wert = 1
SELECT NEXT VALUE FOR MYSEQ FROM SYSIBM.SYSDUMMY1;
Wert = 2
COMMIT;
SELECT PREVIOUS VALUE FOR MYSEQ FROM SYSIBM.SYSDUMMY1; Wert = 2
2)
PREVIOUS/NEXT VALUE FOR sepezifiziert in der SET Klausel eines UPDATE
UPDATE T SET C1 = (SELECT PREVIOUS VALUE FOR S1 FROM T);
UPDATE T SET C1 = NEXT VALUE FOR S1;
3)
Ansehen der „sequence“ während des INSERT
SELECT * FROM FINAL TABLE
(
INSERT INTO TESTTAB (KEYVALUE, TESTSEQ)
VALUES ( NEXT VALUE FOR SEQTEST1, NEXT VALUE FOR SEQTEST2 )
);
Jan 2005
65
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.3 „sequences“ – Beispiel-2
INSERT
CREATE
Jan 2005
INTO table (part_no, col2, col3... )
VALUES
(NEXT VALUE FOR SEQGEN1, col1, col2..)
SEQUENCE SEQGEN1 AS INTEGER
START WITH 1 INCREMENT BY 1
MINVALUE 1 MAXVALUE 5
CYCLE CACHE 5 NO ORDER;
66
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.9 Verbesserungen bei diversen DB2-Objekten
2.9.4 „sequences“ – Konstante
CREATE
CREATE SEQUENCE
SEQUENCEconsequence
consequenceAS
ASINTEGER
INTEGER
START
STARTWITH
WITH
11
INCREMENT
INCREMENTBY
BY
00
MINVALUE
MINVALUE
00
MAXVALUE
MAXVALUE
55
CYCLE
CYCLE
CACHE
CACHE
55
NO
NOORDER;
ORDER;
Jan 2005
67
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 „Qualified Column Names“ in INSERT und UPDATE
• Spaltenname können über „table name“, oder ein „schema“ gefolgt von
einem „table name“ im INSERT
• Spaltennnamen in der SET Klausel in einem UPDATE Statement können
ebenfalls qualifiziert werden
• Diese Verbesserungeh dienen der Kompatibilität innerhalb der „DB2
family“
Beispiel:
Jan 2005
UPDATE T1
SET T1.C1 = C1 + 10 WHERE C1 = 1
UPDATE T1 T
SET T.C1 = C1 + 10 WHERE C1 = 2
68
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 IS NOT DISTINCT FROM
• SQL nutzt eine „three-valued logic“ in der jedes beliebige Ergebnis geliefert
werden kann: TRUE, FALSE, oder NULL
• Applikationen können die Klausel IS NOT DISTINCT FROM verwenden ,
um ein TRUE Resultat anstatt eines NULL zu erhalten, wenn man gegen
NULL Werte vergleicht
Beispiel:
SELECT C1 FROM T1 WHERE C1 IS NOT DISTINCT FROM :hv;
Von der Query
zurückgegeben
Jan 2005
69
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 EXPLAIN Satement-Cache
• Verbesserungen des EXPLAIN Statement ermöglichen die Nutzung von
EXPLAIN Information aus dem DB2 „global statement cache“
• Visual Explain ist entsprechend angepasst
1. Explain des „cached statement“ mit der „statement ID“ 124:
SID = 124;
EXEC SQL EXPLAIN STMTCACHE STMTID :SID;
2. Explain des „cached statement“ mt dem „statement token“ 'SELECTEMP':
EXEC SQL EXPLAIN STMTCACHE STMTTOKEN 'SELECTEMP';
Jan 2005
70
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 neue „builtin functions“
• „Encryption functions“
• Generieren von eindeutigen Werten
SELECT HEX(GENERATE_UNIQUE()) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+---------+--------00BAE9C5B971D0712000010A02
• Neue Funktionen:
- CHARACTER_LENGTH
- POSITION
- SUBSTRING (in V7 nur SUBSTR Funktion)
• Geändert wurden die Funktionen:
- (VAR)CHAR
- (DB)CLOB
- (VAR)GRAPHIC
- INSERT
Jan 2005
-
LEFT
LOCATE
RIGHT
CAST
71
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 Sessionvariable
• Variable gesetzt von DB2, „connection“ oder „sign-on exit“
• „Built in function“ zum Lesen der Werte einer variablen
- in views, triggers, „stored procedures“ und „constraints“
• Mehr generelle, flexible Zugriffsprüfungen (mehrere Spalten, AND/OR Logik, ..)
• Ergänzt andere „security“ Mechanismen
CREATE VIEW V1 AS SELECT *
WHERE
FROM
T1
COL5 = GETVARIABLE(SYSIBM.SECLABEL);
• DB2-defined session variables ( alle im „SYSIBM“ Schema )
SYSIBM.DATA_SHARING_GROUP_NAME
SYSIBM.PACKAGE_NAME
SYSIBM.PACKAGE_SCHEMA
SYSIBM.PACKAGE_VERSION
SYSIBM.PLAN_NAME
SYSIBM.VERSION
SYSIBM.SECLABEL
SYSIBM.SYSTEM_NAME
SYSIBM.SYSTEM_ASCII_CCSID
SYSIBM.SYSTEM_EBCDIC_CCSID
SYSIBM.SYSTEM_UNICODE_CCSID
• Bis zu 10 user-defined session variables
Jan 2005
72
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.10 Transparenz der ROWID
•
Die ROWID braucht nicht mehr explizit als Spalte in einer Tabelle mit LOB‘s
definiert zu werden
•
DB2 generiert eine "hidden" ROWID Spalte, die nicht im SELECT * aufscheint
•
Vereinfacht die Portierung von LOB Applikationen auf/von anderen
Plattformen
CREATE DATABASE BSDBLOB ;
CREATE TABLESPACE BSTSLOB IN BSDBLOB;
CREATE TABLE LOB_TEST
( EMPNO CHAR( 06 ) NOT NULL,
RESUME CLOB( 1K ) )
IN BSDBLOB.BSTSLOB CCSID EBCDIC;
CREATE LOB TABLESPACE BSTSLOBC
IN BSDBLOB LOG NO;
CREATE AUX TABLE AUX_LOB_TEST
IN BSDBLOB.BSTSLOBC STORES LOB_TEST
COLUMN RESUME;
Jan 2005
SELECT SUBSTR(NAME,1,30) AS NAME
, COLTYPE , HIDDEN
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'LOB_TEST';
---------+---------+---------+-------------+
NAME
COLTYPE
HIDDEN
---------+---------+---------+-------------+
EMPNO
CHAR
N
RESUME
CLOB
N
DB2_GEN_ROWID_FOR_LOBS ROWID
P
73
2.
2. Neue
Neue Funktionen
Funktionen im
im SQL
SQL
2.1 „scalar full select“
2.2 „multiple DISTINCT‘s“
2.3 „multi row“ Operationen
2.3.1 „multi row“ INSERT
2.3.2 „multi row“ FETCH
2.4 GET DIAGNOSTICS
2.5 INSERT innerhalb eines SELECT
2.6 Rekursives SQL
2.7 Expressions im GROUP BY
2.8 „common table“ /“nested table“ Ausdrücke
2.9 Verbesserungen bei div Objekten („identity columns“, „sequences“)
2.10 Sonstige
Jan 2005
74