Request a Consultation

CheckPoint Consulting Blog

Oracle Data Integrator (ODI) offers many benefits to users extracting, loading, and transforming data in their data intelligence systems. It offers a practical GUI to the user so that they can effectively manage this data. A small setback, however, appears during troubleshooting: mismatched timestamps.

When a job is started in ODI Studio, a session is created under the ‘Operator’ to allow for easy monitoring of the process. Each step in the job is listed as it completes, along with a timestamp. This particular timestamp is pulled directly from the database, as oppose to using the current time of the server hosting ODI, or the time zone of the user facilitating the work. This can pose a problem when the time zone of the database and the application server are drastically different. For example, a database hosted in UTC (+00:00) and an application server hosted in CST (-06:00). Trouble shooting at this stage is still doable, but converting timestamps to match other events in the EPM environment can quickly become tiresome.

Altering the configuration files for ODI can sometimes compound the issue. Altering the time zone for ODI inside the config file will indeed change the time inside of ODI Studio, but only for any scheduling done; Timestamps for the session events will still be pulled directly from the database level. There is no way to force these timestamps to sync with the server instead of the database. In fact, Oracle has stated that this expected behavior for the application. If you are faced with this issue, the only work around is to create a custom script at the database level that converts all timestamps for that session to a different time zone. The downside to this is, Oracle will not write any custom scripts or support applications that use this method.