20110922-SIG-Development-Jessensky

Automatisiertes Deployment von
Datenbankobjekten im Data Warehouse:
Ein Erfahrungsbericht
Dr. Martin Jursch, WestLB AG
Dr. Olaf Jessensky, OPITZ CONSULTING
Gummersbach GmbH
DOAG SIG Development
Köln, 22. September 2011
Agenda
 Übersicht PCR NG
 Funktionsweise OC Schemaverwaltung
 Performanceoptimierung
Seite 2
Agenda
 Übersicht PCR NG
 Funktionsweise OC Schemaverwaltung
 Performanceoptimierung
Seite 3
Übersicht
PCR NG
PCR NG WestLB Landschaft
Eco. P&L
FO-Systeme
ALADIN
PRIME
SUMMIT
RECONCILIATION
BO-Systeme
PCR NG
UBIX
BASE
HGB DWH
WERS
General Ledger
CDP
SAP FI
IFRS DWH
SAP BA
Seite 4
Übersicht
PCR NG
BASEOS
TT
SSL
PCR NG Applikation
 PCR = Product Control Reporting (next generation)
 Abgleich von Daten aus den Handelssystemen, Abwicklungssystemen und Buchhaltungssystemen (National /
International)
 Qualitätssicherung der Monthly P&L für Handelsprodukte
 Tägliche Aktualisierung der Buchhaltungsdaten
 Reporting für Analyse von Differenzen zwischen den vier Liefersystem-Typen
 Realtime Korrekturfunktionalität der Reports
 Erstellung von Buchungen für Korrekturbedarf in Buchungssystemen
 Datamart zur Analyse von Differenzen zwischen Daily- und Monthly-P&L
 Backend Oracle Datenbank
 Frontend für Reporting und Korrekturen MSAccess-Applikation
 Frontend für P&L Reporting Cognos
Seite 5
PCR NG Applikation Datenbank Objekte
 900 Tabellen
 1550 Indizes
 340 Views
 145 Packages
 40 Prozeduren und Funktionen
 640 Trigger
 50 Java-Klassen
 5 Materialized Views
 90 Types
 1 Advanced Queue
 1,2 TB Gesamtgröße
Seite 6
PCR NG Infrastruktur
TEST Instanz
PRODUKTIONS Instanz
Entwicklung (DEV)
Produktion (QA)
RDS
RDS
ODS
ODS
STG
SVN
STG
RDS
RDS
ODS
ODS
STG
STG
Test (BCK)
Simulation (BE)
 Es gibt die Datenbank-Instanzen Test und Produktion
 In einer Instanz gibt es mehrere Environments (DEV, BCK, …)
 Ein Environment besteht aus den Schemata RDS_NG_..., ODS_NG_... und STG_NG_...
 Die Applikation ist in verschiedene Module / Komponenten unterteilt
 Module / Komponenten bestehen aus Datenbank-Objekten
Seite 7
PCR NG Deployment Anforderungen
Allgemein
Datenbank Objekte
 Alle Sourcen in SubVersion (zentrale Quelle)
 Tabellen mit sicherer Beibehaltung der Daten
 Einsatz von generischen nicht produkt-spezifischen
Source-Formaten und Tools
 Grosse Tabellen mit einigen GB an Volumen
 Deployment auf einen beliebigen Stand
 Deployment mit geringer Downtime
 Deployment flexibel neuen Datenbank-Objekten oder
neuen Attributen anpassbar
 Flexible Festlegung von Datenbank Objekt Attributen
wie Tablespace, Buffer Cache, Compression,…
 Indizes und FKs auf großen Tabellen
 Tabellen, die dynamisch von der Applikation erweitert
werden
 SQL-Typen für Tabellen und Packages
 Repositories
Seite 8
PCR NG Deployment
 Cognos (Deployment über Cognos)
 Framework Manager Model
 Reports (Report Studio)
 Reports (Query Studio)
XML-Dateien
XML-Dateien
XML-Dateien
 VBA (Deployment über VBA-Deployment-Tool (Eigenentwicklung WestLB))
 Module
TXT-Dateien
 Formulare
TXT-Dateien
 Tabellen
TXT-Dateien
 Properties
TXT-Dateien
 Oracle (Deployment Framework OC Schemaverwaltung)
 Tabellen
SQL*Plus Scripte
 Typen
SQL*Plus Scripte
 Views
SQL-Dateien
 Packages, Prozeduren, Funktionen SQL-Dateien
 Trigger
SQL-Dateien
 Sequences
SQL*Plus Scripte
 Grants
SQL*Plus Scripte
 Synonyme
SQL*Plus Scripte
 Java Sourcen
Java-Dateien
 Repositories
SQL-Dateien
Seite 9
Agenda
 Übersicht PCR NG
 Funktionsweise OC Schemaverwaltung
 Performanceoptimierung
Seite 10
Funktionswei
se OC
Schemaverw
altung
Motivation
Sourcecode/
Datenbankobjekte
manuell
Entwicklung
automatisiert
Test
automatisiert
Produktion
Seite 11
Kategorien
Kategorie 1
Datenbankobjekte die problemlos gelöscht und wieder neu
angelegt werden können.
z.B.: PL/SQL (Trigger, Functions, Procedures, Packages …),
Views, …
Seite 12
Kategorien
Kategorie 2
Datenbankobjekte die nicht einfach gelöscht werden dürfen.
Löschen verbietet sich aus 2 Gründen:
Datenerhalt
z.B.: Tabellen, Types
Performance
z.B.: Indizes, Constraints
Seite 13
Kategorien
Kategorie 2
Datenbankobjekte deren Eigenschaften von der Umgebung (Test, Produktion, …) abhängen:
Grants (ods_ng_qa -> rds_ng_qa)
Synonyme
Seite 14
Kategorien
Kategorie 1 Skripte können normale „create or replace“-Skripte sein.
Kategorie 2 Skripte benötigen eine Programmlogik, die diese z. B. mit
den bestehenden Datenbankobjekten vergleicht und nur die
tatsächlich nötigen Änderungen durchführt.
Seite 15
Architektur Kategorie 1
ant
pro Datenbankobjekt-Skript
SQL*Plus
ODS_NG_QA (schemaowner)
Seite 16
Kategorie 1 Beispiel
Via SQL*Plus alle Views einer Komponente aus einem Schema
entfernen.
begin
for cur_sql in
(
select 'drop view ' || OBJECT_NAME text
from user_objects
where object_type = 'VIEW' and object_name like 'SPL%'
) loop
execute immediate cur_sql.text;
end loop;
end;
/
Seite 17
Beispiel Kategorie 1
Via ant alle View-Skripte ausführen.
<!--
DB Views erstellen -->
<target name="build_views" depends="show_location">
<for param="file">
<path>
<fileset dir= "ODS" includes="**/views/*.sql"/>
</path>
<sequential>
<sqlplus script="@{file}"/>
</sequential>
</for>
</target>
Hinweise:
for-Konstrukt stammt von ant-contrib
sqlplus ist ein ant-Makro
Seite 18
Architektur Kategorie 2
ant
pro Tabellen-Datei-Skript
SQL*Plus
pro Anweisung
SQL*Plus
Aufruf als User ODS_NG_QA der
wiederum per Synonym auf das
Package depl_module von
DEPLOY_NG zugreift.
Das Package läuft mit „authid
current_user“.
ODS_NG_QA (schemaowner)
DEPLOY_NG (deployment)
Seite 19
Beispiel Kategorie 2
@&util_path\set_table_structure
no_id
@&util_path\create_table spl_fact_trade_tab spfctr permanent
@&util_path\alter_table_add_column spl_fact_trade_tab guv_ifrs_kz varchar2(1) mandatory "1"
@&util_path\alter_table_add_column spl_fact_trade_tab cob
date
mandatory ""
@&util_path\create_index spl_fact_trade_tab cob cob bitmap LOCAL ""
@&util_path\alter_table_add_foreign_key spl_fact_trade_tab spl_em_legalentity NOTHING "LEGALENTITY_ID"
@&util_path\clear_table
spl_fact_trade_tab
Seite 20
Kategorie 2 create table
@&util_path\set_table_structure no_id
(no_historization, standard)
@&util_path\create_table spl_fact_trade_tab spfctr permanent
 Tabelle mit Spalte ID anlegen, wenn sie noch nicht existiert
 ID ist optional bei no_id, sonst not null
 No_historization, standard: PK-Constraint und Sequenz (spfctr_id_sq) werden erzeugt
 Standard: zusätzliche Felder cu, cmd, lu, lmd
Seite 21
Kategorie 2 add_column
@&util_path\alter_table_add_column spl_fact_trade_tab guv_ifrs_kz varchar2(1) mandatory "1"
 Spalte anlegen, wenn sie noch nicht existiert
 Datentyp prüfen
 Verlängerung mit alter table
 Sonstige Veränderungen
 Neue Spalte anlegen
 Daten kopieren (bei Fehler Abbruch)
 Alte Spalte löschen
 Neue Spalte umbenennen
 NOT NULL prüfen
 Default prüfen
Seite 22
Kategorie 2 create index / add foreign key
@&util_path\create_index spl_fact_trade_tab cob cob bitmap LOCAL ""
 Index anlegen, wenn noch nicht vorhanden
 Index ersetzen, wenn Name, Bitmap/Eindeutigkeit oder Partition Range falsch
@&util_path\alter_table_add_foreign_key spl_fact_trade_tab spl_em_legalentity NOTHING "LEGALENTITY_ID "
 FK anlegen, wenn nicht vorhanden
 FK bezieht sich immer auf Spalte ID
 Delete Rule prüfen
Seite 23
Kategorie 2: Clear Table
@&util_path\clear_table
spl_fact_trade_tab
Aufgabe:
Alle sonstigen Tabellenbezogenen Datenbankobjekte löschen.
Problem:
Das Skript muss „wissen“ welche Datenbankobjekte existieren sollen
und welche nicht.
Lösung:
Alle Skripte registrieren die Datenbankobjekte die sie erzeugen sollen
in einer Tabelle. Nach Verarbeitung einer Komponente werden die Einträge
wieder gelöscht.
Seite 24
Kategorie 2: Grants, Synonyme
@&util_path\grant_object select,update spl_fact_version_tab VBA,COG "with grant option"
Problem:
Die User, an die gegrantet wird, sind je nach Umgebung variabel.
Lösung:
Das Framework kennt die Umgebung: COG -> COG_NG_QA für Umgebung QA
@&util_path\create_synonyms ODS SPL
Erzeuge gleichnamige Synonyme für alle Objekte von ODS -> ODS_NG_QA, die gegranted
wurden und die zur Komponente SPL gehören (deren Name mit SPL beginnt).
Seite 25
Reihenfolge pro Komponente und Schema
Synonyme
Pre-Skripte
Pre-Skripte (Einmalausführung)
Types
Tabellen
Java
Package Header
Functions, Procedures
Views
Type Bodies
Package Bodies
Trigger
DML-Skripte
Post-Skripte (Einmalausführung)
Post-Skripte
Grants
[synonyms]
[pre]
[pre_once]
[types, .tps]
[tables]
[java_sources]
[packages, .pks]
[functions], [procedures]
[views]
[types, .tpb]
[packages, .pkb]
[trigger]
[scripts_dml]
[post_once]
[post]
[grants]
Seite 26
Kat. 2
Kat. 2
Kat. 2
Kat. 1
Kat. 1
Kat. 1
Kat. 1
Kat. 1
Kat. 1
Kat. 1
Kat. 1
Kat. 2
SVN-Integration
Seite 27
Masterbuild-Konfiguration
Seite 28
Agenda
 Übersicht PCR NG
 Funktionsweise OC Schemaverwaltung
 Performanceoptimierung
Seite 29
Performanceo
ptimierung
Performanceproblematik
Dauer eines kompletten Deployments ca. 1 Tag, Downtime bis zu 2 Tagen
Anforderung: Daily P&L soll täglich erstellt werden können
Performancekiller bei großen Tabellen
 Erstellung und Validierung von Constraints
 Erstellung und Rebuild von Indexen
 Droppen von Spalten
 Änderung des Spaltenformats
Große Anzahl von Objekten
 Jeweils neue Session
 Vergleich mit Data Dictionary
Seite 30
Lösungsansätze
 Ein-Pass Verfahren für Tabellen
 Delta-Deployment
 Separate Ausführung von Langläufern per Job
 Parallelisierung von langlaufenden SQL‘s
 Paralleles Deployment mehrerer Komponenten
Seite 31
Ein-Pass vs. Zwei-Pass Verfahren
Problem bei Foreign Key Constraints: Zieltabelle existiert noch gar nicht
Lösung: Skripte werden 2 Mal ausgeführt
 Einmal ohne FK Constraints
 Zweiter Durchlauf mit FK Constraints
Nachteile bei schon existenten großen Tabellen
 Constraints werden beim 2. Durchlauf jedesmal neu erstellt/validiert
 Performanceprobleme beim Deployment
Lösung: Verhalten ist in Konfigurationsdatei umschaltbar
Seite 32
Delta-Deployment
Problem: Menge der deployten Elemente erzeugt hohe Laufzeiten
Lösung: Delta-Deployment mit Vorgabe eines Referenzdatums
 Datum des Commits in SVN als Änderungsdatum der lokalen Datei
 Es werden nur Dateien mit Änderungsdatum >= Referenzdatum deployt
 Vorheriges Löschen der Kategorie 1 Objekte entfällt
Ausnahmen bei aufeinander aufbauenden Objekten (z. B. Views)
Optional: Komponente ohne Änderungen in SVN wird ignoriert
Seite 33
Auslagerung von langlaufenden SQL‘s
Sonderbehandlung für erfahrungsgemäß imperformante Operationen
 SQL‘s werden in einer Tabelle abgelegt (mit Build-Nr., Komponente, Tabellenname)
 Zu bestimmten Zeitpunkten wird für bestimmte Arten von Statements ein Job gestartet
 Bearbeitungsstatus und Fehler werden protokolliert
 Mail-Benachrichtigung bei Abschluss
Drop column: set unused + alter table drop unused columns
Verhalten ist auf Tabellenebene konfigurierbar
@&util_path\set_table_column_drop_mode SETUNUSED
@&util_path\set_table_index_create_mode DBJOB
Seite 34
Parallelisierung von SQL‘s
Erstellen von Indizes
 Create Index mit parallel 8
 Alter index noparallel
Setzen von Default-Werten
 Alter session enable parallel dml
 Update mit Parallel-Hint
Seite 35
Paralleles Deployment
Parallelisierung des Deployments mit Parallel – Tag in Ant
 Komponenten werden auf 2 Threads verteilt
 Feste Reihenfolge innerhalb der Threads
 Finaler Teil ohne Parallelisierung
Ausführungszeit fast halbiert
Seite 36
Zusammenfassung
 Deployment einer definierten Version aller Datenbankobjekte aus Subversion
 Deployment beliebiger Versionsstände in beliebige Zielumgebungen
 Downtime für Monatsdelta in Produktion 2-3 Stunden
 Standard-Werkzeuge Ant und SQL*Plus
 Flexibel änderbar und konfigurierbar
Seite 37
&
Fragen & Antworten
Seite 38
Kontaktdaten
Dr. Martin Jursch
WestLB AG
[email protected]
Dr. Olaf Jessensky
OPITZ CONSULTING Gummersbach GmbH
[email protected]
Vielen Dank für
Ihre Aufmerksamkeit!
Seite 39