Oninit Logo
The Down System Specialists
+1-913-674-0360
+44-2081-337529
Partnerships Contact

Bitmask Manipulation

This article is based on a posting to comp.databases.informix by Colin Bull

If you are unfortunate enough to be still on a very old engine, then the standard bitval functions provided by Informix are very slow, and there aren't ways to manipulate bitmasks from SQL or SPL. The procedures below are written in SPL and use normal math operations to check for a specific bit.

The Code

[bit_routines.c]
#include <mi.h>
mi_boolean bit_on(mi_integer item, mi_integer mask) {
  return ((mask & item) == mask) ? '\1' : '\0';
}
mi_boolean bit_pos_on(mi_integer item, mi_integer bitpos) {
  long mask = 1 << (bitpos-1);
  return ((mask & item) == mask) ? '\1' : '\0';
}
mi_integer bit_or(mi_integer left, mi_integer right) {
  return left | right;
} 
mi_integer bit_and(mi_integer left, mi_integer right) {
  return left & right;
}
mi_integer bit_xor(mi_integer left, mi_integer right) {
  return left ^ right; 
}
mi_integer bit_not(mi_integer value) {
  return ~value; 
}
mi_integer bit_shiftl(mi_integer left, mi_integer right) {
  return left << right;
}
mi_integer bit_shiftr(mi_integer left, mi_integer right) {
  return left >> right;
}

To compile it use this Makefile, this is on Sun Solaris using WorkShop Pro.


[Makefile]
CC     = cc
LD     = ld
LFLAGS = -G
CFLAGS = -xO2 -xCC -DMI_SERVBUILD
INC    = -I$(INFORMIXDIR)/incl/public

LIBRARY  = bit_routines.so
OBJECTS  = bit_routines.o
DATABASE = saproto
REGISTER = ./sp_bit_routines.sh

all: register 
 
.SUFFIXES: .c .o .so

.c.o:
        $(CC) -c $(CFLAGS) $(INC) -o $@ $<

$(LIBRARY): $(OBJECTS)
        $(LD) $(LFLAGS) -o $@ $(OBJECTS) 
        -@nm $(LIBRARY) | grep FUNC

register: $(LIBRARY)
        -make drop_funcs        
        $(REGISTER) | dbaccess $(DATABASE)
        
clean:
        -rm -rf core *.o *.so
        -make drop_funcs        

drop_funcs:
        -echo "drop function bit_on"     | dbaccess $(DATABASE)
        -echo "drop function bit_pos_on" | dbaccess $(DATABASE)
        -echo "drop function bit_or"     | dbaccess $(DATABASE)
        -echo "drop function bit_and"    | dbaccess $(DATABASE)
        -echo "drop function bit_xor"    | dbaccess $(DATABASE)
        -echo "drop function bit_shiftl" | dbaccess $(DATABASE)
        -echo "drop function bit_shiftr" | dbaccess $(DATABASE)
        -echo "drop function bit_not"    | dbaccess $(DATABASE)


$ cc -c -xO2 -xCC -DMI_SERVBUILD
-I/informix/distr/infx.9.21.UC3/incl/public -o bit_routines.o
bit_routines.c
$ ld -G -o bit_routines.so bit_routines.o

This script will do the registering of functions

#!/bin/sh
so_file=`pwd`/bit_routines.so
{ echo "CREATE FUNCTION bit_on(INTEGER, INTEGER) "
  echo "  RETURNS BOOLEAN "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_pos_on(INTEGER, INTEGER) "
  echo "  RETURNS BOOLEAN "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_or(INTEGER, INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_and(INTEGER, INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_xor(INTEGER, INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_not(INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_shiftl(INTEGER, INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
  echo "CREATE FUNCTION bit_shiftr(INTEGER, INTEGER) "
  echo "  RETURNS INTEGER "
  echo "  WITH (NOT VARIANT) "
  echo "  EXTERNAL NAME "
  echo "    '${so_file}'"
  echo "  LANGUAGE C;" 
  echo
}

Performance Test

The table had 40000 rows and tablemaskid is not indexed


# Check C function speed
  select count(*) from checks
  where bit_on(tablemaskid, 16384)
Count: 65
Time: 7.83151197433472 seconds

# Check C function speed (2nd arg is a bit position)
  select count(*) from checks
  where bit_pos_on(tablemaskid, 15)
Count: 65
Time: 7.87693309783936 seconds

# Check speed of SYSMASTER:bitval (installed locally)
#  (you can get the SPL from dbaccess and load it into your own db)
  select count(*) from checks
  where bitval(tablemaskid, 16384) = 1
Count: 65
Time: 12.5782849788666 seconds

# Check the speed of SYSMASTER:bitval
  select count(*) from checks
  where SYSMASTER:bitval(tablemaskid, 16384) = 1
Count: 65
Time: 19.85014295578 seconds

# Check speed of C (various of bit_on)
  select count(*) from checks
  where bit_and(tablemaskid, 16384) = 16384
Count: 65
Time: 7.84405207633972 seconds

To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.