Oppolzer - Informatik / Blog

To make the "normal" binary representation comparable via CLC,
DB2 simply flip-flops the sign bit.

That is:

decimal value normal representation in storage DB2 representation
-----------------------------------------------------------------------
     -2       fffe                             7ffe
     -1       ffff                             7fff
      0       0000                             8000
      1       0001                             8001
      2       0002                             8002

etc. etc.

this way, the binary sorting of the DB2 representation yields the same
result as the numeric value order.

With decimal values, DB2 does a similar trick; the sign halfbyte is
moved to the left of the decimal representation:

decimal value normal representation in storage DB2 representation
-----------------------------------------------------------------------
+123.50       000012350c                       f000012350

I don't know exactly, how negative decimal values are represented,
but the sign halfbyte must be lower than f (maybe zero), and the
numeric part must be inverted somehow (maybe 9s complement or
binary complement). But I'm not sure about that.

As an application developer, you don't see anything of these
conversions, because DB2 does it under the covers and moves the
result to your host variables in the normal representation, of course.
So the internal representation of DB2 is of minor interest.

Kind regards

Bernd




Am 11.07.2012 20:27, schrieb C.M.:
> I wondered about that. The values that were being discussed here looked backwards
> (signed versus unsigned) to me. Thanks for the clarification.
>
> C.
>
>
>
> -----Original Message-----
> From: C.B.
> Sent: Wednesday, July 11, 2012 10:55 AM
> To: db2-l@lists.idug.org
> Subject: [DB2-L] - RE: Binary number internal representation
>
> If you think integers are bad, try floating point numbers, but there is a reason they aren't what you expect.
>
> DB2 converts the values to a form that can be compared using a CLC type instruction.
> That way multiple columns of data can be combined for an index and tested
> using a minimum of instructions. When an index is built that has a column
> with a descending sequence, the integer is converted and then one's complimented.
>
> Value DB2 Value Descending
>    -2 7FFE      8001
>    -1 7FFF      8000
>     0 8000      7FFF
>     1 8001      7FFE
>     2 8002      7FFD
>     3 8003      7FFC
>
> It looks confusing, but once you do it enough, it isn't so bad.
>
> C.B.
>
> -----Original Message-----
> From: D.L.
> Sent: Wednesday, July 11, 2012 10:01 AM
> To: db2-l@lists.idug.org
> Subject: [DB2-L] - Binary number internal representation
>
> Gosh I feel a bit silly asking this but I'm looking at column values and want to
> make sure I've got the representation of my binary numbers correct. Let's use
> small integer as an example. I have a smallint on DB2 for z/OS, which I believe
> is a signed big-endian two's compliment binary number. However, the sign is
> inverted for both positive and negative numbers.
>
> For example my SMALLINT of external value 10 is internally represented as x'800A'
> and my SMALLINT of external value of -3 is internally represented as x'7FFD'
> and -10 is x'7FF6'?
>
> -----End Original Message-----
>
>