Posted by: Serge Rielau

In my job I deal daily with colleagues, customers and partners around the world and even by family is spread out across three continents.

Let's get started:

1. Ensure javac the java compiler is on your path.
   It's normally on sqllib\java\jdk\bin
   
   
2. Create a java file named TIMEZONEUDF.java

   import java.lang.*;              // for String class
   import COM.ibm.db2.app.*;        // UDF and associated classes
   import java.sql.*;
   import java.math.*; 
   import java.io.*;
   import java.util.TimeZone;
   
   public class TIMEZONEUDF extends UDF
   {
     public static int tzOffset(String timeZoneID, Timestamp tstamp)
     throws Exception
     {
       return TimeZone.getTimeZone(timeZoneID).getOffset(tstamp.getTime());
     }
     
     public void listtz(String timezone)
     throws Exception
     {
       String[] ids;
       int id = 0;
      
       byte[] scratchpad = getScratchpad();
   
       // variables to read from SCRATCHPAD area
       ByteArrayInputStream
       byteArrayIn = new ByteArrayInputStream(scratchpad);
       DataInputStream
       dataIn = new DataInputStream(byteArrayIn);
   
       // variables to write into SCRATCHPAD area
       byte[] byteArrayRow;
       int i;
       ByteArrayOutputStream
       byteArrayOut = new ByteArrayOutputStream(10);
       DataOutputStream
       dataOut = new DataOutputStream(byteArrayOut);
   
       switch (getCallType())
       {
         case SQLUDF_TF_FIRST:
           // do initialization for the whole statement
           // (the statement may invoke litstz more than once)
           break;
         case SQLUDF_TF_OPEN:
           // do initialization valid for this invokation of thd function
           id = 0;
           // save data in SCRATCHPAD area
           dataOut.writeInt(id);
           byteArrayRow = byteArrayOut.toByteArray();
           for(i = 0; i < byteArrayRow.length; i++)
           {
             scratchpad[i] = byteArrayRow[i];
           }
           setScratchpad(scratchpad);
           break;
   
         case SQLUDF_TF_FETCH:
           // get data from SCRATCHPAD area
           id = dataIn.readInt();
           ids = TimeZone.getAvailableIDs();
           // work with data
           if(id >= ids.length)
           {
             // Set end-of-file signal and return
             setSQLstate ("02000");
           }
           else
           {
             // Set the current output row and increment the row number
             set(1, ids[id]);
             id++;
           }
   
           // save data in SCRATCHPAD area
           dataOut.writeInt(id);
           byteArrayRow = byteArrayOut.toByteArray();
           for(i = 0; i < byteArrayRow.length; i++)
           {
             scratchpad[i] = byteArrayRow[i];
           }
           setScratchpad(scratchpad);
           break;
   
         case SQLUDF_TF_CLOSE:
           break;
   
         case SQLUDF_TF_FINAL:
           break;
       }
     } // tableUDF
   }

3. Compile the program from your shell

   javac TIMEZONEUDF.java

4. Produce a jar file

   jar cvf TIMEZONEUDF.jar TIMEZONEUDF.class

5. Now, from the same directory we start the DB2 shell CLP

   db2 -t

6. Connect to the database

   Connect to <dbname>;

7. Register the jar file with the database

   call sqlj.install_jar('file:.\TIMEZONEUDF.jar', 'TIMEZONEUDFJAR');

   You may want to move the JAR file to a safe place and adjust the path above accordingly.
   In a multi-member environment, make sure the file is accessible from all members.
   
   
8. Create the functions:

   CREATE OR REPLACE FUNCTION 

   TZ_OFFSET_MSEC

   (zone VARCHAR(64),
                                             stamp TIMESTAMP DEFAULT (CURRENT TIMESTAMP))
     RETURNS INTEGER
     SPECIFIC TZ_OFFSET_MSEC
     LANGUAGE JAVA RETURNS NULL ON NULL INPUT
     PARAMETER STYLE JAVA
     DETERMINISTIC NO EXTERNAL ACTION NO SQL
     EXTERNAL NAME 'TIMEZONEUDFJAR:TIMEZONEUDF.tzOffset' ;
   
   CREATE OR REPLACE FUNCTION 

   FROM_TZ

   (fromz VARCHAR(64), 
                                      toz   VARCHAR(64),
                                      stamp TIMESTAMP DEFAULT(CURRENT TIMESTAMP))
     RETURNS TIMESTAMP
     SPECIFIC FROM_TZ
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     RETURN stamp +((tz_offset_msec(toz, stamp)
                     - tz_offset_msec(fromz, stamp)) / 1000) SECONDS;
   
   CREATE OR REPLACE FUNCTION 

   TZ_OFFSET

   (zone  VARCHAR(64),
                                        stamp TIMESTAMP DEFAULT (CURRENT TIMESTAMP))
     RETURNS VARCHAR(6)
     SPECIFIC TZ_OFFSET
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     RETURN TO_CHAR(TZ_OFFSET_MSEC(zone, stamp) / 1000 / 60 / 60, 'S00')
         || ':' || LTRIM(TO_CHAR(MOD(TZ_OFFSET_MSEC(zone, stamp) / 1000 / 60, 60),'00'));
   
   CREATE OR REPLACE FUNCTION 

   TIMEZONE_NAMES

   () RETURNS TABLE(TZNAME VARCHAR(64))
     SPECIFIC TIMEZONE_NAMES
     LANGUAGE JAVA
     PARAMETER STYLE DB2GENERAL NO FINAL CALL
     DETERMINISTIC NO EXTERNAL ACTION NO SQL
     DISALLOW PARALLEL SCRATCHPAD 100
     EXTERNAL NAME 'TIMEZONEUDFJAR:TIMEZONEUDF.listtz';

We can now test the functions

VALUES TZ_OFFSET_MSEC('America/New_York');
1
-----------
  -18000000
 
  1 record(s) selected.

This is the current (wall clock time) offset of the New York timezone to UTC. So new York is 18,000,000 milliseconds behind UTC.
In June however new York was in Daylight Savings Time (DST):

VALUES TZ_OFFSET_MSEC('America/New_York', '2011-06-01');
1
-----------
  -14400000
 
  1 record(s) selected.

VALUES TZ_OFFSET('America/New_York');
1
------
-05:00

  1 record(s) selected.

VALUES TZ_OFFSET('America/St_Johns', '2011-06-01');
1
------
-03:30

  1 record(s) selected.

VALUES FROM_TZ('America/New_York', 'Europe/Berlin');
1
--------------------------
2012-01-07-23.45.32.859000

  1 record(s) selected.

It's just before midnight in Germany. They are 6 hours ahead.
#Db2