Need help with DB2 SQL for table manipulation and time conversions

Greetings,

I am hoping you all can help me with a question. I have a 2 part process I am trying to write to no avail. DB2 7.2 FP10a, SQL

1 - I am trying to modify the timestamp located in a group of vendor-created data warehousing tables. The vendor assumed that all data woudl be from the same timezone, which it isn't. I need to convert it to the local time zone from where the data originated.

2 – Convert the newly calculated local timestamp to an epoch time so that I can use it in intr_slope and intr_intercept calculations.

Here are the tables:

Fact Table - about 20 almost identical fact tables exist (below is an example of one table)
F_CPU_HOUR – has multiple columns, most are foreign keys. 2 columns of interest here are:
Host_ID – foreign key to D_HOST_ID_LOOKUP table below
MEAS_HOUR – timestamp in GMT

Dimension Table – only 1 is important for this
D_HOST_ID_LOOKUP
HOST_ID – primary key
TMZON_GMT_OFFSET (values like -50000)

This is the process I am trying to build

1 time:
- alter all 20 Fact tables add column “Meas_Hour_Adj”
- alter all 20 Fact tables add column “Meas_Hour_Adj _Epoch Time”

SQL to execute nightly:
for each row in the F tables where Meas_Hour_Adj and Meas_Hour_Adj _Epoch Time are null,
- take the existing Meas_Hour value
- add to it the TMZON_GMT_OFFSET value where F_CPU_HOUR.HOST_ID=D_HOST_ID_LOOKUP.HOST_ID
- insert new value into the new “Meas_Hour_Adj” column
- convert the Meas_Hour_Adj column to an epoch time value and insert it into the Meas_Hour_Adj _Epoch Time column.

I am guessing the last part needs to be done with the timestampsiff function. I found this piece of code which accoutns for that commands weaknesses, but I can not get it to work:


WITH TEMP1 (TS1,TS2) AS
(VALUES ('1995-03-01-00.12.34.000','1995-03-01-00.00.00.000')
,('1995-03-01-00.12.00.034','1995-03-01-00.00.00.000'))
SELECT MS1
,MS2
,MS1 - MS2 AS DIFF
FROM (SELECT BIGINT(DAYS(TS1) * 86400000000
+ MIDNIGHT_SECONDS(TS1) * 1000000
+ MICROSECOND(TS1)) AS MS1
,BIGINT(DAYS(TS2) * 86400000000
+ MIDNIGHT_SECONDS(TS2) * 1000000
+ MICROSECOND(TS2)) AS MS2
FROM (SELECT TIMESTAMP(TS1) AS TS1
,TIMESTAMP(TS2) AS TS2
FROM TEMP1
)AS TEMP2
)AS TEMP3;

IF you can determine the time difference for a particular group of columns from a certain location, couldn't you issue
updates where ts_value = ts_value +- X Hours? depending?
Or at least use that in your calculation?

fv