DB-Links
Werbung
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
