Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / March 2006

Tip: Looking for answers? Try searching our database.

Timestamp equivalent in DB2

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Baski - 22 Mar 2006 07:14 GMT
Hi,

In MS-SQL there is a datatype called timestamp, which generates and
stores a value for each row whenever a row is updated and  is unique
throughout the database.

I am migrating from MS-SQL to DB2. So is there any equivalent to this
concept in DB2, i.e can DB2 generate a value when ever a row is
updated, which is unique in the database????

I am aware of the datatype 'Timestamp' in DB2 which stores data and
time and I am not refering to that here.

Thanks in anticipation.
Baski.
Serge Rielau - 22 Mar 2006 14:04 GMT
> Hi,
>
[quoted text clipped - 8 lines]
> I am aware of the datatype 'Timestamp' in DB2 which stores data and
> time and I am not refering to that here.
Add a column TSTAMP CHAR(13) FOR BIT DATA to your table.
Then
CREATE TRIGGER stamp BEFORE UPDATE ON T REFERENCING NEW AS n
FOR EACH ROW
SET N.TSTAMP = GENERATE_UNIQUE();

CREATE TRIGGER stamp BEFORE INSERT ON T REFERENCING NEW AS n
FOR EACH ROW
SET n.TSTAMP = GENERATE_UNIQUE();

If you update your result set you can use MERGE:

MERGE INTO T USING (VALUES (1, 'hello', '<stamp1>), (2, 'world',
'<stamp2>')) AS S(pk, c1, tstamp)
ON T.pk = S.pk AND S.tstamp = T.tstamp
UPDATE SET c1 = S.c1
WHEN MATCHED THEN SIGNAL SQLSTATE '38000' SET MESSAGE_TEXT = 'Row has
changed!'
ELSE SIGNAL SQLSTATE '38000' SET MESSAGE_TEXT = 'Row has been deleted!'

(you can bury that logic into the BEFORE trigger as well if you wish).

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau - 22 Mar 2006 14:09 GMT
Correction:
MERGE INTO T USING (VALUES (1, 'hello', '<stamp1>), (2, 'world',
'<stamp2>')) AS S(pk, c1, tstamp)
ON T.pk = S.pk
WHEN MATCHED AND S.tstamp = T.tstamp UPDATE SET c1 = S.c1
WHEN MATCHED THEN SIGNAL SQLSTATE '38000' SET MESSAGE_TEXT = 'Row has
changed!'
ELSE SIGNAL SQLSTATE '38000' SET MESSAGE_TEXT = 'Row has been deleted!'

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.