ACCOUNT

(*)
IBM DB2 V7
for OS/390
Neue Funktionen und „features“
(DB2-UPD-V7)
(*) ist eingetragenes Warenzeichen der IBM International Business Machines Inc.
Jan 2002
1
DB2
Überblick)
DB2 V7
V7 Inhalte
Inhalte ((Überblick)
Jan 2002
•
Neue Basis-Funktionen
• Spracherweiterungen
• Funktionserweiterungen
• Performance-Verbesserung
•
Neue Werkzeuge
• S/390 basiert
• Workstation-orientiert
•
•
Erweiterung bestehender Werkzeuge
Migration
•
Addendum/Ausblick: V7R2
2
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“
1.2 CONSTRAINT – Verbesserungen
1.3 Scrollable Cursor
1.4 Row-Expressions
1.5 Limitierter FETCH
1.6 SELF-REFERENCING UPDATE / DELETE
Jan 2002
3
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“
UNION [ ALL ] ist erlaubt in
• CREATE VIEW
• Nested table expressions
• Predicates (Suchargumenten)
- EXISTS
-
quantifizierte Prädikate
IN-Listen
• UPDATE – Befehlen
• INSERT - Anweisungen
Jan 2002
4
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ - Syntax
CREATE VIEW
fullselect
EXISTS
IN
UPDATE...SET col =
INSERT INTO
< , > , = , <=, >= (
2
subselect
)
<, >, = ANY / ALL / SOME
Jan 2002
5
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“
• „Query rewrite“ versucht optimalen Zugriff zu
gewährleisten (kein „view materializing“)
• SQLCODE –811, wenn Subquery kein eindeutiges
Ergebnis liefert, semantisch aber nur ein Wert zulässig
ist („quantified predicate“)
• Performance: Neue Spalten & Werte in PLAN_TABLE für EXPLAIN
- PARENT_QBLOCKNO
- TABLE_TYPE
- Neue Werte TABLEX, UNION, UNIONA in QBLOCK_TYPE
• In DB2/UDB (Intel) schon seit V6 !
Jan 2002
6
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ - Beispiele
select perstype, count(*) from
(
select
'Worker' as perstype, empno from employee
union all
select
'Boss ' as perstype, mgrno from department
)
as T1
group by perstype
;
Select
*
where
Jan 2002
from department
deptno in
( select
deptno
from department
where
mgrno is null
union all
select
workdept
from employee
where
salary > 50000
)
7
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ – DB-Modell
Jan 2002
8
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ – Beispiele(„tablespec“)
Gesamtsummen
Gesamtsummen aller
aller CC
CC nach
nach „accounts“
„accounts“ mit
mit account,
account, account
account name
name und
und total
total
balance.
balance.
SELECT
SELECT ACCOUNT.ACCOUNT,
ACCOUNT.ACCOUNT, ACCOUNT.ACCOUNT_NAME,
ACCOUNT.ACCOUNT_NAME,
SUM(ALLCARDS.AMOUNT)
SUM(ALLCARDS.AMOUNT)
FROM
FROM ACCOUNT,
ACCOUNT, TABLE
TABLE (SELECT
(SELECT ACCOUNT,
ACCOUNT, AMOUNT
AMOUNT
FROM
PLATINUM
FROM PLATINUM
UNION
ALL
UNION ALL
SELECT
SELECT ACCOUNT,
ACCOUNT, AMOUNT
AMOUNT
FROM
FROM GOLD
GOLD
UNION
UNION ALL
ALL
SELECT
SELECT ACCOUNT,
ACCOUNT, AMOUNT
AMOUNT
FROM
FROM BLUE
BLUE
)) AS
ALLCARDS
AS ALLCARDS (ACCOUNT,
(ACCOUNT, AMOUNT),
AMOUNT),
WHERE
ACCOUNT.ACCOUNT
=
ALLCARDS.ACCOUNT
WHERE ACCOUNT.ACCOUNT = ALLCARDS.ACCOUNT
GROUP
GROUP BY
BY ACCOUNT.ACCOUNT,
ACCOUNT.ACCOUNT, ACCOUNT.ACCOUNT_NAME
ACCOUNT.ACCOUNT_NAME
Jan 2002
9
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ – Beispiele(„basic predicate“)
Anzeige,
Anzeige, ob
ob der
der angegebene
angegebene Account
Account ein
ein GOLD-Karten-Inhaber
GOLD-Karten-Inhaber ist.
ist. Wenn
Wenn ja,
ja, zeige
zeige die
die
„account“
„account“ Details
Details an.
an.
SELECT
SELECT
FROM
FROM
WHERE
WHERE
AND
AND
Jan 2002
'GOLD
'GOLD CARD
CARD ACCOUNT:',
ACCOUNT:', ACCOUNT.ACCOUNT,
ACCOUNT.ACCOUNT,
ACCOUNT.ACCOUNT_NAME,
ACCOUNT.ACCOUNT_NAME, ACCOUNT.CREDIT_LIMIT
ACCOUNT.CREDIT_LIMIT
ACCOUNT
ACCOUNT
'GOLD'
SELECT
'PLATINUM'
'GOLD' == ((
SELECT
'PLATINUM'
FROM
PLATINUM
FROM
PLATINUM
WHERE
ACCOUNT
WHERE
ACCOUNT == 'BMP291'
'BMP291'
UNION
UNION
SELECT
'GOLD'
SELECT
'GOLD'
FROM
GOLD
FROM
GOLD
WHERE
ACCOUNT
WHERE
ACCOUNT == 'BMP291'
'BMP291'
UNION
UNION
SELECT
'BLUE'
SELECT
'BLUE'
FROM
BLUE
FROM
BLUE
WHERE
ACCOUNT
WHERE
ACCOUNT == 'BMP291')
'BMP291')
ACCOUNT
ACCOUNT == 'BMP291';
'BMP291';
10
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION– Beispiele(„quantified predicates“: SOME, ANY, ALL)
Zeige,
Zeige, ob
ob irgendwelche
irgendwelche „accounts“
„accounts“ über
über dem
dem Kreditlimit
Kreditlimit liegen
liegen
SELECT
SELECT
FROM
FROM
WHERE
WHERE
AND
AND
Jan 2002
'ACCOUNT
'ACCOUNT OVER
OVER CREDIT
CREDIT LIMIT',
LIMIT', ACCOUNT_NAME
ACCOUNT_NAME
ACCOUNT
T1
ACCOUNT T1
CREDIT_LIMIT
SUM(AMOUNT)
CREDIT_LIMIT << ANY
ANY (( SELECT
SELECT
SUM(AMOUNT)
FROM
PLATINUM
FROM
PLATINUM
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT
T1.ACCOUNT
UNION
UNION
SELECT
SUM(AMOUNT)
SELECT
SUM(AMOUNT)
FROM
GOLD
FROM
GOLD
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT
T1.ACCOUNT
UNION
UNION
SELECT
SUM(AMOUNT)
SELECT
SUM(AMOUNT)
FROM
BLUE
FROM
BLUE
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT)
T1.ACCOUNT)
T1.ACCOUNT
T1.ACCOUNT == T1.ACCOUNT;
T1.ACCOUNT;
11
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ – Beispiele(EXISTS-Prädikat)
Zeige
Zeige alle
alle „accounts“
„accounts“ mit
mit Aktivitäten
Aktivitäten im
im Januar
Januar 2000.
2000.
SELECT
SELECT
FROM
FROM
WHERE
WHERE
Jan 2002
ACCOUNT,
ACCOUNT, ACCOUNT_NAME
ACCOUNT_NAME
ACCOUNT
ACCOUNT T1
T1
EXISTS
((
SELECT
**
EXISTS
SELECT
FROM
PLATINUM
FROM
PLATINUM
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT
T1.ACCOUNT
AND
YEAR(DATE)
AND
YEAR(DATE) == 2000
2000
AND
MONTH(DATE)
=
AND
MONTH(DATE) = 11
UNION
UNION
SELECT
**
SELECT
FROM
GOLD
FROM
GOLD
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT
T1.ACCOUNT
AND
YEAR(DATE)
AND
YEAR(DATE) == 2000
2000
AND
MONTH(DATE)
AND
MONTH(DATE) == 11
UNION
UNION
SELECT
**
SELECT
FROM
BLUE
FROM
BLUE
WHERE
ACCOUNT
WHERE
ACCOUNT == T1.ACCOUNT
T1.ACCOUNT
AND
YEAR(DATE)
AND
YEAR(DATE) == 2000
2000
AND
MONTH(DATE)
=
)) ;;
AND
MONTH(DATE) = 11
12
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ – Beispiele(INSERT)
UNION
UNION in
in INSERT‘s
INSERT‘s
INSERT
INSERT INTO
INTO CARDS_2000
CARDS_2000 (ACCOUNT,
(ACCOUNT, AMOUNT)
AMOUNT)
(SELECT
ACCOUNT,
(SELECT
ACCOUNT, SUM(AMOUNT)
SUM(AMOUNT)
FROM
PLATINUM
FROM
PLATINUM
WHERE
YEAR(DATE)
WHERE
YEAR(DATE) == 2000
2000
GROUP
ACCOUNT
GROUP BY
BY
ACCOUNT
UNION
UNION
SELECT
ACCOUNT,
SELECT
ACCOUNT, SUM(AMOUNT)
SUM(AMOUNT)
FROM
GOLD
FROM
GOLD
WHERE
YEAR(DATE)
WHERE
YEAR(DATE) == 2000
2000
GROUP
BY
GROUP
BY ACCOUNT
ACCOUNT
UNION
UNION
SELECT
ACCOUNT,
SELECT
ACCOUNT, SUM(AMOUNT)
SUM(AMOUNT)
FROM
BLUE
FROM
BLUE
WHERE
YEAR(DATE)
WHERE
YEAR(DATE) == 2000
2000
GROUP
BY
ACCOUNT
);
GROUP BY
ACCOUNT );
Jan 2002
13
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ - Beispiele
UNION
UNION in
in UPDATE‘s
UPDATE‘s
UPDATE
UPDATE
SET
SET
CARDS_2000
CARDS_2000 T1
T1
CARD_TYPE
CARD_TYPE == (( SELECT
SELECT
FROM
FROM
WHERE
WHERE
SELECT
SELECT
FROM
FROM
WHERE
WHERE
SELECT
SELECT
FROM
FROM
WHERE
WHERE
Jan 2002
'P'
'P'
PLATINUM
PLATINUM T2
T2
T1.ACCOUNT
T1.ACCOUNT == T2.ACCOUNT
T2.ACCOUNT
UNION
UNION
'G'
'G'
GOLD
GOLD T3
T3
T1.ACCOUNT
T1.ACCOUNT == T3.ACCOUNT
T3.ACCOUNT
UNION
UNION
'B'
'B'
BLUE
BLUE T4
T4
T1.ACCOUNT
T1.ACCOUNT == T4.ACCOUNT
T4.ACCOUNT );
);
14
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ - Explain
Jan 2002
15
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“ - EXPLAIN
• „Neue Spalten & Werte in PLAN_TABLE für EXPLAIN
- PARENT_QBLOCKNO: enthält die Anzahl von „parent QB‘s“ zu
dem aktuellen QB
- TABLE_TYPE:
F: Funktion
Q: temporäre Ergebnistabelle (für STAR JOIN, nicht für UNION)
T: Tabelle
W: „work“-datei
- QBLOCK_TYPE: Neue Werte zur Anzeige des Verarbeitungstyps des QB
TABLEX:
„table expression“
UNION:
UNION
UNIONA:
UNION ALL
• In DB2/UDB (Intel) schon seit V6 !
• Unser Beispiel:
- Die niedrigsten QB-Levels sind 1 und 6
- In QB 5 ist der „table type“ = „W“, d.h. Materialisierung findet statt
- ... Dies ist der einzige Weg, zu sehen, wie ein „rewrite“ einer Query wirkt...
Jan 2002
16
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT - Verbesserungen
• Bis Version 6
• Nur „foreign keys“ und „check constraints“ haben Namen
• Unterschiedliche (inkosistente) Syntax für die „constraint“-Klauseln
• „Unique“ constraint nicht änderbar (alter / drop)
• Index-Handhabung und Auswirkung unterschiedlich
zwischen „primary“ und „unique“ – constraints
• Dropauf den Index, der den PK darstellt stoppt alle Zugriffe auf die Tabelle
• Katalog-Informationen über „primary key“- und
„unique“-constraints in SYSINDEX, SYSCOLUMNS
• Keine Konsistenz in den „constraints“
Jan 2002
17
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT – Verbesserungen – V7
• Alle Constraints können (müssen) benannt sein
- Foreign Keys:
8-Byte Namen
:
18 Byte (Primary Key, Unique, Check)
- Sonstige
- Eindeutige Namen pro Tabelle
• Default-Name: erste referenzierte Spalte + Zähler
• Alle constraints können mit „alter table ...drop constraint “
entfernt werden
• Drop Index erfordert vorherigen DROP des Constraint
- Für primary und unique
- Ausnahme: „unique“, die vor V7 angelegt wurden
• Alle Details zu „constraints“ werden expilizit im Katalog abgelegt
• „Alte“ Syntax funktioniert weiterhin ...
(OS/390 – Anpassung an UDB-Family - dort bereits seit V6)
Jan 2002
18
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINTs – neue Syntax (Beispiele)
alter
table employee
add constraint
u_emp_name unique
( lastname, firstnme )
create table company_cars
( license_plate
varchar
(20) not null,
constraint cars_prim primary key ( license_plate ),
manufacturer
char
(8),
constraint cars_man check
( manufacturer in (‚BMW', 'GM')),
model
char
(15),
driver
char
(6),
constraint car_driver foreign key ( driver )
references employee
)
alter
Jan 2002
table company_cars
drop constraint car_driver
19
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINTs – neue Syntax (Beispiele)
Version
Version 66
CREATE
CREATE
ALTER
ALTER
2
2
ALTER
ALTER
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
Jan 2002
TABLE...
TABLE...
PRIMARY
PRIMARYKEY
KEY(SUPPLIER_ID)...
(SUPPLIER_ID)...
UNIQUE
UNIQUE(SUPPLIER_ID)...
(SUPPLIER_ID)...
FOREIGN
FOREIGNKEY
KEYFPARTS001
FPARTS001(SUPPLIER_ID)...
(SUPPLIER_ID)...
CONSTRAINT
CPARTS001
CONSTRAINT CPARTS001CHECK
CHECK...
...
TABLE...
TABLE...
ADD
ADDPRIMARY
PRIMARYKEY
KEY(SUPPLIER_ID)...
(SUPPLIER_ID)...
ADD
UNIQUE
(SUPPLIER_ID)...
ADD UNIQUE (SUPPLIER_ID)...
ADD
ADDFOREIGN
FOREIGNKEY
KEYFPARTS001
FPARTS001(SUPPLIER_ID)...
(SUPPLIER_ID)...
ADD
ADDCONSTRAINT
CONSTRAINTCPARTS001
CPARTS001CHECK
CHECK...
...
TABLE...
TABLE...
Version
PRIMARY
Version 77
PRIMARYKEY...
KEY...
CREATE
UNIQUE...
CREATE TABLE...
TABLE...
UNIQUE...
CONSTRAINT
FOREIGN
KEY
FPARTS001...
CONSTRAINTPSUPP
PSUPPPRIMARY
PRIMARYKEY
KEY(SUPPLIER_ID)...
(SUPPLIER_ID)...
FOREIGN KEY FPARTS001...
CONSTRAINT
CHECK
CONSTRAINTUSUPP001
USUPP001UNIQUE
UNIQUE(SUPPLIER_ID)...
(SUPPLIER_ID)...
CHECKCPARTS001...
CPARTS001...
CONSTRAINT
CONSTRAINT
CONSTRAINTFPARTS001
FPARTS001FOREIGN
FOREIGNKEY
KEY(SUPPLIER_ID)...
(SUPPLIER_ID)...
CONSTRAINTFPARTS001...
FPARTS001...
CONSTRAINT
CPARTS001
CHECK
...
CONSTRAINT CPARTS001 CHECK ...
ALTER
ALTER TABLE...
TABLE...
ADD
ADDCONSTRAINT
CONSTRAINTPSUPP
PSUPPPRIMARY
PRIMARYKEY(SUPPLIER_ID)...
KEY(SUPPLIER_ID)...
ADD
CONSTRAINT
USUPP001
ADD CONSTRAINT USUPP001UNIQUE
UNIQUE(SUPPLIER_ID)...
(SUPPLIER_ID)...
ADD
ADDCONSTRAINT
CONSTRAINTFPARTS001
FPARTS001FOREIGN
FOREIGNKEY(SUPPLIER_ID)...
KEY(SUPPLIER_ID)...
ADD
ADDCONSTRAINT
CONSTRAINTCPARTS001
CPARTS001CHECK
CHECK...
...
ALTER
ALTER TABLE...
TABLE...
DROP
PRIMARY
DROP
PRIMARYKEY...
KEY...
DROP
UNIQUE
DROP
UNIQUEUSUPP001
USUPP001...
...
DROP
CHECK
DROP
CHECKCPARTS001...
CPARTS001...
DROP
CONSTRAINT
DROP
CONSTRAINTFPARTS001...
FPARTS001...
DROP
FOREIGN
DROP
FOREIGNKEY
KEYFPARTS001...
FPARTS001...
20
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINTs - Restriktionen beim DROP
Indexes die entweder einen „primary key“ oder „unique key“ darstellen, können
nicht „gedropped“ werden, bis der zugehörige „constraint“ entfernt ist !
1. Entfernen „foreign key constraint“
ALTER TABLE PARTS
DROP CONSTRAINT FPARTS01;
2. Entfernen „parent key constraint“
ALTER TABLE SUPPLIER
DROP PRIMARY PSUPP; oder
ALTER TABLE SUPPLIER
DROP CONSTRAINT PSUPP; oder
ALTER TABLE SUPPLIER
DROP UNIQUE USUPP01; oder
ALTER TABLE SUPPLIER
DROP CONSTRAINT USUPP01;
Jan 2002
21
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT- Informationen im Systemkatalog
• Foreign Key und Check - Constraints
• Wie bisher in SYSRELS und SYSFOREIGNKEYS
• Primary und Unique - Constraints
• Neue Tabellen SYSTABCONST u. SYSKEYCOLUSE
• TABLESTATUS in SYSTABLES
• ‚P‘
für primary keys, falls Index noch fehlt (wie bisher)
• ‚U‘
für unique constraint, falls Index noch nicht angelegt
• ‚PU‘
für das Zusammentreffen der beiden Situationen
Hinweis: OEM-Werkzeuge sind auf „Verträglichkeit“ zu prüfen
Jan 2002
22
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT- Informationen im Systemkatalog
SYSIBM.SYSTABCONST
speichert Details über „primary key“ und „unique key“
constraints. Es gibt referentielle Abhängigkeiten zu
SYSIBM.SYSTABLES und SYSIBM.SYSKEYCOLUSE.
Column name
Jan 2002
Type
Beschreibung
CONSTNAME
VARCHAR(128) NOT NULL
Name des constraint
TBCREATOR
CHAR(8) NOT NULL
Authorization ID des „table-owners“
TBNAME
VARCHAR(18) NOT NULL
Tabellenname des „constraint“
CREATOR
CHAR(8) NOT NULL
Authorization ID des „constraint-Creators“
TYPE
CHAR(1)
Typ des constraint:
P
Primary key
U
Unique key
IXOWNER
CHAR(8) NOT NULL
Owner des Index der den „constraint“ unterstützt
IXNAME
VARCHAR(18) NOT NULL
Name des index
CREATEDTS
TIMESTAMP NOT NULL
Create-Zeotpunkt des „constraint“
IBMREQD
CHAR(1) NOT NULL
DEFAULT ‘N’
N
No
Y
Yes
23
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT- Informationen im Systemkatalog
SYSIBM.SYSKEYCOLUSE
Column name
Jan 2002
speichert Details über „primary key“ und „unique key“
constraints: eine „row“ pro Spalte in Schlüsseln desselben
Typs
Type
Beschreibung
CONSTNAME
VARCHAR(128) NOT NULL
Name des constraint
TBCREATOR
CHAR(8) NOT NULL
Authorization ID des „table-owners“
TBNAME
VARCHAR(18) NOT NULL
Tabellenname auf die der „constraint“ zielt...
COLNAME
VARCHAR(18) NOT NULL
Name der Spalte
COLSEQ
SMALLINT NOT NULL
Position der Spalte im „key“
(erste Position = 1)
COLNO
SMALLINT NOT NULL
Position der Spalte in der Tabelle
IBMREQD
CHAR(1) NOT NULL
DEFAULT ‘N’
N
No
Y
Yes
24
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.2 CONSTRAINT- Informationen im Systemkatalog
Jan 2002
25
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3 Scrollable Cursor
•
Implizit über „temporary tables“ (Anwendungs-Scope),
d.h. Zugriff auf TEMPDB erforderlich
•
Zwei unterschiedliche Typen
1. Insensitive
Î Keine Verbindung zu Basis-Daten
2. Static Sensitive
Î Implizite Synchronisierung über RID und
Re-Evaluierung des WHERE ...
Jan 2002
•
Syntax-Erweiterungen für DECLARE und FETCH
•
Neue SQLCODE‘s beim Zugriff auf nicht(mehr)
existente „Cursor“-Daten
26
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.1 Scrollable Cursor Typen
Non-scrollable cursor-characteristics
• Zum Lesen eines „set of rows“ oder einem „result set“ einer „stored procedure“.
• Die „rows“ werden einzeln verarbeitet.
• Die „rows“ werden sequentiell „gefetched“
• „Result sets“ können in einer „workfile“ abgelegt werden
Scrollable cursor-characteristics
• Werden von einem AP zum Lesen eines „set of rows“ oderr einem „result set“ einer
„stored procedure genutzt
• Die „rows“ können „random“ „gefetched“ werden
• Die „rows“ können „forward“ oder „backward“ „gefetched“ sein.
• Die „rows“ können von einer „current position“ oder vom Anfang der „result table“
oder des „result set“ geholt werden
• Das „result set“ wird zum Zeitpunkt „OPEN CURSOR“ fixiert
• „Result sets“ werden in „Declared Temporary Tables“ (V6 Line Item) abge3lgt.
• „Results sets“ verschwinden zum Zeitpunkt „CLOSE CURSOR“
Jan 2002
27
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.1 Scrollable Cursor Typen
Insensitive scrollable cursor
• immer „static“
• Feste Anzahl „rows“
• Nutzung von „Declared temp tables“
Sensitive static scrollable cursor
•
•
•
•
immer „static“
Feste Anzahl „rows“
Nutzung von „Declared temp tables“
Sensitive auf Änderungen, aber nicht auf INSERT‘s
Sensitive dynamic scrollable cursor
• „direct table access“
• „live data access“
Jan 2002
28
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.1 Scrollable Cursor Typen
Updatability
•
das SELECT Statement des DECLARE CURSOR kann die Ursache dafür sein,
dass ein cursor „read-only“ ist – abhängig von den aktuellen „cursor rules“.
Non-scrollable and scrollable cursor-characteristics
• Das „result set“ kann sensitive gegen seine eigenen Änderungen sein
• Das „result set“ kann sensitive gegen fremde Änderungen sein
• Das „result set“ kann „updatable“ sein.
• Das „result set“ kann „fetched“, „updated“, oder „deleted“ werden
Jan 2002
29
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.1 Scrollable Cursor (Vergleich von Cursortypen)
Ein „cursor“ kann „read-only“ werden, wenn das SELECT-Statement mehr als eine
Tabelle oder GROUP BY etc. enthält
Jan 2002
30
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.2 Scrollable Cursor - Syntax
DECLARE cname
INSENSITIVE
SCROLL
STATIC SENSITIVE
CURSOR
Jan 2002
....
31
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.3 Öffnen eines „Scrollable Cursor“
DECLARE curs1 SENSITIVE SCROLL CURSOR
WITH HOLD FOR
SELECT
account, account_name, credit_limit
,
type
FROM
account
WHERE
account = :in_account
ACCOUNTACCOUNTTabelle
Tabelle
Jan 2002
.....
OPEN curs1 ....
Result Set
-DB2 erzeugt eine TEMP Tabelle
- der Zugriff ist „exklusiv“
- es existiert eine fixe Anzahl „rows“
- Tabelle wird zum Zeitpunkt
„CLOSE CURSOR“ gelöscht
- erfordert TEMP DB und
vordefinierte TS
RESULT
SET
FETCH...
32
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.3 Öffnen eines „Scrollable Cursor“
Schlüsselwort SCROLL
•
Definiert einen „scrollable“ Cursor
•
Bei OPEN CURSOR.... Wird eine temporäre Tabelle in der TEMP Datenbank
angelegt
•
Die TEMP DB wiederum ist von der DBA zur Verfügung zu stellen:
- CREATE DATABASE dbtemp01 AS TEMP STOGROUP gtdb2sms ;
CREATE TABLESPACE tstemp04 IN dbtemp01
USING STOGROUP gtdb2sms SEGSIZE 4 ;
CREATE TABLESPACE tstemp08 IN dbtemp01
USING STOGROUP gtdb2sms SEGSIZE 8 ;
CREATE TABLESPACE tstemp16 IN dbtemp01
USING STOGROUP gtdb2sms SEGSIZE 16;
CREATE TABLESPACE tstemp32 IN dbtemp01
USING STOGROUP gtdb2sms SEGSIZE 32 BUFFERPOOL BP32K;
•
Die bisher definierten CURSOR arbeiten unverändert in DB2V7 !!!
Jan 2002
33
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.4 Scrollable Cursor – (Cursordefinitionen)
• Read only cursor
• Not aware of updates
or deletes in base
table
• Updatable cursor
• Aware of own updates or
deletes within cursor
• Other changes to base
table not visible to cursor
• All inserts not recognized
Jan 2002
• Updatable cursor
• Aware of own updates and
deletes within cursor
• sees all committed updates and
deletes
• All inserts not recognized
34
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.4 Scrollable Cursor – Open Cursor
• Ergebnis wird beim OPEN CURSOR implizit in
temporäre Tabelle übertragen
• Nur sichtbar für diese AnwendungsInstanz (exklusiv)
• FETCH arbeitet gegen diese Kopie
• Anzahl Zeilen steht beim OPEN fest
und ändert sich nicht (!)
• Beim CLOSE wird temporäre Tabelle
automatisch gelöscht
Jan 2002
35
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.5 Scrollable Cursor - Syntax
FETCH
INSENSITIVE
SENSITIVE
NEXT / PRIOR / FIRST / LAST /
CURRENT /BEFORE / AFTER
ABSOLUTE
integer
RELATIVE
host-variable
*
*) Typ integer oder
DECIMAL (18,0)
cursorname
Jan 2002
36
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.6 Scrollable Cursor – FETCH
Ergebnismenge
Aktuelle Position
BEFORE
ABSOLUTE 0
FIRST
ABSOLUTE 1
PRIORT
CURRENT
NEXT
LAST
RELATIVE -1
RELATIVE 0
RELATIVE +1
ABSOLUTE -1
AFTER
Jan 2002
37
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.6 Scrollable Cursor – FETCH(Beispiele)
Absolute Cursorbewegung:
FETCH ... ABSOLUTE + 5 FROM curs1; oder
MOVE 5 TO cursor-position
FETCH ... ABSOLUTE :cursor-position FROM curs1
Relative Cursorbewegung:
FETCH ... RELATIVE - 5 FROM curs1; oder
MOVE -5 TO cursor-move
FETCH ... RELATIVE :cursor-move FROM curs1
FETCH ... NEXT FROM curs1;
Neue FETCH – „keywords“
Jan 2002
NEXT, PRIOR, FIRST, LAST, CURRENT,
BEFORE, AFTER, ABSOLUTE, RELATIVE
38
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.6 Scrollabe Cursor – Hinweise zum FETCH
BEFORE, AFTER
SQLCODE = 0, aber keine neuen Daten !
RELATIVE +/-99999999
Position „AFTER / BEFORE“ + SQLCODE 100
ABSOLUTE +- 9999999
CURRENT
SQLCODE –231, wenn nicht auf einer Datenzeile
ABSOLUTE –2 / -3
zulässig, entspricht vorletzte, drittletzte usw.
ABSOLUTE 0
gleicher Effekt wie BEFORE, aber SQLCODE +100
Jan 2002
39
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.7 Scrollable Cursor – INSENSITIVE vs. SENSITIVE STATIC
•
INSENSITIVE (scrollable) Cursor lassen keinen UPDATE zu
•
READ-ONLY Cursor sind immer INSENSITIVE (ggfs. Fehler bei BIND)
•
Komplettes Ergebnis bei OPEN CURSOR, externe Änderungen
(durch andere UOW) nicht sichtbar
•
STATIC SENSITIVE (scrollable) Cursor erlauben UPDATE und DELETE
•
INSERT ist auch bei SENSITIVE STATIC nicht zulässig
•
Datenänderungen werden synchronisiert über die RID
•
Ein SENSITIVE STATIC–Cursor kann INSENSITIVE ge-FETCHt werden
•
FETCH und UPDATE führt zur evtl. Re-Evaluierung der Where-Klausel
•
Das Result-SET ist „blind“ gegenüber Sätzen, die ausserhalb der
Anwendung eingefügt wurden
Jan 2002
40
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – Szenario 1
Update durch andere
UOW ?
OPEN STATIC SENSITIVE CURSOR
FETCH [ SENSITIVE ]
Neu Lesen
UPDATE WHERE CURRENT...
Vergleich Basis-Tabelle
mit Result-Set, evtl.
SQLCODE -224
Jan 2002
41
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – Szenario 2
Update durch andere
UOW ?
OPEN STATIC SENSITIVE CURSOR
FETCH INSENSITIVE
UPDATE WHERE CURRENT...
Vergleich Basis-Tabelle
mit Result-Set, evtl.
SQLCODE -224
Jan 2002
42
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – UPDATE und DELETE-Holes
Jan 2002
•
Durch DELETE-Operationen oder Änderungen von Feldern, die
in der WHERE-Bedingung angegeben waren, können sog.
„Holes“ im Result-Set entstehen. Diese werden mit SQLCODEs
-222 / - 223 signalisiert
•
HOLES können durch externe Änderungen (andere UOW) oder die
eigene Anwendung verursacht worden sein
•
Extern (durch andere UOW) verursachte HOLES werden sichtbar
• Beim FETCH SENSITIVE
• Spätestens bei einem eventuellen UPDATE
•
„Interne“ HOLES sind auch beim FETCH INSENSITIVE zu beachten
43
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – Szenario 3
Delete durch andere UOW
oder Update, so dass
WHERE des Cursors nicht
mehr erfüllt
OPEN STATIC SENSITIVE CURSOR
FETCH SENSITIVE
HOLE
UPDATE ...
HOLE
Hinweis: HOLES können auch durch eigene DELETEs / UPDATEs der Anwendung entstehen !
Jan 2002
44
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – Szenario 3 (UPDATE/DELETE Holes)
Jan 2002
45
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – INSENSITIVE „scrolling holes“
Jan 2002
46
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.8 Scrollable Cursor – SENSITIVE STATIC
•
UPDATE – Operationen werden immer im Result-Set und der
Basis-Tabelle abgebildet. Bei Nicht-Übereinstimmung der Zeile
wird die Operation mit SQLCODE abgewiesen
•
Ein FETCH SENSITIVE bedeutet, dass ggfs. beim LESEN
die Daten aus der Basis-Tabelle aktualisiert werden
•
Beim FETCH INSENSITIVE wird die Basis-Tabelle nicht
herangezogen, somit ist die Gefahr höher, dass externe
Änderungen bei folgendem UPDATE zu Fehler führen (Zeile nicht
mehr aktuell)
Jan 2002
47
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.9 Scrollable Cursor und die Auflösung von Funktionen
Jan 2002
48
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.10 Scrollable Cursor und Locking
•
Für Isolation Levels RR, RS, UR: unverändert, aber zu beachten:
alle Locks werden beim „OPEN CURSOR“ gesetzt
•
Für CS gilt „optimistic locking mechanism“
• Annahme: nur geringer Anteil gelesener Zeilen soll geändert werden
• Alle Locks werden nach OPEN CURSOR aufgegeben (!)
• Bei UPDATE (DELETE) wird Zeile ge-LOCKED, und durchläuft
die Evaluierung (Vergleich Result-Set <-> Basis-Tabelle)
• Hohe Concurrency möglich, sauberes Error-Handling im AP und
Anwendungsdesign sind unerlässlich
Jan 2002
49
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.10 Scrollable Cursor und Locking
Read Locks werden zum Zeitpunkt „OPEN CURSOR“ gesetzt
Jan 2002
50
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.10 Scrollable Cursor und Locking(optimistic)
Jan 2002
51
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.11 Scrollable Cursor in Stored Procedures
• Sind immer READ-ONLY
• Können aber dennoch STATIC SENSITIVE definiert werden
.
main()
{
EXEC SQL BEGIN DECLARE SECTION;
char hv_account[30];
char hv_acctname[30];
EXEC SQL END DECLARE SECTION;
.
EXEC SQL BEGIN DECLARE SECTION;
static volatile SQL TYPE IS
RESULT_SET_LOCATOR *CRTPROCS_rs_loc;
EXEC SQL END DECLARE SECTION;
.
EXEC SQL CALL SET_CURSOR_ACCOUNT_C1('P');
if (sqlca.sqlcode != 0) prt_sqlc();
.
.
.
EXEC SQL ASSOCIATE LOCATOR( :CRTPROCS_rs_loc )
WITH PROCEDURE PAOLOR2.CRTPROCS;
.
EXEC SQL ALLOCATE C1 CURSOR FOR
RESULT SET :CRTPROCS_rs_loc;
.
EXEC SQL FETCH C1 INTO
:hv_account,
:hv_account_name;
.
EXEC SQL CLOSE C1;
Jan 2002
#pragma linkage(cfunc, fetchable)
#include <stdlib.h>
void cfunc(char parm1[2])
{
EXEC SQL BEGIN DECLARE SECTION;
char hv_type[2];
EXEC SQL END DECLARE SECTION;
strcpy(parm1, hv_type);
EXEC SQL
DECLARE C1 INSENSITIVE SCROLL CURSOR
WITH HOLD WITH RETURN FOR
SELECT ACCOUNT, ACCOUNT_NAME
FROM ACCOUNT
WHERE TYPE = :hv_type
FOR UPDATE OF ACCOUNT_NAME;
EXEC SQL
OPEN C1;
}
Stored procedure
52
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.12 Scrollable Cursor und ODBC
• Drei neue Funktionen (ODBC-Commands)
• SQLFetchScroll
• SQLSetPos
• SQLBulkOperation
Positionieren und Lesen (mit
SQL_FETCH_NEXT, SQL_FETCH_PRIOR usw.)
Positionieren (mit
SQL_POSITION, SQL_REFRESH, usw.)
Updates für „bookmarked rows“(mit
SQL_ADD, SQL_UPDATE_BY_BOOKMARK usw.)
SQLRETURN SQLSetPos
Jan 2002
( SQLHSTMT
StatementHandle,
SQLUSMALLINT
RowNumber,
SQLUSMALLINT
Operation,
SQLSMALLINT
LockType );
53
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.3.13 Scrollable Cursor und „distributed processing“
• Optimieren des sogen. „blocking“, um den Datentransfer
zu minimieren. Blöcke werden weggelassen bei:
• Fetch-Orientierung ist reverse
• Fetch Sensitive ist erforderlich
• Das folgende Ziel für Fetch ist zu weit entfernt
• „multiple result sets“ werden unterstützt
• DRDA Support vorhanden, aber nicht für
• Private protocol
• Sogen. „hop sites“
Jan 2002
54
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
„Scrollable cursor“ sind erlaubt:
3
„static“ und „dynamic“
OS/390 compiled programs
3
compiled stored
procedures (inklusive „SQL
stored procedures“)
3
Jan 2002
ODBC
„Scrollable cursor“ sind nicht
erlaubt für:
2
2
2
2
SPUFI und QMF
REXX Programme
Java Programme
„client“ Programme mit
DB2 Connect
55
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.4 Row-Expressions
• Feldliste (COL1, COL2, ...) als Vergleichsoperator in
• IN-Listen
• „Basic Predicates“ (nur =, <>)
• Quantified Predicates (ANY / ALL)
• Syntaktische Einschränkungen
• IN-List Vergleichsoperator nur SELECT, keine Konstante
• Operatoren = und <> nicht mit SELECT (a,b) verwendbar
• Row-Expression nur vergleichbar mit
• = SOME / ANY
• <> ALL
• Prinzipiell immer „AND“-Verknüpfung
• SQLCODE –216, wenn Listen nicht „passen“
Jan 2002
56
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.4.1 Row-Expressions im Vergleich
SELECT
FROM
WHERE
AND
T1.CREATOR, T1.NAME
SYSIBM.SYSTABLES T1
T1.CREATOR NOT IN
( SELECT T2.TBCREATOR
FROM
SYSIBM.SYSINDEXES T2
WHERE
T2.TBCREATOR =
T1.CREATOR
AND
T2.TBNAME = T1.NAME)
T1.NAME NOT IN
( SELECT T2.TBNAME
FROM
SYSIBM.SYSINDEXES T2
WHERE T2.TBCREATOR =
T1.CREATOR
AND
T2.TBNAME = T1.NAME);
SELECT
FROM
WHERE
T1.CREATOR, T1.NAME
SYSIBM.SYSTABLES T1
(T1.CREATOR, T1.NAME) NOT IN
( SELECT T2.TBCREATOR, T2.TBNAME
FROM SYSIBM.SYSINDEXES T2
);
V6
V7
Dasselbe Resultat bei beiden Queries .....
Jan 2002
57
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.4.2 Row-Expressions - Beispiele
Select
NAME, COLTYPE
From
SYSIBM.SYSCOLUMNS
Where (TBCREATOR, TBNAME)
3
= ('SYSIBM', 'SYSTABLES')
Select
NAME, COLTYPE
From
SYSIBM.SYSCOLUMNS
Where (TBCREATOR, TBNAME)
2
2
2
IN ( ('SYSIBM', 'SYSTABLES'),
('SYSIBM', 'SYSINDEXES‚
'SYSINDEXES‚) )
select CREATOR, NAME
from SYSIBM.SYSTABLES
where TYPE = 'T'
and (CREATOR, NAME) NOT IN
(SELECT TBCREATOR,TBNAME
from SYSIBM.SYSINDEXES)
3
select LASTNAME from employee
where (WORKDEPT, EMPNO) = ANY
(SELECT DEPTNO, MGRNO FROM
department))
3
Jan 2002
Select
NAME, COLTYPE
From
SYSIBM.SYSCOLUMNS
Where (TBCREATOR, TBNAME)
>= ('SYSIBM', 'SYSTABLES')
select LASTNAME from employee
where (WORKDEPT, EMPNO)
= ALL
(SELECT DEPTNO, MGRNO FROM
department))
58
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.5 Limitierter FETCH
fullselect
SELECT
FROM
WHERE
Jan 2002
FETCH
T1.ACCOUNT, T1.ACCOUNT_NAME,
T1.TYPE,
T1.CREDIT_LIMIT
ACCOUNT T1
T1.ACCOUNT =
( SELECT
MIN(T2.ACCOUNT)
FROM
ACCOUNT T2
);
1
integer
EXEC SQL
SELECT
FROM
WHERE
FETCH
ROW/ROWS ONLY
ACCOUNT, ACCOUNT_NAME, TYPE,
CREDIT_LIMIT
INTO
:hv_account,
:hv_acctname,
:hv_type,
:hv_crdlmt
ACCOUNT
ACOCUNT_NAME = :hv_in_acctname
1 ROW ONLY;
59
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.5 FETCH FIRST
fullselect
•
•
•
•
FETCH FIRST
1
integer
ROW/ROWS ONLY
integer kann in Anwendungsprogrammen auch Host-Variable sein
(wirkt beim OPEN CURSOR)
Implizit wird auch „OPTIMIZE FOR n ROWS“ angenommen
Vermeidung von SQLCODE –811 bzw. CURSOR-Konstruktion
bei SELECT INTO
Vereinfachung für Abfragen „grösster“, „kleinster“ :
select *
where
from employee
salary =
(select max(salary)
from employee)
select *
from employee
order by
salary desc
fetch first row only
! Vorsicht, wenn max/min mehr als einmal vorkommt !
Jan 2002
60
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.6 SELF-REFERENCING UPDATE / DELETE
-118
-118
•
THE
THEOBJECT
OBJECTTABLE
TABLEOR
ORVIEW
VIEWOF
OFTHE
THEDELETE
DELETEOR
OR
UPDATE
STATEMENT
IS
ALSO
IDENTIFIED
IN
A
FROM
UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM
CLAUSE
CLAUSE
•
Bisherige Restriktion entfällt: WHERE Bedingung kann eine subquery
enthalten, die sich auf die zu ändernde Tabelle bezieht
Ausnahme: „positioned update/delete“ (CURSOR + WHERE CURRENT OF)
•
Non-correlated subquery: einmalige Evaluierung der Bedingung
•
Correlated subquery: RID-Liste und Spaltenwerte
werden vor UPDATE/DELETE in einer „workfile“ gespeichert
Jan 2002
61
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.6 SELF-REFERENCING UPDATE / DELETE
UPDATE
UPDATE
WHERE
WHERE
ACCOUNT
ACCOUNT
SET
SETCREDIT_LIMIT
CREDIT_LIMIT==CREDIT_LIMIT
CREDIT_LIMIT**1.1
1.1
CREDIT_LIMIT
<
(
AVG(CREDIT_LIMIT
CREDIT_LIMIT
< (SELECT
SELECT
AVG(CREDIT_LIMIT))
FROM
ACCOUNT)
FROM
ACCOUNT)
DELETE
DELETEFROM
FROMGOLD
GOLDT1
T1
WHERE
EXISTS
(
SELECT
WHERE EXISTS ( SELECT**
FROM
ACCOUNT
FROM
ACCOUNTT2
T2
WHERE
T2.CREDIT_LIMIT
<
(SELECT
WHERE T2.CREDIT_LIMIT < (SELECT
FROM
FROM
WHERE
WHERE
AND
ANDT2.ACCOUNT
T2.ACCOUNT==T1.ACCOUNT)
T1.ACCOUNT)
Vor
Vor
V7
V7
Jan 2002
SUM(T3.AMOUNT)
SUM(T3.AMOUNT)
GOLD
GOLDT3
T3
T3.ACCOUNT
T3.ACCOUNT==
T1.ACCOUNT)
T1.ACCOUNT)
DSNT408I
DSNT408I SQLCODE
SQLCODE==-118,
-118,ERROR:
ERROR:THE
THEOBJECT
OBJECTTABLE
TABLEOR
ORVIEW
VIEWOF
OFTHE
THE
DELETE
OR
UPDATE
STATEMENT
IS
ALSO
IDENTIFIED
IN
A
FROM
DELETE
OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROMCLAUSE
CLAUSE
DSNT418I
DSNT418I SQLSTATE
SQLSTATE==42902
42902SQLSTATE
SQLSTATERETURN
RETURNCODE
CODE
62
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.6 SELF-REFERENCING UPDATE / DELETE(Restriktionen)
•
Positioned UPDATE/DELETE: SQLCODE –118 erfolgt beim BIND wenn die WHEREKlausel die Tabelle für den Update enthält oder aber „rows“ für den DELETE referenziert.
EXEC
EXECSQL
SQL
..
EXEC
EXECSQL
SQL
..
..
EXEC
EXECSQL
SQL
..
..
EXEC
EXECSQL
SQL
Jan 2002
DECLARE
DECLARECURSOR
CURSORC1
C1CURSOR
CURSORFOR
FOR
SELECT
T1.ACCOUNT,
T1.ACCOUNT_NAME,
SELECT T1.ACCOUNT, T1.ACCOUNT_NAME,T1.CREDIT_LIMIT
T1.CREDIT_LIMIT
FROM
ACCOUNT
T1
FROM
ACCOUNT T1
WHERE
AVG
WHERE T1.CREDIT_LIMIT
T1.CREDIT_LIMIT << (SELECT
(SELECT
AVG(T2.CREDIT_LIMT)
(T2.CREDIT_LIMT)
FROM
ACCOUNT
FROM
ACCOUNTT2)
T2)
FOR
UPDATE
OF
T1.CREDIT_LIMIT;
FOR UPDATE OF T1.CREDIT_LIMIT;
OPEN
OPENC1;
C1;
FETCH
FETCHC1
C1INTO
INTO:hv_account,
:hv_account,:hv_acctname,
:hv_acctname,:hv_crdlmt;
:hv_crdlmt;
UPDATE
UPDATE
SET
SET
WHERE
WHERE
ACCOUNT
ACCOUNT
CREDIT_LIMIT
CREDIT_LIMIT==CREDIT_LIMIT
CREDIT_LIMIT**1.1
1.1
CURRENT
OF
C1;
CURRENT OF C1;
63
1.
-Funktionen -- Spracherweiterungen
1. Neue
Neue Basis
Basis-Funktionen
Spracherweiterungen
1.1 UNION „everywhere“
1.2 CONSTRAINT – Verbesserungen
1.3 Scrollable Cursor
1.4 Row-Expressions
1.5 Limitierter FETCH
1.6 SELF-REFERENCING UPDATE / DELETE
Jan 2002
64