I am curious to know what kind of application would need this type of
numbering scheme? The net result of the updates that would be required
is a guaranteed performance problem if there is any volume of data. A
better design method might be to create a blob or lvarchar field that
contains delimited data that is easily parsed by any application ...
letting the application display the desired numbering scheme.

Sincerely,

Scott A. Heiser


-----Original Message-----
From: Peters Gordon
[mailto:db2udbdba-ezmlmshield-x88824090.[Email address protected]
Sent: Tuesday, May 31, 2005 2:57 PM
To: LazyDBA Discussion
Subject: RE: Auto Generated Keys

Ruben,

You can construct a trigger that will get the results you desire, but
the
Key column must be GENERATED BY DEFAULT AS IDENTITY so that it can be
changed with an update statement. The trigger will look something like
this:

CREATE TRIGGER RUBEN.MYTRIG
AFTER DELETE ON RUBEN.MYTABLE
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (1=1)
UPDATE RUBEN.MYTABLE SET KEY = KEY - 1 WHERE KEY > O.KEY;

The number of updates that will occur on the table will depend on what
row
gets deleted. In a million row table, if you would delete row 1, you
can
guess how many rows will eventually get updated.

Regards,
Gordon

-----Original Message-----
From: Ruben Prakash D
[mailto:db2udbdba-ezmlmshield-x86770929.[Email address protected]
Sent: Sunday, May 29, 2005 8:54 AM
To: LazyDBA Discussion
Subject: RE: Auto Generated Keys

Rick,

Since it is a surrogate key I thought of having in an order. I accept it
has
adverse effect if I delete a row it will cause cascade update to every
key
row coming after the deleted row in the table. But still I want know is
there any way to do that?

Will it be possible using triggers?

Regards,
Ruben

-----Original Message-----
From: McClendon Rick
[mailto:db2udbdba-ezmlmshield-x53248595.[Email address protected]
Sent: Saturday, May 28, 2005 9:57 PM
To: LazyDBA Discussion
Subject: RE: Auto Generated Keys

Even if you invent a way to do this I don't think you would like the
effect
because the delete of one row, KEY # 1 for example, would cause a
cascade
update to every key row in the table.

My question - can't the data remain "Key 1,3,4,5..." when #2 is deleted?

It is still unique and still in order.

-----Original Message-----
From: Ruben Prakash D
[mailto:db2udbdba-ezmlmshield-x69088801.[Email address protected]
Sent: Saturday, May 28, 2005 8:47 AM
To: LazyDBA Discussion
Subject: Auto Generated Keys

I want to create a table with a surrogate key (auto incremented) as a
primary key. Is there any function that is available in DB2 so that when
I
delete a row from the table it will adjust the key? For example if my
table
contains rows in a order



Key

---

1

2

3



If the row #2 is deleted then order should be



Key

---

1

2



Will it be possible in DB2?



Regards
Ruben






---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page