Re: (Fwd) Re: Any Oracle 9 users? A test please...

Tom Lane wrote:

>
>
>Has anyone done the corresponding experiments on the other DBMSes to
>identify exactly when they allow CURRENT_TIMESTAMP to advance ?
>

I have Db2 on hand and examined CURRENT TIMESTAMP in an sql procedure.
(IBM have implemented it without the "_" ....)

The short of it is that CURRENT TIMESTAMP is the not frozen to the
transaction start,
but reflects time movement within the transaction.

Note that "db2 +c" is equivalent to issueing BEGIN in Pg,
and the command line tool (db2) keeps (the same) connection open until
the TERMINATE is issued :

$ cat stamp.sql

create procedure stamp()
language sql
begin
insert into test values(1,current timestamp);
insert into test values(2,current timestamp);
insert into test values(3,current timestamp);
insert into test values(4,current timestamp);
insert into test values(5,current timestamp);
insert into test values(6,current timestamp);
insert into test values(7,current timestamp);
insert into test values(8,current timestamp);
insert into test values(9,current timestamp);
end
@

$ db2 connect to dss
Database Connection Information

Database server = DB2/LINUX 7.2.3
SQL authorization ID = DB2
Local database alias = DSS

$ db2 -td@ -f stamp.sql
DB20000I The SQL command completed successfully.

$ db2 +c
db2 => call stamp();

"STAMP" RETURN_STATUS: "0"

db2 => commit;

DB20000I The SQL command completed successfully.

db2 => select * from test;

ID VAL
----------- --------------------------
1 2002-10-03-19.35.16.286019
2 2002-10-03-19.35.16.286903
3 2002-10-03-19.35.16.287549
4 2002-10-03-19.35.16.288235
5 2002-10-03-19.35.16.288925
6 2002-10-03-19.35.16.289571
7 2002-10-03-19.35.16.290209
8 2002-10-03-19.35.16.290884
9 2002-10-03-19.35.16.291522

9 record(s) selected.

db2 => terminate;

regards

Mark