Bit arithmetic

Posted by: Serge Rielau

Late during DB2 9.5 for LUW development a small set of functions was added without much fanfare which I always feel has been under appreciated.
So this post is dedicated to explain it. I am speaking here of DB2's bit manipulation functions.

How many columns do you have in tables whose values can only be 'Y' and 'N' or 1 and 0?
A lot, a few, none? If it's only a few or none, then you probably do not care too much, but if you have a lot then bit functions may just be for you.

In a nutshell DB2 9.5 introduced the ability to to interpret whole numbers in the binary system and perform various operations on them.
That is a a SMALLINT type can hold the binary number 0b0000000000000000 (0x0000) to 0b1111111111111111 (0xFFFF).
For example 1024 in binary is 0b0000010000000000 (0x0400).
Note that this number is independent of endianness.
For example:
VALUES HEX(SMALLINT(1024)) on my Laptop returns:

db2 => VALUES HEX(SMALLINT(1024));
 
1
----
0004
 
  1 record(s) selected.

because Intel uses big-endian and the HEX function dumps the internal representation on SMALLINT and NOT the number in hex-format.
 
Also note that in order to access the highest bit you must use negative numbers since two-s complement is being used.
In general I recommend against using the highest bit to avoid confusion.
 
But enough said. Let be introduce the individual function which DB2 provides built-in as well as a set of function I have written to complement the library. You can find the source code for those at the  end of this post.

• BITAND(<arg1>, <arg2>)
  Performs a bitwise "AND" operation identical to a c-style & operator. The arguments can be SMALLINT (16 bit), INTEGER (32 bit), BIGINT (64) or DECFLOAT(34)  (113 bit). The result type is the same as the biggest input type.
  Note that if if DECFLOAT is used the number must be a whole number between -2^112 and (2^112)  -1.
  These rule apply to all the other built in functions as well
• BITOR(<arg1>, <arg2>)
  Performs a bitwise "OR" operation identical to a c-style | operator.
• BITNOT(<arg>)
  Inverts all bits identical to a c-style ~ operator.
• BITANDNOT(<arg1>, <arg2>)
  Unsets all the set bits in <arg2> in <arg1>. It is the equivalent of BITAND(<arg1>, BITNOT(<arg2>))
• BITXOR(<arg1>, <arg2>)
  Sets the result bit to 1 if both bits in the arguments are identical. This is identical to the c-style ^ operator.

In addition to to these built-in functions I have defined the following functions at the end for usability:

• BITSET(<arg>, <pos>)
  Sets the bit in <arg> at position <pos>.
  The position must be a value between
  1 and 16 for SMALLINT,
  1 and 32 for INTEGER,
  1 and 64 for BIGINT
  1 and 113 for DECFLOAT(34)
  1 stands for the lowest bit (0b1).
  The result type matches the type of <arg> and is either SMALLINT, INTEGER, BIGINT, or DECFLOAT(34)
• BITCLEAR(<arg>, <pos>)
  Unsets the bit in <arg> at position <pos>.
• BITTEST(<arg>, <pos>)
  Returns the bit in <arg> at position <pos>.
  That is the result is either 0 or 1 (or NULL if any of the arguments input is NULL)
• DECFLOAT2BIN(<arg>)
BIGINT2BIN(<arg>)
INT2BIN(<arg>
SMALLINT2BIN(<arg>)
  Returns the binary representation of the <arg>. If the input is SMALL INT the result is a VARCHAR(16), for INTEGER VARCHAR(32) and so on. Leading 0s are not shown with the exception of the 0 value (0b0).
• DECFLOAT2HEX(<arg>)
BIGINT2HEX(<arg>)
INT2HEX(<arg>)
SMALLINT2HEX(<arg>)
  Returns the hexadecimal representation of the <arg>. If the input is SMALLINT the result is a VARCHAR(4), for INTEGER VARCHAR(8) and so on. Leading 0s are not show with the exception of the 0 values (0x0) .
• BIN2DECFLOAT(<arg>)
BIN2BIGINT(<arg>)
BIN2INT(<arg>)
BIN2SMALLINT(<arg>)
  Turns a binary string into a number. That is '111' is turned into 7. The input in VARCHAR(16) for SMALLINT VARCHAR(32) for INT and so on.
  If the highest bit (16, 31, 64, or 113) is set the resulting number will be negative. That is BIN2SMALLINT('1111111111111111') is -1
• HEX2DECFLOAT(<arg>)
HEX2BIGINT(<arg>)
HEX2INT(<arg>)
HEX2SMALLINT(<arg>)
  Turns a hexadecimal string into number. That is 'FF' is turned into 255. The input is a VARCHAR(4) for SMALLINT, VARCHAR(8) for INTEGER and so on. If the highest bit is set the  resulting number is negative. That is HEX2INT('FFFFFFFF') is -1

Examples:

1. INT2BIN(BITAND(BIN2INT('1111'), BIN2INT('0101')))
   => '101'
2. INT2BIN(BITOR(BIN2INT(101), BIN2INT('101011')))
   => '101111'
3. SMALLINT2BIN(BITNOT(BIN2SMALLINT('1100101')))
   => 1111111110011010
4. INT2HEX(BITXOR(HEX2INT('FFFF'), HEX2INT('2DE4')))
   => 'D21B'
5. INT2BIN(BITANDNOT(BIN2INT('11011010101'), BIN2INT('010011101')))
   =>11001000000
6. BITTEST(BIN2INT('100010011'), 5)
   => 1
7. INT2BIN(BITSET(BIN2INT('01010101'), 10))
   =>1001010101
8. INT2BIN(BITCLEAR(BIN2INT('01010101'), 3))
   => 1010001

Here is the source code for the entire contraption:

--Execute with "db2 -tvf <thisfile>"
CREATE OR REPLACE FUNCTION BITSET(vector DECFLOAT(34), pos INTEGER)
RETURNS DECFLOAT(34)
SPECIFIC BITSET_DECFLOAT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN  CASE WHEN pos BETWEEN 1 AND 112 
             THEN BITOR(vector, POWER(CAST(2 AS DECFLOAT(34)), pos - 1))
             WHEN pos = 113
             THEN BITOR(vector, DECFLOAT('-5192296858534827628530496329220096'))
             WHEN pos IS NULL THEN NULL
             ELSE RAISE_ERROR('78000', 'Bit must be: 1..113 or NULL')
             END;
 
CREATE OR REPLACE FUNCTION BITCLEAR(vector DECFLOAT(34), pos INTEGER)
RETURNS DECFLOAT(34)
SPECIFIC BITCLEAR_DECFLOAT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 112 
            THEN BITANDNOT(vector, POWER(CAST(2 AS DECFLOAT(34)), pos - 1))
            WHEN pos = 113 
            THEN BITANDNOT(vector, DECFLOAT('-5192296858534827628530496329220096'))
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..113 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITTEST(vector DECFLOAT(34), pos INTEGER)
RETURNS INTEGER
SPECIFIC BITTEST_DECFLOAT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos IS NULL 
            THEN NULL 
            WHEN pos BETWEEN 1 AND 112
            THEN CASE WHEN BITAND(vector, POWER(CAST(2 AS DECFLOAT(34)), pos - 1)) != 0
                      THEN 1 ELSE 0 END
            WHEN pos = 113
            THEN CASE WHEN BITAND(vector, DECFLOAT('-5192296858534827628530496329220096')) != 0
                      THEN 1 ELSE 0 END    
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..113 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITSET(vector BIGINT, pos INTEGER)
RETURNS BIGINT
SPECIFIC BITSET_BIGINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 63 
            THEN BITOR(vector, POWER(CAST(2 AS BIGINT), pos - 1))
            WHEN pos = 64 
            THEN BITOR(vector, -9223372036854775808)
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..64 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITCLEAR(vector BIGINT, pos INTEGER)
RETURNS BIGINT
SPECIFIC BITCLEAR_BIGINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 63
            THEN BITANDNOT(vector, POWER(CAST(2 AS BIGINT), pos - 1))
            WHEN pos = 64
            THEN BITANDNOT(vector, -9223372036854775808)
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..64 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITTEST(vector BIGINT, pos INTEGER)
RETURNS INTEGER
SPECIFIC BITTEST_BIGINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos IS NULL 
            THEN NULL 
            WHEN pos BETWEEN 1 AND 63
            THEN CASE WHEN BITAND(vector, POWER(CAST(2 AS BIGINT), pos - 1)) != 0
                      THEN 1 ELSE 0 END
            WHEN pos = 64
            THEN CASE WHEN BITAND(vector, -9223372036854775808) != 0
                      THEN 1 ELSE 0 END    
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..64 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITSET(vector INTEGER, pos INTEGER)
RETURNS INTEGER
SPECIFIC BITSET_INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 31 
            THEN BITOR(vector, POWER(CAST(2 AS INTEGER), pos - 1))
            WHEN pos = 32 
            THEN BITOR(vector, -2147483648)
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..32 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITCLEAR(vector INTEGER, pos INTEGER)
RETURNS INTEGER
SPECIFIC BITCLEAR_INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 31
            THEN BITANDNOT(vector, POWER(CAST(2 AS INTEGER), pos - 1))
            WHEN pos = 32
            THEN BITANDNOT(vector, -2147483648)
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..32 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITTEST(vector INTEGER, pos INTEGER)
RETURNS INTEGER
SPECIFIC BITTEST_INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos IS NULL 
            THEN NULL 
            WHEN pos BETWEEN 1 AND 31
            THEN CASE WHEN BITAND(vector, POWER(CAST(2 AS INTEGER), pos - 1)) != 0
                      THEN 1 ELSE 0 END
            WHEN pos = 32
            THEN CASE WHEN BITAND(vector, -2147483648) != 0
                      THEN 1 ELSE 0 END    
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..32 or NULL')
            END;
             
CREATE OR REPLACE FUNCTION BITSET(vector SMALLINT, pos INTEGER)
RETURNS SMALLINT
SPECIFIC BITSET_SMALLINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 15 
            THEN BITOR(vector, SMALLINT(POWER(CAST(2 AS INTEGER), pos - 1)))
            WHEN pos = 16
            THEN BITOR(vector, -32768)    
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..16 or NULL')
            END;
 
CREATE OR REPLACE FUNCTION BITCLEAR(vector SMALLINT, pos INTEGER)
RETURNS SMALLINT
SPECIFIC BITCLEAR_SMALLINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos BETWEEN 1 AND 15
            THEN BITANDNOT(vector, SMALLINT(POWER(CAST(2 AS INTEGER), pos - 1)))
            WHEN pos = 16
            THEN BITANDNOT(vector, -32768)
            WHEN pos IS NULL THEN NULL
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..16 or NULL')
            END;
            
CREATE OR REPLACE FUNCTION BITTEST(vector SMALLINT, pos INTEGER)
RETURNS INTEGER
SPECIFIC BITTEST_SMALLINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE WHEN pos IS NULL 
            THEN NULL 
            WHEN pos BETWEEN 1 AND 15
            THEN CASE WHEN BITAND(vector, SMALLINT(POWER(CAST(2 AS INTEGER), pos - 1))) != 0
                      THEN 1 ELSE 0 END
            WHEN pos = 16
            THEN CASE WHEN BITAND(vector, -32768) != 0
                      THEN 1 ELSE 0 END    
            ELSE RAISE_ERROR('78000', 'Bit must be: 1..16 or NULL')
            END;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIN2DECFLOAT(str VARCHAR(113))
RETURNS DECFLOAT(34)
SPECIFIC BIN2DECFLOAT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res DECFLOAT(34) DEFAULT 0;
  DECLARE pos INTEGER      DEFAULT 1;
  WHILE pos <= LENGTH(str) DO
    SET res = BITOR(CASE WHEN BITAND(res, DECFLOAT('2596148429267413814265248164610048')) != 0
                         THEN BITOR(2 * BITANDNOT(res, DECFLOAT('2596148429267413814265248164610048')),
                                    DECFLOAT('-5192296858534827628530496329220096'))
                         ELSE 2 * res END,
                     CASE WHEN SUBSTR(str, pos, 1) = '1' THEN 1
                         WHEN SUBSTR(str, pos, 1) = '0' THEN 0
                            ELSE RAISE_ERROR('78000', 'Not a binary string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIN2BIGINT(str VARCHAR(64))
RETURNS BIGINT
SPECIFIC BIN2BIGINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res BIGINT DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  WHILE pos <= LENGTH(str) DO
    SET res = BITOR(CASE WHEN BITAND(res, 4611686018427387904) != 0
                         THEN BITOR(2 * BITANDNOT(res, 4611686018427387904),
                                    -9223372036854775808)
                         ELSE 2 * res END,
                     CASE WHEN SUBSTR(str, pos, 1) = '1' THEN 1
                         WHEN SUBSTR(str, pos, 1) = '0' THEN 0
                            ELSE RAISE_ERROR('78000', 'Not a binary string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIN2INT(str VARCHAR(32))
RETURNS INTEGER
SPECIFIC BIN2INT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  WHILE pos <= LENGTH(str) DO
    SET res = BITOR(CASE WHEN BITAND(res, 1073741824) != 0
                         THEN BITOR(2 * BITANDNOT(res, 1073741824),
                                    -2147483648)
                         ELSE 2 * res END,
                     CASE WHEN SUBSTR(str, pos, 1) = '1' THEN 1
                         WHEN SUBSTR(str, pos, 1) = '0' THEN 0
                            ELSE RAISE_ERROR('78000', 'Not a binary string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIN2SMALLINT(str VARCHAR(16))
RETURNS SMALLINT
SPECIFIC BIN2SMALLINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  WHILE pos <= LENGTH(str) DO
    SET res = BITOR(CASE WHEN BITAND(res, 16384) != 0
                         THEN BITOR(2 * BITANDNOT(res, 16384),
                                    -32768)
                         ELSE 2 * res END,
                     CASE WHEN SUBSTR(str, pos, 1) = '1' THEN 1
                         WHEN SUBSTR(str, pos, 1) = '0' THEN 0
                            ELSE RAISE_ERROR('78000', 'Not a binary string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2DECFLOAT(str VARCHAR(29))
RETURNS DECFLOAT(34)
SPECIFIC HEX2DECFLOAT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res DECFLOAT DEFAULT 0;
  DECLARE pos INTEGER  DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, DECFLOAT('324518553658426726783156020576256')) != 0
                         THEN BITOR(16 * BITANDNOT(res, DECFLOAT('324518553658426726783156020576256')),
                                    DECFLOAT('-5192296858534827628530496329220096'))
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2BIGINT(str VARCHAR(16))
RETURNS BIGINT
SPECIFIC HEX2BIGINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res BIGINT  DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 576460752303423488) != 0
                         THEN BITOR(16 * BITANDNOT(res, 576460752303423488),
                                    -9223372036854775808)
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2INT(str VARCHAR(8))
RETURNS INTEGER
SPECIFIC HEX2INT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER  DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 134217728) != 0
                         THEN BITOR(16 * BITANDNOT(res, 134217728),
                                    -2147483648)
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2SMALLINT(str VARCHAR(4))
RETURNS INTEGER
SPECIFIC HEX2SMALLINT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res SMALLINT DEFAULT 0;
  DECLARE pos INTEGER  DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 2048) != 0
                         THEN BITOR(16 * BITANDNOT(res, 2048),
                                    -32768)
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION DECFLOAT2BIN(vector DECFLOAT(34))
RETURNS VARCHAR(113)
SPECIFIC DECFLOAT2BIN
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res VARCHAR(114) DEFAULT '0';
  DECLARE vec DECFLOAT(34);
  SET vec = VECTOR;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET res = CASE WHEN BITAND(vec, 1) = 1 THEN '1' ELSE '0' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR((BITANDNOT(vec, 
                                        DECFLOAT('-5192296858534827628530496329220096'))
                               - BITAND(vec, 1) ) / 2,
                              DECFLOAT('2596148429267413814265248164610048'))
                   ELSE (vec - BITAND(vec, 1)) / 2 END;
  END WHILE;
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIGINT2BIN(vector BIGINT)
RETURNS VARCHAR(64)
SPECIFIC BIGINT2BIN
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res VARCHAR(65) DEFAULT '0';
  DECLARE vec BIGINT;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET res = CASE WHEN BITAND(vec, 1) = 1 THEN '1' ELSE '0' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -9223372036854775808) / 2,
                               4611686018427387904)
                   ELSE vec / 2 END;
  END WHILE;
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION INT2BIN(vector INTEGER)
RETURNS VARCHAR(32)
SPECIFIC INT2BIN
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res VARCHAR(33) DEFAULT '0';
  DECLARE vec INTEGER;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET res = CASE WHEN BITAND(vec, 1) = 1 THEN '1' ELSE '0' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -2147483648) / 2,
                              1073741824)
                   ELSE vec / 2 END;
  END WHILE;
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION SMALLINT2BIN(vector SMALLINT)
RETURNS VARCHAR(16)
SPECIFIC SMALLINT2BIN
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res VARCHAR(17) DEFAULT '0';
  DECLARE vec SMALLINT;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET res = CASE WHEN BITAND(vec, 1) = 1 THEN '1' ELSE '0' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -32768) / 2,
                              16384)
                   ELSE vec / 2 END;
  END WHILE;
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION DECFLOAT2HEX(vector DECFLOAT(34))
RETURNS VARCHAR(29)
SPECIFIC DECFLOAT2HEX
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res    VARCHAR(30) DEFAULT '0';
  DECLARE vec    DECFLOAT(34);
  DECLARE nibble INTEGER;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET nibble = INTEGER(BITAND(vec, 15));
    SET res = CASE nibble WHEN 0 THEN '0'
                          WHEN 1 THEN '1'
                          WHEN 2 THEN '2'
                          WHEN 3 THEN '3'
                          WHEN 4 THEN '4'
                          WHEN 5 THEN '5'
                          WHEN 6 THEN '6'
                          WHEN 7 THEN '7'
                          WHEN 8 THEN '8'
                          WHEN 9 THEN '9'
                          WHEN 10 THEN 'A'
                          WHEN 11 THEN 'B'
                          WHEN 12 THEN 'C'
                          WHEN 13 THEN 'D'
                          WHEN 14 THEN 'E'
                          ELSE 'F' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR((BITANDNOT(vec, 
                                        DECFLOAT('-5192296858534827628530496329220096'))
                               - nibble) / 16,
                              DECFLOAT('324518553658426726783156020576256'))
                   ELSE BITANDNOT(vec, 15) / 16 END;
  END WHILE;
  
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION BIGINT2HEX(vector BIGINT)
RETURNS VARCHAR(16)
SPECIFIC BIGINT2HEX
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res    VARCHAR(17) DEFAULT '0';
  DECLARE vec    BIGINT;
  DECLARE nibble INTEGER;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET nibble = INTEGER(BITAND(vec, 15));
    SET res = CASE nibble WHEN 0 THEN '0'
                          WHEN 1 THEN '1'
                          WHEN 2 THEN '2'
                          WHEN 3 THEN '3'
                          WHEN 4 THEN '4'
                          WHEN 5 THEN '5'
                          WHEN 6 THEN '6'
                          WHEN 7 THEN '7'
                          WHEN 8 THEN '8'
                          WHEN 9 THEN '9'
                          WHEN 10 THEN 'A'
                          WHEN 11 THEN 'B'
                          WHEN 12 THEN 'C'
                          WHEN 13 THEN 'D'
                          WHEN 14 THEN 'E'
                          ELSE 'F' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -9223372036854775808) / 16,
                              576460752303423488)
                   ELSE vec / 16 END;
  END WHILE;
  
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION INT2HEX(vector INTEGER)
RETURNS VARCHAR(8)
SPECIFIC INT2HEX
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res    VARCHAR(9) DEFAULT '0';
  DECLARE vec    INTEGER;
  DECLARE nibble INTEGER;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET nibble = INTEGER(BITAND(vec, 15));
    SET res = CASE nibble WHEN 0 THEN '0'
                          WHEN 1 THEN '1'
                          WHEN 2 THEN '2'
                          WHEN 3 THEN '3'
                          WHEN 4 THEN '4'
                          WHEN 5 THEN '5'
                          WHEN 6 THEN '6'
                          WHEN 7 THEN '7'
                          WHEN 8 THEN '8'
                          WHEN 9 THEN '9'
                          WHEN 10 THEN 'A'
                          WHEN 11 THEN 'B'
                          WHEN 12 THEN 'C'
                          WHEN 13 THEN 'D'
                          WHEN 14 THEN 'E'
                          ELSE 'F' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -2147483648) / 16,
                              134217728)
                   ELSE vec / 16 END;
  END WHILE;
  
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;
 
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION SMALLINT2HEX(vector SMALLINT)
RETURNS VARCHAR(4)
SPECIFIC SMALLINT2HEX
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res    VARCHAR(5) DEFAULT '0';
  DECLARE vec    SMALLINT;
  DECLARE nibble INTEGER;
  SET vec = vector;
  WHILE (vec < 0 OR vec >= 1) AND vector IS NOT NULL DO
    SET nibble = INTEGER(BITAND(vec, 15));
    SET res = CASE nibble WHEN 0 THEN '0'
                          WHEN 1 THEN '1'
                          WHEN 2 THEN '2'
                          WHEN 3 THEN '3'
                          WHEN 4 THEN '4'
                          WHEN 5 THEN '5'
                          WHEN 6 THEN '6'
                          WHEN 7 THEN '7'
                          WHEN 8 THEN '8'
                          WHEN 9 THEN '9'
                          WHEN 10 THEN 'A'
                          WHEN 11 THEN 'B'
                          WHEN 12 THEN 'C'
                          WHEN 13 THEN 'D'
                          WHEN 14 THEN 'E'
                          ELSE 'F' END || res,
        vec = CASE WHEN vec < 0 
                   THEN BITOR(BITANDNOT(vec, 
                                        -32768) / 16,
                              2048)
                   ELSE vec / 16 END;
  END WHILE;
  
  RETURN CASE WHEN vector IS NULL THEN NULL
              WHEN LENGTH(res) > 1 THEN SUBSTR(res, 1, LENGTH(res) - 1)
              ELSE '0' END;
END
@
--#SET TERMINATOR ;

#Db2