2012-06 SOUNDEX and other “cool” features – part three for DB2 10

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                                    
;

DB2 10

SELECT                                                   
  DECODE( IBMREQD, 'Y', 'DB2 10', 'N' , 'DB2 9' , 'DB2 V8')
FROM SYSIBM.SYSDUMMY1                                    
;

DB2 10

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

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.

TTFN,
Roy Boxwell
Senior Architect