Re: SQL insert using "current timestamp" across machines

I believe it is a good idea and even recommended to set up a (simple) network time protocol client on the i - either to a server locally or a public one. NTP client is available since 7.3, it seems, according to Knowledge Base. That is the more robust version - SNTP (S for simple) can be fine if you requirements are not so stringent. This would keep you times very close on all your IBM i servers, and in sync generally with Windows systems, too, especially if you have Active Directory in your enterprise.


Cheers
Vern


On Thu, 20 Jul, 2023 at 8:32 AM, smith5646midrange@xxxxxxxxx <smith5646midrange@xxxxxxxxx> wrote:


To: midrange rpg list; 'midrange systems technical discussion'

I stumbled over something weird so I wanted to pass it along for you folks
to put in the back of your mind and maybe remember if it ever shows up in
something you are dealing with.

Our system has files on one machine (M1) with SQL aliases that connect to
them from other machines (M2 and M3). These work very similar to DDMFs
except you can use SQL to update the aliases. When a program does an SQL
"insert into." into one of the aliases on M2 or M3, the data is inserted
into the file on M1 as it does with a normal write to a DDMF.

Here is the weird part. When the program on M2 does an "insert into." and
one of the values specified is "current timestamp", the record that is
inserted uses the M1 "current timestamp", not the M2 "current timestamp".
How I found this is the system times between M1 and M2 are off by about a
minute and we have records with timestamps when nobody was logged on to M2
with the ID that wrote the records. If I subtract about 1 minute from the
timestamp in the record, it would match the user login time on M2.

The workaround that I have found is to create a timestamp variable in the
RPG, load it with the current timestamp, and use the variable in the insert
statement instead of "current timestamp". Obviously this will not work if
the systems are in different time zones so if anyone has a better solution
(other than keep your system's times in sync) I would love to know about it.