Rekursives SQL

Seit Version 8 kann DB2 for z/OS auch rekursives SQL verstehen. Hierzu wird die Technik einer Common Table Expression verwendet.
Der Basisaufbau des Statements ist dabei immer gleich:

WITH common_table ( spaltenliste ) AS
( Ursprungsselect
UNION ALL
Rekursionsselect )
SELECT spaltenliste FROM common_table WHERE ...


Die tatsächliche Ausformulierung des entsprechenden SELECTs ist allerdings zumindest gewöhnungsbedürftig.
Hilfreich ist es, sich an diese Fragen zu halten:

1) Welche Spalten sollen in meiner Ergebnismenge auftauchen und/oder werden für die Rekursionsbedingung benötigt ?
2) Wie lautet der SELECT für den Satz, von dem die Rekursion ausgehen soll ?
3) Wie lautet die Rekursionsbedingung ?

nehmen wir dieses einfache Beispiel:

In einem Unternehmen gibt es mehrere Hierarchiestufen. Ganz oben stehen die Obermotze. Diese sind die Vorgesetzten der Mittelmotze, die wiederum die Untermotze leiten. Jeder Untermotz hat dann noch seine Roedldoedl, weil irgendjemand muss ja die ganze Arbeit machen. In einer DB2 - Tabelle sind diese Beziehungen abgelegt:

Tabelle: ORGA
Spalten: PERSNR , NAME , PERSNR#CHEF

Der Tabelleninhalt sieht so aus:

 PERSNR   NAME  PERSNR#CHEF 
1 Obermotz 1 <null>
2 Obermotz 2 <null>
3 Obermotz 3 <null>
11 Mittelmotz 11 1
12 Mittelmotz 12 1
13 Mittelmotz 13 1
21 Mittelmotz 21 2
22 Mittelmotz 22 2
23 Mittelmotz 23 2
31 Mittelmotz 31 3
32 Mittelmotz 32 3
33 Mittelmotz 33 3
111 Untermotz 111 11
112 Untermotz 112 11
113 Untermotz 113 11
114 Untermotz 114 11
121 Untermotz 121 12
122 Untermotz 122 12
123 Untermotz 123 12
211 Untermotz 211 21
212 Untermotz 212 21
213 Untermotz 213 21
311 Untermotz 311 31
312 Untermotz 312 31
313 Untermotz 313 31
321 Untermotz 321 32
1111  Roedldoedl 1111  111
1112 Roedldoedl 1112 111
1113 Roedldoedl 1113 111
1114 Roedldoedl 1114 111
1121 Roedldoedl 1121 112
1122 Roedldoedl 1122 112
1211 Roedldoedl 1211 121
1212 Roedldoedl 1212 121
1231 Roedldoedl 1231 123
1232 Roedldoedl 1232 123
1311 Roedldoedl 1311 131
1312 Roedldoedl 1312 131
2111 Roedldoedl 2111 211
2112 Roedldoedl 2112 211
2113 Roedldoedl 2113 211
3111 Roedldoedl 3111 311
3112 Roedldoedl 3112 311
3311 Roedldoedl 3311 331
3211 Roedldoedl 3211 321


Die typische Fragestellung lautet nun: welche Untergebenen hat Obermotz 1 ?
Der klassische SELECT:

SELECT B.*
FROM ORGA A INNER JOIN ORGA B
ON A.PERSNR = B.PERSNR#CHEF
WHERE A.NAME = 'Obermotz 1' ;

scheitert, da hier nur die direkten Untergebenen erfasst werden.

 PERSNR   NAME  PERSNR#CHEF 
11  Mittelmotz 11  1
12 Mittelmotz 12 1
13 Mittelmotz 13 1


Nicht erfasst werden hingegen die Untergebenen der Untergebenen und die Untergebenen der Untergebenen der Untergebenen …
Hier liegt eine typische Anwendung für rekursives SQL vor.

Zum Aufbau des Statements beantworten wir nun einfach die obigen Fragen:

1) Welche Spalten sollen in meiner Ergebnismenge auftauchen und/oder werden für die Rekusionsbedingung benötigt ?
Antwort: PERSNR , NAME

2) Wie lautet der SELECT für den Satz, von dem die Rekursion ausgehen soll ?
Antwort:
SELECT PERSNR , NAME
FROM ORGA
WHERE PERSNR#CHEF = 1;

3 ) Wie lautet die Rekursionsbedingung ?
Antwort verbal: Wer ist Untergebener eines in der vorherigen Rekursion ermittelten Chefs. Also:
neuhinzuzufügen.PERSNR#CHEF = gerade_ermittelt.PERSNR


mit diesem Wissen ist das Aufbauen des SQL-Statements nun nicht mehr schwierig:

WITH ABCDE ( PERSNR , NAME ) AS -- feste Syntax für Common table expression, Spaltenliste lt. Frage 1
( SELECT PERSNR, NAME FROM ORGA WHERE PERSNR#CHEF = 1 -- Basisselect lt. Frage 2
UNION ALL-- feste Syntax für Rekursion
SELECT A.PERSNR , A.NAME FROM ORGA A-- Spaltenliste (wie gehabt lt. Frage 1)
INNER JOIN ABCDE B ON A.PERSNR#CHEF = B.PERSNR )-- Rekursionsbedingung lt. Frage 3
SELECT * FROM ABCDE;-- der letzendliche Select

der Name der common table ( in meinem Beispiel ABCDE ) ist dabei frei wählbar. Die Result-table enspricht nun genau dem gewünschten Ergebnis:

 PERSNR   NAME 
11 Mittelmotz 11
12 Mittelmotz 12
13 Mittelmotz 13
111 Untermotz 111
112 Untermotz 112
113 Untermotz 113
114 Untermotz 114
121 Untermotz 121
122 Untermotz 122
123 Untermotz 123
131 Untermotz 131
1111  Roedldoedl 1111 
1112 Roedldoedl 1112
1113 Roedldoedl 1113
1114 Roedldoedl 1114
1121 Roedldoedl 1121
1122 Roedldoedl 1122
1211 Roedldoedl 1211
1212 Roedldoedl 1212
1231 Roedldoedl 1231
1232 Roedldoedl 1232
1311 Roedldoedl 1311
1312 Roedldoedl 1312


Wäre als UrsprungsSELECT ein

SELECT PERSNR, NAME FROM ORGA
WHERE NAME = 'Obermotz 1'

gewählt worden, so wäre das Ergebnis um eine Zeile ( nämlich um den Obermotz selbst ) grösser gewesen.

Es ist übrigens auch möglich ( und durchaus sinnvoll ), die Anzahl der Iterationsstufen mitzuzählen. Hier z.B:
Auf welcher Hierarchiestufe steht ein Mitarbeiter ( Obermotz = 1 ), alle anderen 2,3,4 ...

Die Query sieht -leicht erweitert- dann so aus:

WITH ABCDE ( PERSNR , NAME , STUFE ) AS
( SELECT PERSNR, NAME , 1 AS STUFE FROM ORGA WHERE PERSNR = 1
UNION ALL
SELECT A.PERSNR , A.NAME , B.STUFE + 1 FROM ORGA A
INNER JOIN ABCDE B ON A.PERSNR#CHEF = B.PERSNR )
SELECT PERSNR , NAME , STUFE FROM ABCDE ;


 PERSNR   NAME  STUFE 
1 Obermotz 1 1
11 Mittelmotz 11 2
12 Mittelmotz 12 2
13 Mittelmotz 13 2
111 Untermotz 111 3
112 Untermotz 112 3
113 Untermotz 113 3
114 Untermotz 114 3
121 Untermotz 121 3
122 Untermotz 122 3
123 Untermotz 123 3
1111  Roedldoedl 1111  4
1112 Roedldoedl 1112 4
1113 Roedldoedl 1113 4
1114 Roedldoedl 1114 4
1121 Roedldoedl 1121 4
1122 Roedldoedl 1122 4
1211 Roedldoedl 1211 4
1212 Roedldoedl 1212 4
1231 Roedldoedl 1231 4
1232 Roedldoedl 1232 4


Warum kann ein derartiges "Mitzählen" grundsätzlich sinnvoll sein ?
Ganz einfach - um die Anzahl der Iterationen begrenzen zu können. DB2 ist nämlich seeeeehr geduldig. Entsprechende Daten ( oder eine fehlerhaft formulierte Bedingung ) könnten nämlich durchaus zu einer Endlosschleife führen ( und wenn nicht ein Governor oder RLF einschreitet, dann .......... ).

Hat man allerdings mitgezählt, dann kann man nach einer bestimmten Anzahl Iterationen abbrechen.

WITH ABCDE ( PERSNR , NAME , STUFE ) AS
( SELECT PERSNR, NAME , 1 AS STUFE FROM ORGA WHERE PERSNR = 1
UNION ALL
SELECT A.PERSNR , A.NAME , B.STUFE + 1 FROM ORGA A
INNER JOIN ABCDE B ON A.PERSNR#CHEF = B.PERSNR
WHERE B.STUFE < 3 )
SELECT PERSNR , NAME , STUFE FROM ABCDE ;


Bei obiger Abfrage wäre das Ergebnis das gleiche wie bei:

WITH ABCDE ( PERSNR , NAME , STUE ) AS
( SELECT PERSNR, NAME , 1 AS STUFE FROM ORGA WHERE PERSNR = 1
UNION ALL
SELECT A.PERSNR , A.NAME , B.STUFE + 1 FROM ORGA A
INNER JOIN ABCDE B ON A.PERSNR#CHEF = B.PERSNR )
SELECT PERSNR , NAME , STUFE
FROM ABCDE
WHERE STUFE <= 3 ;

Allerdings ist erste Query performanter und "ungefährlicher".
( Bei der zweiten Query wird zuerst alles ermittelt und erst später gefiltert - zuviel Arbeit und es besteht das Risiko der Endlosschleife. )

Soweit alles klar ? Guuuut !! - dann ist es Zeit für eine Übung:
Die Fragestellung lautet:
Welche Vorgesetzten hat Roedldoedl 1121 ? ( seine Personalnummer ist bekannt, sie ist 1121 ). Roedldoed l121 darf/muss/soll dabei selbst in der Liste auftauchen.


Also, wie lautet hier die Antwort auf Frage 1, Frage 2, Frage 3 ?? Und wie der gesamte SELECT ? ...

eine mögliche Lösung ist:

WITH ABCDE ( PERSNR , NAME , PERSNR#CHEF )
AS
( SELECT PERSNR , NAME , PERSNR#CHEF
FROM ORGA
WHERE PERSNR = 1121
UNION ALL
SELECT A.PERSNR , A.NAME , A.PERSNR#CHEF
FROM ORGA A INNER JOIN ABCDE B
ON A.PERSNR = B.PERSNR#CHEF
)
SELECT * FROM ABCDE ;

 PERSNR   NAME  PERSNR#CHEF 
1121 Roedldoedl 1121 112
112 Untermotz 112 11
11 Mittelmotz 11 1
1 Obermotz 1 -


Und wenn die Personalnummer von Roedldoedl nicht bekannt ist ( und erst ermittelt werden muss ) ? Und er selbst nicht in der Liste auftauchen soll ?
... nene, das machen Sie jetzt alleine !


Zurück zu DB2 Home Impressum / Datenschutz