Searching for gaps within consecutive numbers using SQL
If you would have asked me one month ago to come up with a query that coughs up missing numbers, I would have told you that there is no such a need … until a few days ago.
In preparation of a migration of our Db2 (LUW) instances from one hardware platform to another we saw that we exhausted all our IP ports. Counting the number of instances per environment indicated some of the IP port ranges were missed. It is not that we would not be able to find them if we were to look for open ranges, but that would minimize the re-usability of it. As we register the used IP ports into a database, a repeatable query could come to the rescue.
The wisdom of the internet helped us out more than once, but we got it glued together.
The follow conventions are at play:
· use only port numbers between 50500 and 59999
· a range starts with a number ending with a zero and end with a number ending with a 9
· within a [0-9] range, every ending number is an indicator for another environment
o an instance can exist within a maximum of 10 environments
· the IP port numbers are registered in the table IDUG.INSTANCE_INFORMATION
An example:
· instance idug[environment]1
· environments
o development (ends with 0)
o user acceptance testing (ends with 5)
o production (ends with 6)
o bi (ends with 7)
The above elements would have as a result:
We are in need for a free IP range …
The challenge:
How can we match the existing IP ports against a non-existing list of IP ports?
o Generate a list of numbers and match against those
· How to find a consecutive list free IP ports?
o Search for IP ports with the BETWEEN keyword (other methods might work as well)
The query:
WITH portRange( low, high, step ) AS (
--
-- Specify the range within which the IP port should exist
--
VALUES ( 50500, 59999, 10 )
)
, validPortList( port, high, step ) AS (
--
-- Recursive list of all possible ports
--
SELECT low, high, step FROM portRange
UNION ALL
SELECT validPortList.port + validPortList.step
, validPortList.high
, validPortList.step
FROM validPortList
WHERE validPortList.port <= validPortList.high - validPortList.step
)
--
-- Show the port number(s) for each environment
--
SELECT port AS DEVELOPMENT_IP
, port + 5 AS UAT_IP
, port + 6 AS PRODUCTION_IP
, port + 7 AS BI_IP
FROM validPortList
WHERE NOT EXISTS (
--
-- Have a look if the calculated port numbers are in use.
-- Retain those that are still free.
--
SELECT 1
FROM IDUG.INSTANCE_INFORMATION inUse
, portRange
WHERE inUse.IP_PORT BETWEEN validPortList.port AND ( validPortList.port + ( portRange.step - 1 ) )
)
ORDER BY port
The query breakdown:
You probably noticed we abused the usage of CTEs a bit to our advantage. This should help us to read the query still within a year when we are wondering what it should do.
· portRange
Configure the boundaries within which we want to play:
o the lowest IP port that might be used (50500)
o the highest one (59999)
o how many consecutive empty spots we are searching for (10)
· validPortList
Generate a list of all possible numbers between ( 50500 + step ) and 59999. Only enlist the begin value of each range. This will make the list “step” times smaller. e.g.
50500
50510
50520
…
59990
· main query
Select all the IP port numbers that are generated by validPortList, but do check whether the validPortList.port does not exist in the table (IDUG.INSTANCE_INFORMATION) with the already registered IP ports. As we know the range of ports we want to have free, use the BETWEEN keyword.
This example query shows you our need, but I’m sure you’ll come up with your own use case(s).
Enjoy!