Part three of my walk through new Scalar functions and I will start with a whole new “group” of functions all to do with Bit manipulation. In the past it took assembler or REXX or Language Environment math functions to manipulate bits but now in DB2 10 it is easy! Five functions arrived BITAND, BITANDNOT , BITOR, BITXOR, and BITNOT
See the Scalar functions in DB2 V8: Newsletter 2012-04 SOUNDEX part 1
See the Scalar functions in DB2 V9: Newsletter 2012-05 SOUNDEX part 2
Bit Manipulation in DB2 10
SELECT BITAND( 128 , 1) FROM SYSIBM.SYSDUMMY1 ; 0 SELECT BITAND( 129 , 1) FROM SYSIBM.SYSDUMMY1 ; 1 SELECT BITOR ( 128 , 1) FROM SYSIBM.SYSDUMMY1 ; 129 SELECT BITXOR( 129 , 1) FROM SYSIBM.SYSDUMMY1 ; 128 SELECT BITNOT( 1 ) FROM SYSIBM.SYSDUMMY1 ; -2 SELECT BITNOT( -1 ) FROM SYSIBM.SYSDUMMY1 ; 0
All pretty straightforward stuff at the BIT level – remember how DB2 stores numbers though! That’s why the 1 NOTted goes to -2 and -1 goes to 0. Very handy for testing, setting and resetting bits – Stuff that we all still do (well I do!)
Next up is DECODE which is an apt name because it is a condensed rewrite of CASE which automatically handles the NULL equal case (You could argue that it is a simplified version!) Here is a CASE structure and the equivalent DECODE:
SELECT CASE IBMREQD WHEN 'Y' THEN 'DB2 10' WHEN 'N' THEN 'DB2 9' ELSE 'DB2 V8' END FROM SYSIBM.SYSDUMMY1 ;
SELECT DECODE( IBMREQD, 'Y', 'DB2 10', 'N' , 'DB2 9' , 'DB2 V8') FROM SYSIBM.SYSDUMMY1 ;
Here you can see how it works, first is the column to be tested then pairs of data and finally (optional) the ELSE value. This really comes in handy when any of the columns can be NULL (remember that NULL = NULL is not actually EQUAL – Null is an unknown value and two unknowns are never equivalent) to get around this people would add cumbersome OR and AND logic like in the IBM Docu example
CASE WHEN c1 = var1 OR (c1 IS NULL AND var1 ISNULL) THEN ’a’ WHEN c1 = var2 OR (c1 IS NULL AND var2 ISNULL) THEN ’b’ ELSE NULL END
The values of c1, var1, and var2 can be null values.
This nasty CASE can be simply replaced with this
DECODE(c1, var1, ’a’, var2, ’b’)
This would definitely be a coding win!
NVL arrived but is just a synonym for COALESCE
SELECT NVL((SELECT IBMREQD FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = 'Y') , 'B' , 'C') FROM SYSIBM.SYSDUMMY1 ; Y SELECT NVL((SELECT IBMREQD FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = 'N') , 'B' , 'C') FROM SYSIBM.SYSDUMMY1 ; B
First embedded select gets a row with value Y back second gets NULL of course leading to B being output.
Then we got TIMESTAMP_TZ which is basically an embedded CAST statement around a TIMESTAMP column that looks like this
SELECT CURRENT TIMESTAMP ,TIMESTAMP_TZ(CURRENT TIMESTAMP) ,TIMESTAMP_TZ(CURRENT TIMESTAMP , 8) ,TIMESTAMP_TZ(CURRENT TIMESTAMP , '+02:00') ,TIMESTAMP_TZ(CURRENT TIMESTAMP , '+02:00' , 8) FROM SYSIBM.SYSDUMMY1
|2012-03-30-22.214.171.1244659+00:00||Now with a Timezone field|
|2012-03-30-126.96.36.199465900+00:00||Now with eight digits for seconds and a timezone|
|2012-03-30-188.8.131.524659+02:00||Now with an adjustment Timezone|
|2012-03-30-184.108.40.206465900+02:00||Now lengthened and adjusted|
The documentation does not make it that clear that you can actually have three parameters. The numeric parameter is the accuracy of the seconds (Six is the default) and the string must be a valid Time zone offset in the range -12:59 to +14:00.
Finally for this three month long race through the new Scalar functions is my personal favorite TRIM. Now TRIM simply does what LTRIM and RTRIM have always done but at the same time and is basically the same as STRIP except that the enabling PTF was only closed on the 19. March 2012… So a bit new for most sites!
Feel free to send me your comments and ask questions.