By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

Timestamp equivalent in DB2

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.