PDF-Reporting mit PL/SQL - · PDF fileEinblick ins Reporting Management des Private Bankings 3 16.09.2014 PDF-Reporting mit PL/SQL Allgemein • Reporting Modul ist komplett integriert

DOAG Regionaltreffen Hamburg/Nord

16. September 2014

PDF-Reporting mit PL/SQLDaniel Dymala

Einleitung

16.09.2014 PDF-Reporting mit PL/SQL2

• Einblick ins Reporting Management des Private Bankings

• Das PDF-Reporting Tool PL/PDF

• Spezielle Anforderungen und deren Umsetzung mit Hilfe von

PL/SQL und PL/PDF

Worum geht es

• Evaluierung von PDF-Tools in Verwendung mit der Oracle Datenbank

• Einführung in PL/PDF

(“Hello World”-Beispiele auf plpdf.com vorhanden)

Worum geht es NICHT

Einblick ins Reporting Management des Private Bankings


Allgemein

• Reporting Modul ist komplett integriert ins Portfolio Management Systems

• Wird von Kundenberatern, von externen Vermögensverwaltern aber auch von Kunden für einmalige Reportanfragen verwendet

• Stellt alle relevanten Kundenreportings (sogenannte Vermögensausweise) durch Reportpläne bereit

• Einfache und schnelle Anwendung durch vordefinierte Standardreportings

• Aufgrund hoher Kundenanforderungen sehr flexibel ausgerichtet durch Verwendung beliebig individueller Reportings

16.09.2014 PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis4








Konfiguration, Ausführung und Bereitstellung von Reportings



Beispiel für einen Reportplan



Reportliste (mit Reportauftrag aus Reportplan)



Reporting Engine

• Verteilung von Reportaufträgen und Zuweisung genau eines Reportsets (=1 PDF) zu einem Prozess der Reporting Engine.

• Die Reportsets werden zerlegt und die darin enthaltenden Reportbausteine (=1-N Seiten Daten und Layout innerhalb des gesamten PDFs vom Reportset) einzeln abgerufen.

• Priorisierung von Reportaufträgen und Steuerung der Auslastung (interne/externe Reports, Reportpläne (Batch))

• Kontinuierliche Sammlung von Performance-Daten zur späteren Analyse



Reporting Statistik und Benchmark

• Die ermittelten Performance-Daten zu jedem einzelnen Report innerhalb eines Reportauftrags werden aggregiert und vor der Löschung der Reportaufträge persistiert.

• Regelmäßige Auswertung der Statistikdaten und Früherkennung von Veränderungen (Laufzeit) bezogen auf

− Datenaufbereiten (SQL)− Erstellung PDF (verwendete Version der DB Methoden)− Zeitspanne der enthaltenen Charts (Response-Request Time)

• Implementierung eines sinnvollen (automatischen) Benchmarks zur regelmäßigen Überprüfung von Spitzenauslastungen (z.B. das Reporting vieler Kunden zu einem ULTIMO)

• Bereitstellung von Referenz-Reportsets

Das PDF-Reporting Tool PL/PDF


Erstellung von PDF Dokumenten inkl. Verwendung von

TrueType Schriften.

Unterstützung von PDF/A (ISO), digitaler Signatur,

Barcodes und Grafiken (PNG bzw. JPG).

Erzeugung von einfachen Charts.

Software Development

Kit (SDK)

Änderung von PDF Metadaten.

Extrahierung von einzelnen PDF-Seiten.

Zusammenfügen von PDFs (Merge).

Digitale Signatur und Wasserzeichen.

Komprimierung von PDFs.

Ermittlung der Anzahl von Seiten von PDFs.

Toolkit

PL/PDF (http://www.plpdf.com)



• Wird in der Oracle Datenbank installiert (kein weiterer Server)

• Direkt via PL/SQL ansprechbar

• Einbindung von externen Grafiken möglich

• Überdurchschnittlich guter Support, teilweise sogar Umsetzung von Kundenwünschen bereits im nächsten Release

• Gutes Preis-/Leistungsverhältnis

Vorteile

• Keine Skalierung von Haus aus und wenn, dann nur durchweiteren Datenbankserver möglich

• Ohne PL/SQL geht nichts (kein WYSIWYG-Editor)

• Kann nur einfache Grafiken/Charts erstellen

Aber es gibt ggfs. auch Nachteile …



CREATE OR REPLACE FUNCTION GET_PDF(IN_TEXT IN VARCHAR2) RETURN BLOB IS

l_pdf BLOB;BEGIN

REPORT.PLPDF.Init; REPORT.PLPDF.NewPage; REPORT.PLPDF.SetPrintFont(

p_family => 'Arial', p_style => NULL, p_size => 12);

REPORT.PLPDF.PrintText(p_x => 10,p_y => 10, p_txt => IN_TEXT);REPORT.PLPDF.SendDoc(p_blob => l_pdf);RETURN l_pdf;

END;/SELECT GET_PDF('Herzlich Willkommen bei Berenberg') PDF FROM DUAL;

Und so einfach geht es los …

Zusammenführen einzelner PDFs (Merge)


DECLAREl_pdf_1 BLOB;l_pdf_2 BLOB;l_pdf_merge BLOB;

BEGINl_pdf_1 := < pdf zuweisen >;l_pdf_2 := < pdf zuweisen >;

l_pdf_merge := PLPDF_TOOLKIT.Merge(P_INPUTFILE1 => l_pdf_1,P_INPUTFILE2 => l_pdf_2);

< l_pdf_merge z.B. INSERT INTO oder Return oder … >END;/

Variante 1 (optimal für 2 oder wenige PDFs)

Zusammenführen einzelner PDFs (Merge)


DECLAREl_pdf_merge BLOB;-- eindeutige transaktionsnr. fuer den merge-auftrag

l_id NUMBER := SYS_CONTEXT('USERENV','SID');BEGIN

INSERT INTO PLPDF_TK_MERGE_INPUTS (TR_ID, ORDER_NUM,INPUTFILE)SELECT l_id, ROWNUM, PDF FROM MYREPORTS;

l_pdf_merge := PLPDF_TOOLKIT.MergeX( P_TR_ID => l_id, --P_FONT => NULL,P_FONTSIZE => NULL);

-- bereitgestellte daten wieder entfernen

DELETE FROM PLPDF_TK_MERGE_INPUTS WHERE TR_ID = l_id;

< l_pdf_merge z.B. INSERT INTO oder Return oder … >END;/

Variante 2 (geeignet für viele PDFs)

Inhaltsverzeichnis mit variablen Seitenzahlen


-- z.B. erst nach dem Deckblatt die Numerierung bei 1 beginnen lassen

REPORT.PLPDF.SetPageNoStart;

-- die aktuelle Seitenzahl merkt sich PL/PDF gleich mit

REPORT.PLPDF.AddTOCItem(P_TXT => ‘<Reportüberschrift>‘);

Numerierung starten und Inhaltsverzeichnis befüllen

t_tocs PLPDF_TYPE.T_TOC;t_tocs := PLPDF.GetTOCItems;

PLPDF.PrintCell(P_W => 120, P_H => 10,P_TXT => RPAD(t_tocs(r_tocs).TEXT, 200,'.'),P_LINK => t_tocs(r_tocs).LINK_ID);

PLPDF.InsertMovePage(P_NEW_POS => 1, -- an den anfang, sofern ohne DeckblattP_ORIG_POS => PLPDF.CurrentPagenumber);

Inhaltsverzeichnis auslesen und an den Anfang schieben

Parallelisierung der Reporting Engine


• Temporärer Hintergrundprozess für jede einzelne Reportanfragebzw. für N Reportaufträgen aus Reportplänen.

• Beendet sich, wenn keine weiteren Reportaufträge mehrvorhanden sind.

• Permanent laufender Hintergrundprozess als Sicherheit im Falleeines DB Reboots. Interval dabei nicht zu gering wählen, damitkeine ständige CPU-Last entsteht.

Scheduler Jobs für die Reporting Engine

IHRE ANSPRECHPARTNER

Andriy TerletskyyAbteilungsdirektor DB-Entwicklung WertpapierOrganisation und IT+49 40 350 [email protected]

Daniel DymalaOracle Consultant (extern)Organisation und IT+49 40 350 [email protected]

Joh. Berenberg, Gossler & Co. KGNeuer Jungfernstieg 2020354 Hamburg


Vom TABLE OF TYPE zum REF CURSORPerformance-Tuning in einer bestehenden Anwendung

Sebastian Lauff & Thies Rubarth

DOAG Regionaltreffen Hamburg/Nord

16. September 2014

Ausgangssituation

Anwendungsarchitektur bei Berenberg:

• DB-Zugriff über generierte Java-Klassen

• Business-Logik komplett in der DB (PL/SQL-Packages und –Types)

• Java-Client mit (fast) reiner Anzeigefunktion

• Java-Server übersetzt zwischen PL/SQL und XML

Problem:

• Anzeigen von großen Datenmengen sehr langsam

16.09.2014 Vom TABLE OF TYPE zum REF CURSOR24

Datenlesen IST-Stand

SQL

PL/SQL

XML

Analyse-Ergebnis:

• PL/SQL-Overhead sehr teuer

• Erstellen, übertragen, parsen des XMLs sehr teuer


OPTIMIERUNG DB-TEIL

Vorher - Die PGA bekommt einen dicken Bauch und der Anwender einen dicken Hals

Alte Implementierung:

• Selektierung aller IDs

• Loop und jeweils den Type zu einer ID erstellen

• Type in Table of Types zwischenspeichern

• Gesamte Table of Types zurück geben

Type→ Selektiert sich selbst→ Selektiert andere Types→ Selektiert Stammdaten über

PL/SQL Cache


Nachher – Lass die SQL Engine die Arbeit machen

Neue Implementierung:

• GUI Datensatz soweit wie möglich in SQL nachbauen

• Kapselung der Anzeigelogik in einer View

• Weniger Joins benutzen

• Stammdaten über Scalar Subquery Selects mit PL/SQL Result Cache

• Dadurch Predicate Pushing ermöglichen


Codebeispiel

PL/SQL Result Cache

• Session übergreifender RESULT_CACHE bei Funktionen

• Oracle 11gR2 erkennt automatisch die Abhängigkeiten zur Tabelle / zu den Tabellen

Scalar Subquery Caching

• SELECT FROM DUAL als Subquery erstellt einen Cache für das einzelne Select -> Der Aufruf an PLSQL wird im spezifischen Select nur einmal pro ID durchgeführt


Codebeispiel

Predicate Pushing

• Die WHERE Bedingung wird im Optimalfall auf der richtigen Tabelle ausgeführt, und es wird nicht die komplette View ermittelt und dann gefiltert

HINT Nutzung bei Views

• Angabe des Tabellenalias aus der Viewdefinition notwendig


Codebeispiel

Function Return RefCursor

• Record definieren

• Ref Cursor definieren

• Function bauen

Weitere Möglichkeit:

• Arbeiten mit %ROWTYPE statt Record


OPTIMIERUNG JAVA-TEIL

Datenlesen SOLL-Stand

SQL


Datenlesen - Implementierung

Client

JBoss

IncrementalGet IncrementalWorker

DataProvider DB

Queue

Dialog

IncrementalReaderTable (Model)

Stateful EJB

Singleton EJB

Singleton EJB

JMS

Framework-Code

Anwendungs-Code


Optimierungsergebnis im Prototyp

Nr. ALT (1541 Sätze) NEU (1541 Sätze) NEU (10000 Sätze)

1 847ms 195ms 838ms

2 857ms 178ms 874ms

3 887ms 220ms 814ms

4 854ms 224ms 789ms

5 845ms 182ms 814ms

6 853ms 223ms 818ms

7 879ms 180ms 794ms

Ø 860ms 200ms 820ms


Vielen Dank für Ihre Aufmerksamkeit.

Documents

PDF-Reporting mit PL/SQL - · PDF fileEinblick ins Reporting Management des Private Bankings 3 16.09.2014 PDF-Reporting mit PL/SQL Allgemein • Reporting Modul ist komplett integriert