Geschäftslogik in der Datenbank - karriere.berenberg.de bei Berenberg/201006-kreidel-nagel... ·...

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);

RAISE EINE_EXCEPTION;

EXCEPTION

WHEN EINE_EXCEPTION THEN

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;

RAISE_APPLICATION_ERROR(K_EINE_EXCEPTION, ...);

EXCEPTION

WHEN EINE_EXCEPTION THEN

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

PA_EXCEPTION.RAISE('EINE_EXCEPTION');

EXCEPTION

WHEN OTHERS THEN

IF PA_EXCEPTION.IST('EINE_EXCEPTION') THEN

END IF;

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_SET

REIHENFOLGE

VARIANTE

TABELLE

LOESCH_BEDINGUNG

LETZTER_AUFRUF

REORG_SET

DELETE

TRUNCATE

SHRINK

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

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

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

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

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

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

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');

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

Manager

Popup, eMail

Kundenorder

Starten

Stoppen

ü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;

/* 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;

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

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

Produktio

Berenber

g Test

Berenber

Integratio

Berenber

g Entw

Extern

Produktio

Extern

Propagations

Anbindung externer Systeme - Umsetzung

Extern

MessageTabelle

TypeMessage

Verarbeiten

Speichern

InsteadOfTrigger

TypeXYZ

Geschäftslogik

DBMS_SCHEDULER

Verarbeiten

nach Fehlern

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.

• 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)

v_ident VARCHAR2(50);

v_id NUMBER(12);

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);

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

END CASE;

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?

Geschäftslogik in der Datenbank - karriere.berenberg.de bei Berenberg/201006-kreidel-nagel... ·...

Documents

Business Process Management mit SAP NetWeaver BPM · 92 3 Prozessmodellierung mit SAP NetWeaver BPM modell die Geschäftslogik hinzufügen, um das Prozes smodell später ausführen

OOP2017-7.1-2 Moderne Android-Entwicklung mit Kotlin ... · 2. Kotlin im Projekt aktivieren Enter action or option name: Q kotlinl Kotlin Kotlin: Kotlin Internal Mode Kotlin REPL

zen Platform 2.0 Developers Guide - zeos.de · und das Frontend bei Cluster Deployments ... Workflow- und Datenzustand und der damit verknüpften Geschäftslogik beantwortet

ZENOS goes Messaging Neue Kopplungsarchitektur bei der ... · Event Basis- Skalierung get Queuename MQ PK-Out MBW- DB Repl- Tabs LokaleQ PK-In Kontroller Kontroller LVQ-Reader PK-Replikation

Modelle und Programmierparadigmen für Geschäftslogik...Viele Software-Systeme mit Geschäftslogik sind über Jahrzehnte gewachsen und sind durch stetige Änderungen und Erweiterungen

Uwe Habermann Uwe@VandU.eu Venelina Jordanova Venelina@VandU.eu Übernahme der Visual FoxPro Geschäftslogik

Vorlesung Grundzüge der Wirtschaftsinformatik W1311 · er ggf. bei jedem Aufruf als HTML-Dokument, basierend auf der Geschäftslogik des Anwendungsservers, neu erstellt. •Der Anwendungsserver

Datenverteilung in mobilen Ad-hoc-Netzen, Implementierung ... fileKurzübersicht Thema dieser Arbeit ist die Datenverteilung in mobilen Ad-hoc-Netzen. Im Rahmen die-ser Arbeit werden

Datenverteilung in Ad-hoc Netzen 1/24 Adaptive Datenverteilung in mobilen Ad-hoc Netzen unter Verwendung des Area Graph basierten Bewegungsmodells S. Bittner,

Geschäftslogik in der Datenbank - berenberg.de · 2 12.06.2012 Geschäftslogik in der Datenbank Geschäftslogik Geschäftslogik (engl.Business Logic, auch Anwendungslogik) ist ein

CAPTURE & DISTRIBUTION AUtOStORE 6 · PPT Digitale Daten Twain-Scanner Daten-, Fax- und E-Mail-Dienste @ DMS Netzwerk-/ FTP-Ordner ODBC Datenerfassung Datenverarbeitung Datenverteilung

Kernel StandardRating Ein Architekturkonzept für die Plattform-unabhängige Realisierung der Geschäftslogik komplexer Anwendungen Stefan Knuth, Stefan Vocke

Mobile Datenbanken und Informationssysteme Thema: Strategien zur Datenverteilung Push und Broadcast

In-Memory Analytics für Big Data - Sas Institute...Prinzip, wie Teradata und Greenplum, sind die beste Wahl für die In-Memory-Techno-logie von SAS. Sie erfüllen hinsichtlich Datenverteilung,

Vergleichsmaterialien für die Allergenanalytik ... · Homogenitätstests – Vergleich FAPAS Gluten (ELISA) / Brühwurst Soja 100 mg/kg Tetraplex PCR Repl.1 Repl. 2 Repl. 1 Repl

ENVELOPE - Effizienz und Zuverlässigkeit ... · envelope Datenerhaltung auf Anwendungsebene LAIK, eine Bibliothek zur dynamischen Datenverteilung Ermoglicht die Repartitionierung