Ask the Expert

Working with date and timestamp in DB2

Is it possible to DB2/400 to automatically keep track of the timestamp when the record is inserted or updated? I created the following PF.

 
 FMT PF
.....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++
*************** Beginning of data *************************************
0001.00      A          R PETEST

0002.00      A            PEER           3A

0003.00      A            PEEN          10A

0004.00      A            PTIM            T

0005.00      A            PDAT            L         DATFMT(*ISO)

0006.00      A            PTST            Z

****************** End of data ************************* 

When the record is written to the PF, timestamp, date and time are initialized as follows. I read the documentation, the time/date and timestamp information should be initialized with current date and time. When I've updated this record with SQL, timestamp is not updating with the current date and time. I wonder if this is even possible with DB2/400 to keep track of time stamp automatically (I mean with out pushing time stamp value by some program).

PEER: AAA
PEEN: AAA
PTIM: 00.00.00
PDAT: 0001-01-01
PTST: 0001-01-01-00.00.00.000000


If the ALWNULL & DFT keywords are left off of a Date or Timestamp field, then DB2 UDB for iSeries will assign the current date and time as the default value when a record is inserted. Updates to a record with a date or timestamp field does not change the value of these fields unless the Write or Update operation has explicitly changed the value of the date or timestamp field. The net is that DB2 UDB for iSeries can only automatically timestamp new records inserted into the database.

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.


This was first published in February 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: