How timestamps are represented in WebSphere Business Monitor

Because WebSphere® Business Monitor uses multiple databases in its data services component, it is important to understand how timestamps are transformed by the system between these databases during the databases replication.

There are many possible configurations for WebSphere Business Monitor topologies. You need to know how to set up these topologies to achieve the results you want for timestamps. The servers involved are the server containing the Runtime Database, the server containing the Historical database, and the server on which the WebSphere Portal is installed, which is the location of DB2® Alphablox. If the result is to report all times in Greenwich mean time (GMT), then the Runtime database, Historical database, and WebSphere Portal machines must all have their system clocks set to GMT. All timestamps and dimensional reporting will then be in GMT.

If you want to report all times in Eastern Standard Time (EST), then all servers should be set to EST. If you want clients to be in different time zones, the recommended setting is to set all servers to GMT. If the client settings through WebSphere Portal are not GMT, then there will be some discrepancies between reports that use timestamps (these will be relative to the settings on WebSphere Portal) and the reports that do dimensional analysis. These will be GMT based. The technical details follow.

The timestamp columns in the State database are stored as long Java™ data types (serialized Java timestamps based on GMT). As these timestamps move through the three databases, they are converted to actual DB2 timestamps from these Java longs during the ETL steps between the State and Runtime databases. This change is done using a Java-based UDF that converts the long to a timestamp and returns the timestamp datatype to DB2. At this point, the timestamps are converted, based on the clock settings of the server where the Runtime database is hosted. If this system clock is set to GMT, these timestamps are converted to GMT; otherwise, they will be converted, based on the time-zone offset and daylight-saving-time offsets of the system clock. They are stored in DB2 relative to that time zone and not in GMT. DB2 provides special registers to retrieve the time-zone offset and apply it to timestamps.

Timestamps that are moved into the Historical database are not converted, so the Historical database will store the timestamps in the same time zone as the Runtime system. This means that the Runtime and Historical database servers must use the same time-zone settings. During the ETL, these timestamps are compared to the DIM_TIME table. The DIM_TIME table by itself has no time zone, but when coupled with a database server, it will use the time-zone settings of the server. Therefore, all mappings to the DIM_TIME table are done assuming the DIM_TIME and the timestamp being looked up are relative to the time zone of the Historical database server, which may not be GMT.

The server hosting the WebSphere Portal also needs to be in the same time zone that the Runtime and Historical database servers are in. At present, when the dashboards query the timestamps columns directly (not using the time dimension), the current architecture assumes that the time zone of the Runtime and Historical databases are the same as the dashboard server's. The timestamps are converted back into Java timestamps, and the WebSphere Portal assumes that the database server timestamps are in the same time zone as itself, and will convert the timestamps back to GMT, based on these settings. A client machine could have different time zone settings. No problems will arise as long as the WebSphere Portal converted the timestamps to GMT correctly. The only time it converts correctly occurs when the WebSphere Portal and the Runtime and Historical servers have the same time-zone settings

The last item is not so straightforward because of the design of the TIME DIMENSION in WebSphere Business Monitor. During the ETL phase, there are several links to the TIME DIMENSION for DIMENSIONAL analysis. The individual doing the analysis must recognize that, regardless of the client time-zone settings, these reports are based on the time zone of the Runtime and Historical database servers, where the conversion from GMT to the local time of these servers occurs. Even though the granularity of the TIME DIMENSION is only day, a difference of time zone could change the DAY on which a particular record occurred.


Copyright IBM Corporation 2005, 2006. All Rights Reserved.