RE: SQL insert using "current timestamp" across machines

I'll have to agree with Vern's suggestion to run NTP. It seems like one of the basics of a secure environment. Otherwise timestamps aren't real meaningful. And logically, it only makes sense to use the timestamp on the local machine where the row is being insert. Otherwise (again) the timestamp becomes meaningless.

We run the SNTP server function on our system. We have it set to star the server and the client when TCP starts and we use TIME.NIST.GOV for the time server and ntp-d.nist.gov as a backup. I would probably set M1 as your time server for the other machines. (We use our IBM i as the time server for our entire network.) Then any forensic audit or even a simple investigation becomes much more doable. You'll have a single standard of time.

Michael Quigley
Computer Services
The Way international
www.TheWay.org

-----Original Message-----
message: 3
date: Thu, 20 Jul 2023 06:45:40 -0700
from: VERNON HAMBERG Owner via MIDRANGE-L
<midrange-l@xxxxxxxxxxxxxxxxxx>
subject: 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.

--