467,185 Members | 1,226 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

Timestamp equivalent in DB2

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.

Mar 22 '06 #1
  • viewed: 2017
Share:
2 Replies
Baski wrote:
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.

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 22 '06 #2
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!'
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by David Greenberg | last post: by
7 posts views Thread by Ross | last post: by
5 posts views Thread by Vai2000 | last post: by
3 posts views Thread by Dean Slindee | last post: by
6 posts views Thread by Scott Nixon | last post: by
7 posts views Thread by JJ | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.