DB-Links

Kap. 1.2 Existierende Infrastruktur
am Beispiel von Oracle9
•
•
•
•
PL/SQL
Packages & Stored Procedures
Verteilte Datenbanken (DB-Links)
Verteilte Transaktionen
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 1
PL/SQL
‰
Procedural Language extensions to SQL
•
•
•
‰
proprietäre 4GL-Sprache
SQL erweitert um klassische Programmiersprachen-Konstrukte
(turing-vollständiges SQL)
PL/SQL erstmals 1991 in Oracle Version 6.0 von Oracle-DB-Servern
unterstützt
Dient unter anderem zur Entwicklung von Stored Procedures in
Oracle
• Anwendungen, die in der DB gespeichert, verwaltet und auch dort ausgeführt
werden
‰
‰
Ab Version 8i: Java VM innerhalb der DB
– Java als Alternative zu PL/SQL möglich
Manuals: http://www.dbs.ethz.ch/oracle8/
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 2
PL/SQL-Block
‰
Header
•
Block Header
IS
Declaration Section
‰
Declaration Section:
Angabe aller verwendeter
• Variablen
• Cursors
• Sub-Blöcke
BEGIN
Execution Section
EXCEPTION
Exception Section
‰
END;
‰
Execution Section
•
Programm-Code
Exception Section
•
•
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Name des Blocks
Ausnahmeverarbeitung bei
Fehlern
Warnungen
Kapitel 1: Workshop Oracle9 – 3
Header
‰
Procedure, Function
PROCEDURE name ( parameter, parameter, ...)
IS
FUNCTION name ( parameter, parameter, ...)
RETURN type
IS
‰
Parameter
•
Variable Name, Direction, Type
Dummy1 IN NUMBER, Dummy2 OUT NUMBER
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 4
Datentypen
‰
Numerisch
• BINARY_INTEGER,
NATURAL
‰
Zeichenketten
• CHAR,
‰
‰
‰
VARCHAR2, LONG
Binärdaten
• RAW,
‰
INTEGER, SMALLINT, INT, POSITIVE,
LONG RAW
Verschiedenes
• ROWID,
BOOLEAN, DATE, NULL, CURSOR, TABLE
Verankerte Datentypen (Anchored)
• %TYPE,
%ROWTYPE
Konvertierung zwischen Datentypen
•
•
Implizit
explizit über Konvertierungsfunktionen: TO_CHAR, TO_NUMBER,
TO_DATE, CHARTOROWID, CONVERT HEXTORAW, RAWTOHEX, ROWIDTOCHAR
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 5
Declaration Section
Deklaration von Variablen, Cursors, Subtypen, Records, Tables
‰
variable_name NUMBER (2) [:= value];
‰
variable_name NUMBER (2) [DEFAULT value];
‰
variable_name schema.name%TYPE;
•
•
Deklaration, ohne den genauen Datentyp zu kennen (automatische Übernahme
aus dem DB-Katalog)
Prozedur ist von späteren Änderungen des Datentyps in der entsprechenden
Tabelle nicht betroffen
‰
TYPE type_name IS RECORD
( name1 typ1, name2 typ2, ...);
‰
TYPE type_name IS TABLE OF type;
‰
SUBTYPE subtype_typname IS base_type;
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 6
Cursors
‰
Eine Besonderheit von PL/SQL ist, dass immer nur einzelne Tupel
zurückgegeben werden können, aber keine Mengen
•
Abhilfe: Cursor
« Iteration über eine Tupelmenge innerhalb der DB und Auslesen
einzelner Tupel
•
•
•
•
•
Deklaration:
– CURSOR c1 IS SELECT empno, ename, sal FROM emp;
– CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10;
Öffnen:
OPEN c1;
Auslesen: FETCH c1 INTO my_empno, my_ename, my_sal;
Schliessen: CLOSE c1;
Attribute:
%ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 7
Wertübergabe
DECLARE
qty_on_hand
NUMBER(5);
new_order_id
NUMBER(5);
...
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET';
...
new_order_id := 42;
...
INSERT INTO ORDER(order_id, orderitem)
VALUES (new_order_id, this_orderitem);
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 8
Kontrollstrukturen: Verzweigungen, Schleifen
‰
Verzweigungen:
• IF
• IF
• IF
‰
– THEN - END IF;
– THEN – ELSE - END IF;
– THEN – ELSIF – THEN - ELSE - END IF;
Schleifen:
• LOOP - EXIT WHEN - END LOOP;
• FOR counter IN start ... end LOOP
• WHILE predicate LOOP - END LOOP;
Objektverwaltung höherer Ordnung (OHO) – SS 2002
- END LOOP;
Kapitel 1: Workshop Oracle9 – 9
Zusätzlich: Built-in Functions
‰
Character Functions
•
‰
Date Functions
•
‰
ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND,
SYSDATE, TRUNC
Numeric Functions
•
‰
ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, LPAD, LTRIM,
REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, UPPER
ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, COS, COSH, EXP(n), FLOOR,
LN(a), LOG(a,b), MOD(a,b), POWER(a,b), ROUND(a,b), SIGN(a), SIN,
SINH, SQRT, TAN, TAHH, TRUNC(a,b), DUMP, GREATEST, LAST, NVL,
SQLCODE, SQLERRM, UID, USER, USERENV, VSIZE
Built-In Packages
•
DBMS_STANDARD, DBMS_ALERT, DBMS_DDL, DBMS_JOB, DBMS_LOCK,
DBMS_MAIL, DBMS_OUTPUT, DBMS_PIPE, DBMS_SESSION, DBMS_SNAPSHOT,
DBMS_SQL, DBMS_TRANSACTION, DBMS_UTILITY
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 10
Exception Section
‰
Möglichkeit, auf vordefinierte Ausnahmen und auch auf individuell
deklarierte (und explizit anzustossende) Ausnahmen zu reagieren
DECLARE
my_exception EXCEPTION;
BEGIN
...
IF ... THEN RAISE my_exception;
...
EXCEPTION
WHEN ZERO_DIVIDE THEN
-- vordefinierte Ausnahme
INSERT INTO ... VALUES ...; COMMIT;
WHEN my_exception THEN Update ... SET ... ;
...
WHEN OTHERS THEN
ROLLBACK;
END;
-- Default-Fehlerbehandlung
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 11
Packages
‰
‰
Logisch zusammengehörige Prozeduren/Funktionen, die in
der DB gespeichert werden, lassen sich in Packages
zusammenfassen
Eine Package besteht aus zwei Teilen:
•
•
Spezifikation
– globale Variablen
– Konstanten
– Interfaces der Funktionen und Prozeduren
Body
– Implementation der Prozeduren bzw. Funktionen
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 12
Packages & Stored Procedures – Beispiel
CREATE PACKAGE emps AS
PROCEDURE hire (empno INTEGER,
empname VARCHAR2(50))
END emps;
Spezifikation
CREATE PACKAGE BODY emps AS
PROCEDURE hire (empno INTEGER,
empname VARCHAR2(50))
IS BEGIN
INSERT INTO employees VALUES
(empno, empname);
END hire;
END emps;
Implementation
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 13
Verteilte Datenbanken (DB-Links)
‰
Verteilte Datenbank in Oracle-Terminologie:
•
•
‰
Homogene Konfiguration verschiedener
Oracle-DB-Server
Verbindung zu DB-Servern
– direkt (via Applikation)
– indirekt (via Datenbank-Link)
Datenbank-Link
•
•
unidirektionale Kommunikation zwischen
DB-Servern
basierend auf Net8 (Netzwerkprotokoll von Oracle)
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 14
DB-Links (Beispiel)
CREATE [PUBLIC|SHARED] DATABASE LINK
Sales.inf.ethz.ch [CONNECT TO user IDENTIFIED BY password |
CONNECT TO CURRENT_USER] USING ‘SALES’;
SELECT s.QoH
FROM Stock s, [email protected] o
WHERE s.product_ID = o.product_ID
DB
Server
(“PRODUCTS”)
DB
Server
(“SALES”)
Net8
Net8
Netzwerk
Database Link
Stock
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Orders
Kapitel 1: Workshop Oracle9 – 15
Datenbank-Links – Eigenschaften …
‰
‰
Datenbank-Link ermöglicht Zugriff auf sämtliche Schema-Objekte
(Tabellen, Views, Stored Procedures, etc.) des Remote-DB-Servers
Optionen von DB-Links
•
•
Gültigkeit
– Private: Link gilt nur für den Benutzer, der den Link angelegt hat
– Public: für die Allgemeinheit verfügbarer Link
Zugriffsschutz
– Fixed User: Benutzer & Passwort werden bereits im DB-Link angegeben;
jeder Zugriff über DB-Link erfolgt auf das Schema des fixed users in der
Remote-DB
– Connected User: Der DB-Link führt zum Schema des Benutzers, der den
Link verwendet (der am lokalen DB-Server angemeldet ist). Voraussetzung ist, dass dieser Benutzer an beiden DB-Servern bekannt ist
– Current User: Der DB-Link wird im Kontext des aktuellen Schemas
ausgeführt (z.B. beim Aufruf einer Stored Procedure, die einen DB-Link
beinhaltet, im Kontext des Benutzers der die Prozedur definiert hat, nicht
in dem des Aufrufenden)
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 16
… Datenbank-Links – Eigenschaften
‰
Jeder Zugriff auf den Remote-DB-Server via DB-Link baut eine
eigene Connection auf
•
•
‰
Grosser Overhead
Abhilfe: Shared Database Links
Shared Database Links
•
•
Voraussetzung: lokaler Server muss im multi-threaded-mode (MTS)
konfiguriert sein
(Oracle-Terminologie für multiplen Server mit asymmetrischer
Konfiguration, d.h. mit dynamischer Zuteilung von Anwendungsprozessen an Server-Prozesse)
Netzwerkverbindung zum Remote-Server kann von allen Benutzern,
die von einem Server-Prozess bearbeitet werden, wiederverwendet
werden
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 17
Zugriff auf Oracle-Objekte
[schema_name.][pkg_name.]object_name[@dblink_name] [params]
Tabellen:
•
•
‰
(Tabelle in eigenem Schema)
(Zugriff via DB-Link)
Prozeduren:
•
•
‰
Select * from konto;
Select * from [email protected];
Aus SQL-Frontend (sqlplus)
– Execute user.emps.hire(10, ‘Bill Gates’);
– Execute [email protected](100, 5004);
Aus einer anderen Prozedur (execute nicht erforderlich)
– [email protected](100, 5004);
Funktionen:
•
•
Aus SQL-Frontend (sqlplus)
– Select user.emps.foo( 10, empno) from emp;
(Funktion foo wird für jedes Tupel
von emp aufgerufen)
– Select [email protected](‘Ogi’) from Dual;
Aus einer anderen Prozedur bzw. Funktion
– [email protected](‘Ogi’);
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 18
Verteilte Transaktionen
‰
‰
In verteilten DB-Umgebungen wendet Oracle8 automatisch ein
(modifiziertes) 2PC-Protokoll an
Zwei-Phasen-Commit-Protokoll (2PC)
Ziel: Garantie der Atomarität verteilter Transaktionen
•
•
•
Phase 1:
– Koordinator initiiert verteilte Transaktion und ermittelt
Erfolg/Misserfolg (schickt prepare-Statement an alle
Komponenten; diese antworten gemäss dem Ergebnis ihrer
lokalen Subtransaktion mit „prepared“ oder „abort“)
– Agentensysteme geben Garantie ab, falls Transaktion lokal
erfolgreich abgeschlossen werden kann (prepared)
Phase 2:
– Koordinator ermittelt globale Commit-Entscheidung
– Jede Komponente muss diese Entscheidung umsetzen (führt je
nach Koordinatorentscheid lokal Commit/Abort durch)
genaue Beschreibung in IS-K Unterlagen
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 19
Oracle 2PC - Terminologie
‰
‰
‰
‰
Globaler Koordinator: DB-Server, der verteilte
Transaktion initiiert (erster Aufruf via DB-Link)
Lokaler Koordinator: DB-Server (≠ globaler
Koordinator), der wiederum Daten in Remote-DBServern referenziert (geschachteltes 2PC)
Commit Point Strength
•
Jedem Knoten wird bei Konfiguration ein Gewicht
“Commit Point Strength” [0…255] zugewiesen
(erfolgt bei der Installation/Konfiguration der DB)
Commit Point Site
•
•
Knoten mit grösstem Gewicht
Muss nicht in Prepared-Zustand übergehen
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 20
Oracle 2PC - Beispiel-Ablauf …
‰
Konfiguration (“Transaktionsbaum”)
•
•
•
•
n Knoten (= DB-Server)
Globaler Koordinator
Lokaler Koordinator
Commit Point Site
SALES.dbs.ethz.ch
(45)
WAREHOUSE.dbs.ethz.ch
(165)
FINANCE.dbs.ethz.ch
(60)
Objektverwaltung höherer Ordnung (OHO) – SS 2002
HQ.dbs.ethz.ch
(145)
HR.dbs.ethz.ch
(45)
Kapitel 1: Workshop Oracle9 – 21
… Oracle 2PC - Beispiel-Ablauf …
BEGIN
INSERT INTO orders o VALUES …;
EXECUTE [email protected];
UPDATE [email protected] SET …;
COMMIT;
CREATE PACKAGE BODY admin
SALES.dbs.ethz.ch
(45)
WAREHOUSE.dbs.ethz.ch
(165)
FINANCE.dbs.ethz.ch
(60)
AS (
PROCEDURE man ( …
BEGIN
UPDATE balance@FINANCE…
SET …;
INSERT INTO management
HQ.dbs.ethz.ch
VALUES …;
(145)
UPDATE employees@HR…
SET …;
Commit;
END man;
…)
HR.dbs.ethz.ch
(45)
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 22
… Oracle 2PC - Beispiel-Ablauf
‰
Globaler Koordinator schickt prepare-Nachricht an (n-1)
Knoten (ohne Commit Point Site)
hier: HQ & SALES
‰
Lokaler Koordinator (HQ)
verschickt prepare an
FINANCE & HR
‰
Bei Commit-Entscheidung
erfolgt Commit in Commit
Point Site (WAREHOUSE)
‰
Bei Commit in WAREHOUSE
auch Commit der verteilten
Transaktion, ansonsten Rücksetzen
Objektverwaltung höherer Ordnung (OHO) – SS 2002
SALES
WAREHOUSE
HQ
FINANCE
HR
Kapitel 1: Workshop Oracle9 – 23
Oracle 2PC - Zusammenfassung
‰
Transparente Verwendung von 2PC in verteilten Datenbanken
‰
Verteilte Transaktionen auch in heterogenen DatenbankUmgebungen möglich
•
•
DB-Link kann auch zu Nicht-Oracle-DB-Servern eingerichtet werden
– Voraussetzung: transparente Umsetzung zwischen
unterschiedlichen SQL-Dialekten durch speziellen “Agenten”, der
für jedes einzubindende Nicht-Oracle-System bereitgestellt
werden muss
Teilnahme an verteilten Transaktionen je nach Eigenschaften des
Nicht-Oracle-Systems
– Voraussetzung für 2PC: X/A Interface
– Ansonsten: Nur Teilnahme als Commit Point Site bzw. nur
Read-Only Transaktionen erlaubt
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 24
„TP-Lite“-2PC am Beispiel von Pseudo PL/SQL
CREATE PACKAGE BODY banking AS
PROCEDURE transfer (int amount, int account_from, int account_to)
IS BEGIN ...
execute debit@bern(amount, account_from)
IF ... THEN RAISE fehler; // Fehlerbehandlung ...
execute credit@basel(amount, accout_to)
IF ... THEN RAISE fehler; // Fehlerbehandlung ...
execute PREPARECommit@bern
IF ... THEN RAISE fehler;
execute PREPARECommit@basel
IF ... THEN RAISE fehler;
// Fehlerbehandlung ...
// Fehlerbehandlung ...
// alles ist ok
execute Commit@bern;
execute Commit@basel;
execute Globale Transaktion;
EXCEPTION
WHEN fehler THEN ...
execute Abort@bern;
execute Abort@basel;
Weiterhin: Logging im
Koordinator nötig für
korrekte Rücksetzbarkeit.
// ROLLBACK;
Dies ist hier allerdings
nicht dargestellt.
...
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 25
Beispielszenario der praktischen Übung
Tabelle account_bern
Tabelle customer_bern
Tabelle account
Tabelle customer
Tabelle Kunde
Tabelle Konto
Package bank_transfer
Prozedur transfer
Package ks_banking
Prozeduren
abheben
Package withdraw_deposit
einzahlen
Prozeduren
withdraw
deposit
DB-Link STUD3.inf.ethz.ch
DB-Link STUDOHO.inf.ethz.ch
selbst anzulegen (in schema_VBS_ZH.sql)
Alias: ks_banking, ks_konto
[email protected]()
bereits vorgegeben
Package finance
Prozeduren
debit
credit
STUD3
VBS-Filiale Bern
STUD2
STUD6
VBS-Filiale Zürich
KS-Datenbank
Objektverwaltung höherer Ordnung (OHO) – SS 2002
Kapitel 1: Workshop Oracle9 – 26