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