IBM Support

How are CREATETS / MODIFYTS fields calculated before INSERT / UPDATE SQL statements?

Troubleshooting


Problem

When checking SQLDEBUG logs, there can be a time difference between the timestamp of the actual log statement and the timestamp that is inserted / updated by a SQL. This document explains how Sterling Order Management calculates these timestamps and where the difference is coming from.

Cause

The difference originates from the time setting on the Operating System level of the application server's and database's environments.

Diagnosing The Problem

A possible time difference can be seen once SQLDEBUG trace is enabled:


2014-02-26 13:31:29,979:SQLDEBUG:Thread-13_MyServer_0: Executing sql [PREPARED STATEMENT - INSERT /*YANTRA*/ INTO YFS_TASK_Q(TASK_Q_KEY,TRANSACTION_KEY,DATA_KEY,DATA_TYPE,AVAILABLE_DATE,HOLD_FLAG,LOCKID,CREATETS,MODIFYTS,CREATEUSERID,MODIFYUSERID,CREATEPROGID,MODIFYPROGID) values ( '20140226133417401775215','SCHEDULE.0001','20140226133416401775167','OrderHeaderKey','2014-02-26T13:34:16','N',0,'2014-02-26T13:34:16','2014-02-26T13:34:16','MyServer','MyServer','MyServer','MyServer' ) : YFCEntityDBHome


Note that the time of the log statement is 2014-02-26 13:31:29,979, but CREATETS and MODIFYTS are both calculated as 2014-02-26T13:34:16. There is a difference of nearly 3 minutes, although both application server and database are in the same timezone.

Resolving The Problem

Before inserting / updating fields like CREATETS / MODIFYTS, Sterling will do a conversion from the time of the application server to the time of the database. To do so, a SQL is issued when connecting to the database to obtain the current timestamp.

Oracle: "SELECT /* YANTRA */ SYSDATE FROM DUAL"

DB2: "select CURRENT TIMESTAMP from SYSIBM.SYSDUMMY1"

Then the current time of the application is obtained using System.currentTimeMillis(). The difference is calculated as applicationTime-dbTime. YCPConnholder will print the difference in milliseconds if VERBOSE trace is enabled:



2014-02-26 13:31:30,169:VERBOSE:Thread-13_MyServer_0: DBDiff -166272 [system] : YCPConnHolder

In this example, DBDiff -166272 means that the database is nearly 3 minutes ahead of time (166272 ms / 1000 / 60 = 2,7 min). This difference is then used to calculate CREATETS / MODIFYTS as System.currentTimeMillis()-dbdiff. Whereas, log4j only uses the current timestamp of the application to print the log statement.

It should be taken care of the times that are set at the Operating System level of both the servers. They should be adjusted to be in sync to avoid this kind of confusion.

[{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Documentation","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB77","label":"Automation Platform"}}]

Document Information

Modified date:
30 June 2025

UID

swg21671327

Page Feedback