Hi,
I've got a Java stored procedure that I use to "register" systems on
our network. In the middle of it is a function that returns the 1st
free ip address in a class C network which is used in the registration
process.

In this process a free ip address is defined as a number between 1 and
254

At the moment I'm fetching an ordered list of ip addresses (which will
be i the range 1 - 254) and then using a while loop with a couple of
variables and 2 break statements to figure out the lowest ip address
that's free.
Basically i'm checking that each entry in the list differs from the
next (higher) value by 1. If it doesn't then the free address is lower
value + 1 and you breat out of the room.

just wondering if there might be a faster way to do it.
Alex

alexs wrote:
> Hi,
> I've got a Java stored procedure that I use to "register" systems on
> our network. In the middle of it is a function that returns the 1st
> free ip address in a class C network which is used in the registration
> process.
>
> In this process a free ip address is defined as a number between 1 and
> 254
>
> At the moment I'm fetching an ordered list of ip addresses (which will
> be i the range 1 - 254) and then using a while loop with a couple of
> variables and 2 break statements to figure out the lowest ip address
> that's free.
> Basically i'm checking that each entry in the list differs from the
> next (higher) value by 1. If it doesn't then the free address is lower
> value + 1 and you breat out of the room.

You don't specify the platform or version of DB2, so I'll assume
Linux/UNIX/Windows and DB2 V8. You also don't specify a table
design, so here's something.


-- setup
create table registered_ips (subnet char(11), addr smallint);

insert into registered_ips
values ('192.168.1', 1),
('192.168.1', 2),
('192.168.1', 3),
('192.168.1', 6),
('192.168.1', 12),
('10.0.0', 1),
('10.0.0', 2);



-- SQL statement to find first open address for a particular subnet.
select
subnet, addr+1 as avail_addr
from
(select
subnet,
addr,
min(addr) over (partition by subnet
order by addr
rows between 1 following and 1 following) as
next_addr
from
registered_ips
) as a
where
subnet = '192.168.1' and
(next_addr is null or next_addr <> addr+1)
fetch first 1 row only
;



Obviously specify which submit in the where clause above.

The above SQL returns, it finds '4' as the lowest address for the
192.168.1 subnet.

SUBNET AVAIL_ADDR
----------- -----------
192.168.1 4

If there aren't any holes (as is the case with 10.0.0), it will
give you the equivalent of max(addr)+1:

SUBNET AVAIL_ADDR
----------- -----------
10.0.0 3


Finding appropriate index(es) for this query is left as an exercise
for the reader.

Ian wrote:
>
> Finding appropriate index(es) for this query is left as an exercise
> for the reader.
>
>

Also, bounds-checking is left as an exercise for the reader, too :-)

Serge could probably even write a single statement that combines
finding the value AND inserting it into the table AND returning it
to the application. ;-)



Ian

If there is not addr = 1 for some subnet, it may need to add some more
code.
(I don't know this is true or there is always a row with addr = 1 for
each subnet).

--- Data ---
insert into registered_ips
values ('192.168.0', 3),
('192.168.0', 4),
('192.168.0', 6),
('192.168.0', 12),
('192.168.1', 1),
('192.168.1', 2),
('192.168.1', 3),
('192.168.1', 6),
('192.168.1', 12),
('10.0.0', 1),
('10.0.0', 2);

------------------------------ Commands Entered
------------------------------
select
subnet,
coalesce(nullif(min(addr),min(min_addr)),0)+1 as avail_addr
from
(select
subnet,
addr,
min(addr)
over (partition by subnet
order by addr
rows between 1 following
and 1 following) as next_addr,
min(addr)
over (partition by subnet) as min_addr
from
registered_ips
) as a
where
(next_addr is null or next_addr <> addr+1)
or (min_addr = addr and addr > 1)
group by
subnet
;
------------------------------------------------------------------------------

SUBNET AVAIL_ADDR
----------- -----------
10.0.0 3
192.168.0 1
192.168.1 4

3 record(s) selected.

Following is another example without using OLAP functions.
------------------------------ Commands Entered
------------------------------
select
sbn.subnet, coalesce(avail_addr,0)+1 as avail_addr
from
(select
distinct subnet
from
registered_ips
) sbn
left outer join
(select
subnet, min(addr) avail_addr
from
registered_ips ips
where
not exists
(select *
from
registered_ips ipn
where
ipn.subnet = ips.subnet
and ipn.addr = ips.addr + 1
)
and exists
(select *
from
registered_ips ipe
where
ipe.subnet = ips.subnet
and ipe.addr = 1
)
group by
subnet
) adr
on sbn.subnet = adr.subnet
;
------------------------------------------------------------------------------

SUBNET AVAIL_ADDR
----------- -----------
10.0.0 3
192.168.0 1
192.168.1 4

3 record(s) selected.

If you want get available addr for a specific subnet,
you can replace

(select
distinct subnet
from
registered_ips
) sbn

with

(values '192.168.1'
) sbn (subnet)

Hello.

First available for all subnets:
---
with t(subnet, addr) as (
select distinct subnet, 0
from registered_ips
union all
select t.subnet, r.addr
from registered_ips r, t
where r.subnet=t.subnet and r.addr=t.addr+1
)
select subnet, max(addr)+1 addr
from t
group by subnet;
---

For particular subnet:
---
with t(subnet, addr) as (
values ('10.0.0', 0)
union all
select t.subnet, r.addr
from registered_ips r, t
where r.subnet=t.subnet and r.addr=t.addr+1
)
select max(addr)+1 addr
from t;
---

Sincerely,
Mark B.

> Hi,
> I've got a Java stored procedure that I use to "register" systems on
> our network. In the middle of it is a function that returns the 1st
> free ip address in a class C network which is used in the registration
> process.
>
> In this process a free ip address is defined as a number between 1 and
> 254
>
> At the moment I'm fetching an ordered list of ip addresses (which will
> be i the range 1 - 254) and then using a while loop with a couple of
> variables and 2 break statements to figure out the lowest ip address
> that's free.
> Basically i'm checking that each entry in the list differs from the
> next (higher) value by 1. If it doesn't then the free address is lower
> value + 1 and you breat out of the room.
>
> just wondering if there might be a faster way to do it.
> Alex

This is another shorter example.
------------------------------ Commands Entered
------------------------------
select
subnet,
coalesce(nullif(1,min(addr)),min(nullif(addr,1))+1 ) avail_addr
from
registered_ips ips
where
not exists
(select *
from
registered_ips ipn
where
ipn.subnet = ips.subnet
and ipn.addr = ips.addr + 1
)
or ips.addr =
(select min(addr)
from
registered_ips ipm
where
ipm.subnet = ips.subnet
)
group by
subnet
;
------------------------------------------------------------------------------

SUBNET AVAIL_ADDR
----------- -----------
10.0.0 3
192.168.0 1
192.168.1 4

3 record(s) selected.

alexs wrote:

> Hi,
> I've got a Java stored procedure that I use to "register" systems on
> our network. In the middle of it is a function that returns the 1st
> free ip address in a class C network which is used in the registration
> process.
>
> In this process a free ip address is defined as a number between 1 and
> 254
>
> At the moment I'm fetching an ordered list of ip addresses (which will
> be i the range 1 - 254) and then using a while loop with a couple of
> variables and 2 break statements to figure out the lowest ip address
> that's free.
> Basically i'm checking that each entry in the list differs from the
> next (higher) value by 1. If it doesn't then the free address is lower
> value + 1 and you breat out of the room.

You could simply select the lowest value (larger than 1) for which there is
no predecessor.

SELECT MIN(number)-1
FROM ...
WHERE number -1 NOT IN ( SELECT number
FROM ... ) AND
number > 1

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze wrote:

> alexs wrote:
>
>> Hi,
>> I've got a Java stored procedure that I use to "register" systems on
>> our network. In the middle of it is a function that returns the 1st
>> free ip address in a class C network which is used in the registration
>> process.
>>
>> In this process a free ip address is defined as a number between 1 and
>> 254
>>
>> At the moment I'm fetching an ordered list of ip addresses (which will
>> be i the range 1 - 254) and then using a while loop with a couple of
>> variables and 2 break statements to figure out the lowest ip address
>> that's free.
>> Basically i'm checking that each entry in the list differs from the
>> next (higher) value by 1. If it doesn't then the free address is lower
>> value + 1 and you breat out of the room.
>
> You could simply select the lowest value (larger than 1) for which there
> is no predecessor.
>
> SELECT MIN(number)-1
> FROM ...
> WHERE number -1 NOT IN ( SELECT number
> FROM ... ) AND
> number > 1
>

I forgot: of course, you will have to take care of empty tables. But a
COALESCE in the SELECT clause will do the trick. And for multiple subnets,
you can simply nest the above query in another query over all subnets.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

alexs wrote:
> Hi,
> I've got a Java stored procedure that I use to "register" systems on
> our network. In the middle of it is a function that returns the 1st
> free ip address in a class C network which is used in the registration
> process.
>
> In this process a free ip address is defined as a number between 1 and
> 254
>
> At the moment I'm fetching an ordered list of ip addresses (which will
> be i the range 1 - 254) and then using a while loop with a couple of
> variables and 2 break statements to figure out the lowest ip address
> that's free.
> Basically i'm checking that each entry in the list differs from the
> next (higher) value by 1. If it doesn't then the free address is lower
> value + 1 and you breat out of the room.
>
> just wondering if there might be a faster way to do it.
> Alex

Perhaps you could store all 254 IP addess in the TABLE, and use a
second COLUMN to denote if it is taken.

CREATE Table Ip_Address(A INT, B INT, C INT, D INT, Status INT)

Add obvious CONSTRAINTs, and all possible ips.

Then:

SELECT MIN(D) FROM Ip_Address WHERE A = 192 AND B = 168 AND C = ? AND
Staus IS NULL;

B.

>> Perhaps you could store all 254 IP addess in the TABLE, and use a second COLUMN to denote if it is taken. <<

Go one step further and have a DATE column that tells you when the IP
addess was assigned and perhaps a column for the assginee.