SEO

#1 (**permalink**) 02-27-2008, 06:28 AM

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.

#2 (**permalink**) 02-27-2008, 06:28 AM

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

#3 (**permalink**) 02-27-2008, 06:28 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Timestamp equivalent in DB2