Power of Recursive SQL

Details: Category: DB2; Published: Thursday, 28 April 2016 23:08; Written by Super User; Hits: 1521

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in DB2 (from version 8 in both LUW and z/OS).
Why? Well, with recursive sql queries you can achieve things you would not have imagined being possible with SQL and all with just one sql-statement.

You can solve many types of business problems and even reduce some complex application logic down to a simple sql-call to the database.

What I like most of in using recursive sql query is ability to produce large amount of test-data in amount of seconds or minutes depending on complexity of the data and how many hundreds, thousands or millions of records that I want.

It has been proven that recursive queries outperforms other queries that take days to execute on huge amount of data by running in several minutes.

So how do you do it ?

The word recursive says it all. You have a query that repeatedly calls it self with some starting point and that which is EXTREAMELY IMPORTANT an ending point (a fail-safe exit) . If you don't have ending point or your recursive formula goes beyond the ending point you are in deep trouble. Your query will go into an infinite loop resulting in very high CPU and very high LOG utilization that can lead to storage exhaustion. If your query goes haywire you must think very fast and stop it or if you are unable to do so to alert your DBA immediately so he/she can prevent the database system of choking.

So lets look at simple example that generates sequence from 1 to 1000


WITH NUMBERS (LEVEL, NEXTONE) AS
(
SELECT 1, 1
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL +1, LEVEL +1
FROM NUMBERS
WHERE LEVEL < 1000
)

SELECT NEXTONE
FROM NUMBERS
ORDER BY NEXTONE

First of all is the CTE (Common Table Expression - yellow) where you define your temporary table result set, NUMBERS, with the columns LEVEL and NEXTONE where the LEVEL is our safe entry and exit point the query.

Next is our initial data (green) and starting point (our prime values) where we set LEVEL and NEXTONE to 1.

Next comes our DATA PUMP (blue gray) where the actual recursive traversal occurs with ( EXTREMELY IMPORTANT that this is done right) our exit point which in this case is LEVEL < 1000.

Finally you have the USE results from the CTE section (orange) where you select the desired result from your CTE.

Lets take this a little bit further. For example,.lets say we have a table ACCOUNT with the following columns

CREATE TABLE ACCOUNT(

BANKNO CHAR(4) NOT NULL,

ACCOUNTNO CHAR(10) NOT NULL,

CUSTNO CHAR(10) NOT NULL,

BALANCE DECIMAL(17,2) NOT NULL WITH DEFAULT,

CONSTRAINT ACCOUNTPK PRIMARY KEY (BANKNO,ACCOUNTNO,CUSTNO)

) IN MYSPACE INDEX IN MYSPACEIX;


CREATE INDEX ACCNO_IX ON ACCOUNT(ACCOUNTNO ASC);

INSERT INTO ACCOUNT VALUES('0100','5','1054',1000),('0100','2','1040',3000),('0100','10','1044',4000);

and for testing purposes we populate the table with this data

INSERT INTO ACCOUNT VALUES('0100','5','1044',1000),('0100','2','1044',3000),('0100','10','1044',4000);

where BANKNO is the number of a bank branch and this table hold all accounts of all customers in all branches of a bank.

The bank gives it's customer a choice to choose any available number for their new account from a list of To be able to produce a list available account numbers the bank tellers system executes the following query to produce the list.

WITH NUMBERS (LEVEL, NEXTONE) AS

(

SELECT 1, 1

FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT LEVEL +1, LEVEL +1

FROM NUMBERS

WHERE LEVEL < 999999

) SELECT NEXTONE AVAILABLE_ACCOUNT_NO

FROM NUMBERS

EXCEPT

SELECT INTEGER(ACCOUNTNO) AVAILABLE_ACCOUNT_NO

FROM ACCOUNT

WHERE BANKNO = '0100'

ORDER BY AVAILABLE_ACCOUNT_NO FETCH FIRST 10 ROWS ONLY

This query produces the following list in just matter of few seconds and lists the first 10 available account numbers of a list of 999,999 numbers.

As you can see the accountno 2, 5 and 10 are already taken.

For more available info and examples you can check out Suresh Sane's IDUG 2008 presentation available here as well as examples in the online IBM DB2 SQL Guide.