Geschäftslogik in der Datenbank - karriere.berenberg.de bei Berenberg/201006-kreidel-nagel... · Datenverteilung (Histor. und Repl.) – Generator Tabelle in der über einen Trigger

Geschäftslogik in der Datenbank Best Practice

Tobias Kreidel

Datenbankentwickler

Nis Nagel

Datenbankentwickler

Hamburg, 17.06.2010

2 12.06.2012 Geschäftslogik in der Datenbank - Best Practice

Geschäftslogik in der Datenbank - Best Practice

• Standardfelder- und Trigger

• Exceptionhandling

• Regelmäßige Reorganisation von Tabellen

• Datenverteilung (Historisierung und Replikation)

• Kapselung von PL/SQL

• Sichtbarkeiten mit Virtual Private Database

• Rules Manager

• PL/PDF

• Import von Massendaten

• Anbindung externer Systeme

• Asynchrone Prozesse


Standardfelder- und Trigger

TABELLE

TABELLE_ID

...

ERSTELLT_ID

ERSTELLT_ZST

GEAENDERT_ID

GEAENDERT_ZST

GEAENDERT_ZAHLER

GELOESCHT_ZST

Eindeutige technische ID. Wird über Before-

Insert-Trigger ermittelt, wenn nicht

übergeben

Erstellt-Felder. Werden über Spalten-

Defaults belegt

Letzte Änderungs-Felder. Werden über einen Before-

Update-Trigger ermittelt: :NEW.GEAENDERT_ZST := SYS_CONTEXT('VPD', 'USERID');

:NEW.GEAENDERT_ZST := SYSDATE;

:NEW.GEAENDERT_ZAEHLER := :OLD.GEAENDERT_ZAEHLER + 1;

Über den Gelöscht-Zst können einzelne

Sätze logisch gelöscht werden


Exceptionhandling - Anforderungen

• Zentrale Definition von Exceptions (SQL-Codes)

um die Mehrfachverwendung von Fehlercodes

verhindern zu können

• Überblick über verwendete Exceptions


Exceptionhandling - Möglichkeiten

DECLARE

EINE_EXCEPTION EXCEPTION;

PRAGMA EXCEPTION_INIT (EINE_EXCEPTION, -20003);

BEGIN

...

RAISE EINE_EXCEPTION;

...

EXCEPTION

WHEN EINE_EXCEPTION THEN

...

END;

Möglichkeit 1

CREATE OR REPLACE PACKAGE PA_EXCEPTIONS IS

EINE_EXCEPTION EXCEPTION;

K_EINE_EXCEPTION CONSTANT NUMBER(5) := -20003;

PRAGMA EXCEPTION_INIT (EINE_EXCEPTION, -20003);

END PA_EXCEPTIONS;

...

BEGIN

...

RAISE_APPLICATION_ERROR(K_EINE_EXCEPTION, ...);

...

EXCEPTION

WHEN EINE_EXCEPTION THEN

...

END;

Möglichkeit 2


Exceptionhandling - Zentrale Definition

Fehlertabelle

Package CREATE OR REPLACE PACKAGE PA_EXCEPTION IS

PROCEDURE RAISE(

IN_FEHLER_CODE IN VARCHAR2

, IN_FEHLER_TEXT IN VARCHAR2 DEFAULT NULL

);

FUNCTION IST(


, IN_FEHLER_NUMMER IN PLS_INTEGER DEFAULT SQLCODE

) RETURN BOOLEAN;

FUNCTION GET_FEHLER_NUMMER(


) RETURN NUMBER;

END PA_EXCEPTION;

FEHLER_CODE FEHLER_NUMMER FEHLER_TEXT

EINE_EXCEPTION -20003 Fehler aufgetreten

... ... ...


Exceptionhandling - Anwendung

BEGIN

...

PA_EXCEPTION.RAISE('EINE_EXCEPTION');

...

EXCEPTION

WHEN OTHERS THEN

IF PA_EXCEPTION.IST('EINE_EXCEPTION') THEN

...

END IF;

END;


Exceptionhandling - Fazit

• zentrale (und dadurch eindeutige) Vergabe der

Fehlercodes

• keine Gefahr von invaliden Objekte durch einen Fehler

im zentralen Package

• keine Probleme bei der Entwicklung in größeren Teams


Regelmäßige Reorganisation von Tabellen - Anforderungen

• Operative Datenbank soll klein gehalten werden

• Zentrale und übersichtliche Definition der Reorg-

Vorgänge

• Flexible und einfache Erweiterbarkeit und

Anpassungsmöglichkeiten


Regelmäßige Reorganisation von Tabellen - Tabellenaufbau

REORG

REORG_SET

REIHENFOLGE

VARIANTE

TABELLE

LOESCH_BEDINGUNG

AKTIV

LETZTER_AUFRUF

REORG_SET

NAME

DELETE

TRUNCATE

SHRINK

CALL

Gruppierung der

Reorg-Vorgänge

Reihenfolge innerhalb

des Reorg-Sets

Zu reorganisierende Tabelle

/ Bei CALL steht hier der

Name der aufzurufenden

Prozedur

Soll Reorg-Vorgang

durchgeführt werden? WHERE-Bedingung,

die die zu löschenden

Sätze beschreibt Zeitpunkt des letzten

Reorg-Laufs


Regelmäßige Reorganisation von Tabellen - Ablauf

Reorg-Sets durchlaufen

Vorgänge des Reorg-Sets durchlaufen

CASE VARIANTE

WHEN 'DELETE' THEN

EXECUTE IMMEDIATE 'DELETE FROM ' || TABELLE || ' WHERE ' || LOESCH_BEDINGUNG;

WHEN 'TRUNCATE' THEN

EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || TABELLE;

WHEN 'SHRINK' THEN

EXECUTE IMMEDIATE 'ALTER TABLE '|| TABELLE ||' SHRINK SPACE';

WHEN 'CALL' THEN

EXECUTE IMMEDIATE 'BEGIN ' || TABELLE || '; END;';

END CASE;


Regelmäßige Reorganisation von Tabellen - Fazit

• Läuft seit Einführung 2007 fehlerfrei

• Sehr flexibel bei kurzfristigen Änderungen


Datenverteilung (Historisierung und Replikation)

• Daten-Änderungen mitloggen

• Tabellen auf zwei Datenbanken synchron halten

• Sichern von Daten, die auf einer Instanz nur kurze

Zeit vorgehalten werden sollen


Datenverteilung (Histor. und Repl.) – Bestandteile

(Capture-)Trigger

Quell - DB Ziel - DB

LCR

bauen

Apply-Prozess Propagation-Prozess


Datenverteilung (Histor. und Repl.) – Methoden

ALL / CHANGE

• Änderung hat neuen Eintrag in der Zieltabelle zur Folge

• Jede Änderung an einem Datensatz ist so

nachvollziehbar

• ALL loggt auch die Anlage, CHANGE nur die

Änderungen mit

ARCHIVE / REPLICATION

• Änderungen in der Quelltabelle werden auch als

Änderungen in die Zieltabelle übernommen

• ARCHIVE überträgt keine Löschungen, eine

Reorganisation in der Quelltabelle hat dadurch keine

Auswirkungen auf die Zieltabelle


Datenverteilung (Histor. und Repl.) – Methode LOG

MODIFY_LOG

QUELLTABELLE

SUBSCRIBER

LAST_IMPORT

(Capture-)Trigger

Quelltabelle

Quelltabelle$LOG

PK_ID

LAST_CHANGED

Subscriber

Zieltabelle

Geänderte Sätze

für den Subscriber

Auslesen und Hochsetzen

des letzten Importdatums

Aufräumen wenn alle

Subscriber Änderung

übernommen haben


Datenverteilung (Histor. und Repl.) – Generator

Tabelle in der über einen Trigger die nötigen Skripte

generiert werden, um den Capture-Trigger,

Historisierungstabellen und Initialisierungsskripte zu

erzeugen

HISTORICAL_TABLE

TABELLE

METHODE

TRIGGER_DDL

TABLE_DDL

INIT_SKRIPT


Datenverteilung (Histor. und Repl.) – Fazit

• Einfache, schnelle und komfortable Lösung zur

Historisierung von Daten

• Hoher Erstellungsaufwand für den Generator

• Sehr flexible Verwendung der $LOG-Methode (andere

Instanzen, Dateiexport, etc.)


Kapselung von PL/SQL - Problemstellung

COBOL

Zugriffsschicht

Datenbank

Nur Select, Insert,

Update, Delete

• Zugriff von „alten“

COBOL-Programmen

auf die DB erfolgt über

eine Zugriffschicht, die

nur Select, Insert,

Update und Delete

unterstützt

• Der Aufruf von

PL/SQL-Sourcen soll

ermöglicht werden


Kapselung von PL/SQL - Lösung

View

Instead-Of

Trigger Package

Ergebnis

Fehlercode

PL/SQL

Source 1

PL/SQL

Source n

Spalten

Funktion

Parameter

Ergebnis

Fehlercode COBOL

UPDATE

Funktion

Parameter SELECT


Kapselung von PL/SQL - Fazit

• Gute Möglichkeit um PL/SQL für „alte“ Systeme

ansprechbar zu machen

• Package-Variablen sind aus SQL (der View) nicht

ansprechbar => Wrapper-Funktionen nötig


Geschäftslogik in der Datenbank

• Einführung nach Börsencrash 1929 in den USA

• Trennung von Interessenskonflikten

Research Kundenhandel Eigenhandel

Sichtbarkeiten mit Virtual Private Database


Sichtbarkeiten mit VPD - Umsetzung

-- LogOn-Trigger Anmelden

-- VPDID: 1=Kundenhandel, 2=Eigenhandel, 3=...

DBMS_SESSION.SET_CONTEXT ('VPD', 'VPDID', vpdid);

CREATE OR REPLACE PACKAGE BODY OR.ORDER_SECURITY AS

FUNCTION ORDER_SEC (schema IN VARCHAR2, tab IN

VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN 'VPDID = '||SYS_CONTEXT('VPD','VPDID');

END;

END;

/

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema => 'OR',

object_name => 'ORDER',

policy_name => 'ORDER_POLICY',

function_schema => 'OR',

policy_function => 'ORDER_SECURITY.ORDER_SEC',

statement_types => 'SELECT,INSERT,UPDATE,DELETE');

END;

/

ORDER

ORDER_ID

...

...

...

VPD_ID


Sichtbarkeiten mit VPD - Fazit

Sicher, kann nicht umgangen/vergessen werden

Komplexe Zugriffsrechte möglich, aber…

Materialized Views

LogicalChangeRecords



Es soll überwacht werden, dass Kundenorder an die Börse weitergeleitet und

innerhalb einer bestimmten Zeit bestätigt werden.

Dazu soll

• nach 1 Minute ein Popup

• nach 3 Minuten eine eMail

erstellt werden.

Rules Manager


Datenbank

Rules Manager - Umsetzung

Kunde

Rules

Manager

Popup, eMail

Kundenorder

Starten

Stoppen

Order

übernehmen


Rules Manager - Fazit

komfortabel, um auf fehlende Ereignisse zu reagieren

Reorganisieren der Events

Sehr individuell anpassbar durch komplexe Eventstrukturen => überdimensioniert



Tägliche Reports erstellen als pdf

Reports direkt aus der Datenbank heraus drucken

PL/PDF


PL/PDF - Umsetzung

CREATE OR REPLACE PROCEDURE HELLOWORLD IS

l_blob BLOB;

BEGIN

/* Initialize, without parameters means: page orientation: portrait; page format: A4 */

plpdf.init;

plpdf.NewPage;

/* Sets the font and its properties */

plpdf.SetPrintFont(

p_family => 'Arial', -- Font family: Arial

p_size => 12 -- Font size: 12 pt

);

/* Draws a rectangle cell with text inside. The rectangle may have a border and fill color specified. */

plpdf.PrintCell(

p_w => 50, -- Rectangle width

p_h => 10, -- Rectangle heigth

p_txt => 'Hello World!' -- Text in rectangle

);

/* Returns the generated PDF document. The document is closed and then returned in the OUT parameter. */

plpdf.SendDoc(p_blob => l_blob); -- The generated document

INSERT INTO STORE_BLOB (blob_file, created_date) VALUES (l_blob, sysdate);

COMMIT;

END;


PL/PDF - Fazit

PL/PDF ist ausschließlich in PL/SQL geschrieben

Erzeugung von PDF-Dokumenten direkt in der Datenbank

Geringe Kosten

kein WYSIWYG

keine automatischen Spaltensummen

einfache Charts möglich



Es müssen täglich Dateien in unterschiedlichen Formaten importiert werden.

Die Dateien beinhalten beispielsweise:

• Wertpapierkurse

• Stammdaten

• Datenabgleiche mit „End of Day“-Dateien

Import von Massendaten


Import von Massendaten - Umsetzung

Variante 1:

• Datei bereitstellen als „external table“

• Dateistrukturen direkt in der Tabellendefinition angeben

• Laden der Daten per Merge-Statement

• Fehlerhafte Daten in Error-Tabelle schreiben

• Nachverarbeiten der Fehler

Variante 2:

• Dateistruktur mit Hilfe eines PL/SQL-Type parsen

Variante 3:

• Einzelverarbeitung in FOR LOOP anstatt MERGE

• Type zum Parsen und Speichern der Daten


Import von Massendaten – Beispiel

MERGE INTO WP.KURSE s

USING ( SELECT tab.typ.isin isin

, tab.typ.boerse boerse_id

, tab.typ.kurs_datum datum

, tab.typ.kurs kurs

, tab.typ.waehrung waehrung_id

FROM (SELECT WP.TYPE_KURS(Ext.SATZ) Typ -- Type zum Parsen

FROM WP.EXTERNAL_KURSE Ext

) Tab

) t

ON ( s.ISIN = t.ISIN

AND s.BOERSE_ID = t.BOERSE_ID

AND s.WAEHRUNG_ID = t.WAEHRUNG_ID)

WHEN MATCHED THEN UPDATE SET s.DATUM = t.DATUM

, s.KURS = t.KURS

WHERE s.DARUM < t.DATUM

WHEN NOT MATCHED THEN INSERT (s.ISIN

,s.BOERSE_ID

,s.DATUM

,s.KURS

,s.WAEHRUNG_ID)

VALUES (t.ISIN

,t.BOERSE_ID

,t.DATUM

,t.KURS

,t.WAEHRUNG_ID)

LOG ERRORS INTO WP.KURSE_ERR (v_err_ident) -- Errorlogging

REJECT LIMIT 100;

-- Bei Fehlern eine Mail verschicken


Import von Massendaten - Fazit

Möglichst Variante 1 benutzen

Kein Abbruch bei einzelnen fehlerhaften Daten

Optimizer hat Probleme mit „external tables“

Nachverarbeiten aus Error-Log schwierig

Formatangabe in „external table“



• mehrere interne Testinstanzen an eine externe Testinstanz anbinden

• einheitliche Kommunikation mit externen Systemen

• einfaches Handling von fachlichen Fehlern ermöglichen

Anbindung externer Systeme


Anbindung externer Systeme - Umsetzung

Berenber

g

Produktio

n

Berenber

g Test

Berenber

g QS

Berenber

g

Integratio

n

Berenber

g Entw

Extern

Produktio

n

Extern

Test

Inbox

Ou

tbo

x

Inb

ox

Ou

tbo

x

Propagations


Anbindung externer Systeme - Umsetzung

Extern

MessageTabelle

View

TypeMessage

Verarbeiten

Speichern

InsteadOfTrigger

TypeXYZ

Geschäftslogik

Send

DBMS_SCHEDULER

Verarbeiten

nach Fehlern

Inbox

Outbox


Anbindung externer Systeme - Fazit

Sehr gute Erfahrungen

Einfache und schnelle Nachverarbeitung im Fehlerfall

Empfangen und Beantworten von Nachrichten in der gleichen Transaktion



Verschiedene Situationen erfordern es Aufrufe asynchron zu verarbeiten.

z.B.:

• Performance

• „ORA-04091 mutating table“

• Transaktionsicherer eMail Versand

Asynchrone Prozesse


Asynchrone Prozesse - Umsetzung

Unterteilung in serielle und parallele Verarbeitung

Standardattribute ID und IDENT zum Aufrufen der Funktionen

Dispatcher-Package zum Verteilen der Aufrufe

Aufruf

seriell

parallel

Dispatcher

Package

Queues

DBMS_SCHEDULER


Asynchrone Prozesse - Beispiel

PROCEDURE APPLY_MESSAGE(IN_MSG IN SYS.XMLTYPE)

IS

v_ident VARCHAR2(50);

v_id NUMBER(12);

BEGIN

v_ident := IN_MSG.EXTRACT('/async/ident//text()');

v_id := IN_MSG.EXTRACT('/async/parameter/id//text()');

CASE v_ident

WHEN 'GLOBAL.EXPORT' THEN -- Export anstossen

GLOBAL.PA_EXPORT.EXPORT_USER(IN_USER_ID => v_id);

WHEN 'GLOBAL.VERARBEITE_ORDER' THEN -- Order einarbeiten

GLOBAL.PA_ORDER.VERARBEITEN(IN_ORDER_ID => v_id);

WHEN 'GLOBAL.MAIL' THEN -- eMail verschicken.

GLOBAL.PA_MAIL.SEND_MAIL(IN_MAIL_ID => v_id);

WHEN 'GLOBAL.MAIL_MESSAGE' THEN -- eMail verschicken.

GLOBAL.PA_MAIL.SEND_MAIL(IN_MESSAGE => IN_MSG);

WHEN 'ORDER.GATTUNG_LOESCHUNG' THEN -- Löschen einer Gattung

ORDER.PA_GATTUNG.LOESCHE_GATTUNG(IN_GATTUNG_ID => v_id);

ELSE

RAISE_APPLICATION_ERROR(-20000,'QUEUE_DISPATCHER : Unbekannter Message Typ.');

END CASE;

END;


Asynchrone Prozesse - Beispiel


Asynchrone Prozesse - Fazit

„Bremsen“ im seriellen Ablauf verzögern nachfolgende Aufrufe

Zustand der Datenbank ändert sich bis asynchrone Verarbeitung startet

Geschäftslogik in der Datenbank Best Practice

Tobias Kreidel

Datenbankentwickler

Nis Nagel

Datenbankentwickler

Hamburg, 17.06.2010

Fragen? Anregungen?

Documents

Geschäftslogik in der Datenbank - karriere.berenberg.de bei Berenberg/201006-kreidel-nagel... · Datenverteilung (Histor. und Repl.) – Generator Tabelle in der über einen Trigger