Why no unsigned integers in DB2?

John,

the SQL Standard has no concept of unsigned integer.

You coudl constrain teh values to unsigned, but I take it you want the
extra range.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

On Sun, 01 Feb 2004 18:05:14 GMT, John Rigler
<john.rigler@verizon.net> wrote:
[color=blue]
>It seems like I should be able to define an unsigned integer
>type for a DB2 field, but I don't see that option. What is
>the cleanest way to do this; I could represent my number as
> a signed integer, but would have to flip the last bit. Also,
>I could move up to using a bigint, but that seems like a
>waste of space because my numbers will never become that large.
>
>Am I looking at this the wrong way?[/color]

What about the DECIMAL data type?


--
Bob Hairgrove
Join Bytes!

Serge Rielau wrote:[color=blue]
> John,
>
> the SQL Standard has no concept of unsigned integer.
>
> You coudl constrain teh values to unsigned, but I take it you want the
> extra range.
>
> Cheers
> Serge
>[/color]
Yep, I am just trying to make the most efficient use of my space. For
example, a TCPIP address represented as a number would perfectly fit
into an unsigned int. As it stands now, all addresses past
127.255.255.255 must be mapped in negative numbers. The convention
appears to be to just do a bitflip on 128.0.0.0 and above. Actually, if
the data is then viewed with the db2 hex function, it still appears
correct. Of course a more straight-forward approach would be to use
bigint, but at a cost of double my field length.

Bob Hairgrove wrote:

I will look at it, but I don't need decimals, so unless I could choose
to use all my bits as real positive numbers, it wouldn't help me out.
[color=blue]
> On Sun, 01 Feb 2004 18:05:14 GMT, John Rigler
> <john.rigler@verizon.net> wrote:
>
>[color=green]
>>It seems like I should be able to define an unsigned integer
>>type for a DB2 field, but I don't see that option. What is
>>the cleanest way to do this; I could represent my number as
>> a signed integer, but would have to flip the last bit. Also,
>>I could move up to using a bigint, but that seems like a
>>waste of space because my numbers will never become that large.
>>
>>Am I looking at this the wrong way?[/color]
>
>
> What about the DECIMAL data type?
>
>
> --
> Bob Hairgrove
> Join Bytes![/color]

You don't have to use scale on a decimal...just declare a decimal large
enough to hold your max value...you can use a decimal(10,0)...that'll
take up 5 bytes per row instead of 8 for a bigint.

John Rigler wrote:
[color=blue]
> Bob Hairgrove wrote:
>
> I will look at it, but I don't need decimals, so unless I could choose
> to use all my bits as real positive numbers, it wouldn't help me out.
>[color=green]
>> On Sun, 01 Feb 2004 18:05:14 GMT, John Rigler
>> <john.rigler@verizon.net> wrote:
>>
>>[color=darkred]
>>> It seems like I should be able to define an unsigned integer
>>> type for a DB2 field, but I don't see that option. What is
>>> the cleanest way to do this; I could represent my number as
>>> a signed integer, but would have to flip the last bit. Also,
>>> I could move up to using a bigint, but that seems like a
>>> waste of space because my numbers will never become that large.
>>>
>>> Am I looking at this the wrong way?[/color]
>>
>>
>>
>> What about the DECIMAL data type?
>>
>>
>> --
>> Bob Hairgrove
>> Join Bytes![/color]
>
>[/color]

In terms of address space, IPv4 is about 4.3 x 10^9, being 32 bits.
On the other hand, Ipv6 becomes the astronomical figure of 3.4 x 10^38
because its length is 128 bits.

PM

"John Rigler" <john.rigler@verizon.net> a écrit dans le message de
news:eG7Ub.3433$EH5.9@nwrddc01.gnilink.net...[color=blue]
> Serge Rielau wrote:[color=green]
> > John,
> >
> > the SQL Standard has no concept of unsigned integer.
> >
> > You coudl constrain teh values to unsigned, but I take it you want the
> > extra range.
> >
> > Cheers
> > Serge
> >[/color]
> Yep, I am just trying to make the most efficient use of my space. For
> example, a TCPIP address represented as a number would perfectly fit
> into an unsigned int. As it stands now, all addresses past
> 127.255.255.255 must be mapped in negative numbers. The convention
> appears to be to just do a bitflip on 128.0.0.0 and above. Actually, if
> the data is then viewed with the db2 hex function, it still appears
> correct. Of course a more straight-forward approach would be to use
> bigint, but at a cost of double my field length.
>[/color]

Sean McKeough wrote:
You guys may be onto something, clearly I need to investigate this.[color=blue]
> You don't have to use scale on a decimal...just declare a decimal large
> enough to hold your max value...you can use a decimal(10,0)...that'll
> take up 5 bytes per row instead of 8 for a bigint.
>
> John Rigler wrote:
>[color=green]
>> Bob Hairgrove wrote:
>>
>> I will look at it, but I don't need decimals, so unless I could choose
>> to use all my bits as real positive numbers, it wouldn't help me out.
>>[color=darkred]
>>> On Sun, 01 Feb 2004 18:05:14 GMT, John Rigler
>>> <john.rigler@verizon.net> wrote:
>>>
>>>
>>>> It seems like I should be able to define an unsigned integer
>>>> type for a DB2 field, but I don't see that option. What is
>>>> the cleanest way to do this; I could represent my number as
>>>> a signed integer, but would have to flip the last bit. Also,
>>>> I could move up to using a bigint, but that seems like a
>>>> waste of space because my numbers will never become that large.
>>>>
>>>> Am I looking at this the wrong way?
>>>
>>>
>>>
>>>
>>> What about the DECIMAL data type?
>>>
>>>
>>> --
>>> Bob Hairgrove
>>> Join Bytes![/color]
>>
>>
>>[/color][/color]

> > You don't have to use scale on a decimal...just declare a decimal large[color=blue][color=green]
> > enough to hold your max value...you can use a decimal(10,0)...that'll
> > take up 5 bytes per row instead of 8 for a bigint.
> >[/color][/color]
I believe that it takes up 6 bytes. The last half byte is for the sign, so
that decimal(10,0) and decimal(11,0) take the same space.

But I am really wondering how significant this is (versus bigint), and
whether the whole thing is much ado about nothing (or very little).

"John Rigler" <john.rigler@verizon.net> wrote in message
news:eG7Ub.3433$EH5.9@nwrddc01.gnilink.net...
[snip][color=blue]
> Yep, I am just trying to make the most efficient use of my space. For
> example, a TCPIP address represented as a number would perfectly fit
> into an unsigned int. As it stands now, all addresses past
> 127.255.255.255 must be mapped in negative numbers. The convention
> appears to be to just do a bitflip on 128.0.0.0 and above. Actually, if
> the data is then viewed with the db2 hex function, it still appears
> correct. Of course a more straight-forward approach would be to use
> bigint, but at a cost of double my field length.[/color]

I store IP addresses in DB2's signed 32-bit INTEGERs and just minus or add
2147483648 (=2^31) when working with them. E.g.

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)))
)
@
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'
@
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)
@
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'
@

Regards
Paul Vernon
Business Intelligence, IBM Global Services