How do you like your eggs? Conditional Compilation in DB2
Just recently in DB2 9.7.5 we introduced the HEXTORAW function.
This function converts a hex-string into a VARCHAR FOR BIT DATA and is thus the inverse of the HEX function.
But is it really? Not quite. the HEX function dumps the internal representation of all sorts of types, not just strings.
So the question arises: How hard would it be to add other conversion functions which re-constitute values from their hex-dump?
Perhaps I'll write a little library and publish it here in the near future, but for now let's look at one such function only: HEXTOINT
On my laptop when I run
I get:VALUES HEX(12345)
This is the hexa-decimal presentation of a binary 4 byte integer in little-endian architectures.
Should return: 12345
Here is what I came up with. Note that for high performance I would likely choose an UNFENCED C-UDF.
But SQL PL is so much more portable.
Do we handle negative values?--#SET TERMINATOR @ CREATE OR REPLACE FUNCTION HEXTOINT(arg CHAR(8)) RETURNS INTEGER SPECIFIC HEXTOINT BEGIN DECLARE i INT;
VALUES (HEX(-1), HEXTOINT(HEX(-1)));
Done. And thanks to the portable SQL PL this will work everywhere on DB2 for LUW - or not!
Recall that my laptop is using little-endian.
So now I have to write another SQL PL function doing the same work, but this time in big-endian to support e.g AIX on pSeries.
Then, if I want to publish my code I need to tell customers which DDL to run for which function. This is messy.
DB2 itself runs on all these architectures with minimal code-changes.
The way we do this is development is to sue pre-compile options which split the source-code just where it is needed and inject fragments that are endian or OS aware.
So you would see code like:
#define LITTLE_ENDIAN 1 #ifdef LITTLE_ENDIAN
That way only one sport in the DB2 code needs to decide the endianness and all the other code will compile properly.
In DB2 9.7 FP1 we introduced that same concept into SQL PL
Instead of #define we can either use global variables or a new register names SQL_CCFLAGS.think of SQL_CCFLAGS as the "-D" option in your C-compiler
and global variables as the #def
We can now add conditional compilation directives into any compiled (not inlined!) SQL PL or PL/SQL code with the exception of anonymous blocks.
Conditional compilation precedes regular parsing, so you can substitute any fragment of code after the
Global variables are referenced as normal, while SQL_CCFLAGS are referenced with a double under-bar: __LITTLE_ENDIAN
The condition itself is preceded with a single under-bar: _IF _ELSEIF _THEN _END
For compatibility with Oracle $IF, etc. and $$LITTLE_ENDIAN is also supported in ORA mode.
Equipped with this knowledge we can now write an endian-tolerant HEXTOINT function:
A quick test:UPDATE DB CFG USING SQL_CCFLAGS "LIT
I leave it to you, the reader, to test verify the code works on a power System as well.
All you must do is either: