Timestamp equivalent in DB2

[Baski]

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]

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

[Serge Rielau]

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!'