PDF-Reporting mit PL/SQL

PDF-Reporting mit PL/SQL
Daniel Dymala
DOAG Regionaltreffen Hamburg/Nord
16. September 2014
Einleitung
Worum geht es
•
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 NICHT
•
Evaluierung von PDF-Tools in Verwendung mit der Oracle
Datenbank
•
Einführung in PL/PDF
(“Hello World”-Beispiele auf plpdf.com vorhanden)
2
16.09.2014
PDF-Reporting mit PL/SQL
Einblick ins Reporting Management des Private Bankings
Allgemein
3
•
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
4
16.09.2014
PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis
5
16.09.2014
PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis
6
16.09.2014
PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis
7
16.09.2014
PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis
8
16.09.2014
PDF-Reporting mit PL/SQL
9
16.09.2014
PDF-Reporting mit PL/SQL – Beispiel Vermögensausweis
Einblick ins Reporting Management des Private Bankings
Konfiguration, Ausführung und Bereitstellung von Reportings
10
16.09.2014
PDF-Reporting mit PL/SQL
Einblick ins Reporting Management des Private Bankings
Beispiel für einen Reportplan
11
16.09.2014
PDF-Reporting mit PL/SQL
Einblick ins Reporting Management des Private Bankings
Reportliste (mit Reportauftrag aus Reportplan)
12
16.09.2014
PDF-Reporting mit PL/SQL
Einblick ins Reporting Management des Private Bankings
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
13
16.09.2014
PDF-Reporting mit PL/SQL
Einblick ins Reporting Management des Private Bankings
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
14
16.09.2014
PDF-Reporting mit PL/SQL
Das PDF-Reporting Tool PL/PDF
PL/PDF (http://www.plpdf.com)
Erstellung von PDF Dokumenten inkl. Verwendung von
Software
Development
Kit (SDK)
TrueType Schriften.
Unterstützung von PDF/A (ISO), digitaler Signatur,
Barcodes und Grafiken (PNG bzw. JPG).
Erzeugung von einfachen Charts.
Änderung von PDF Metadaten.
Extrahierung von einzelnen PDF-Seiten.
Toolkit
Zusammenfügen von PDFs (Merge).
Digitale Signatur und Wasserzeichen.
Komprimierung von PDFs.
Ermittlung der Anzahl von Seiten von PDFs.
15
16.09.2014
PDF-Reporting mit PL/SQL
Das PDF-Reporting Tool PL/PDF
Vorteile
•
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
Aber es gibt ggfs. auch Nachteile …
•
Keine Skalierung von Haus aus und wenn, dann nur durch
weiteren Datenbankserver möglich
•
Ohne PL/SQL geht nichts (kein WYSIWYG-Editor)
•
Kann nur einfache Grafiken/Charts erstellen
16
16.09.2014
PDF-Reporting mit PL/SQL
Das PDF-Reporting Tool PL/PDF
Und so einfach geht es los …
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;
17
16.09.2014
PDF-Reporting mit PL/SQL
Zusammenführen einzelner PDFs (Merge)
Variante 1 (optimal für 2 oder wenige PDFs)
DECLARE
l_pdf_1 BLOB;
l_pdf_2 BLOB;
l_pdf_merge BLOB;
BEGIN
l_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;
/
18
16.09.2014
PDF-Reporting mit PL/SQL
Zusammenführen einzelner PDFs (Merge)
Variante 2 (geeignet für viele PDFs)
DECLARE
l_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;
/
19
16.09.2014
PDF-Reporting mit PL/SQL
Inhaltsverzeichnis mit variablen Seitenzahlen
Numerierung starten und Inhaltsverzeichnis befüllen
-- 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>‘);
Inhaltsverzeichnis auslesen und an den Anfang schieben
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 Deckblatt
P_ORIG_POS => PLPDF.CurrentPagenumber);
20
16.09.2014
PDF-Reporting mit PL/SQL
Parallelisierung der Reporting Engine
Scheduler Jobs für die Reporting Engine
•
Temporärer Hintergrundprozess für jede einzelne Reportanfrage
bzw. für N Reportaufträgen aus Reportplänen.
•
Beendet sich, wenn keine weiteren Reportaufträge mehr
vorhanden sind.
•
Permanent laufender Hintergrundprozess als Sicherheit im Falle
eines DB Reboots. Interval dabei nicht zu gering wählen, damit
keine ständige CPU-Last entsteht.
21
16.09.2014
PDF-Reporting mit PL/SQL
IHRE ANSPRECHPARTNER
Andriy Terletskyy
Daniel Dymala
Abteilungsdirektor DB-Entwicklung Wertpapier
Organisation und IT
+49 40 350 60-219
[email protected]
Oracle Consultant (extern)
Organisation und IT
+49 40 350 60-8057
[email protected]
Joh. Berenberg, Gossler & Co. KG
Neuer Jungfernstieg 20
20354 Hamburg
22
16.09.2014
PDF-Reporting mit PL/SQL
Vom TABLE OF TYPE zum REF CURSOR
Performance-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
24
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
Datenlesen IST-Stand
XML
PL/SQL
Analyse-Ergebnis:
SQL
25
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
•
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
27
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
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
•
28
Weniger Joins benutzen
•
Stammdaten über Scalar Subquery Selects mit PL/SQL Result Cache
•
Dadurch Predicate Pushing ermöglichen
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
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
29
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
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
30
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
Codebeispiel
Function Return RefCursor
•
Record definieren
•
Ref Cursor definieren
•
Function bauen
Weitere Möglichkeit:
•
Arbeiten mit %ROWTYPE statt
Record
31
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
OPTIMIERUNG JAVA-TEIL
Datenlesen SOLL-Stand
SQL
33
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
Datenlesen - Implementierung
Client
FrameworkCode
AnwendungsCode
Dialog
IncrementalReader
Table (Model)
JMS
Queue
IncrementalGet
IncrementalWorker
Singleton
EJB
Stateful
EJB
JBoss
34
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
Singleton
EJB
DataProvider
DB
Optimierungsergebnis im Prototyp
35
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
16.09.2014
Vom TABLE OF TYPE zum REF CURSOR
Vielen Dank für Ihre Aufmerksamkeit.