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
VALUES HEX(12345)
I get:
1
--------
39300000
This is the hexa-decimal presentation of a binary 4 byte integer in little-endian architectures.
HEXTOINT('39300000')
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.
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEXTOINT(arg CHAR(8)) RETURNS INTEGER
SPECIFIC HEXTOINT
BEGIN
DECLARE i INT;
DECLARE res DECFLOAT DEFAULT 0;
DECLARE nibble CHAR(1);
SET i = 7;
WHILE i >= 0 DO
SET nibble = SUBSTR(arg, i, 1);
SET res = res * 16
+ CASE WHEN nibble BETWEEN '0' AND '9' THEN ASCII(nibble) - ASCII('0')
WHEN nibble BETWEEN 'A' AND 'F' THEN ASCII(nibble) - ASCII('A') + 10
WHEN nibble BETWEEN 'a' AND 'f' THEN ASCII(nibble) - ASCII('a') + 10
ELSE RAISE_ERROR('78000', 'Not a hex-string') END;
SET i = i + 1;
SET nibble = SUBSTR(arg, i, 1);
SET res = res * 16
+ CASE WHEN nibble BETWEEN '0' AND '9' THEN ASCII(nibble) - ASCII('0')
WHEN nibble BETWEEN 'A' AND 'F' THEN ASCII(nibble) - ASCII('A') + 10
WHEN nibble BETWEEN 'a' AND 'f' THEN ASCII(nibble) - ASCII('a') + 10
ELSE RAISE_ERROR('78000', 'Not a hex-string') END;
SET i = i -3;
END WHILE;
-- Exceeding MAXINT? Then flip to negative
IF res > 2147483647 THEN
SET res = res - 2147483648 * 2;
END IF;
RETURN res;
END
@
--#SET TERMINATOR ;
VALUES HEXTOINT(HEX(12345));
1
-----------
12345
Do we handle negative values?
VALUES (HEX(-1), HEXTOINT(HEX(-1)));
1 2
-------- -----------
FFFFFFFF -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
....
#endif
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 #define.
SQL_CCFLAGS can be set:
- For the current session:
SET CURRENT SQL_CCFLAGS = 'LITTLE_ENDIAN:TRUE'
- For the entire database
UPDATE DB CFG USING SQL_CCFLAGS 'LITTLE_ENDIAN:TRUE'
The variables can be of type BOOLEAN, INTEGER or VARCHAR and are types implicitly. In this case LITTLE_ENDIAN is a BOOLEAN and we have set it to TRUE.
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
- FUNCTION,
- TRIGGER or
- PROCEDURE keyword
whether it is a section within a SQL statement or a set of SQL statements.
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:
UPDATE DB CFG USING SQL_CCFLAGS "LITTLE_ENDIAN:TRUE";
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEXTOINT(arg CHAR(8)) RETURNS INTEGER
SPECIFIC HEXTOINT
BEGIN
DECLARE i INT;
DECLARE res DECFLOAT DEFAULT 0;
DECLARE nibble CHAR(1);
SET i = _IF __LITTLE_ENDIAN _THEN 7 _ELSE 1 _END ;
WHILE i BETWEEN 0 AND 8 DO
SET nibble = SUBSTR(arg, i, 1);
SET res = res * 16
+ CASE WHEN nibble BETWEEN '0' AND '9' THEN ASCII(nibble) - ASCII('0')
WHEN nibble BETWEEN 'A' AND 'F' THEN ASCII(nibble) - ASCII('A') + 10
WHEN nibble BETWEEN 'a' AND 'f' THEN ASCII(nibble) - ASCII('a') + 10
ELSE RAISE_ERROR('78000', 'Not a hex-string') END;
SET i = i + 1;
SET nibble = SUBSTR(arg, i, 1);
SET res = res * 16
+ CASE WHEN nibble BETWEEN '0' AND '9' THEN ASCII(nibble) - ASCII('0')
WHEN nibble BETWEEN 'A' AND 'F' THEN ASCII(nibble) - ASCII('A') + 10
WHEN nibble BETWEEN 'a' AND 'f' THEN ASCII(nibble) - ASCII('a') + 10
ELSE RAISE_ERROR('78000', 'Not a hex-string') END;
SET i = i _IF __LITTLE_ENDIAN _THEN -3 _ELSE +1 _END ;
END WHILE;
-- Exceeding MAXINT? Then flip to negative
IF res > 2147483647 THEN
SET res = res - 2147483648 * 2;
END IF;
RETURN res;
END
@
--#SET TERMINATOR ;
A quick test:
VALUES HEX(-35645);
VALUES HEXTOINT(HEX(-35645));
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:
- Not set any SQL_CCFLAGS
In that case the variable is undefined and the _IF will resolve to UNKNOWN
- Set the SQL_CCFLAGS to 'LITTLE_ENDIAN:FALSE'
You can find more details on
SQL_CCFLAGS and conditional compilation in the
Information Center