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.
[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 }
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.