can anyone suggest a more elegant solution to this?

[alexs]

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

[Ian]

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]

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

[Tonkuma]

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)

[4.spam]

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

[Tonkuma]

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.

[Knut Stolze]

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]

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

[Brian Tkatch]

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.

[--CELKO--]

>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.

[alexs]

Phew!!!
many thanks toall of you that replied. I'll have a closer look when i
get back to work
What I've actually got is one table called IP_subnet that has all my
subnet details, e.g. def gateway, dns servers, subnet mask ip pool
address range, whether to generate dynamic dns entries in my dhcpd
config file etc and another one called ip_client where a subnet column
is a foreign key pointing to the IP_subnet table and another column is
the host component of the address.

The ip_client table holds things such client machine operating system,
use dhcp , generate a dns entry,owning user, registering user, date
created, date modified hostname , a foreign ptr to a table of dhcp
options associated with that host.and a few other things.

I did think of having 1 ip_client record for every ip address and a
boolean that indicated whether it was in use, but we've got a class b
network, and that's a lot of addresses :-))
BTW I'm using db2 V9.1 on a RH advanced server 4

--CELKO-- wrote:

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.

[Brian Tkatch]

alexs wrote:

Phew!!!
many thanks toall of you that replied. I'll have a closer look when i
get back to work
What I've actually got is one table called IP_subnet that has all my
subnet details, e.g. def gateway, dns servers, subnet mask ip pool
address range, whether to generate dynamic dns entries in my dhcpd
config file etc and another one called ip_client where a subnet column
is a foreign key pointing to the IP_subnet table and another column is
the host component of the address.

The ip_client table holds things such client machine operating system,
use dhcp , generate a dns entry,owning user, registering user, date
created, date modified hostname , a foreign ptr to a table of dhcp
options associated with that host.and a few other things.

I did think of having 1 ip_client record for every ip address and a
boolean that indicated whether it was in use, but we've got a class b
network, and that's a lot of addresses :-))

Not that much. A class B has 65k records. Truthfully, that's nothing
for a DB. If you had a Class A, then maybe you'd look at performance
issues.

B.