Re: Help Data-Types

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Mon, 19 Aug 2002 11:37:39 +0100
Message-ID: <ajqhv6$28um$1_at_sp15at20.hursley.ibm.com>

I've read six replies to this question, and none were great IMHO. Without getting into a conversation on Actual vs Possible representations; V4 IP addresses just ARE 32-bit numbers (http://www.faqs.org/rfcs/rfc791.html)

Paul got closest, but I'd argue that for 10million V4 IP address 320 Meg (excluding extra index space, summary table space etc) might not be much in storage terms, but its still 4 times as much data for your queries to trawl through. I agree that it is a trade off against future work however.

For a 2TB data warehouse I recently helped build, we went for SQL's 32bit signed INTEGERs, and a couple of SQL User Defined Functions. I guess we could have gone for DISTINCT TYPEs, but some query tools baulk at those.

We used DB2, and here our UDFs

CREATE FUNCTION F.IPV4DSPLY (X INTEGER)

               RETURNS CHAR(15)
       LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
RETURN
(       RTRIM(CHAR( (BIGINT(X) + 2147483648) / 16777216)) 
       || '.' 
       || RTRIM(CHAR(MOD( (BIGINT(X) + 2147483648)/65536, 256)))
       || '.' 
       || RTRIM(CHAR(MOD( (BIGINT(X) + 2147483648)/256 , 256)))
       || '.' 
       || RTRIM(CHAR( MOD( (BIGINT(X) + 2147483648) , 256)))

)
_at_

COMMENT ON FUNCTION "F"."IPV4DSPLY" IS
       'Coverts a signed integer representation of an IP address to the standard character representaion of 4 decimal group [0-255] seperated by decimal points'
_at_

CREATE FUNCTION FUNC.DSPLY2IPV4 (IP VARCHAR(15))

               RETURNS INTEGER
       LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
RETURN
       INTEGER(   BIGINT(SUBSTR(ip,1,3))
         * 16777216 +    BIGINT(SUBSTR(ip,5,3)) * 65536 + 
BIGINT(SUBSTR(ip,9,3))
         * 256 +    BIGINT(SUBSTR(ip,13,3)) - 2147483648)

_at_

COMMENT ON FUNCTION "F"."DSPLY2IPV4" IS
       'Coverts an IP address from the standard character representaion of 4 decimal group [0-255] to a signed integer representation. Only works if leading zeros are kept'
_at_

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Mon Aug 19 2002 - 12:37:39 CEST