Ahhh What a topic! The most scary SQL that anyone can write must be a recursive cursor…The possibility to do it was introduced in DB2 V8 but I still only see sporadic use and normally it is my own SQLs! The thing is to learn to trust the recursive SQL and once you know how it works it can become a great friend but you must build trust up over time.
Here is a typical example use of recursive SQL for the trivial task of getting a list of numbers
WITH TEMP(N) AS (SELECT 1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT N+1 FROM TEMP WHERE N < 10) SELECT N FROM TEMP ;
Running this in SPUFI gives you:
---------+---------+---------+---------+---------+
N
---------+---------+---------+---------+---------+
1
2
3
4
5
6
7
8
9
10
DSNE610I NUMBER OF ROWS DISPLAYED IS 10
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
So how does it work and what controls are there?
Basically it was the creation of CTEs (Common Table Expressions) that allowed the creation of recursive SQL. The WITH xxx AS is the definition of a CTE – Then within (‘s the SQL has two “stages” the first is what I call the seeder – It starts the whole process off and generates the “first row” if you like. Then there is the UNION ALL that is needed to actually join all of the data together. If you try and use just UNION you get a nasty SQL error.
WITH TEMP(N)
AS
(SELECT 1
FROM SYSIBM.SYSDUMMY1
UNION
SELECT N+1
FROM TEMP
WHERE N < 10)
SELECT N FROM TEMP
;
---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE= -342, ERROR:THE COMMON TABLE EXPRESSION TEMP MUST
NOT USE
SELECT DISTINCT AND MUST USE UNION ALL BECAUSE IT IS RECURSIVESee? The SQL parser knows what it is doing. So now onto the next stage and that is the recursion statement and *very important* the emergency brake for the whole caboodle.
The recursive part is relatively clear. In this case we wish to select one number higher than the current number in the TEMP table. This could carry on forever so there is also an emergency brake that will stop any runaway SQL. In this case I wish to stop after reaching 10 (WHERE N < 10).
An explain of this query yields this access path:
---+---------+---------+---------+---------+---------+-------+----+ QNO PNO SQ M TABLE_NAME A CS INDEX IO UJOG P CE TYPE --+---------+---------+---------+---------+---------+--------+----+ 01 01 00 0 TEMP R 00 N ---- S SELECT 02 01 00 0 00 ---- UNIONA 03 01 00 0 SYSDUMMY1 R 00 N ---- S NCOSUB 04 01 00 0 TEMP R 00 N ---- S NCOSUB
Not really a great access path, but it is recursive after all!
Now you can write recursive SQL without using an “emergency brake” but let me show you a sensible usage of recursive SQL first. Imagine you’re interested in extracting DDL for a specific table and you are not sure about the dependencies of the VIEWs or MQTs that are being used (you could always buy our DDL Generator pocket tool of course 😉 <cough> <cough> anyway let us assume you do *not* have it. How do you find all of the “exploding VIEWs/MQTs”? Which ones are dependant on which objects etc. from one level all the way “back up the tree”? How would you write this SQL? You could do it with numerous fetches and probes of the DB2 catalog, but you could also do it with one single SQL statement (note that this SQL statement has been surgically killed. – If you are interested in getting an actual running version then please contact us.
Here’s the outline SQL
WITH VIVLIST
(MAX
,BCREATOR
,BNAME
,BTYPE
,DCREATOR
,DNAME
,DTYPE) AS
(SELECT 1
,STRIP(A.BCREATOR)
,STRIP(A.BNAME)
,A.BTYPE
,STRIP(A.DCREATOR)
,STRIP(A.DNAME)
,A.DTYPE
FROM SYSIBM.SYSVIEWDEP A
WHERE A.DCREATOR = :WS-CREATOR
AND A.DNAME = :WS-NAME
UNION ALL
SELECT B.MAX + 1
,STRIP(A.BCREATOR)
,STRIP(A.BNAME)
,A.BTYPE
,STRIP(A.DCREATOR)
,STRIP(A.DNAME)
,A.DTYPE
FROM SYSIBM.SYSVIEWDEP A
,VIVLIST B
WHERE A.DNAME = B.BNAME
AND A.DCREATOR = B.BCREATOR
AND B.MAX < 256
)
.
.
.
;Now, in this cursor my emergency brake is the B.MAX < 256, which stops this after 256 number of “view in view” definitions have been found. In practice a loop is impossible, as DB2 guarantees that you cannot generate cyclic views and I am pretty sure that no-one has more than 255 dependencies on a single view, or MQT. Anyway, I still like the brake, because when you do not have one (for example if you comment out the “AND B.MAX < 256” line) you get this “worrying” warning at BIND time:
DSNX105I -S810 BIND SQL WARNING
USING MDB2 AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=SQLDDLD
STATEMENT=2476
SQLCODE=347
SQLSTATE=01605
TOKENS=VIVLISTFor details refer to DB2 for z/OS messages.
A quick look in the DB2 for z/OS codes leads you to
>>
+347 THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP
Explanation: The recursive common table expression called name may not complete. This warning is based on not finding specific syntax as part of the iterative portion of the recursive common table expression. The expected syntax includes:
- incrementing an INTEGER column in the iterative select list by 1.
- a predicate in the where clause of the iterative portion of the form “counter_col < constant” or “counter_col < :hostvar”.
The absence of this syntax in the recursive common table expression may result in an infinite loop. The data or some other characteristic of the recursive common table expression may allow the successful completion of the statement anyway.
<<
So now you know why I use that brake!
Finally here are two examples
The first copied and slightly changed from the IDUG Code Place for doing maths with recursion:
--- PRINTS THE FACTORIAL OF NUMBERS FROM 1 TO N
WITH TEMP( N , FACT) AS
( SELECT
1
,CAST (1 AS DECIMAL(31 , 0))
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N + 1 , FACT * ( N + 1 )
FROM TEMP
WHERE N < 21
)
SELECT *
FROM TEMP
;Running this query gives you:
---------+---------+---------+---------+-------
N FACT
---------+---------+---------+---------+-------
1 1.
2 2.
3 6.
4 24.
5 120.
6 720.
7 5040.
8 40320.
9 362880.
10 3628800.
11 39916800.
12 479001600.
13 6227020800.
14 87178291200.
15 1307674368000.
16 20922789888000.
17 355687428096000.
18 6402373705728000.
19 121645100408832000.
20 2432902008176640000.
21 51090942171709440000.
DSNE610I NUMBER OF ROWS DISPLAYED IS 21And now something new from the List Serv (again slightly changed)
One of the posters posted this request:
Given a table A1:
Number Asset 1 AAAA 1 BBBB 1 CCCC 2 DDDD 2 EEEE
The result should be:
1 AAAA,BBBB,CCCC 2 DDDD,EEEE
“Does that work? I am not sure, if recursive SQL will do it.”
A short time later the same poster posted this extremely elegant piece of SQL code
WITH S (LEVEL, NUMBER, ASSET, ASSET_LIST) AS
(SELECT 1 , A1.NUMBER, MIN(A1.ASSET) AS ASSET,
MIN(A1.ASSET CONCAT SPACE(3000)) AS ASSET_LIST
FROM A1
GROUP BY A1.NUMBER
UNION ALL
SELECT LEVEL + 1 , S.NUMBER, A1.ASSET,
STRIP(S.ASSET_LIST) CONCAT ',' CONCAT A1.ASSET
FROM S
INNER JOIN TABLE
(SELECT A1.NUMBER, MIN(A1.ASSET) AS ASSET
FROM A1
WHERE S.NUMBER = A1.NUMBER
AND S.ASSET < A1.ASSET
GROUP BY A1.NUMBER
) AS A1
ON S.NUMBER = A1.NUMBER
)
SELECT S.NUMBER, MAX(S.ASSET_LIST) AS LIST
FROM S
GROUP BY S.NUMBER
;
I hope that this little bit of data was of interest and/or of use and as always comments or questions are more than welcome!
TTFN,
Roy Boxwell
Senior Architect