How can I initialize a timestamp column on an INSERT record so that it will store and display the actual microsecond values instead of defaulting to 00.000000?

Document ID : KB000011270
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

When a row is inserted and then displayed, the timestamp field is populated down to the second as follows:

DB2_TIMESTAMP_DZ
2017-03-04-14.59.00.000000

Question:

How can I initialize a timestamp column on an INSERT record so that it will store and display the actual microsecond values instead of defaulting to 00.000000?

Environment:
z/os
Answer:

There is a parameter that can be added to the RCEDIT member of the highlevel.CDBAPARM dataset. The parameter is MICROSEC (Y) and can be included in the RC/Edit-Browse Default Values section of the RCEDIT member. This parameter will specify whether you want microseconds displayed in the default timestamp.

With the value set to Y, the actual microsecond characters will display if the input was the result of an insert of a new data row where a timestamp column is inserted and the default system timestamp is used to populate the column in the row.

For example: 2017-03-04-14.59.01.123456

This not only decides on the DISPLAY but also what actual data is stored on the table that will be displayed. This parameter will have no impact on existing rows.

Using the EP function on the main menu:

EP Edit Parmlib Members

Select

S RCEDIT - RC/Update

Enter Y or N here:

Actual microseconds for timestamp> Y

Enter to save the change.

In hlq.CDBAPARM(RCEDIT)

you will see:

MICROSEC (Y)             /* MICROSECONDS FOR TIMESTAMP  (Y OR N)    */
                         /*    Y - USE ACTUAL MIRCOSECONDS FOR      */
                         /*        DEFAULT TIMESTAMPS               */
                         /*    N - REPLACE MICROSECONDS WITH ZEROES */
                         /*        FOR DEFAULT TIMESTAMPS           */

 

Additional Information:

Information for this option can be found in the CA Database Management Solutions for DB2 for z/OS, Installing section.

Customize Product Parmlib Members