Track Records on Database using DB2

For Database Administrators and Developers, this is a common practice. Sometimes we would love to track who has accessed a particular record from the users’ perspective based on ID and time.

How can we track it then? This is usually the job for the developer to code into their SQL query to do a simple INSERT or a simple UPDATE. It is known to anyone who wants to start coding, the best way to track is to get the user ID of the specific user and the date and time of the transaction was done. For the simplicity and best way to track date and time, the usage of TIMESTAMP would be the best way.

If you’ve just joined a company and they have a field name DATE_UPDATED and values 1/7/2011 2:39:35 PM, now how do you update this?

Yes, just by using TIMESTAMP.

SQL DB2

Using DB2 as the platform to perform my SQL queries, this is how a TIMESTAMP could be updated from the previous TIMESTAMP.

UPDATE [tablename] SET DATE_UPDATED = (CURRENT TIMESTAMP);

Make sure to always use CURRENT TIMESTAMP in order to get the latest date and time. Now you can track when was the last transaction done. This only applies for developers that are still using native SQL to update their database. Those using Hibernatewould not have a big problem with this.

One Response

  1. danielctw February 10, 2011

Leave a Reply to danielctw Cancel reply