All,

DB2 LUW v9.5 FP 5.

Has anyone implemented or could point articles to an UDF that returns
the Subnet ID of a (IP, NetMask) pair?

Examples:

(IP = 192.168.2.70, NetMask = 255.255.255.0) -> Expected UDF result
is 192.168.2.0 / 24
(IP = 172.17.206.235, NetMask = 255.255.224.0) -> Expected UDF result
is 172.17.192.0 / 19

The UDF should return exactly what http://www.subnet-calculator.com
does.

Any links/suggestions?

Thanks,

-Michel

On 2011-01-14 15:52, Michel Esber wrote:
Michel, I tossed together a sketch, perhaps you can make something out
of it. I've used 2 helper functions:

create function mask_length(ip smallint)
returns smallint
return case ip when 255 then 8
when 254 then 7
when 252 then 6
when 248 then 5
when 240 then 4
when 224 then 3
when 192 then 2
when 128 then 1
else 0
end @

create function split_ip(ip varchar(15))
returns table (octet smallint, ipval smallint)
begin atomic
declare p1 smallint;
declare p2 smallint;
declare p3 smallint;

set p1 = locate('.', ip, 1);
set p2 = locate('.', ip, p1+1);
set p3 = locate('.', ip, p2+1);

return values(1, smallint(substr(ip,1,p1-1)))
union all
values(2, smallint(substr(ip,p1+1,p2-p1-1)))
union all
values(3, smallint(substr(ip,p2+1,p3-p2-1)))
union all
values(4, smallint(substr(ip,p3+1))) ;
end @

Example:

db2 "select * from table(split_ip('172.17.206.235')) order by octet"

OCTET IPVAL
------ ------
1 172
2 17
3 206
4 235


This would probably be a good time to dig into string aggregation via
xml functions, but I don't have the time to figure that out now:

create function subnet_id (ip varchar(15), mask varchar(15))
returns varchar (20)
return with tmp as (
select x.octet, bitand(x.ipval, y.ipval) as bitwise,
mask_length(y.ipval) as mask_length
from table(split_ip(ip)) x
join table(split_ip(mask)) y
on x.octet = y.octet
) select rtrim(char(t1.bitwise)) || '.' ||
rtrim(char(t2.bitwise)) || '.' ||
rtrim(char(t3.bitwise)) || '.' ||
rtrim(char(t4.bitwise)) || '/' ||
rtrim(char((select sum(mask_length) from tmp)))
from tmp t1, tmp t2, tmp t3, tmp t4
where t1.octet = 1
and t2.octet = 2
and t3.octet = 3
and t4.octet = 4 @

Example:

db2 "values subnet_id('172.17.206.235', '255.255.224.0')"

1
--------------------
172.17.192.0/19

1 record(s) selected.


db2 "values subnet_id('192.168.2.70', '255.255.255.0')"

1
--------------------
192.168.2.0/24




Tested on:

db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL09072"
with level identifier "08030107".
Informational tokens are "DB2 v9.7.0.2", "s100514", "IP23088", and Fix Pack
"2".
Product is installed at "/opt/ibm/db2/V9.7".

Lennart, thanks a lot. I will test everything and let you know the
results.

Example (1) used duplicated codes for IP and NetMask.
Example (2) eliminated the duplication by using a common-table-
expression. But, it will be slower than Example (1).

Both will not work on DB2 for LUW 9.5 or ealier, because of using
VARCHAR(numeric).

They may work on DB2 for LUW 9.5, if changed VARCHAR(numeric) to
RTRIM(CHAR(numeric)).


Example (1):

CREATE FUNCTION subnet_id( ip VARCHAR(15) , netmask VARCHAR(15) )
RETURNS VARCHAR(20)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT
SUBSTR(
XMLCAST(
XMLGROUP(
'.' || VARCHAR( BITAND(ip_k , mask_k) ) AS a
ORDER BY i.rn )
AS VARCHAR(16) )
, 2 ) || '/' ||
VARCHAR( 32
- SMALLINT( SUM( LN( 256 - SMALLINT(mask_k) ) )
/ LN(2) ) )
FROM
(SELECT SUBSTR( ip
, LAG(pos , 1 , 0)
OVER(ORDER BY pos) + 1
, pos -
LAG(pos , 1 , 0)
OVER(ORDER BY pos) - 1
) AS ip_k
, ROW_NUMBER() OVER(ORDER BY pos) AS rn
FROM (VALUES 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) p(pos)
WHERE pos <= LENGTH(ip) + 1
AND SUBSTR(ip || '. ' , pos , 1) = '.'
) i
INNER JOIN
(SELECT SUBSTR( netmask
, LAG(pos , 1 , 0)
OVER(ORDER BY pos) + 1
, pos -
LAG(pos , 1 , 0)
OVER(ORDER BY pos) - 1
) AS mask_k
, ROW_NUMBER() OVER(ORDER BY pos) AS rn
FROM (VALUES 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) p(pos)
WHERE pos <= LENGTH(netmask) + 1
AND SUBSTR(netmask || '. ' , pos , 1) = '.'
) m
ON i.rn = m.rn
;


------------------------------ Commands Entered
------------------------------
VALUES subnet_id('192.168.2.70' , '255.255.255.0');
------------------------------------------------------------------------------

1
--------------------
192.168.2.0/24

1 record(s) selected.


Example (2):

CREATE FUNCTION subnet_id_2( ip VARCHAR(15) , netmask VARCHAR(15) )
RETURNS VARCHAR(20)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
WITH split_ip(id , ip_k , rn) AS (
SELECT id
, SUBSTR( ip_a , lag_pos + 1 , pos - lag_pos - 1)
, ROW_NUMBER() OVER(PARTITION BY id
ORDER BY pos)
FROM (SELECT id , ip_a
, pos
, LAG(pos , 1 , 0)
OVER(PARTITION BY id
ORDER BY pos) AS lag_pos
FROM (VALUES ('I' , ip) , ('M' , netmask) ) q(id , ip_a)
INNER JOIN
(VALUES 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) p(pos)
ON pos <= LENGTH(ip_a) + 1
AND SUBSTR(ip_a || '. ' , pos , 1) = '.'
)
)
SELECT
SUBSTR(
XMLCAST(
XMLGROUP(
'.' || VARCHAR( BITAND(i.ip_k , m.ip_k) ) AS a
ORDER BY i.rn )
AS VARCHAR(16) )
, 2 ) || '/' ||
VARCHAR( 32
- SMALLINT( SUM( LN( 256 - SMALLINT(m.ip_k) ) )
/ LN(2) ) )
FROM
split_ip i
INNER JOIN
split_ip m
ON i.id = 'I'
AND m.id = 'M'
AND i.rn = m.rn
;


------------------------------ Commands Entered
------------------------------
VALUES subnet_id_2('192.168.2.70' , '255.255.255.0');
------------------------------------------------------------------------------

1
--------------------
192.168.2.0/24

1 record(s) selected.

On 2011-01-14 21:40, Michel Esber wrote:
Entering the twilight zone ...

create function split_ip(ip varchar(15))
returns table (octet smallint, ipval smallint)
begin atomic
declare p1 smallint;
declare p2 smallint;
declare p3 smallint;

set p1 = locate('.', ip, 1);
set p2 = locate('.', ip, p1+1);
set p3 = locate('.', ip, p2+1);

return values(1, smallint(substr(ip,1,p1-1)))
union all
values(2, smallint(substr(ip,p1+1,p2-p1-1)))
union all
values(3, smallint(substr(ip,p2+1,p3-p2-1)))
union all
values(4, smallint(substr(ip,p3+1))) ;
end @

create function subnet_id2 (ip varchar(15), mask varchar(15))
returns varchar (20)
return
with tmp as (
select x.octet,
bitand(x.ipval, y.ipval) as bitwise,
case y.ipval when 255 then 8
when 254 then 7
when 252 then 6
when 248 then 5
when 240 then 4
when 224 then 3
when 192 then 2
when 128 then 1
else 0
end as mask_length
from table(split_ip(ip)) x
join table(split_ip(mask)) y
on x.octet = y.octet
), tmp2(s) as (
select replace(replace(
xmlserialize(content xmlagg(xmlelement(name x,
bitwise)
order by octet) as
varchar(40)
),'<X>',''
),'</X>','.'
) from tmp
) select rtrim(substr(tmp2.s,1,length(tmp2.s)-1)) ||
' / ' || rtrim(char((select sum(mask_length) from tmp)))
from tmp2
@