Temporäre Tabellen in DB2 auf IBM i – Effizient, flexibel, unverzichtbar

Technisches Fundament

Auf IBM i definierst Du temporäre Tabellen mit DECLARE GLOBAL TEMPORARY TABLE. Sie landen automatisch in der Session-Bibliothek QTEMP, sind vom Systemkatalog abgekoppelt und können mit NOT LOGGED besonders performant betrieben werden.

„The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current application process. […] Its description does not appear in the system catalog.“ IBM Docs

Temporäre Tabelle erstellen

In Db2 for i (DB2/400) werden temporäre Tabellen meist mit dem SESSION-Schema erstellt. Wichtig: QTEMP darf hier nicht verwendet werden.

-- Temporäre Tabelle (nur Struktur, keine Daten)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP AS (
  SELECT EMPNO, SALARY
  FROM MYLIB.EMPLOYEES
) WITH NO DATA;

CTE für Zwischenergebnisse

Common Table Expressions (CTEs) lassen sich direkt mit einem SELECT kombinieren:

WITH TempSales AS (
  SELECT ORDERID, SUM(AMOUNT) AS TOTAL
  FROM MYLIB.SALES
  GROUP BY ORDERID
)
SELECT * FROM TempSales
WHERE TOTAL > 1000;

CTEs und temporäre Tabellen kombiniert

Für komplexere Abfragen mit mehreren Verarbeitungsschritten kann es sinnvoll sein, CTE-Ergebnisse in eine temporäre Tabelle zu schreiben:

-- 1. Temporäre Tabelle anlegen (Struktur vorbereiten)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_AVGSAL AS (
  SELECT DEPTNO, DECIMAL(0,2) AS AVG_SAL
  FROM MYLIB.EMPLOYEES
) WITH NO DATA;

-- 2. CTE verwenden und einfügen
INSERT INTO SESSION.TEMP_AVGSAL
WITH Aggregated AS (
  SELECT DEPTNO, AVG(SALARY) AS AVG_SAL
  FROM MYLIB.EMPLOYEES
  GROUP BY DEPTNO
)
SELECT * FROM Aggregated;

• Performance: Schneller als DELETE FROM, da keine einzelnen Zeilendeletionen protokolliert werden.
• Wiederverwendbarkeit: Die Tabellenstruktur bleibt erhalten, sodass die Tabelle ohne Neudefinition weiterverwendet werden kann.
• Anwendungsfälle: Ideal in Stored Procedures oder Batch-Jobs, bei denen eine temporäre Tabelle mehrfach mit neuen Daten gefüllt wird.

Beispiel: In einer Stored Procedure, die Verkaufsdaten für mehrere Monate verarbeitet, kann die Tabelle für jeden Monat geleert und wiederverwendet werden:

-- Schritt 1: Temporäre Tabelle erstellen
DECLARE GLOBAL TEMPORARY TABLE SESSION.MONTHLY_SALES (
    MONTH_ID INT,
    TOTAL_SALES DECIMAL(10,2)
) ON COMMIT PRESERVE ROWS NOT LOGGED;

-- Schritt 2: Daten für Januar einfügen
INSERT INTO SESSION.MONTHLY_SALES 
SELECT 1, SUM(AMOUNT) 
FROM MYLIB.SALES 
WHERE ORDER_DATE BETWEEN '2025-01-01' AND '2025-01-31';

-- Schritt 3: Daten verarbeiten (z. B. Bericht erstellen)
SELECT * FROM SESSION.MONTHLY_SALES;

-- Schritt 4: Tabelle für Februar leeren
TRUNCATE TABLE SESSION.MONTHLY_SALES;

-- Schritt 5: Daten für Februar einfügen
INSERT INTO SESSION.MONTHLY_SALES 
SELECT 2, SUM(AMOUNT) 
FROM MYLIB.SALES 
WHERE ORDER_DATE BETWEEN '2025-02-01' AND '2025-02-28';

Vergleich mit WITH REPLACE: Bei WITH REPLACE würde die Tabelle für jeden Monat neu erstellt werden, was zusätzlichen Overhead verursacht:

DECLARE GLOBAL TEMPORARY TABLE SESSION.MONTHLY_SALES (
    MONTH_ID INT,
    TOTAL_SALES DECIMAL(10,2)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

TRUNCATE TABLE ist in diesem Fall effizienter, da die Struktur erhalten bleibt und nur die Daten gelöscht werden.
Expertenmeinung:
„Die Unterstützung für TRUNCATE TABLE in DB2 for i ab Version 7.2 ermöglicht ein effizientes Leeren von temporären Tabellen ohne Strukturänderung.“ – IBM Docs: TRUNCATE.

TRUNCATE auf temporären Tabellen

Seit IBM i Version 7.2 unterstützt Db2 for i das Truncating von temporären Tabellen. Mit dem TRUNCATE TABLE-Befehl können alle Zeilen einer DECLARE GLOBAL TEMPORARY TABLE (Session-Tabelle) in einem Schritt gelöscht werden, während die Tabellenstruktur erhalten bleibt. Dies entspricht funktional dem CL-Befehl CLRPFM (Clear Physical File Member) in SQL-Form und bietet eine performante Alternative zu DELETE oder dem Neuerstellen der Tabelle mit WITH REPLACE. Der Befehl ist sowohl für persistente als auch für temporäre Tabellen anwendbar und wurde eingeführt, um effizientes Leeren zu ermöglichen und die Kompatibilität mit anderen Db2-Plattformen zu gewährleisten. IT Jungle: DB2 for i 7.2 Features.Ein Beispiel für die Anwendung:

TRUNCATE TABLE SESSION.MY_TEMP;

Dieser Befehl löscht alle Zeilen der temporären Tabelle MY_TEMP in der aktuellen Session, ohne die Tabellenstruktur zu verändern. Besonders nützlich ist die Option NOT LOGGED, die den Logging-Overhead minimiert und die Performance weiter steigert.

Indizes auf temporären Tabellen

Entgegen der häufigen Annahme, dass temporäre Tabellen keine Indizes unterstützen, können in Db2 for i Indizes auf temporären Tabellen erstellt werden – jedoch nur innerhalb der aktuellen Session und ab IBM i 7.4 (DB2 for i V11.1). Laut IBM-Dokumentation: „If a declared temporary table is referenced in a CREATE INDEX or CREATE VIEW statement, the index or view must be created in SESSION (or library QTEMP).“ IBM Docs: DECLARE GLOBAL TEMPORARY TABLE. Solche Indizes werden im Schema SESSION (QTEMP) angelegt und sind nur für die Dauer der Session verfügbar. Ein praktisches Beispiel:

CREATE INDEX SESSION.IDX_TEMP ON SESSION.TEMP_EMP (Col1, Col2);

Dieser Index beschleunigt Abfragen auf der temporären Tabelle TEMP_EMP, existiert aber nur in der aktuellen Session und wird nach Session-Ende automatisch gelöscht, genau wie die Tabelle selbst. Tests mit IBM i Access Client Solutions (ACS) bestätigen, dass solche Indizes erfolgreich erstellt werden können. Dennoch fehlen temporären Tabellen standardmäßig Indizes und Statistiken, was den SQL-Optimizer einschränken kann, wie Kent Milligan betont: „QTEMP tables limit the query optimizer due to rare indexing.“ MCPress. Entwickler müssen Indizes bei Bedarf manuell in QTEMP erstellen, um die Performance komplexer Abfragen zu verbessern.

1. Erstellung der temporären Tabelle: Eine temporäre Tabelle wird erstellt, um die aggregierten Umsatzdaten pro Abteilung zu speichern. Die Option NOT LOGGED sorgt für eine höhere Performance, da kein Journaling-Overhead entsteht.
2. Datenaggregation: Die Verkaufsdaten für den aktuellen Monat werden aus der Haupttabelle aggregiert und in die temporäre Tabelle eingefügt.
3. Abfrage des Berichts: Der Bericht wird durch eine einfache Abfrage der temporären Tabelle erstellt – schnell und effizient.
4. Automatische Bereinigung: Nach Beendigung der Session wird die temporäre Tabelle automatisch gelöscht, ohne dass manuelle Schritte nötig sind.

SQL-Code für das Beispiel:

-- Schritt 1: Temporäre Tabelle erstellen
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_SALES_REPORT (
    DEPTNO CHAR(3),
    TOTAL_SALES DECIMAL(15,2)
) ON COMMIT PRESERVE ROWS NOT LOGGED;

-- Schritt 2: Daten aggregieren und in die temporäre Tabelle einfügen
INSERT INTO SESSION.TEMP_SALES_REPORT
SELECT DEPTNO, SUM(AMOUNT) AS TOTAL_SALES
FROM MYLIB.SALES
WHERE ORDER_DATE BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY DEPTNO;

-- Schritt 3: Bericht abfragen
SELECT * FROM SESSION.TEMP_SALES_REPORT
ORDER BY TOTAL_SALES DESC;

-- Hinweis: Die temporäre Tabelle wird am Ende der Session automatisch gelöscht.

Vorteile in diesem Szenario:

• Schnelle Verarbeitung: Erstellung und Löschung laufen in Millisekunden, was die Erstellung des Berichts beschleunigt.
• Optimierte I/O: NOT LOGGED minimiert Overhead bei Inserts, wodurch die Aggregation der Verkaufsdaten effizienter wird.
• Session-Isolation: Kein Konflikt mit anderen Jobs dank QTEMP, ideal für parallele Berichtserstellungen.
• RPG-freundlich: Gut kombinierbar mit CL und SQLRPGLE, was die Integration in bestehende IBM i-Programme erleichtert.
• Flexibilität: Die temporäre Tabelle kann für weitere Analysen oder Abfragen genutzt werden, ohne die Haupttabelle zu beeinträchtigen.
• Automatische Bereinigung: Kein manueller Aufwand für das Löschen der Tabelle nach der Verwendung.

Nachteile in diesem Szenario:

• Index-Nutzung eingeschränkt: Vor IBM i 7.4 keine Indizes möglich, was die Performance bei komplexen WHERE-Bedingungen einschränken kann. Ab IBM i 7.4 können Indizes erstellt werden, was die Abfrageleistung verbessert.
• Flüchtig: Daten sind nach Session-Ende weg, was bei längeren Analysen oder Debugging beachtet werden muss.
• SQL-Plan Cache: QTEMP-Objekte können den Cache belasten, besonders bei häufiger Erstellung ähnlicher Berichte.

Ergebnis: Dieses Beispiel zeigt, wie temporäre Tabellen in einem realen Geschäftsszenario eingesetzt werden können, um die Effizienz zu steigern und die Systemressourcen optimal zu nutzen. Es ist besonders nützlich für IBM i-Entwickler:innen, die komplexe Berichte erstellen müssen, ohne die Produktivdatenbank zu überlasten.

table-name

Der Tabellenname muss explizit oder implizit mit SESSION qualifiziert werden. Wird kein Qualifier angegeben, wird automatisch SESSION angenommen. Achtung bei Namenskonflikten – temporäre Tabellen überschreiben keine permanenten automatisch.

FOR SYSTEM NAME

Mit dieser Option definierst Du einen internen Systemnamen (max. 10 Zeichen), der bei der Objekterstellung im Hintergrund verwendet wird. Praktisch bei QSHELL- oder API-Zugriffen.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (
    EMPNO CHAR(6),
    SALARY DEC(10,2)
) FOR SYSTEM NAME MYTEMP01 WITH REPLACE NOT LOGGED;

„Temporary tables are powerful but require careful testing.“ – Dan Cruikshank, DB2 Portal

“Temporary tables are powerful but require careful testing.” – Dan Cruikshank, DB2 Portal

“Effective DB2 performance tuning begins with a thorough analysis of the application’s requirements and workload characteristics.” – Craig Mullins, Top 10 Performance Tuning Tips – Planet Mainframe