Externe Tabellen in Db2

Seit der aktuellen Db2-Version 11.5 unterstützt nun auch die IBM-Datenbank sogenannte Externe Tabellen. Der Vorteil liegt auf der Hand: Wo man bisher mit Tools wie Export, Import oder dem Load Daten von einem File in eine Tabelle kopiert hat oder umgekehrt Tabellendaten in eine Datei geschrieben hat, können externe Tabellen diesen Schritt erleichtern, da sich mit SQL-Mitteln mehr Möglichkeiten für die Weiterverarbeitung der Daten ergeben.

Externe Tabellen: Typen

Grundsätzlich existieren zwei Arten von externen Tabellen. Zum einen externe Tabellen, welche mit einer Create-Anweisung erstellt werden und damit auch einen Namen besitzen und deren Struktur und Definition im Datenbankkatalog hinterlegt wird. Andererseits sogenannte transiente also vorübergehend bestehende externe Tabellen, welche nur temporär, z.B. beim Zugriff über ein Select-Statement, bestehen.

Vorübergehende externe Tabellen

Transiente externe Tabellen werden z.B. für einmalige Aktionen verwendet. Man greift dabei direkt per Select-Anweisung auf die Datei zu, ohne dass zunächst ein Datenbankobjekt erstellt werden muss. Die Definition der Spalten wird hier direkt in der Anweisung angegeben.

Beispiel:

SELECT * FROM  EXTERNAL '/db2/staging/produkte.csv' 
    (PRODUKTNR BIGINT,
    PRODUKT VARCHAR(30),
    KATEGORIE VARCHAR(30),
    EK DECIMAL(5,2),
    VK DECIMAL(5,2))
    USING (DELIMITER ',' SKIPROWS 1);  

Im Beispiel wird das Komma als Spaltentrennzeichen (Delimiter) verwendet und per SKIPROW-Option mitgeteilt, dass die erste Zeile übersprungen werden soll, was z.B. oftmals verwendet wird, wenn die Datei eine Überschrift besitzt.

Persistente externe Tabellen

Weitaus öfter ist der Gebrauch von externen Tabellen, wenn diese persistent als Datenbankobjekt gespeichert werden. Diese werden beim Erstellen auch im Katalog hinterlegt und besitzen einen Namen, über welchen darauf zugegriffen werden kann.

Einfaches Beispiel:

CREATE EXTERNAL TABLE myschema.staging LIKE myschema.produkte
    USING (DATAOBJECT ‚/db2/staging/produkte.del‘ DELIMITER ‚|‘; 

Daten laden und entladen

Beispiel Laden: 

INSERT INTO PRODUKTE SELECT * FROM STAGING_PRODUKTE WHERE KATEGORIE = ‚ELEKTRO‘; 

Beispiel Entladen:

CREATE EXTERNAL TABLE ‚/db2/staging/adressen.del‘ USING (DELIMITER ‚|‘)
    AS SELECT * FROM APP1.ADRESSEN WHERE MOD_DATE=CURRENT DATE; 

Optionen

Neben den schon in den Beispielen gezeigten Optionen DELIMITER und SKIPROWS gibt es noch zahlreiche weitere Möglichkeiten, die externen Tabellen auf die eigenen Bedürfnisse anzupassen. Dies sind z.B. DATESTYLE, DATE_FORMAT, DECIMALDEL, BOOLSTYLE, um das Format der Daten zu bestimmen.

Auch der Zugriff auf komprimierte Dateien ist möglich. Db2 unterstützt den LZ4- und den GZIP-Komprimierungsalgorithmus. Das GZIP-Format wird hierbei sogar automatisch erkannt. 

• Das LZ4-Format muss mit der COMPRESS LZ4 -Option mitgeteilt werden.
• Mit REMOTESOURCE, SWIFT bzw. S3 kann auf entsprechende Cloud-Services zugegriffen werden.
• Die Option MAXERROR ermöglicht es, nach der angegebenen Anzahl der Fehler die Verarbeitung zu stoppen und ggf. einen Rollback durchzuführen. Dies kann bei der Verarbeitung von großen Files ggf. von Vorteil sein.
• MAXROWS gibt an, wie viele Zeilen maximal verarbeitet werden sollen. Insbesondere beim Testen ist diese Option oft hilfreich.

Neben den hier beschriebenen, wichtigen Optionen gibt es noch zahlreiche weitere Möglichkeiten, den Zugriff auf seine Bedürfnisse zu gestalten, auf die hier jedoch nicht weiter eingegangen werden soll.

Beispiel:

CREATE EXTERNAL TABLE myschema.staging_produkte
    (PRODUKTNR BIGINT,
    PRODUKT VARCHAR(30),
    GELISTET_SEIT DATE,
    AKTIV BOOLEAN,
    EK DECIMAL(8,2),
    VK DECIMAL(8,2))
    USING (DATAOBJECT '/db2/staging/produkte.csv.gz' DELLIMITER '#'
    DECIMALDELLIM ',' date_format 'DD.MM.YYYY' COMPRESS GZIP
    BOOLSTYLE Y_N SKIPROWS 1 MAXERRORS 50); 

Parameter

Die Dateien der externen Tabellen können selbstverständlich nicht an jedem Ort stehen, da dies sicherheitstechnisch ggf. ein Risiko wäre. Aus diesem Grund gibt es den Datenbankparameter EXTBL_LOCATION. Hiermit lässt sich angeben, in welchem Pfad (inklusive Unterverzeichnisse) die externen Daten liegen. Mit Semikolon getrennt können auch mehrere Pfade angegeben werden. Ein Zugriff auf Files, welche nicht in diesen Pfaden liegen, ist nicht möglich.

Zusätzlich kann der Pfad mit dem Parameter EXTBL_STRICT_IO nochmals nach Benutzer getrennt werden. Ist der Parameter auf YES gesetzt, gilt folgendes:

Zu dem im Parameter EXTBL_LOCATION angegebenen Pfad kann nur in dessen Unterverzeichnis mit dem Namen des Benutzers der jeweilige User auf externe Tabellen zugreifen. Ist zum Beispiel EXTBL_LOCATION auf den Pfad /db2/staging gesetzt, kann der User loadusr nur auf externe Tabellen im Verzeichnis /db2/staging/loadusr zugreifen.

Allerdings können dann nicht mehrere Pfade im Parameter EXTBL_LOCATION angegeben werden.

Logging und Fehlersuche

Bei jedem Zugriff auf eine externe Tabelle wird ein Logfile geschrieben. Dieses hat jeweils folgendes Format:

<dbname>.<schema>.<tabellenname>.<filename>.<applicationhandle>.<id>.log 

In diesem File sind detaillierte Informationen über den Zugriff auf die Tabelle zu finden. Fehlerhafte Datensätze, welche nicht verarbeitet werden können, werden hingegen in folgendes File geschrieben:

<dbname>.<schema>.<tabellenname>.<filename>.<applicationhandle>.<id>.bad 

Standardmäßig sind diese Logfiles im gleichen Verzeichnis wie der File der dazugehörigen externen Tabelle zu finden. Da dies schnell unübersichtlich werden, kann dieses Logverzeichnis jedoch mit der Option LOGDIR bzw. ERROR_LOG auch geändert werden.

Fazit

Externe Tabellen sind eine einfache und schnelle Möglichkeit, auf strukturierte Daten außerhalb der Datenbank zuzugreifen. Ebenso kann bequem der umgekehrte Weg gegangen und Tabelleninhalte schnell in eine Datei entladen werden.

Da dies mit einfachen Select- und Insert-Anweisungen geschieht, bei welchen man z.B. mit einer Where-Bedingung Zeilen filtern kann oder z.B. SQL-Funktionen anwenden kann, besteht hier die Möglichkeit, umfangreiche Datentransformationen vorzunehmen, welche ggf. nur mit komplexen ETL-Tools zu bewerkstelligen wären.