Hi Guys,

DB2 LUW V9.5 Fp5.

There is another issue that needs a high performance solution.

I have a class A, B or C range. For example, 10.*, 10.1.* or 10.1.3.*

I want to determine whether an IP address (modeled as an Integer in
the DB) belongs to a given Class (which is a string).
This function should return True/False.

One possible solution is to create a function that compares each of
the octets available on the Class Range, and match them with the IP
address octets.

Is there a better approach for this problem? Performance is crucial
and I need to avoid heavy UDFs.

Thanks,

Bruno.

On 2010-09-28 17:43, brunoalsantos wrote:
I'm not sure I understand, but if you can spare some space and memory,
the number of ranges is roughly 17 miljons. A table like:

create table ip_ranges (
ip_mask varchar(12) not null,
start_ip bigint not null,
end_ip bigint not null
);

create unique index ... on ip_ranges (ip_mask) include (start_ip, end_ip);

allows you to answer your question like:

select 'YES' from ip_ranges
where ip_mask = ?
and ? between start_ip and end_ip;

no rows would mean 'NO'


If you have a bufferpool that can hold the data/index, the lookup should
be reasonable fast.


Just some random thoughts

/Lennart