Manage Learn to apply best practices and optimize your operations.

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.

*************** 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).

PTIM: 00.00.00
PDAT: 0001-01-01
PTST: 0001-01-01-

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.


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.

Dig Deeper on Data backup, storage and retrieval on iSeries

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.