Eine Tabelle zu leeren scheint ein einfaches Unterfangen zu sein, aber auch hier gibt es einige Dinge zu bedenken. Bei einem Besuch im IBM Db2 Development Labor in Toronto habe ich neue Erkenntnisse erlangt, die ich in diesem Artikel gerne teilen möchte.
Db2 TRUNCATE Statement
Welche Optionen gibt es eine Tabelle zu leeren:
Der folgende Command
leert die Tabelle extrem effizient und kann nicht per Rollback rückgängig gemacht werden.
Nur für spalten-organisierte Tabellen – sogenannte BLU-Tabellen – ist die IMMEDIATE Option optional und erlaubt es die Löschung rückgängig zu machen, solange IMMEDIATE nicht spezifiziert wurde. Das ist möglich, weil nur logisch gelöscht wird – d.h. die Zeilen werden als gelöscht geflaggt und nur diese Information wird geloggt.
Schauen wir uns die weiteren Optionen an:
Von der Theorie zur Praxis
So viel zu den Grundlagen des Statements – schauen wir auf einen konkreten Problemfall aus einem Data Warehouse Kundenszenario:
Während eines aufwendigen und lange laufenden Initialladevorgangs wurde der Zeitpunkt für das online Backup erreicht, das erfolgreich gestartet wurde. Zusätzlich setzte dann – ein paar Stunden später – der nächtliche ETL-Ladelauf ein, der u.a. ein Truncate-Statement auf einer Tabelle auslöst. Da das Backup auf Grund der Lastsituation ebenfalls deutlich länger als normal dauerte, kam es hier zu einem Lock-Konflikt, der über Stunden anhielt.
Wie hätte das nun vermieden werden können?
Wie oben beschrieben ist DROP STORAGE nicht kompatibel mit einem Online Backup. Diese Option war nicht explizit spezifiziert, was dazu geführt hat, dass sie implizit gesetzt wurde, weil sie auch den Default darstellt.
Ein explizit gesetztes REUSE STORAGE hätte hier das Problem umgangen.
Tipp aus dem IBM Db2 Lab
An dieser Stelle gibt es noch eine weitere – sehr interessante – Lösung, die ich Mike Springgay aus dem IBM Db2 Labor zu verdanken habe.
Es gibt die Registry Variable DB2_TRUNCATE_REUSESTORAGE, die folgendermaßen gesetzt werden kann:
Ein Durchstarten der Instance ist hier nicht notwendig.
Für IMPORT und LOAD gilt das Konfliktpotential nur wenn die REPLACE Option spezifiziert wurde.
Durch dieses Setting wird die IMPLIZIT gesetzte DROP STORAGE Option in die REUSE STORAGE Option gewandelt, um die Blockierung zu vermeiden.
Hinweis: Diese Registry-Variable hat keinen Effekt, falls die Option explizit gesetzt wurde!
Somit ist diese Registry-Variable eine generelle Empfehlung, wenn man Sperrkonflikte in dieser Situation vermeiden will.
Wenn Sie weitere Fragen haben, können Sie sich gerne bei uns melden unter: kontakt@mip.de
Wir freuen uns auf den persönlichen Austausch mit Ihnen!
Ihr Michael Tiefenbacher
Business and Customer Success Manager & Principal Consultant
Sie sind interessiert daran, mehr über unser Leistungsangebot zu erfahren, dann besuchen Sie unsere Themenseite Datenbanken.
Db2 TRUNCATE Statement
Welche Optionen gibt es eine Tabelle zu leeren:
- DROP TABLE – die Holzhammer-Methode
Das hat den gravierenden Nachteil, dass man die Tabelle anschließend wieder anlegen muss. Zusätzlich sind auch alle weiteren abhängigen Strukturen betroffen – wie Indizes, Views, Rechte etc. Somit ist dies in den meisten Fällen indiskutabel. - DELETE Statement
Das ist zwar recht einfach zu schreiben, wird aber in einer Transaktion ausgeführt, was bei größeren Datenmengen zu Logging-Problemen führt, da Db2 sich ja alle Daten merken muss, um diese im Rollback-Fall wieder erstellen zu können. - TRUNCATE Statement
Massenlöschung ohne Logging-Overhead.
Der folgende Command
leert die Tabelle extrem effizient und kann nicht per Rollback rückgängig gemacht werden.
Nur für spalten-organisierte Tabellen – sogenannte BLU-Tabellen – ist die IMMEDIATE Option optional und erlaubt es die Löschung rückgängig zu machen, solange IMMEDIATE nicht spezifiziert wurde. Das ist möglich, weil nur logisch gelöscht wird – d.h. die Zeilen werden als gelöscht geflaggt und nur diese Information wird geloggt.
Schauen wir uns die weiteren Optionen an:
- DROP STORAGE
DROP STORAGE gibt den freigewordenen Platz an den Tablespace zurück, der so von anderen Tabellen wiederverwendet werden kann.
ACHTUNG:
Mit dieser Option kann es zu Konflikten mit einem Online-Backup kommen, da beide Operationen eine Sperre (OLB Lock) auf dem Tablespace benötigen. - REUSE STORAGE
Ist sinnvoll, wenn eine Tabelle geleert wurde und gleich wieder geladen werden soll – in Data Warehouse Projekten eine häufige Anforderung. - IGNORE DELETE TRIGGERS
- RESTRICT WHEN DELETE TRIGGERS
Diese Option stellt sicher, dass ein Fehler zurückgegeben wird, wenn ein DELETE Trigger vorhanden ist. - IMMEDIATE
Von der Theorie zur Praxis
So viel zu den Grundlagen des Statements – schauen wir auf einen konkreten Problemfall aus einem Data Warehouse Kundenszenario:
Während eines aufwendigen und lange laufenden Initialladevorgangs wurde der Zeitpunkt für das online Backup erreicht, das erfolgreich gestartet wurde. Zusätzlich setzte dann – ein paar Stunden später – der nächtliche ETL-Ladelauf ein, der u.a. ein Truncate-Statement auf einer Tabelle auslöst. Da das Backup auf Grund der Lastsituation ebenfalls deutlich länger als normal dauerte, kam es hier zu einem Lock-Konflikt, der über Stunden anhielt.
Wie hätte das nun vermieden werden können?
Wie oben beschrieben ist DROP STORAGE nicht kompatibel mit einem Online Backup. Diese Option war nicht explizit spezifiziert, was dazu geführt hat, dass sie implizit gesetzt wurde, weil sie auch den Default darstellt.
Ein explizit gesetztes REUSE STORAGE hätte hier das Problem umgangen.
Tipp aus dem IBM Db2 Lab
An dieser Stelle gibt es noch eine weitere – sehr interessante – Lösung, die ich Mike Springgay aus dem IBM Db2 Labor zu verdanken habe.
Es gibt die Registry Variable DB2_TRUNCATE_REUSESTORAGE, die folgendermaßen gesetzt werden kann:
Ein Durchstarten der Instance ist hier nicht notwendig.
Für IMPORT und LOAD gilt das Konfliktpotential nur wenn die REPLACE Option spezifiziert wurde.
Durch dieses Setting wird die IMPLIZIT gesetzte DROP STORAGE Option in die REUSE STORAGE Option gewandelt, um die Blockierung zu vermeiden.
Hinweis: Diese Registry-Variable hat keinen Effekt, falls die Option explizit gesetzt wurde!
Somit ist diese Registry-Variable eine generelle Empfehlung, wenn man Sperrkonflikte in dieser Situation vermeiden will.
Wenn Sie weitere Fragen haben, können Sie sich gerne bei uns melden unter: kontakt@mip.de
Wir freuen uns auf den persönlichen Austausch mit Ihnen!
Ihr Michael Tiefenbacher
Business and Customer Success Manager & Principal Consultant
Sie sind interessiert daran, mehr über unser Leistungsangebot zu erfahren, dann besuchen Sie unsere Themenseite Datenbanken.
