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.
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.
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
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) )
NO EXTERNAL ACTION
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');
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.