Hello,

I am working with DB2 v9, and I am looking to create a TIMESTAMP
column in one of my tables that will automatically insert the current
date/time when I add a new row. Is there a way to do this by
specifying a generation formula, or must I create a trigger? Thank
you.

Jeff Shantz wrote:
> I am working with DB2 v9, and I am looking to create a TIMESTAMP
> column in one of my tables that will automatically insert the current
> date/time when I add a new row. Is there a way to do this by
> specifying a generation formula, or must I create a trigger? Thank
> you.
The easiest would be to use a default:

CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

Or create a before trigger:
CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS n FOR EACH ROW
SET c1 = CURRENT TIMESTAMP

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
>> I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
> The easiest would be to use a default:
>
> CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)

By the way, what about removing "CURRENT TIMESTAMP" from the above ?

| CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)

The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value : "For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..
--
Konstantin Andreev.

Konstantin Andreev wrote:
> Serge Rielau wrote:

> The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
> "For existing rows, a date corresponding to January 1, 0001, and a time
> corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What
> this crypto means ? My experimentation did not clear it..

Documentation is correct. Here is an example:

---------------------------- Example start -----------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT x.x.x
SQL authorization ID = xxxxxxxx
Local database alias = SAMPLE


C:\>db2 create table t (c1 int with default)
DB20000I The SQL command completed successfully.


C:\>db2 insert into t values default
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1
-----------
0

1 record(s) selected.


C:\>db2 alter table t add column c2 timestamp with default
DB20000I The SQL command completed successfully.


C:\>db2 insert into t values (1,default)
DB20000I The SQL command completed successfully.

C:\>db2 select * from t

C1 C2
----------- --------------------------
0 0001-01-01-00.00.00.000000
1 2007-06-07-18.37.02.993000

2 record(s) selected.

---------------------------- Example end -----------------------------



Perhaps I should visit you and we can exchange experiences?


Jan M. Nelken

Jan M. Nelken wrote:

> Konstantin Andreev wrote:

>> The documentation ( DB2 V9 IC/SQL/ALTER TABLE ) states default value :
>> "For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds." What this crypto means ? My experimentation did not clear it..
>
> Documentation is correct.

Correctness does not mean neither lucidity nor completeness

> C:\>db2 create table t (c1 int with default)
> C:\>db2 insert into t values default
> C:\>db2 alter table t add column c2 timestamp with default
> C:\>db2 insert into t values (1,default)
> C:\>db2 select * from t
>
> C1 C2
> ----------- --------------------------
> 0 0001-01-01-00.00.00.000000
> 1 2007-06-07-18.37.02.993000


Gotcha! The statement in question is about *added* columns, not about the columns the table was created with...
Thank you again, Jan.
--
Konstantin Andreev.

On 8 Juni, 00:06, Konstantin Andreev <feobalcaip...@datatech.ru>
wrote:
> Serge Rielau wrote:
> >> I am working with DB2 v9, and I am looking to create a TIMESTAMP column ...
> > The easiest would be to use a default:
>
> > CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)
>
> By the way, what about removing "CURRENT TIMESTAMP" from the above ?
>
> | CREATE TABLE T(c1 TIMESTAMP WITH DEFAULT)
>

[...]

Minor nitpick on the syntax used.

CREATE TABLE T(c1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

is standard compliant

/Lennart

[...]