Converting TDW timestamps to DB2 Timestamps

One of the key aspect of history tables is timestamps. But as you might have noticed, performing date operations with TDW timestamps is not straight forward. Part of the reason is that TDW uses "Candle timestamps" rather than RDBMS timestamps. This article discusses a way to convert the "Candle timestamps" into DB2 timestamps.

The problem

First, in TDW, the "Candle timestamps" are stored in a "CHAR" format rather than "timestamp" or "datetime" format. If you would like to utilize the powerful date functions of the RDBMS, you will not be able to do unless you convert the string into RDBMS timestamp format.

So, are you ready to convert the string into timestamp using DB2 Timestamp() function? Well, it is not so simple, my friend! The "Candle timestamps" are stored in a julian year like "CYYMMDDhhmmssSSS" format where C is the century (0 for 20th, 1 for 21st, etc). This non-standard format makes it difficult to convert the string into timestamps directly.

The solution

Okay, enough whining. Let us see how it can be converted to DB2 timestamps. You can use the SUBSTR() function of DB2 to extract the desired fields from the database but it will lead to a very complicated queries especially if you need to convert the timestamps at multiple places in the same query.

For a related blog article, see here

The idea here is to do something similar but define it in a DB2 User Defined Function (UDF). After defining it, we can use the UDF just like any other function.

Creating a UDF.

I am going to rush thru the UDF creation steps like the guy in marriage ceremony in Budlight superbowl commercial. See . To create an UDF, goto "Control Center" -> Tools -> "Development Center" -> Project -> New Project -> Add Connection -> User Defined Functions -> New SQL User Defined Function.

Now enter the following UDF definition in the Editor view and click Build. Make sure to replace the ITMUSER with your TDW user id.


CREATE FUNCTION ITMUSER.STR2TIMESTAMP (
STRDATE CHAR(16) )
RETURNS TIMESTAMP
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
BEGIN
ATOMIC
DECLARE RESULT TIMESTAMP ;
SET RESULT = TIMESTAMP_FORMAT(
'20' || SUBSTR(STRDATE,2,2) || '-' ||
SUBSTR(STRDATE,4,2) || '-' ||
SUBSTR(STRDATE,6,2) || ' ' ||
SUBSTR(STRDATE,8,2) || ':' ||
SUBSTR(STRDATE,10,2) || ':' ||
SUBSTR(STRDATE,12,2), 'YYYY-MM-DD HH24:MI:SS');
RETURN RESULT;
END



Using the UDF in SQL queries

Once the UDF is defined, converting the "Candle Timestamps" to DB2 is very easy. The following shows an example.


SELECT "Server_Name", ITMUSER.STR2TIMESTAMP(A."Timestamp"), "%_Disk_Used" From ""NT_Logical_Disk" A

Hope you find this useful.