Geschäftslogik in der Datenbank - Best Practice

Geschäftslogik in der Datenbank
Best Practice
Tobias Kreidel
Datenbankentwickler
Nis Nagel
Datenbankentwickler
Hamburg, 17.06.2010
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
2
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Standardfelder- und Trigger
TABELLE
TABELLE_ID
Eindeutige technische ID. Wird über BeforeInsert-Trigger ermittelt, wenn nicht
übergeben
...
ERSTELLT_ID
ERSTELLT_ZST
Erstellt-Felder. Werden über SpaltenDefaults belegt
GEAENDERT_ID
GEAENDERT_ZST
GEAENDERT_ZAHLER
GELOESCHT_ZST
Letzte Änderungs-Felder. Werden über einen BeforeUpdate-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
3
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Exceptionhandling - Anforderungen
• Zentrale Definition von Exceptions (SQL-Codes)
um die Mehrfachverwendung von Fehlercodes
verhindern zu können
• Überblick über verwendete Exceptions
4
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Exceptionhandling - Möglichkeiten
Möglichkeit 1
DECLARE
EINE_EXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT (EINE_EXCEPTION, -20003);
BEGIN
...
RAISE EINE_EXCEPTION;
...
EXCEPTION
WHEN EINE_EXCEPTION THEN
CREATE OR REPLACE PACKAGE PA_EXCEPTIONS IS
...
EINE_EXCEPTION EXCEPTION;
END;
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
5
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Exceptionhandling - Zentrale Definition
Fehlertabelle
FEHLER_CODE
FEHLER_NUMMER
FEHLER_TEXT
EINE_EXCEPTION
-20003
Fehler aufgetreten
...
...
...
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_CODE
IN VARCHAR2
, IN_FEHLER_NUMMER IN PLS_INTEGER DEFAULT SQLCODE
) RETURN BOOLEAN;
FUNCTION GET_FEHLER_NUMMER(
IN_FEHLER_CODE IN VARCHAR2
) RETURN NUMBER;
END PA_EXCEPTION;
6
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Exceptionhandling - Anwendung
BEGIN
...
PA_EXCEPTION.RAISE('EINE_EXCEPTION');
...
EXCEPTION
WHEN OTHERS THEN
IF PA_EXCEPTION.IST('EINE_EXCEPTION') THEN
...
END IF;
END;
7
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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
8
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Regelmäßige Reorganisation von Tabellen - Anforderungen
• Operative Datenbank soll klein gehalten werden
• Zentrale und übersichtliche Definition der ReorgVorgänge
• Flexible und einfache Erweiterbarkeit und
Anpassungsmöglichkeiten
9
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Regelmäßige Reorganisation von Tabellen - Tabellenaufbau
Gruppierung der
Reorg-Vorgänge
REORG_SET
NAME
REORG
Reihenfolge innerhalb
des Reorg-Sets
REORG_SET
DELETE
TRUNCATE
SHRINK
CALL
REIHENFOLGE
VARIANTE
TABELLE
LOESCH_BEDINGUNG
AKTIV
LETZTER_AUFRUF
WHERE-Bedingung,
die die zu löschenden
Sätze beschreibt
10
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Zu reorganisierende Tabelle
/ Bei CALL steht hier der
Name der aufzurufenden
Prozedur
Soll Reorg-Vorgang
durchgeführt werden?
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;
11
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Regelmäßige Reorganisation von Tabellen - Fazit
• Läuft seit Einführung 2007 fehlerfrei
• Sehr flexibel bei kurzfristigen Änderungen
12
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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
13
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Datenverteilung (Histor. und Repl.) – Bestandteile
Quell - DB
Ziel - DB
LCR
bauen
(Capture-)Trigger
Propagation-Prozess
14
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Apply-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
15
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Datenverteilung (Histor. und Repl.) – Methode LOG
Quelltabelle
(Capture-)Trigger
Quelltabelle$LOG
PK_ID
LAST_CHANGED
Geänderte Sätze
für den Subscriber
Aufräumen wenn alle
Subscriber Änderung
übernommen haben
Zieltabelle
MODIFY_LOG
QUELLTABELLE
SUBSCRIBER
LAST_IMPORT
Subscriber
Auslesen und Hochsetzen
des letzten Importdatums
16
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Datenverteilung (Histor. und Repl.) – Generator
HISTORICAL_TABLE
TABELLE
METHODE
TRIGGER_DDL
TABLE_DDL
INIT_SKRIPT
Tabelle in der über einen Trigger die nötigen Skripte
generiert werden, um den Capture-Trigger,
Historisierungstabellen und Initialisierungsskripte zu
erzeugen
17
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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.)
18
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Kapselung von PL/SQL - Problemstellung
COBOL
• 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
19
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Zugriffsschicht
Nur Select, Insert,
Update, Delete
Datenbank
Kapselung von PL/SQL - Lösung
UPDATE
COBOL
Funktion
SELECT
Parameter
View
Instead-Of
Trigger
20
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Spalten
Funktion
Parameter
Ergebnis
Fehlercode
PL/SQL
Source 1
Package
Ergebnis
Fehlercode
PL/SQL
Source n
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
21
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
Sichtbarkeiten mit Virtual Private Database
• Einführung nach Börsencrash 1929 in den USA
• Trennung von Interessenskonflikten
Research
22
12.06.2012
Kundenhandel
Geschäftslogik in der Datenbank - Best Practice
Eigenhandel
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
ORDER
ORDER_ID
IS
...
BEGIN
RETURN 'VPDID = '||SYS_CONTEXT('VPD','VPDID');
...
END;
...
END;
/
VPD_ID
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;
/
23
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Sichtbarkeiten mit VPD - Fazit
Sicher, kann nicht umgangen/vergessen werden
Komplexe Zugriffsrechte möglich, aber…
Materialized Views
LogicalChangeRecords
24
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
Rules Manager
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.
25
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Rules Manager - Umsetzung
Popup, eMail
Order
übernehmen
Kunde
Starten
Kundenorder
Stoppen
Datenbank
26
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Rules
Manager
Rules Manager - Fazit
komfortabel, um auf fehlende Ereignisse zu reagieren
Reorganisieren der Events
Sehr individuell anpassbar durch komplexe Eventstrukturen => überdimensioniert
27
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
PL/PDF
Tägliche Reports erstellen als pdf
Reports direkt aus der Datenbank heraus drucken
28
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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;
29
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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
30
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
Import von Massendaten
Es müssen täglich Dateien in unterschiedlichen Formaten importiert werden.
Die Dateien beinhalten beispielsweise:
• Wertpapierkurse
• Stammdaten
• Datenabgleiche mit „End of Day“-Dateien
31
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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
32
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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
, s.KURS
= t.DATUM
= 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
33
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
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“
34
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
Anbindung externer Systeme
• mehrere interne Testinstanzen an eine externe Testinstanz anbinden
• einheitliche Kommunikation mit externen Systemen
• einfaches Handling von fachlichen Fehlern ermöglichen
35
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Anbindung externer Systeme - Umsetzung
Extern
Produktio
n
Berenber
g
Produktio
n
Outbox
Inbox
Berenber
g QS
Berenber
g Test
Propagations
Berenber
g
Integratio
n
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Inbox
36
Berenber
g Entw
Outbox
Extern
Test
Anbindung externer Systeme - Umsetzung
Extern
Inbox
DBMS_SCHEDULER
Outbox
Send
Verarbeiten
TypeMessage
Speichern
MessageTabelle
View
Verarbeiten
nach Fehlern
TypeXYZ
Geschäftslogik
37
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
InsteadOfTrigger
Anbindung externer Systeme - Fazit
Sehr gute Erfahrungen
Einfache und schnelle Nachverarbeitung im Fehlerfall
Empfangen und Beantworten von Nachrichten in der gleichen Transaktion
38
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Geschäftslogik in der Datenbank
Asynchrone Prozesse
Verschiedene Situationen erfordern es Aufrufe asynchron zu verarbeiten.
z.B.:
• Performance
• „ORA-04091 mutating table“
• Transaktionsicherer eMail Versand
39
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Asynchrone Prozesse - Umsetzung
Unterteilung in serielle und parallele Verarbeitung
Standardattribute ID und IDENT zum Aufrufen der Funktionen
Dispatcher-Package zum Verteilen der Aufrufe
Queues
seriell
DBMS_SCHEDULER
Aufruf
parallel
40
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Dispatcher
Package
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;
41
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Asynchrone Prozesse - Beispiel
42
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Asynchrone Prozesse - Fazit
„Bremsen“ im seriellen Ablauf verzögern nachfolgende Aufrufe
Zustand der Datenbank ändert sich bis asynchrone Verarbeitung startet
43
12.06.2012
Geschäftslogik in der Datenbank - Best Practice
Fragen? Anregungen?
Geschäftslogik in der Datenbank
Best Practice
Tobias Kreidel
Datenbankentwickler
Nis Nagel
Datenbankentwickler
Hamburg, 17.06.2010