It is possible to create a stored procedure that will generate a sequence number, however, is it possible for a stored procedure to renumber the sequence?

For example, say there are ten rows in the table. Row nbr 5 is deleted. Row nbr 6 becomes row nbr 5, row nbr 7 becomes row nbr 6, row nbr 8 becomes row nbr 7, etc.

This can be done thru programming but if possible I would rather have the database perform the function. I am using DB2 V7.1 on an MVS os/390.

I don't quite understand what you want to achieve. Could you possibly explain this in an example?

A sequence is not tied to a table. Therefore, you could get one number from the sequence for table A, another for table B, yet another as input for a UDF, etc. As a consequence, you get gaps in the sequence numbers used for table A only. Maybe IDENTITY columns are a better way to get closer to what you want to do.

Another important aspect is that a transaction may be rolled back. The sequence numbers acquired by that transaction are not reused, of course. So you have even more gaps.

Lastly, if you want to renumber data in a table, you are aware that this would potentially imply updating millions of rows just due to a DELETE on a single row? What should happen in case of dependent rows in other tables? Cascading updates? If the answers would by "yes", you should reconsider your design decisions.

Usually, there is no need to have contiguous sequence numbers in the first place and any application should live with gaps. I would be very intrigued if you have a scenario where you really need that.

In case,you want to generate the row number in a table,you can use the OLAP function(ROW_NUMBER()).

--Naga